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.

PL/SQL Simple LOOP Statements

The first type of loop we'll look at in our PL/SQL tutorial 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 <body of loop> END LOOP;

Incidentally, layout and case are unimportant in PL/SQL. you only need a space between each word, but by convention reserved words are written in upper case and you would generally put each statement on a seperate line. We could (should) have written the above code on several lines, 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 shows us an anonymous Oracle PL/SQL block (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; dbms_output.put_line(i); EXIT; END LOOP;

LOOP i:=i+1; CONTINUE WHEN i<5; dbms_output.put_line(i); EXIT; 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

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:

Oracle PL/SQL Cursor FOR LOOP Statements

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

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.

If you're looking for more Oracle PL/SQL training our partners offer instructor-led Oracle PLSQL training in the UK and Oracle PL/SQL training in New Zealand.

Return to PL/SQL tutorial - language elements