INPUT
Listing 6.7. Example of a WHILE loop that never executes.

 1: DECLARE
 2:       v_Calc NUMBER := 0;
 3: BEGIN
 4:      WHILE v_Calc >= 10 LOOP
 5:           v_Calc := v_Calc + 1;
 6:           DBMS_OUTPUT.PUT_LINE(`The value of v_Calc is ` || v_Calc);
 7:      END LOOP;
 8: END;
 9: /

In Listing 6.7, the condition is never evaluated to true. The condition v_Calc >= 10 from line 4 is never true because v_Calc is initialized at line 2 to a value of 0, which is less, not greater than 10. When Listing 6.7 is executed, no output is sent to the screen.

Listing 6.8 shows the corrected version of this WHILE loop.

INPUT
Listing 6.8. Corrected WHILE loop that executes.

 1: DECLARE
 2:       v_Calc NUMBER := 0;
 3: BEGIN
 4:      WHILE v_Calc <= 10 LOOP
 5:           v_Calc := v_Calc + 1;
 6:           DBMS_OUTPUT.PUT_LINE(`The value of v_Calc is ` || v_Calc);
 7:      END LOOP;
 8: END;
 9: /
The value of v_Calc is 1
The value of v_Calc is 2
The value of v_Calc is 3
The value of v_Calc is 4
The value of v_Calc is 5
The value of v_Calc is 6
The value of v_Calc is 7
The value of v_Calc is 8
The value of v_Calc is 9
The value of v_Calc is 10
The value of v_Calc is 11

ANALYSIS To make the WHILE loop execute, I simply changed the >= to <= in line 4. The loop executes at least once because v_Calc <= 10.

It is important to understand that the loop continues to execute until v_Calc <= 10. This can potentially be a source of logic error flaws if the intent was to enter the loop until v_Calc had a value of 10 and not 11.

Listing 6.9 illustrates how to step through a WHILE loop in increments other than one.

INPUT
Listing 6.9. Stepping through a WHILE loop.

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

On Day 5, you created a method to trick Oracle into stepping through a FOR loop. The WHILE loop gives you more flexibility in looping, whether you are stepping through a loop, or even executing a loop! Listing 6.9 demonstrates stepping through the loop. This sequence increments v_Radius by a value of 2 from line 7 until it is equal to 10 from the condition specified in line 4. The following segment shows the output of this practice loop:

OUTPUT

The Area is 12.56
The Area is 50.24
The Area is 113.04
The Area is 200.96
The Area is 314

The next WHILE loop will be contained in a function. In addition, this will allow you to review functions from Day 5. If you run into any problems, it doesn't hurt to go back and review the prior chapter. Go ahead and create the stored function in Listing 6.10.

INPUT
Listing 6.10. The WHILE loop as part of a function.

 1: CREATE OR REPLACE FUNCTION dontcountsp(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:      END LOOP;
14:      RETURN v_COUNTNOSP ;
15: END dontcountsp;
16: /

ANALISYS You have just created a function called dontcountsp in Listing 6.10, which will count all characters except spaces from a variable-length string up to 20 characters long. The function is passed a string from p_PASS_STRING called from the procedure. The return type in line 14 is simply a number telling you how many characters are actually contained in the string.

Of the two variables, v_MYCOUNTER holds the positional location for the current location in the string. V_COUNTNOSP holds the total count of characters that are not spaces.

The program finally enters the WHILE loop. The loop will continue to execute as long as v_MYCOUNTER is less than the total LENGTH of the string. In the body of the loop, the program checks each character, beginning at position one all the way to the length of the string, and checks for the value of a space, defined by ` `. If there is no space, the program increments v_COUNTNOSP by 1 because the value was not a space. If the value is a space, the program does nothing, as indicated by the NULL statement. The placeholder in the string v_MYCOUNTER is then incremented by 1, and the loop continues to execute until it reaches the end of the string.
To see the function in action, go ahead and type the procedure in Listing 6.11 and then execute it.

INPUT
Listing 6.11. Executing the WHILE loop function.

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