Loop Labels
You can give a name to a loop by using a label. (We introduced labels in Chapter 3.) A loop label in PL/SQL has the following format:
<<label_name>>
where label_name is the name of the label, and that loop label appears immediately before the LOOP statement:
<<all_emps>> FOR emp_rec IN emp_cur LOOP ... END LOOP;
The label can also appear optionally after the END LOOP reserved words, as the following example demonstrates:
<<year_loop>> WHILE year_number <= 1995 LOOP <<month_loop>> FOR month_number IN 1 .. 12 LOOP ... END LOOP month_loop; END LOOP year_loop;
The loop label is potentially useful in several ways:
When you have written a loop with a large body (say one that starts at line 50, ends on line 725, and has 16 nested loops inside it), use a loop label to tie the end of the loop back explicitly to its start. This visual tag will make it easier for a developer to maintain and debug the program. Without the loop label, it can be very difficult to keep track of which LOOP goes with which END LOOP.
You can use the loop label to qualify the name of the loop indexing variable (either a record or a number). Again, this can be helpful for readability. Here is an example:
<<year_loop>> FOR year_number IN 1800..1995 LOOP <<month_loop>> FOR month_number IN 1 .. 12 LOOP IF year_loop.year_number = 1900 THEN ... END IF; END LOOP month_loop; END LOOP year_loop;
When you have nested loops, you can use the label both to improve readability and to increase control over the execution ...
Get Oracle PL/SQL Programming, Third 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.