Oracle PL/SQL Programming, 5th Edition

Labels

A PL/SQL label is a way to name a particular part of your 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, as discussed earlier in the section Identifiers). 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;

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_dups>>
BEGIN
   INSERT INTO catalog
   VALUES (...);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      NULL;
END insert_but_ignore_dups;

One reason you might label a block is to improve the readability of your code. When you give something a name, you self-document that code. You also clarify your 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 you 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
   counter INTEGER := 0;
BEGIN
   ...
   DECLARE
      counter INTEGER := 1;
   BEGIN
      IF counter = outerblock.counter
      THEN
         ...
      END IF;
   END;
END;

Without the block label, there would be no way to distinguish between the two “counter” variables. Again, though, a better solution would probably ...