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_curLOOP ...END LOOP;

The label can also appear optionally after the END LOOP reserved words, as the following example demonstrates:

<<year_loop>>WHILE year_number <= 1995LOOP <<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 of your loops. Y ou can, in fact, stop the execution of a specific named outer loop by adding a loop label after the EXIT keyword in the EXIT statement of a loop, as follows:

· EXIT loop_label;EXIT loop_label WHEN condition;

While it is possible to use loop labels in this fashion, I recommend that you avoid it. It leads to very unstructured logic (quite similar to GOTOs) that is hard to debug. If you feel that you need to insert code like this, you should consider restructuring your loop, and possibly switching from a FOR loop to a simple or WHILE loop.

5.7 Tips for Iterative Processing

Loops are very powerful and useful constructs, but they are structures that you should use with care. Performance issues within a program often are traced back to loops, and any problem within a loop is magnified by its repeated execution. The logic determining when to stop a loop can be very complex. This section offers some tips on how to write loops that are clean, easy to understand, and easy to maintain.