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

 

 

   
 

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.

  
 

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