ANALISYS The code in Listing 6.11 simply creates a variable called v_MYTEXT and assigns it a value of `THIS IS A TEST' in line 2. It then outputs to the console (screen) the total count of characters not including spaces in line 4.

The EXIT and EXIT WHEN Statements

The EXIT and EXIT WHEN statements enable you to escape out of the control of a loop. The format of the EXIT loop is

EXIT;

To terminate a loop, simply follow your condition with the EXIT statement. This is common when using IF statements.

The Syntax for the EXIT WHEN Loop
The syntax of the EXIT WHEN loop is

EXIT WHEN <condition is true>;

The EXIT WHEN statement enables you to specify the condition required to exit the execution of the loop. In this case, no IF statement is required.

Examples Using EXIT and EXIT WHEN

In this chapter, you created a WHILE loop that incremented by a value of 2 to calculate the area of a circle. You will change this code so that the program exits when the value of the radius is 10 after you have calculated the area. Enter and execute the code in Listing 6.12.

INPUT
Listing 6.12. Using EXIT with a WHILE loop.

 1: DECLARE
 2:     v_Radius NUMBER := 2;
 3: BEGIN
 4:     WHILE TRUE LOOP
 5:          DBMS_OUTPUT.PUT_LINE(`The Area is ` ||
 6:               mypi * v_Radius * v_Radius);
 7:          IF v_Radius = 10 THEN
 8:              EXIT;
 9:          END IF;
10:          v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius
11:     END LOOP;
12: END;
13: /

You will notice that the output is the same as the WHILE loop output from Listing 6.9.

Switching the output statements with the IF statement from Listing 6.12, which would alter your output, is illustrated in the following block of code:

IF v_Radius = 10 THEN
    EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(`The Area is ` || mypi * v_Radius * v_Radius);

Logic errors cause the most problems in any coding situation and can be very difficult to resolve! Next, you will see how to code EXIT WHEN instead of EXIT in Listing 6.13 to achieve the same results.

INPUT
Listing 6.13. Using EXIT WHEN with a WHILE loop.

 1: DECLARE
 2:     v_Radius NUMBER := 2;
 3: BEGIN
 4:     WHILE TRUE LOOP
 5:          DBMS_OUTPUT.PUT_LINE(`The Area is ` ||
 6:               mypi * v_Radius * v_Radius);
 7:          EXIT WHEN v_RADIUS = 10;
 8:          v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius
 9:     END LOOP;
10: END;
11: /

ANALYSIS Listing 6.13 performs the same function as Listing 6.12, but uses the EXIT WHEN statement on one line, instead of the multiple lines of IF...THEN...EXIT statements from Listing 6.12. This version is much easier to read and understand.

If you can exit from a WHILE loop, you should be able to exit from a FOR loop. The code from Listing 6.14 will perform the same function as the code from Listings 6.9, 6.12, and 6.13 to calculate the area of a circle, but this time using a FOR loop.

INPUT
Listing 6.14. Using EXIT with a FOR loop.

 1: BEGIN
 2:      FOR v_loopcounter IN 1..20 LOOP
 3:           IF MOD(v_loopcounter,2) = 0 THEN
 4:                DBMS_OUTPUT.PUT_LINE(`The AREA of the circle is ` ||
 5:                     v_loopcounter*v_loopcounter * mypi);
 6:           END IF; -- End execution of statements for even counter
 7:           IF v_loopcounter = 10 THEN
 8:                EXIT;
 9:           END IF;
10:      END LOOP;
11: END;
12: /

ANALYSIS The loop terminates after the area has been calculated for a radius of 10 from line 7. Notice that the IF condition from line 7 fully terminates the loop prematurely before the loop can increment to a value of 20.

If you exit out of a loop in the middle of the function, what happens? To see the outcome, first enter the code in Listing 6.15 to create the function called exitfunc.

INPUT
Listing 6.15. Impact of EXIT in a function.

 1: CREATE OR REPLACE FUNCTION exitfunc(p_pass_string VARCHAR2)
 2:      RETURN NUMBER IS
 3:           v_MYCOUNTER INTEGER := 1;
 4:           v_COUNTNOSP NUMBER := 0;
 5: BEGIN
 6:      WHILE v_MYCOUNTER <= LENGTH(p_PASS_STRING) LOOP
 7:           IF SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) != ` ` THEN
 8:                v_COUNTNOSP := v_COUNTNOSP + 1;
 9:           ELSE
10:                NULL;
11:           END IF;
12:           v_MYCOUNTER := v_MYCOUNTER + 1;
13:           EXIT WHEN SUBSTR(p_PASS_STRING,v_MYCOUNTER,1) = ` `;
14:      END LOOP;
15:      RETURN v_COUNTNOSP ;
16: END exitfunc;
17: /

ANALYSIS You will notice the addition of only one statement that tells the program to exit the loop if it encounters a space. Now, to test and execute the function, enter the code from Listing 6.16.

INPUT
Listing 6.16. Executing EXIT within a function.

1: DECLARE
2:      v_MYTEXT VARCHAR2(20) := `THIS IS A TEST';
3: BEGIN
4:      DBMS_OUTPUT.PUT_LINE(`Total count is ` || exitfunc(v_MYTEXT));
5: END;
6: /

OUTPUT
The output when executed should be

Total count is 4

The effect of breaking out of a loop in the function is that it will still return the value of the variable when the EXIT statement has been executed. Instead of counting all of the characters in the line, it stops when it hits the first space and properly returns the value of 4 for the word `Test'.