O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required