Table 6.1. continued

Loop Guidelines

Some loop guidelines you should follow are shown in the following Do/Don't box.

Style Tips for Loops

Two last things I want to mention on loops are the use of label names and proper indentation. Always use loop names if creating any type of nested loop or when nesting FOR loops to make the index counter more meaningful.

Proper spacing should include aligning the END LOOP with the LOOP statement, and the usual indent of five spaces for statements within the loop. Listing 6.21 contains an example in which the spacing is proper, but the loop itself would appear confusing by not following these tips. After Listing 6.21, you will see the same example but with a better style of coding in Listings 6.22 and 6.23.

INPUT
Listing 6.21. A confusing FOR loop.

 1: BEGIN
 2:      FOR I = 1995 to 1997
 3:      LOOP
 4:           FOR J = 1 to 31
 5:           LOOP
 6:                FOR K = 1 to 12
 7:                LOOP
 8:                     <statements>
 9:                END LOOP;
10:           END LOOP;
11:      END LOOP;
12: END;

A programmer might take an initial look at the code in Listing 6.21 and say "Huh?" A better approach is shown in Listing 6.22.

INPUT
Listing 6.22. Making the FOR loop more meaningful.

 1: BEGIN
 2:      FOR year = 1995 to 1997
 3:      LOOP
 4:           FOR day = 1 to 31
 5:           LOOP
 6:                FOR month = 1 to 12
 7:                LOOP
 8:                     <statements>
 9:                END LOOP; --end month
10:           END LOOP; --end day
11:      END LOOP; --end year
12: END;

ANALYSIS As you can see, not only does this example clarify the counters, but it also clarifies the END LOOP statements. You can further clarify the loop by adding label names as seen in Listing 6.23.

INPUT
Listing 6.23. Further clarifying the FOR loop.

 1: BEGIN
 2:      <<year_loop>>
 3:      FOR year = v_START_YEAR to v_END_YEAR
 4:      LOOP
 5:           <<day_loop>>
 6:           FOR day = 1 to v_last_day_of_month
 7:           LOOP
 8:                <<month_loop>>
 9:                FOR month = 1 to 12
10:                LOOP
11:                     <statements>
12:                END LOOP month_loop;
13:           END LOOP day_loop;
14:      END LOOP year_loop;
15: END;

ANALYSIS Again, the code in Listing 6.23 is the ideal way to code this nested FOR loop. The label names are concise and easy to follow. In addition, I changed the outer boundary of the day loop to a variable called v_last_day_of_month. Because this value is truly a variable, you should code it as such. I also made the beginning and ending years a variable because the analysis period could change some time down the road!

Summary

Congratulations on completing another day! You learned how to branch by using the GOTO statement followed by the appropriate label name. You also learned that the label name must appear in the same block and within the same scope as the GOTO statement.

This day continued with more ways to loop PL/SQL code. You started off with the WHILE loop, which allows you to not even execute the loop once because it checks for a condition first. This concept is very important and should be retained, because the other loops must execute once (unless you use EXIT or EXIT WHEN statements at the beginning of the other loop types, which is probably not the best way to code the loop). You continued with learning ways to exit loops and ways to change the execution of nested loops by using the EXIT or EXIT WHEN statements in conjunction with label names. You then learned about the simple LOOP and how to create a REPEAT...UNTIL loop. Finally, to clear up the loop options, you reviewed the loop types and the best time to use which loop.

Q&A

Q What is the scope of the label called by the GOTO statement?

A The label_name must be within the same PL/SQL block of code that was called by the corresponding GOTO statement.

Q How many times must the WHILE loop execute?

A Depending upon the condition, the WHILE loop might never have to execute. This is one of the great features of this type of loop.

Q What is the syntax and purpose of the EXIT statement?

A The EXIT statement is simply coded as EXIT. It gives you a means to abort out of a loop without executing the loop in its entirety.

Q Can you exit out of loops contained in a function and still return a value?

A Yes. However, the returned value is the value assigned to the variable at the time the EXIT statement has been called.

Q Can you change the execution order of nested loops with EXIT or EXIT WHEN?

A Yes. You can abort both loops with the use of EXIT and EXIT WHEN if you use label names with loops.

Q What statement must be present in a simple LOOP or it will become an infinite loop?

A The EXIT or EXIT WHEN statement is required. Although these statements are not part of the syntax, not using them will make a loop infinite, which you should try to avoid at all costs.

Workshop

Use the following workshop to review and practice the GOTO statement, WHILE loops, and the simple LOOP statement. The answers to the quiz and exercises can be found in Appendix A, "Answers."

Quiz

1. True or False: The label name must be within the same PL/SQL block of code as the GOTO statement calling the label name.
2. When should GOTO be used?
3. WHILE loops must end with a(n) ____________ statement.
4. Can you potentially write a WHILE loop that never ends?
5. What statement(s) allow you to abort the processing of a loop?