Lutz Haake | Freelance Oracle Consultant

Using the WITH CLAUSE, loops and even nested loops can be imple­mented using plain SQL.

To illus­trate the idea, lets have a look at how you select a sequence 1…n of numbers in SQL:

select rownum from dual connect by level <= n;

To select all integers from 1 to 3:

select rownum n1 from dual connect by level <= 3;
        N1
----------
         1 
         2 
         3 

The struc­trure of a sql statement using a WHITH CLAUSE is like this:

WITH <query block> AS (<outer query>) <inner query>;

Both queries need to be joined in the inner query to make this work. Lets select a sequence from 1 to 3 in the outer sql (n1) and also in the inner sql (n2) and see what happens:

WITH q1 AS
  (SELECT rownum n1 FROM dual CONNECT BY level <= 3
  )
SELECT q1.n1,
  q2.n2
FROM
  (SELECT rownum n2 FROM dual CONNECT BY level <= 3
  ) q2,
  q1;

        N1         N2
---------- ----------
         1          1 
         2          1 
         3          1 
         1          2 
         2          2 
         3          2 
         1          3 
         2          3 
         3          3 

The inner sql is executed for each row of the outer sql, the inner sql being executed first, meaning that the next row of the inner query is fetched only after the outer query has cycled through all its rows.