Oracle PL/SQL - Exiting from Nested Loops via Label

Introduction

To exit an outer loop from an EXIT statement in an inner loop.

To do this, name the loop by using a label.

The labels are enclosed in << and >> as in <<myLabel>>.

Demo

SQL>
SQL> declare--  w  ww.  j  a  v  a2 s  .co  m
  2       v_ind_nr     NUMBER;
  3       v_current_nr NUMBER;
  4       v_max_printed_nr NUMBER :=10;
  5       v_printed_nr NUMBER:=0;
  6  begin
  7       v_current_nr:=0; -- should not be null!
  8       <<Main>>
  9       loop
 10             v_ind_nr:=0; -- reset each time
 11             <<Inner>>
 12             loop
 13                 v_ind_nr:=v_ind_nr+1;
 14                 DBMS_OUTPUT.put_line(v_current_nr);
 15                 v_printed_nr:=v_printed_nr+1;
 16                 exit Main when v_printed_nr = v_max_printed_nr;
 17                 exit when v_ind_nr=4;
 18             end loop Inner;
 19             v_current_nr:=v_current_nr+5;
 20             exit when v_current_nr=25;
 21       end loop Main;
 22  end;
 23  /
0
0
0
0
5
5
5
5
10
10

PL/SQL procedure successfully completed.

SQL>

Here, both loops are marked with the labels <<Main>> and <<Inner>>.

These lines must immediately precede their respective loops.

You can explicitly reference the external loop via its label to indicate which loop to exit from.

Related Topic