|
 |
|
PL/SQL WHILE Loop
Oracle Tips by Burleson |
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 loop repeats one or more executable statements placed in its structure based on the condition at the beginning of the loop. On each iteration, these executable statements get executed and the control shifts to the top of the loop, checks the entry condition and if it is satisfied, the control gets ready for the next iteration else, the loop will be terminated and control passes on to the next section. % Note – If the entry condition is not satisfied when the control reaches the WHILE loop, the complete WHILE loop will be skipped from execution and the control proceeds on to the next section and the loop will not be executed even once. | Unlike the simple loops, only the index value has to be managed by the programmer and the exit condition will be automatically taken care by the loop’s entry condition. In this loop type, if the counter variable or the entry condition variable is not initialized, the loop will not be executed even once as this is a “guard at the beginning of the loop” type thus an extra IF-END IF conditional check is not required. The below prototype defines the basic structure of the WHILE loop. WHILE <condition> loop <Executable statements>; End loop; Here, the <condition> can be single or multiple separated by conjoining operator (AND) or include operator (OR) and can be of either Relational, Boolean or Null check. Simple While loop 1. DECLARE 2. l_n_var1 NUMBER:=1; 3. BEGIN 4. WHILE l_n_var1<=5 loop 5. dbms_output.put_line(l_n_var1); 6. l_n_var1 :=l_n_var1+1; 7. END LOOP; 8. END; 9. / Result: 1 2 3 4 5 | Line no. | Description | | 1 | Start of the declare section of the block | | 2 | The local variable l_n_var1 of number data type is created and assigned to 1. | | 3 | Start of the execution section of the block | | 4 | Start of the WHILE loop with the condition as l_n_var1<=5 | | 5 | The local variable’s value is printed using the DBMS_OUTPUT.PUT_LINE procedure | | 6 | The local variable l_n_var1 is incremented by 1 for each iteration | | 7 | End of the WHILE loop | | 8,9 | End of the execution section of the block |
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. |
|
and include the URL for the page.
