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
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
2. l_n_var1 NUMBER:=1;
4. WHILE l_n_var1 =5 loop
Burleson is the American Team
Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum .
Verifyexperience!Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications .
Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page.
The Oracle of Database Support
Copyright 1996 – 2016
All rights reserved by Burleson
Oracle is the registered trademark of Oracle Corporation.