 |
|
EnterpriseDB: LOOPING
Oracle Tips by
Burleson
|
SPL
provides three types of looping constructs: LOOP, WHILE and FOR.
LOOP
LOOP
is the most basic of the three loop structures. It is a
LOOP
forever construct. The only way to exit a LOOP loop, is with the EXIT
keyword. LOOP takes no parameters and is unconditional.
The LOOP
syntax is:
LOOP
EXIT WHEN <conditional conditional expression = TRUE>;
<executable code>
END LOOP;
A very
basic example is:
DECLARE
v_counter NUMBER := 5; -- Step 1
BEGIN
LOOP -- Step 2
EXIT WHEN v_counter <= 0; -- Step 3
-- Step 4
DBMS_OUTPUT.PUT_LINE( 'Counter = ' || to_char(v_counter) );
-- Step 5
v_counter := v_counter – 1;
END LOOP; -- Step 6
END;
INFO: Counter = 5
INFO: Counter = 4
INFO: Counter = 3
INFO: Counter = 2
INFO: Counter = 1
*
Warning: It is very easy to get into a never-ending loop with the
LOOP command. If you forget your exit statement or are not
initializing or checking your variables correctly, you could get into
a "runaway code" situation. Try Ctrl+C to end your program. If that
doesn't work, you may be able to end the program by terminating your
connection to the database. As a last resort, you may need to restart
the EnterpriseDB server.
In
Step1, I declared a variable that will hold a counter. I will use
this variable to control the number of times I loop. I initialize the
counter with an assignment of 5.
Step 2
is the LOOP keyword. Notice that it has no parameters. You could put
Step 3 on the same line but I think it is a better coding practice to
separate the two.
Step 3
is the EXIT conditional. In this case, I am going to terminate the
LOOP
when the counter is less than or equal to (<=) zero. This line is
very important. If you comment out this line (or forget to enter it),
you have a run away query.
Step 4
simply displays my counter so that I can see the value.
Step 5
is very important. This is the step that decrements the counter. If
you comment out this line (or forget to enter it), you have a run away
query.
Step 6
is the END LOOP keyword and terminates my loop. Use the unconditional
LOOP when you want the loop to always execute at least once. If you
want the loop to only execute if the condition is TRUE, use the WHILE
loop.
WHILE
The
WHILE loop looks a lot like the LOOP but incorporates the conditional
logic in the LOOP command itself. As I mentioned above, if the
conditional evaluates to FALSE, you will never enter the loop at all.
The
syntax for a WHILE loop is:
WHILE
<conditional expression = TRUE> LOOP
<executable code>
END LOOP;
A very
basic example (that duplicates the logic of the LOOP above) is:
DECLARE
v_counter NUMBER := 5; -- Step 1
BEGIN
WHILE v_counter > 0 LOOP -- Step 2 & 3
-- Step 4
DBMS_OUTPUT.PUT_LINE( 'Counter = ' || to_char(v_counter) );
-- Step 5
v_counter := v_counter – 1;
END LOOP; -- Step 6
END;
INFO: Counter = 5
INFO: Counter = 4
INFO: Counter = 3
INFO: Counter = 2
INFO: Counter = 1
Step 1
and 4-6 are exactly the same as the
LOOP
above. The difference to notice between the
LOOP and
the WHILE is that the WHILE combines the conditional logic. Because
the
LOOP
will EXIT WHEN and the WHILE will LOOP WHILE, the condition had to be
changed. The
LOOP
exited when the counter was zero or less. The WHILE executed while
the counter was greater than zero.
You can
EXIT a WHILE loop with the EXIT keyword just as you would in the LOOP.
If
you can programmatically determine the number of iterations you must
do before executing your code, you would use the FOR loop.
FOR
The FOR
loop is the most complex of the three loop types but it still is
rather simple.
The
syntax is:
FOR <counter declaration> IN <counter begin>..<counter
end>
LOOP
<executable code>
END LOOP;
SPL
cannot duplicate the logic of the above two loops using a FOR LOOP.
The counter in a FOR loop can only be incremented and will only be
incremented by 1. A very basic example (that counts up instead of
down) is:
BEGIN
FOR v_counter IN 1..5 LOOP -- Step 1, 2 & 3
-- Step 4
DBMS_OUTPUT.PUT_LINE( 'Counter = ' ||
to_char(v_counter) );
END LOOP; -- Step 6
END;
INFO: Counter = 1
INFO: Counter = 2
INFO: Counter = 3
INFO: Counter = 4
INFO: Counter = 5
Notice
that steps 1-3 are all combined into a single statement and step 5
(the decrement) goes away.
I said
that the above LOOP and WHILE logic could not be duplicated. It
really can but it's almost not worth the effort. Here is the code
that duplicates that logic. If I was writing an application and
needed to do this, I would probably not use a FOR loop to do this:
DECLARE
v_counter NUMBER := 5; -- Step 1
BEGIN
FOR i IN 1..5 LOOP -- Step 2 & 3
-- Step 4
DBMS_OUTPUT.PUT_LINE( 'Counter = ' || to_char(v_counter) );
-- Step 5
v_counter := v_counter – 1;
END LOOP; --
Step 6
END;
INFO: Counter = 5
INFO: Counter = 4
INFO: Counter = 3
INFO: Counter = 2
INFO: Counter = 1
That
looks a lot like the WHILE doesn't it. It requires an extra variable
(i) and I personally think the WHILE is both more readable and more
maintainable.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
