This discussion is archived
3 Replies Latest reply: May 9, 2012 12:17 PM by BillyVerreynne RSS

Looping through rows of a table without using PL SQL features

915810 Newbie
Currently Being Moderated
Hi,
Is there any way to loop through or basically navigate through the values of a column(s) in SQL without using
PL/SQL features lik cursors.

Basically any analytical function or any other way ??

Its just a thought .

Any help ??

Regards,
Sree
  • 1. Re: Looping through rows of a table without using PL SQL features
    Centinul Guru
    Currently Being Moderated
    user12033188 wrote:
    Hi,
    Is there any way to loop through or basically navigate through the values of a column(s) in SQL without using
    PL/SQL features lik cursors.
    It depends on your requirements. Can you please provide an example of what you are trying to accomplish? See

    {message:id=9360002}
  • 2. Re: Looping through rows of a table without using PL SQL features
    chris227 Guru
    Currently Being Moderated
    i think yes
    loop => model clause, recursive subquery, connect by
    navigate => model clause, analytical lag + lead

    Edited by: chris227 on 09.05.2012 08:31
  • 3. Re: Looping through rows of a table without using PL SQL features
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user12033188 wrote:

    Is there any way to loop through or basically navigate through the values of a column(s) in SQL without using
    PL/SQL features lik cursors.
    Cursors is not a PL/SQL concept. It is a SQL concept.

    To execute a SQL statement, requires the SQL engine to execute a SQL cursor. It is a fundamental construct in the SQL engine. There is no such concept as not using a cursor and executing SQL. You have one choice only. Cursors. There is no 2nd choice.

    What you are referring to is row-by-row fetching the output of a cursor - using a PL/SQL loop. Yes, that approach is not ideal ito performance and scalability.

    Whether PL/SQL is needed to process the output of a SQL cursor, or whether that processing complexity can be done entirely in the SQL itself depends on the requirements. What needs to be done? How complex is it?

    Most times PL/SQL is not needed - as the SQL language is a powerful and flexible data processing language. But often the output of such a SQL cursor has to be used to do "something" - and that is typically where PL/SQL is used. E.g. write the cursor output as a HTML report table into an e-mail and mail that to a number of users.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points