PL/SQL Labels

PLSQL Labels

     A PL/SQL label is a way to name a particular part of our program. Syntactically, a label has the format:

  << identifier >> 

    Where identifier is a valid PL/SQL identifier (up to 30 characters in length and starting with a letter). There is no terminator. Labels appear directly in front of the thing they're labeling, which must be an executable statement—even if it is merely the NULL statement:

 begin
     ...
     <<the_spot >>       
     NULL;    
 end;     

     Because anonymous blocks are themselves executable statements, a label can "name" an anonymous block for the duration of its execution.

For example :

 <<insert_but_ignore_duplicate>> 
 begin
    INSERT INTO  student
    VALUES (...);
 EXCEPTION
 WHEN DUP_VAL_ON_INDEX THEN
    NULL;    
 end insert_but_ignore_duplicate;   

    One reason we might label a block is to improve the readability of our code. When we give something a name, we self-document that code. We also clarify our own thinking about what that code is supposed to do, sometimes ferreting out errors in the process.
.
    Another reason to use a block label is to allow we to qualify references to elements from an enclosing block that have duplicate names in the current, nested block. Here’s a schematic example:

 <<outerblock>> 
 DECLARE
    l_counter NUMBER := 0;
 begin
   ...
   <<innerblock>>
   DECLARE
        l_counter NUMBER := 1;
   begin
        IF innerblock.l_counter = outerblock.l_counter   
        THEN
           ...
        END IF;     
   end innerblock; 
 end outerblock;   

     Without the block label, there would be no way to distinguish between the two counter variables. Again, though, a better solution would probably have been to use distinct variable names.

     A third function of labels is to serve as the target of a GOTO statement.

     Although few programs I've seen or worked on require the use of labels, there is one final use of this feature that is more significant than the previous three combined: a label can serve as a target for the EXIT statement in a nested loop. Here’s some example code:

 begin
   <<outer_loop>>
   LOOP
        LOOP
            EXIT outer_loop;  
        END LOOP;
        some_statement;
    END LOOP;
 end;   

     Without the <<outer_loop>> label, the EXIT statement would have exited only the inner loop and would have executed some_statement. But I didn’t want it to do that. So, in this case, the label provides functionality that PL/SQL does not offer in any other straight forward way.