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