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 ...
Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.