Oracle PL/SQL Loops
One of the key things that PL/SQL offers is the ability to set up loops and to process
records individually within the loop. There are several looping styles, we will start with
what is defined as a BASIC loop. Within the basic loop, the exit statement is used to
terminate the loop. The syntax for the BASIC LOOP is:
- LOOP
- Processing code within the loop
- EXIT which can include a WHEN condition
- (note there can be multiple EXIT statements)
- END LOOP;
First I am creating a table that I will use in at least some of the example that test the
loop. It contains a record number, a counter and a column for whatever.
SQL CODE:
SQL> CREATE TABLE testloop
2 (rec_no NUMBER(3), ctr NUMBER(3), data_in VARCHAR2(10));
Table created.
SQL> DESC testloop;
Name Null? Type
------------------------------- -------- ----
REC_NO NUMBER(3)
CTR NUMBER(3)
DATA_IN VARCHAR2(10)
SQL> edit basicloop1
This is an example of a basic loop. I have set up two variables, v_recno which I
initialized at 1 and v_ctr which I initialized at 100. In the processing part of the
block I inserted a LOOP which will insert the v_recno into the rec_no column on the table
and the v_ctr into the ctr column of the table. After doing the insert, I incremented the
v_recno by 1 and the v_ctr by 10. Then I used the IF to test if the v_recno was greater
than 5. If it was I coded EXIT which will leave the loop. If it wasn't the if ended.
When the END LOOP was encountered the loop was restarted which meant that another record
was inserted, the variables were incremented and the test was made. This loop will
continue until the IF statement tests true for v_recno > 5. At that point the EXIT
statement takes processing outside the loop. Since there are no other instructions the
block is complete and the message procedure is successfully completed will be displayed.
I then did a select on the testloop table and displayed the records that were produced as
a result of this loop. Notice that v_recno runs from 1 to 5. After the row with 5 is
inserted, the code added one making v_recno 6 and that is when the IF statement caused the
EXIT to happen. Notice also that v_ctr started at 100 and was incremented by 10 each pass
through the loop. No data was put in the column data_in.
EXIT can be inserted multiple times in the code. From a programming point of view, I
prefer EXIT once at the bottom of the loop to avoid confusion. Using EXIT to leave the
loop before it is complete is a programming practice that I try to avoid.
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=1;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
IF v_recno > 5 THEN
EXIT;
END IF;
END LOOP;
END;
/
SQL CODE:
SQL> @ basicloop1
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
1 100
2 110
3 120
4 130
5 140
Next I deleted all of the records from testloop so I start again.
SQL CODE:
SQL> DELETE FROM testloop;
5 rows deleted.
SQL> edit basicloop2
The change in this code is that I eliminated the IF statement to test for exiting the loop
and instead used the EXIT WHEN with the condition. The condition is the same, when v_recno
> 5. Again I prefer placing this test at the bottom of the loop. Note that when I code a
loop, I always need to provide some means of exiting or the loop will run indefinitely.
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=1;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
EXIT WHEN v_recno > 5;
END LOOP;
END;
/
SQL CODE:
SQL> @ basicloop2
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
1 100
2 110
3 120
4 130
5 140
In the example below, I first deleted all the records from testloop and then I went in and
made a change to the code that set the initial value of v_recno to 6. Notice that in the
basic loop structure, you have execution of the loop at least once even if the exit
condition has been met prior to entering the loop. In other words, it enters the loop
before testing. The insert could have been controlled by an IF so that no insert would be
executed, but the loop would still be processed. This is illustrated in the second example
below.
SQL CODE:
SQL> edit basicloop3
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=6;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
EXIT WHEN v_recno > 5;
END LOOP;
END;
/
SQL CODE:
SQL> @ basicloop3
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
6 100
SQL> edit basicloop3a
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=6;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
LOOP
IF v_recno < 6 THEN
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
END IF;
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
EXIT WHEN v_recno > 5;
END LOOP;
END;
/
SQL CODE:
SQL> @ basicloop3a
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
no rows selected
WHILE LOOP
The WHILE loop allows the programmer to put a condition on the WHILE statement that starts
the loop. The syntax is:
- WHILE condition LOOP
- Processing code
- END LOOP;
This format calls for the condition to be evaluated at the start of each iteration of the
loop. If the condition is TRUE then the loop is processed, if the condition is FALSE then
the loop is not processed. With loops it is important to have something to exit the loop
and something that impacts or changes the condition on which you base that exit.
In the example below, the WHILE condition is v_recno < 6 which means the processing will
continue until v_recno is no longer less than 6. Adding 1 to v_recno is the statement that
impacts the condtion. When the add results in 6, the loop is stopped and so the insert for
rec_no 6 never happens.
SQL CODE:
SQL> edit whileloop1
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=1;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
WHILE v_recno < 6 LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
END LOOP;
END;
/
SQL CODE:
SQL> @ whileloop1
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
1 100
2 110
3 120
4 130
5 140
In the example below, I went in and changes the code so that v_recno had an initial value
of 6. The WHILE condition prevented the loop from being entered and no output was produced.
SQL CODE:
SQL> edit whileloop2
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=6;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
WHILE v_recno < 6 LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
END LOOP;
END;
/
SQL CODE:
SQL> @ whileloop2
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
no rows selected
FOR LOOP
The FOR loop controls the iterations in the FOR statement. The syntax is:
- FOR index in [REVERSE] lower_bound..upper_bound LOOP
- Processing code
- END LOOP;
The index is an implicitly declared integer which is automatically incremented by 1 on each
iteration until the appropriate bound is reached. Notice that the index is not declared by
the coder and can only be used in the loop specifications. Without the reverse clause, 1
is added starting from the lower_bound until the upper_bound is reached. With the reverse
clause, everything is reverse and the upper_bound is decreased until the lower_bound is
reached. The lower_bound and upper_bound can be literals or variable names or conditions
that evaluate to an integer.
In the example below, the index is called I and the loop runs with the index starting at 1
and running through 5. The insert and incrementing of v_recno and v_ctr are strictly to
supply data for the rows that are being inserted.
SQL CODE:
SQL> edit forloop1
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=1;
v_ctr testloop.ctr%TYPE :=100;
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
END LOOP;
END;
/
SQL CODE:
SQL> @ forloop1
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
1 100
2 110
3 120
4 130
5 140
The code below has been changed to use v_lower and v_upper to set the lower and upper
limits of the FOR.
SQL CODE:
SQL> edit forloop2
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE :=1;
v_ctr testloop.ctr%TYPE :=100;
v_lower NUMBER :=1;
v_upper NUMBER :=5;
BEGIN
FOR i IN v_lower..v_upper LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_recno :=v_recno + 1;
v_ctr := v_ctr + 10;
END LOOP;
END;
/
SQL CODE:
SQL> @ forloop2
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
1 100
2 110
3 120
4 130
5 140
In this example I want to use the reverse and I decided to put the value of the index in as
the record number. First, I eliminated the initial value on v_recno. Then I added in the
REVERSE clause in the FOR loop and finally I moved the command that assigns a value to
v_recno to before the insert and assigned the value of the index to it: v_recno := i;.
This means that I will start with the value in v_upper which is 5 and immediately assign
the 5 to the v_recno. It will then do the insert and increment v_ctr. Since the index has
not reached the lower limit one will be subtracted from 5 and the index will now be 4.
This is assigned to the v_recno and the next record will be inserted. Note that I could
have eliminated v_recno completely and assigned the value of the index in the INSERT.
SQL CODE:
SQL> edit forloop3
PL/SQL CODE:
DECLARE
v_recno testloop.rec_no%TYPE;
v_ctr testloop.ctr%TYPE :=100;
v_lower NUMBER :=1;
v_upper NUMBER :=5;
BEGIN
FOR i IN REVERSE v_lower..v_upper LOOP
v_recno :=i;
INSERT INTO testloop(rec_no, ctr)
VALUES(v_recno, v_ctr);
v_ctr := v_ctr + 10;
END LOOP;
END;
/
Alternative PL/SQL CODE:
DECLARE
v_ctr testloop.ctr%TYPE :=100;
v_lower NUMBER :=1;
v_upper NUMBER :=5;
BEGIN
FOR i IN REVERSE v_lower..v_upper LOOP
INSERT INTO testloop(rec_no, ctr)
VALUES(i, v_ctr);
v_ctr := v_ctr + 10;
END LOOP;
END;
/
SQL CODE:
SQL> @ forloop3
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testloop;
REC_NO CTR DATA_IN
--------- --------- ----------
5 100
4 110
3 120
2 130
1 140