| |
 |
|
Oracle Concepts - PL/SQL WHILE Loop
Oracle Tips by Burleson Consulting |
The PL/SQL WHILE Loop
The WHILE loop, also called a conditional loop,
evaluates a condition before each loop executes, and if false, the loop is
terminated. If the expression is false when the program reaches the WHILE loop,
the loop code is jumped and never executed. Use a WHILE loop when the condition
test is required at the start of the loop. The next example contains three
WHILE loops.
SQL>
declare
2
v_test varchar2(8) := 'RUN';
3
n_numb number := 2;
4
begin
5
while v_test <> 'STOP' loop
6
if n_numb > 5
7 then v_test := 'STOP';
8 end if;
9
dbms_output.put_line (v_test||': '||n_numb);
10
n_numb := n_numb + 1;
11
end loop;
12
13
v_test := 'DOWN';
14
while n_numb > 1 AND v_test = 'DOWN' loop
15
dbms_output.put_line (v_test||': '||n_numb);
16
n_numb := n_numb - 1;
17
end loop;
18
19
while 7 = 4 loop
20
NULL; -- never get here
21
end loop;
22 end;
23 /
RUN: 2
RUN: 3
RUN: 4
RUN: 5
STOP: 6
DOWN: 7
DOWN: 6
DOWN: 5
DOWN: 4
DOWN: 3
DOWN: 2
The last loop will never execute because the condition
will never be true. The middle loop uses multiple condition tests, using the
AND key word. The first loop runs while v_test does not equal ‘STOP’. Notice
that the check that changes v_test in lines 6, 7, 8 is at the top of the loop.
This is a poor choice because even though v_test may change, it is not evaluated
again until the program gets back to the top of the loop. This results in the
output stopping after n_numb reached 6, but notice in the results that at
completion of the first loop, n_numb was left with a value of 7. Unless this
was the programmer’s intent, a small, hard to locate bug has been introduced
into the code.
For the complete story, we recommend the book “Easy
Oracle PL/SQL Programming”. Once you have mastered basic SQL you are ready
for the advanced book “Oracle
PL/SQL Tuning” by Dr. Timothy Hall.
The programmer must ensure that the order of the
statements inside the loop will leave the variables in the required state when
the loop terminates. Remember that the WHILE loop tests at the start of the
loop and does not test again until the loop has completely run and returned to
the loop start. Both the endless loop and the WHILE loop execute until a
condition is met. These loops are effective if the programmer does not know how
many times the loop will execute. If the loop will run for a specified number
of iterations, it is more efficient to use a FOR loop.
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
publisher
here.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
