Loops with PL/SQL
Types of loops
Basic loop
loop
/* statements */
end loop;
While loop
while a > b loop
/* statements */
end loop
See also Iterating over collection variables.
For loop
for i in 1..1000 loop
insert into a values(i,i*2);
end loop;
for i in reverse 1..1000 loop
insert into a values(i,i*2);
end loop;
See also Iterating over collection variables.
Cursor for loop
for rec in (select col_1, col_2 from table_a) loop
/*Statements, use rec.col_1 and rec.col_2 */
end loop;
for rec in cursor_name loop
/*Statements, use rec.col_1 and rec.col_2 */
end loop;
for rec in cursor_name(cursor_param_1, cursor_param_2...) loop
/*Statements, use rec.col_1 and rec.col_2 */
end loop;
Labels
Each of the loops can be labeled:
<<label_name>>
loop
....
end loop label_name;
When a loop is labeled, the exit statement can then refer to that label:
begin
<<i_loop>> for i in 1 .. 10 loop
<<j_loop>> for j in 1 .. 10 loop
dbms_output.put(to_char(j, '999'));
exit j_loop when j=i;
end loop;
dbms_output.new_line;
end loop;
end;
/
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5
1 2 3 4 5 6
1 2 3 4 5 6 7
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9 10
exit
exit;
exit when foo > bar;
exit label_name;
exit label_name when foo > bar;
Any of these loops can be exited with the exit statement.