Oracle PL/SQL - Exiting from Nested Loops via Label

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Statement
  4. Basic LOOP Statement

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--   ww  w  .  j a v  a 2s.c om
  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