Previous | Table of Contents | Next

Page 140

Using Labels and EXIT Statements with Loops

You can use labels within loops to identify a loop. When you're nesting loops, labels help to document the coding.

The Syntax for Using Labels with Loops 

<<label_name1>>
LOOP (FOR, WHILE, LOOP)
     <<label_name2>>
     LOOP (FOR, WHILE, LOOP)
          ...
     END LOOP <<label_name2>>
END LOOP <<label_name1>>

You will use the example of nested FOR loops from Day 5 (Listing 5.14) and modify it with label names, as shown in Listing 6.17.

INPUT
Listing 6.17. Using labels with loops. 

 1: BEGIN
 2:      <<outerloop>>
 3:      FOR v_outerloopcounter IN 1..2 LOOP
 4:           <<innerloop>>
 5:           FOR v_innerloopcounter IN 1..4 LOOP
 6:                DBMS_OUTPUT.PUT_LINE(`Outer Loop counter is ` ||
 7:                     v_outerloopcounter ||
 8:                     ` Inner Loop counter is ` || v_innerloopcounter);
 9:           END LOOP innerloop;
10:      END LOOP outerloop;
11: END;
12: /
ANALYSIS The only difference between Listing 5.14 in Day 5 and Listing 6.17 is the use of the label names outerloop and innerloop. Otherwise there is no difference in execution, output, and so on, but it is much easier to follow the logic.

You can even change the order of execution of a loop by using the EXIT and EXIT WHEN statements, as seen in Listing 6.18.

INPUT
Listing 6.18. Changing labeled loop execution with EXIT statements. 

 1: BEGIN
 2:      <<outerloop>>
 3:      FOR v_outerloopcounter IN 1..2 LOOP
 4:           <<innerloop>>
 5:           FOR v_innerloopcounter IN 1..4 LOOP
 6:                DBMS_OUTPUT.PUT_LINE(`Outer Loop counter is `
 7:                     || v_outerloopcounter ||

Page 141

 
 8:                     ` Inner Loop counter is ` || v_innerloopcounter);
 9:                     EXIT outerloop WHEN v_innerloopcounter = 3;
10:           END LOOP innerloop;
11:      END LOOP outerloop;
12: END;
13: /

OUTPUT
When you run the code shown in Listing 6.18, you should see the following output: 

Outer Loop counter is 1 Inner Loop counter is 1
Outer Loop counter is 1 Inner Loop counter is 2
Outer Loop counter is 1 Inner Loop counter is 3

The EXIT WHEN statement directs the program to exit the outer loop when the inner loop reaches a value of 3. Notice that this completely aborts the execution of both loops.

Simple LOOPs

The final loop to discuss today is the simple LOOP.

The Syntax for a Simple LOOP
The syntax of the simple LOOP is 

LOOP
  <statements>
END LOOP;

If you do not have an EXIT or EXIT WHEN statement located in the loop, you will have an infinite loop.

TIP
When using EXIT or EXIT WHEN, always try to place these commands either at the beginning of the LOOP block or at the end of the LOOP block. This way, you can avoid many logic errors.

Sample Simple LOOPs

WARNING
The following is an example of an infinite loop. You will probably not want to execute this example. As you can see, the loop will never end, and the loop will never do anything!

Page 142

 
BEGIN
     LOOP
          NULL;
     END LOOP;
END;
/

You can now properly exit out of a loop by simply adding the word EXIT after the NULL statement. Go ahead and execute the code in Listing 6.19.

INPUT
Listing 6.19. Using EXIT with a simple LOOP. 

1: BEGIN
2:      LOOP
3:           NULL;
4:           EXIT;
5:      END LOOP;
6: END;

Creating a REPEAT...UNTIL Loop

Oracle does not have a built-in REPEAT <statements> UNTIL <condition is true> loop. However, you can simulate this by using the simple LOOP and the EXIT or EXIT WHEN statements.

The Syntax for a Simulated REPEAT...UNTIL Loop 

LOOP
  <statements>
     IF <condition is true>
          EXIT;
     END IF;
END LOOP;

Alternatively, you could use the preferable method of 

LOOP
  <statements>
     EXIT WHEN <condition is true>;
END LOOP;


TIP
Always try to use the EXIT WHEN statement. It requires much less coding on your part, and it is easier to follow. The only reason you would use nested IF statements is for fine-tuning Oracle to speed up the process.

Page 143

An Example of a Simulated REPEAT...UNTIL Loop

Go ahead and enter the code in Listing 6.20. You will still be calculating the area of a circle as you did in Listings 6.9, 6.12, 6.13, and 6.14, but this time you will use a simulated REPEAT...UNTIL loop.

INPUT
Listing 6.20. Demonstrating a REPEAT...UNTIL loop. 

 1: DECLARE
 2:      v_Radius NUMBER := 2;
 3: BEGIN
 4:      LOOP
 5:           DBMS_OUTPUT.PUT_LINE(`The Area is `
 6:                || v_RADIUS*v_RADIUS * mypi);
 7:           v_Radius := v_Radius + 2;
 8:           EXIT WHEN v_Radius > 10;
 9:      END LOOP;
10: END;
ANALYSIS Notice that the code in Listing 6.20 creates the same five output lines computing the area of the circle that were produced by Listing 6.12. This simulated REPEAT...UNTIL loop simply starts the loop, outputs the area of the loop to the screen, increments the radius, and then exits when the radius is greater than 10. This allows you to use the values 2, 4, 6, 8, and 10 as in the other examples.

What Loop Should I Use?

All of these loop options can get confusing! As you saw in the examples, you can take the FOR, WHILE, and LOOP statements to create the same output. However, Table 6.1 shows some general guidelines for when to use what type of loop.

Table 6.1. When to use which loop.

Loop When to Use
FOR Always use the FOR loop if you know specifically how many times the loop should execute. If you have to code an EXIT or EXIT WHEN statement in a FOR loop, you might want to reconsider your coding and go with a different loop or different approach.
WHILE Use this if you might never even want to execute the loop one time. Although you could duplicate this in a FOR loop using EXIT or EXIT WHEN, this is best left for the WHILE loop. The WHILE loop is the most commonly used loop because it provides the most flexibility. 
                                                      continues

Previous | Table of Contents | Next

Используются технологии uCoz