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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

IF-THEN-ELSE Logic
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
   <statements>
 
ELSIF <condition> THEN
   <statements>
 
ELSE
   <statements>
END IF;

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
    <statements>
END 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:

FOR x IN REVERSE 1..50 LOOP
    <statements>
END LOOP;

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:

LOOP
    <statements>
   IF <condition> THEN
        EXIT;
    END IF;
END LOOP;

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
   <statements>
END 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.

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

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:

EXIT WHEN Students_cur%NOTFOUND;

    

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