Oracle PL/SQL/PL SQL/Label

A labeled block.

   <source lang="sql">
  

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>

 </source>
   
  

Block with label

   <source lang="sql">
  

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>

 </source>
   
  

Changing labeled loop execution with EXIT statements

   <source lang="sql">
 

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>

 </source>
   
  

Loop with a Label

   <source lang="sql">
  

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>

 </source>
   
  

Use label to mark outer loop and inner loop

   <source lang="sql">
  

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> --

 </source>
   
  

Using labels with loops

   <source lang="sql">
 

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>

 </source>