3 Replies Latest reply: May 9, 2012 2:17 PM by Billy~Verreynne RSS

    Looping through rows of a table without using PL SQL features

    915810
      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
          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
            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
              Billy~Verreynne
              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.