Loop

No doubt, most important features in any computer languages are condition (if statement) and loop.

In SQL, we have decode/case to do conditional check. To do a loop, before Oracle 9i, it was a no-go; now, you can have something like

SQL> SELECT rownum FROM (SELECT * FROM DUAL CONNECT BY LEVEL <= 10);

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

Simple? Ok, let's have some fun

SQL> select * from x;

C1 C2
---------- ----------
11111 1
22222 2
33333 3
44444 4


SQL> create table y (c1 number);

Table created.

SQL> insert into y
2 select a.c1 from x a,
3 (
4 select rownum rn from (select max(c2) maxlevel from x) a connect by 1=1 and level <=
5 maxlevel) b
6 where a.c2 >= rn
7 order by c1
8 /

10 rows created.

SQL> select * from y;

C1
----------
11111
22222
22222
33333
33333
33333
44444
44444
44444
44444