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