For loop in Oracle SQL

Question

I am new to Oracle and I am unaware about using for loop in Oracle SQL  not PL SQL    I had a requirement to increase the number by  1 in the query and execute the statements  is it possible to use a for loop in SQL

User · Answer

You will certainly be able to do that using WITH clause, or use analytic functions available in Oracle SQL.

With some effort you'd be able to get anything out of them in terms of cycles as in ordinary procedural languages. Both approaches are pretty powerful compared to ordinary SQL.

http://www.dba-oracle.com/t_with_clause.htm

http://www.orafaq.com/node/55

It requires some effort though. Don't be afraid to post a concrete example.

Using simple pseudo table DUAL helps too.

User · Answer

You are pretty confused my friend  There are no LOOPS in SQL  only in PL SQL  Here s a few examples based on existing Oracle table - copy paste to see results   -- Numeric FOR loop -- set serveroutput on -- gt  gt  do not use in TOAD -- DECLARE   k NUMBER   0  BEGIN   FOR i IN 1  10 LOOP     k   k 1      dbms output put line i       k    END LOOP  END     -- Cursor FOR loop -- set serveroutput on DECLARE    CURSOR c1 IS SELECT   FROM scott emp     i NUMBER   0  BEGIN   FOR e rec IN c1 LOOP   i   i 1      dbms output put line i  chr 9   e rec empno  chr 9   e rec ename     END LOOP  END     -- SQL example to generate 10 rows -- SELECT 1   LEVEL-1 idx   FROM dual CONNECT BY LEVEL  lt   10

[sql] For loop in Oracle SQL

Examples related to sql

Examples related to oracle