Oracle PL/SQL Programming, Third Edition

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