D
AY
5
W
EEK
1
Implementing Loops and
GOTO
s
by Tom Luers
Day 4, “Using Functions,
IF
Statements, and Loops,” demonstrates ways to
change the order of execution with PL/SQL. Today’s lesson covers several
additional methods of changing the order of execution. Today’s material covers
the following topics:
•Statement labels
• The
GOTO
statement
• The
WHILE
loop
• The simple
LOOP
• Emulating a
REPEAT...UNTIL
loop
• Recursion
07 7982 ch05 11.8.00 11:23 AM Page 127
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

to execute the code after a label, you should re-evaluate your code decisions
and choose an alternate method such as a function.
Caution
The
GOTO
Statement
The
GOTO
statement enables you to immediately transfer control to another labeled
PL/SQL block without the need for conditional checking. As soon as the
GOTO
statement
is encountered, all control is transferred to the code following the matching
label_name
.
This target label can appear anywhere in the same block of code.
The Syntax for the
GOTO
Statement
GOTO label_name;
The
label_name
is the matching
label_name
that must be contained within the same
PL/SQL block of code.
S
YNTAX
07 7982 ch05 11.8.00 11:23 AM Page 128
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

If you want a more global approach, using stored functions is one appropriate method.
Jumping into a Lower-Level Block
You can’t jump from an outer block of PL/SQL code back to an inner block of PL/SQL
code. Listing 5.1 is an example of an illegal
GOTO
call.
The following listing, and the next few after it, is for illustration purposes
only. Due to the errors they generate, you might not want to enter and exe-
cute them. However, if you do enter and execute these listings, they will not
destroy anything, and they might help you to troubleshoot errors in your
code in the future because you can see what errors these listings generate.
Caution
L
ISTING
5.1
Illegal GOTO Call to an Inner Block
1: DECLARE
2: v_Emergency_Warning VARCHAR2(50);
3: v_Status NUMBER = 0;
4: BEGIN
5: GOTO Emergency_Check;
6: BEGIN
7: <<Emergency_Check>>
8: IF v_Status = 1 THEN
9: DBMS_OUTPUT PUT_LINE(‘Emergency!’);
10: END IF;
11: END;
12: END;
I
NPUT

A
NALYSIS
The following listing is another that serves illustration purposes only
because it generates several errors. You might or might not want to enter
and execute this listing, depending on whether you want to see what kinds
of errors it generates.
Caution
L
ISTING
5.2
Illegal GOTO Call to a Loop
1: BEGIN
2: GOTO insideloop;
3: FOR v_loopcounter IN 1..5 LOOP
4: <<insideloop>
5: DBMS_OUTPUT.PUT_LINE(‘Loop counter is ‘ || v_loopcounter);
6: END LOOP;
7: END;
Error at Line 2
ORA-06550 Line 2
PLS-00201: Identifier ‘insideloop’ must be declared
As you can see, although the loop and the GOTO statement are within the same
block of PL/SQL code, Oracle does not know how to handle the jump inside the
loop. The obvious question is “What is the value of the loop counter?” Because there is
no answer, any attempt to implement this logic results in a compile error.
I
NPUT
O
UTPUT
A

6: GOTO Emergency_Check;
7: IF v_ReactorStatus = ‘Very Hot’ THEN
8: <<Emergency_Check>>
9: DBMS_OUTPUT PUT_LINE(‘Emergency!’);
10: END IF;
11: END;
From the
GOTO
call in Listing 5.3, if this block of PL/SQL code were allowed to
actually execute, it would never check to see whether
v_ReactorStatus = ‘Very
Hot’
. There might not even be an emergency because
v_ReactorStatus
could have a
value of
‘Cool’
. Because the value is never evaluated, the program always goes into cri-
sis mode. Fortunately, this improper use of
GOTO
is not allowed!
Jumping from One Part of an
IF
Statement to Another
Although you can call a label from an
IF
statement, it is illegal for the jump to go from
the
IF
clause to the

statement as
true to executing code as if the entire statement were false. This is a definite misuse of
the
GOTO
statement, and the code in this case probably does not require a
GOTO
statement.
From Listing 5.5, it should be apparent that you can’t raise an error and then return to the
original block of code where the error was generated from the exception handler.
132 Day 5
The following listing is another that serves illustration purposes only
because it generates several errors. You might or might not want to enter
and execute this listing, depending on whether you want to see what kinds
of errors it generates.
Caution
I
NPUT
The following listing is another that serves illustration purposes only
because it generates several errors. You might or might not want to enter
and execute this listing, depending on whether you want to see what kinds
of errors it generates.
Caution
L
ISTING
5.5
Illegal GOTO Call from an Exception Handler
1: DECLARE
2: v_Emergency_Warning VARCHAR2(50);
3: v_Status NUMBER = 0;
4: v_ReactorStatus VARCHAR2(10);

3: BEGIN
4: IF v_Status = 1 THEN
5: GOTO mybranch;
6: ELSE
7: v_Status := 1;
8: END IF;
9: <<mybranch>>
10: NULL;
11: END;
In the
GOTO
example from Listing 5.6, the program checks the value of
v_Status
.
If the value is equal to
1
,then the program goes immediately to the block
<<mybranch>>
; if the value is
false
,the program changes the value of
v_Status
to
equal
1
.
Why Use the
GOTO
Statement?
As in any procedural language, the use of

•Almost all cases in which you use the
GOTO
statement can be written with other
Oracle constructs.
Perhaps the only proper use of
GOTO
statements is to immediately stop all other execution
of statements and branch to a section of code to handle an emergency situation.
WHILE
Loops
The
WHILE
loop enables you to evaluate a condition before a sequence of statements is
executed. In fact, if the condition is false, the code is never executed. This situation is
different from the
FOR
loop where you must execute the loop at least once.
The Syntax for the
WHILE
Loop
The syntax of the
WHILE
loop is
WHILE <condition is true> LOOP
<statements>
END LOOP;
The
WHILE
loop requires the keywords
LOOP

package on
Day 17, “Writing to Files and the Display,” using this statement allows you
to see the actual output as the PL/SQL code executes to make PL/SQL easier
to understand.
Note
07 7982 ch05 11.8.00 11:23 AM Page 134
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Implementing Loops and
GOTO
s 135
5
You can enter the loops directly or use the
EDIT
command to save a file, which can be
executed at any time. Listing 5.7 demonstrates how the conditions for a
WHILE
loop can
cause the loop to never execute.
L
ISTING
5.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;

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
I
NPUT
A
NALYSIS
I
NPUT
O
UTPUT
07 7982 ch05 11.8.00 11:23 AM Page 135
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
To make the
WHILE
loop execute, I simply changed the
>=
to
<=
in line 4. The
loop executes at least once because
v_Calc <= 10
.

5: DBMS_OUTPUT.PUT_LINE(‘The Area is ‘ ||
6: 3.14 * v_Radius * v_Radius);
7: v_Radius := v_Radius + 2 ; — Calculates Area for Even Radius
8: END LOOP;
9: END;
10: /
The Area is 12.56
The Area is 50.24
The Area is 113.04
The Area is 200.96
The Area is 314
On Day 4, 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 5.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.
I
NPUT
O
UTPUT
A

15: END dontcountsp;
16: /
You create a function called
dontcountsp
from Listing 5.10, which counts 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 continues to execute as long as
v_MYCOUNTER
is less than the total
LENGTH
of the string. In the body of the loop, the pro-
gram 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 the value in a position is
not a space, the program increments
v_COUNTNOSP

characters not including spaces in line 4.
138 Day 5
I
NPUT
A
NALYSIS
Both the
SUBSTR()
function and the
LENGTH()
function are covered on Day 6,
“Using Oracle’s Built-In Functions.”
Note
The
EXIT
and
EXIT WHEN
Statements
The
EXIT
and
EXIT WHEN
statements enable you to escape out of the control of a loop.
When an
EXIT
statement is encountered, the loop completes immediately and control is
passed to the next statement. The format of the
EXIT
loop is
EXIT;

EXIT
and
EXIT WHEN
In this lesson, 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
5.12.
L
ISTING
5.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: 3.14 * 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: /
Notice that the output is the same as the
WHILE

Switching the output statements with the
IF
statement from Listing 5.12, which alters
your output, is illustrated in the following code:
IF v_Radius = 10 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(‘The Area is ‘ || 3.14 * v_Radius * v_Radius);
Logic errors cause the most problems in any coding situation and can be difficult to
resolve. Next, you will see how to code
EXIT WHEN
instead of
EXIT
in Listing 5.13 to
achieve the same results.
L
ISTING
5.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: 3.14 * 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: /

12: /
140 Day 5
I
NPUT
A
NALYSIS
I
NPUT
07 7982 ch05 11.8.00 11:23 AM Page 140
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Implementing Loops and
GOTO
s 141
5
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 pre-
maturely 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 out-
come, first enter the code in Listing 5.15 to create the function called
exitfunc
.
L
ISTING
5.15
Impact of EXIT in a Function
1: CREATE OR REPLACE function exitfunc(p_pass_string VARCHAR2)

Total count is 4
A
NALYSIS
I
NPUT
A
NALYSIS
I
NPUT
O
UTPUT
07 7982 ch05 11.8.00 11:23 AM Page 141
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The effect of breaking out of a loop in the function is that it still returns the
value of the variable when the
EXIT
statement has been executed. Instead of
counting all the characters in the line, it stops when it hits the first space and properly
returns the value of
4
for the word
‘Test’
.
142 Day 5
A
NALYSIS
If you do use the
EXIT
or
EXIT WHEN

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: /
The only difference between Listing 4.15 in Day 4 and Listing 5.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.
,
S
YNTAX
,
I
NPUT
A
NALYSIS
07 7982 ch05 11.8.00 11:23 AM Page 142
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Implementing Loops and
GOTO
s 143
5
You can even change the order of execution of a loop by using the
EXIT

LOOP
s
The final loop to discuss today is the simple
LOOP
. This type of loop is the simplest to
use and understand out of all the loops. The Simple Loop is a simple variation of the
other loops presented.
The Syntax for a Simple
LOOP
The syntax of the simple
LOOP
is
LOOP
<statement(s)>
END LOOP;
If you do not have an
EXIT
or
EXIT WHEN
statement located in the loop, you have an infi-
nite loop.
I
NPUT
O
UTPUT
A
NALYSIS
S
YNTAX
07 7982 ch05 11.8.00 11:23 AM Page 143

NULL
state-
ment. Execute the code in Listing 5.19.
L
ISTING
5.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 one by using the simple
LOOP
and the
EXIT
or
EXIT
WHEN
statements.
I
NPUT
07 7982 ch05 11.8.00 11:23 AM Page 144
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Tip
An Example of a Simulated
REPEAT...UNTIL
Loop
Enter the code in Listing 5.20. You are still calculating the area of a circle as you did in
Listings 5.9, 5.12, 5.13, and 5.14, but this time, you use a simulated
REPEAT...UNTIL
loop.
L
ISTING
5.20
Demonstrating a REPEAT...UNTIL Loop
1: DECLARE
2: v_Radius NUMBER := 2;
3: BEGIN
4: LOOP
5: DBMS_OUTPUT.PUT_LINE(‘The AREA of the circle is ‘
6: || v_RADIUS*v_RADIUS * mypi);
7: v_Radius := v_Radius + 2;
8: EXIT WHEN v_Radius > 10;
9: END LOOP;
10: END;
I
NPUT
07 7982 ch05 11.8.00 11:23 AM Page 145
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Notice that the code in Listing 5.20 creates the same five output lines computing
the area of the circle that were produced by Listing 5.12. This simulated
REPEAT...UNTIL
loop simply starts the loop, outputs the area of the loop to the screen,

should execute. If you have to code an
EXIT
or
EXIT WHEN
statement in a
FOR
loop, you might want to reconsider your code 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 can duplicate this result in a
FOR
loop using
EXIT
or
EXIT WHEN
,this situa-
tion is best left for the
WHILE
loop. The
WHILE
loop is the most commonly
used loop because it provides the most flexibility.
LOOP
You can use the simple
LOOP
if you want to create a
REPEAT <statements>
UNTIL <condition is true>
type of loop. The simple