Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Tips by Burleson

PL/SQL introduced to Oracle developers the ability to control the flow of code using IF-THEN-ELSE logic. This type of logic is very similar to the logic found in other languages, as shown by the following code block:

IF <condition> THEN
ELSIF <condition> THEN

The NULL Statement

Not to be confused with a NULL value, the NULL statement is often placed in code to indicate that the code is to take no action. The NULL statement does nothing.

Looping Constructs

In addition to the CURSOR FOR loop that was discussed previously, PL/SQL supports FOR and WHILE loops, as well as allowing developers to use a generic loop structure.

The FOR Loop

As you can see, the FOR loop in PL/SQL is quite similar to FOR loops in other languages:

FOR x IN 1..50 LOOP

The lower and upper bounds of the FOR loop must evaluate to integers. If the lower and upper bounds of the loop are equal, the loop will execute only once. If the lower bound is greater than the upper bound, the loop will not execute at all. Variables may be substituted for both the lower and upper bounds of the loop.

To run a loop from a high value to a low value, the syntax is slightly different from other languages, as shown by this example:


Just like a CURSOR FOR loop, the loop index variable (in this case x ) never has to be declared.

The number of iterations of a FOR loop can be easily calculated before the loop is entered by checking the difference between the upper and lower bounds. A FOR loop can execute between zero and MAXINT (the largest integer supported by your hardware and operating system) times.

The LOOP Statement

The PL/SQL LOOP statement can be used alone to create the precise loop structure you need. For instance, you might create a loop that always executes at least once, as follows:

   IF <condition> THEN
    END IF;

The WHILE Loop

As with the FOR loop, the WHILE Loop is quite similar to a WHILE loop in other languages. For example:

WHILE MoreRowsToProcess = TRUE LOOP

A WHILE loop can execute from zero to an infinite number of times. The number of iterations of a WHILE loop can never be known until after the loop has finished executing.

The EXIT Statement

PL/SQL provides the EXIT statement to end a loop prematurely. The functionality of the EXIT statement will allow you to exit a single loop or all loops currently executing. If you wish to be able to use the statement to accomplish either type of exit, each of your loops must be given a label. Listing 2.17 illustrates the use of the EXIT statement with multiple loops.

Listing 2.17 Using an EXIT statement with multiple loops.

      IF <condition> THEN
          EXIT inner_loop;
      END IF;
      IF <condition> THEN
          EXIT outer_loop;
      END IF:

In addition to this functionality, the EXIT statement can also be followed by a WHEN condition, instead of using IF-THEN logic to test the condition. For example:



This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter