Oracle PL/SQL/PL SQL/Label

A labeled block.

   
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> <<l_InsertIntoTemp>>
  2  DECLARE
  3    v_Num1      NUMBER := 1;
  4    v_Num2      NUMBER := 2;
  5    v_String1   VARCHAR2(50) := "Hello World!";
  6    v_String2   VARCHAR2(50) := "-- This message brought to you by PL/SQL!";
  7    v_OutputStr VARCHAR2(50);
  8  BEGIN
  9    INSERT INTO MyTable (num_col, char_col)
 10      VALUES (v_Num1, v_String1);
 11    INSERT INTO MyTable (num_col, char_col)
 12      VALUES (v_Num2, v_String2);
 13
 14    SELECT char_col
 15      INTO v_OutputStr
 16     FROM MyTable
 17     WHERE num_col = v_Num1;
 18    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 19
 20    SELECT char_col
 21      INTO v_OutputStr
 22     FROM MyTable
 23     WHERE num_col = v_Num2;
 24    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 25
 26    ROLLBACK;
 27
 28  END l_InsertIntoTemp;
 29  /
Hello World!
-- This message brought to you by PL/SQL!
PL/SQL procedure successfully completed.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

Block with label

   
SQL> <<l_outer_block>>
  2  DECLARE
  3     visibleValue VARCHAR2(30);
  4     hiddenValue VARCHAR2(30);
  5  BEGIN
  6     visibleValue := "visibleValue";
  7     hiddenValue := "hiddenValue";
  8
  9     DBMS_OUTPUT.PUT_LINE("OUTER BLOCK");
 10     DBMS_OUTPUT.PUT_LINE(visibleValue);
 11     DBMS_OUTPUT.PUT_LINE(hiddenValue);
 12
 13     DECLARE
 14        hiddenValue NUMBER(10);
 15     BEGIN
 16        DBMS_OUTPUT.PUT_LINE("INNER BLOCK");
 17        l_outer_block.hiddenValue := "inner hiddenValue";
 18        DBMS_OUTPUT.PUT_LINE(l_outer_block.hiddenValue);
 19     EXCEPTION
 20        WHEN OTHERS
 21        THEN
 22           DBMS_OUTPUT.PUT_LINE("hiddenValue of type VARCHAR2 was...hidden");
 23     END;
 24  END;
 25  /
OUTER BLOCK
visibleValue
hiddenValue
INNER BLOCK
inner hiddenValue
PL/SQL procedure successfully completed.
SQL>

Changing labeled loop execution with EXIT statements

  
SQL>
SQL> -- Changing labeled loop execution with EXIT statements.
SQL> BEGIN
  2        <<outerloop>>
  3        FOR i IN 1..2 LOOP
  4             <<innerloop>>
  5             FOR j IN 1..4 LOOP
  6                  DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " || i || " Inner Loop counter is " || j);
  7                       EXIT outerloop WHEN j = 3;
  8             END LOOP innerloop;
  9        END LOOP outerloop;
 10  END;
 11   /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
PL/SQL procedure successfully completed.
SQL>

Loop with a Label

   
SQL> BEGIN
  2     <<l_For_Loop>>
  3     FOR v_count IN 1 .. 20
  4     LOOP
  5        DBMS_OUTPUT.PUT_LINE("Iteration: "||v_count);
  6     END LOOP l_For_Loop;
  7  END;
  8  /
Iteration: 1
Iteration: 2
Iteration: 3
Iteration: 4
Iteration: 5
Iteration: 6
Iteration: 7
Iteration: 8
Iteration: 9
Iteration: 10
Iteration: 11
Iteration: 12
Iteration: 13
Iteration: 14
Iteration: 15
Iteration: 16
Iteration: 17
Iteration: 18
Iteration: 19
Iteration: 20
PL/SQL procedure successfully completed.
SQL>

Use label to mark outer loop and inner loop

   
SQL>
SQL>   BEGIN
  2          <<outerloop>>
  3          FOR v_outerloopcounter IN 1..2 LOOP
  4               <<innerloop>>
  5               FOR v_innerloopcounter IN 1..4 LOOP
  6                    DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " ||
  7                         v_outerloopcounter ||
  8                         " Inner Loop counter is " || v_innerloopcounter);
  9               END LOOP innerloop;
 10         END LOOP outerloop;
 11    END;
 12    /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

Using labels with loops

  
SQL>
SQL> -- Using labels with loops.
SQL> BEGIN
  2        <<outerloop>>
  3        FOR i IN 1..2 LOOP
  4             <<innerloop>>
  5             FOR j IN 1..4 LOOP
  6                  DBMS_OUTPUT.PUT_LINE("Outer Loop counter is " || i ||
  7                       " Inner Loop counter is " || j);
  8             END LOOP innerloop;
  9        END LOOP outerloop;
 10  END;
 11  /
Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3
Outer Loop counter is 1 Inner Loop counter is 4
Outer Loop counter is 2 Inner Loop counter is 1
Outer Loop counter is 2 Inner Loop counter is 2
Outer Loop counter is 2 Inner Loop counter is 3
Outer Loop counter is 2 Inner Loop counter is 4
PL/SQL procedure successfully completed.
SQL>
SQL>