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

Loop Labels

You can give a name to a loop by using a label. (I 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;
   year_number := year_number + 1;

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

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