Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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

Script Explanation

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

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.

Verify experience! 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.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.