René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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.