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.