Oracle PL/SQL Tutorial -
How To Use Loops

This brief Oracle PL/SQL tutorial is one of a series providing an introduction to PL/SQL.

In this tutorial we'll see how to use cursor for loops, numeric for loops, while loops and simple loops.

For a quick introduction to using loops, watch our PL/SQL video tutorial.

Simple PL/SQL LOOP Statements

The first type of loop we'll look at is very simple. It is also an infinite loop, unless you specifically code an exit, as we'll see. So let's take a look. 

The simplest loop statement in Oracle PL/SQL looks like this:-

LOOP
    <PL/SQL statements in body of loop>
END LOOP;

Incidentally, layout and case are unimportant in PL/SQL. A single space between each word is sufficient, but by convention reserved words are written in upper case and you would generally put each statement on a seperate line. This is especially important when the body of the loop contains multiple statements;

The loop shown above is an infinite loop. You have to code your own loop control statements. We could use GOTO to jump out of the loop, a better way would be to use the EXIT statement (see the next example) but an even better way would be code it so there was no need to jump out of the loop - perhaps by using one of the other types of PL/SQL loops that we'll cover later on in this PL/SQL tutorial.

BEGIN

   LOOP dbms_output.put_line('hello world'); EXIT; END LOOP;
   dbms_output.put_line('finished');

END;

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.


The above example in our PL/SQL tutorial shows us an anonymous Oracle PL/SQL block (anonymous because it it has no name). If we run it, the loop will be executed once and will display 'hello world', then the loop will finish due to the EXIT statement and the first line of code outside of the loop will be run - in this case it wil display 'finished'.

That's not much good as it is. All we've seen so far is how to build a loop that runs forever or just runs once. Fortunately we can make the exit statement conditional (usually on the value of some variable that is changed withing the body of the loop).

DECLARE i PLS_INTEGER := 1;
BEGIN

  LOOP
    dbms_output.put_line(i); i:=i+1; EXIT WHEN i>3;
  END LOOP;

  dbms_output.put_line('finished');

END;

In this example the loop will run 3 times (we exit when i is greater than 3 but increment i before we test it!).

We could change the EXIT WHEN statement to an IF statement which would give us something like this:-

LOOP
    dbms_output.put_line(i);
    i:=i+1;
    IF
i>3 THEN EXIT ; END IF;
END LOOP;

We can also use the CONTINUE or CONTINUE WHEN statements in Oracle PL/SQL 11g or above to skip the remainder of the loop (but not jump out of the loop) like this:-

LOOP
  i:=i+1;
  IF
i<5 THEN CONTINUE; END IF; --jump to end of loop if i<5
   dbms_output.put_line(i);
  EXIT;
END LOOP;

LOOP i:=i+1;
 
CONTINUE WHEN i<5;  -- jump to end of lopp if i<5
  /* else */
  dbms_output.put_line(i);
  EXIT;
  /* end else */
END LOOP;

In these loops, the value of i will be written out just once - when i = 3 (assuming its initial value was 1).

PL/SQL WHILE LOOP Statements

Let's continue our PL/SQL tutorial with a look at While Loops. The format of this type of loop is WHILE <condition> LOOP and, as with other languages, the condition must evaluate to true or false and is tested at the beginning of the loop.

In this next piece of code for example the body of the loop will never be executed as the condition evaluates to false the first time it is tested.

DECLARE
  go BOOLEAN := FALSE;
BEGIN
  WHILE go LOOP
    dbms_output.put_line('still going');
    go := FALSE;

   END LOOP;
END;

Whereas if we reverse the initial value of go to be TRUE, the body of the loop will be executed once (and only once because we change the value of go to be false inside the loop).

DECLARE
  go BOOLEAN := TRUE;
BEGIN
  WHILE go LOOP
    dbms_output.put_line('still going');
    go := FALSE;

   END LOOP;
END;

PL/SQL FOR LOOP Statements

Oracle PL/SQL FOR LOOPs are also quite straightforward. The loop starts at the initial value of the index and continues until the upper value has been passed (i..e the number of times the loop will be executed is determined by the formula 1 + upper value - lower value). Therefore in this following piece of code, the loop will be executed once.

BEGIN
  FOR i in 1..1 LOOP dbms_output.put_line(i); END LOOP;
END:

Note that the loop index is declared implicitly and is in scope only in the body of the loop and is a read only variable. The upper and lower bounds can be any value you like but the upper bound must be greater than or equal to the lower bound or the loop won't be executed at all.

If you need to count down from high to low you can do that by adding the keyword REVERSE as in the following example.

BEGIN
  FOR i in REVERSE 1..10 LOOP
    dbms_output.put_line(i); 
  END LOOP;
END;

Note that the upper and lower bounds of the index must be numbers but they don't have to be constants. You can use variables as long as they are of a numeric type. So we could code our loop like this:-

DECLARE
i PLS_INTEGER :=1; j 
PLS_INTEGER := 4;
BEGIN
  FOR k in 
i..j LOOP
    dbms_output.put_line(i); 
  END LOOP;
END:

PL/SQL Cursor FOR LOOPs

PL/SQL cursor FOR LOOPs make it very easy to process multiple rows from a query and can reduce the amount of coding as all the cursor control (open, fetch, close) is done for you. Let's continue this PL/SQL tutorial with a look at a couple of examples.

The first example uses an implicit cursor.

BEGIN
  FOR emp IN (SELECT id, salary FROM employees) LOOP

      DBMS_OUTPUT.PUT_LINE(
              'Id = '||emp.id||' salary = '||emp.salary);

  END LOOP;
END;


The second example uses an explicit cursor.

DECLARE
CURSOR emp_cur IS SELECT id, salary FROM employees;
BEGIN
  FOR emp IN emp_cur LOOP

      DBMS_OUTPUT.PUT_LINE('Id = '||emp.id||' salary = '||emp.salary);
  END LOOP;
END;

You might use an explicit cursor when your query is quite long or quite complex and you wish to hide the complexity at the point of use (to make the code easier to read) or when you'll be running the same query two or more times.

One thing to remember is that if the query returns no rows, the body of the loop will not be executed, so you may need to check for that.

One way not to use a cursor for loop is for an operation that would be better done in SQL such as just using it to update the table you're querying, as shown in this last example in our PL/SQL tutorial.

BEGIN
  FOR emp IN (SELECT id, salary FROM employees) LOOP
    UPDATE emp SET salary=salary*1.1 WHERE id=emp.id;
                                      --10% pay rise

  END LOOP;
END;


It would be much quicker to write and much quicker to execute as just one SQL statement:  UPDATE emp SET salary=salary*1.1 . That way there is no context switching back and forth between sql and pl/sql as there would be with the loop.

Continue this PL/SQL tutorial with PL/SQL functions or go back to PL/SQL tutorial part2 - why and when should you use PL/SQL?

Looking for a PL/SQL training course? Learn PLSQL from real-world experts with guaranteed PL/SQL training and sky rocket your career.

Return to intorductory PL/SQL tutorial
Return to home page