Simple Looping in PL/SQL

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops along with their Java loop equivalents to make creating useful PL/SQL loops much easier.
 

While

Under many circumstances a while(true) loop isn't the most useful tool but a basic while loop in PL/SQL can be very useful for performing repeating actions such as multiple simple insert statements.

Java

while (true)
                      {
                         // do something
                         if (exit_condition)
                            break;
                      }

Select all Open in new window

PL/SQL

DECLARE
                      BEGIN
                         LOOP
                            // do something
                            IF exit_condition THEN
                               EXIT;
                            END IF;
                         END LOOP;
                      END;
                      /

Select all Open in new window

A more common while loop with a given breaking condition can be a cleaner way to perform similar tasks.

Java

while (condition)
                      {
                         // do something
                      }

Select all Open in new window

PL/SQL

DECLARE
                      BEGIN
                         WHILE condition LOOP
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

For

A basic for loop can more clearly perform a distinct number of iterations of your statements.  I've often used this to test code-enforced thresholds by inserting the maximum number of rows into a table to ensure my code reacts correctly.

Java

for (int i=0; i>length; i++)
                      {
                         // do something
                      }

Select all Open in new window

PL/SQL

DECLARE
                      BEGIN
                         FOR i IN 1..length LOOP
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

Java

for (int i=length; i<0; i--)
                      {
                         // do something
                      }

Select all Open in new window

PL/SQL

DECLARE
                      BEGIN
                         FOR i IN REVERSE 1..length LOOP
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

Possibly the most useful loop is a for loop performed on a list of objects. This will allow you to do things like performing similar insert statements using different source data, allowing you to have more dynamic statements.

Java

for (object o : objects)
                      {
                         // do something
                      }

Select all Open in new window

PL/SQL

int objects:

DECLARE
                         TYPE table_nums is TABLE of NUMBER;
                         nums table_nums;
                         num NUMBER;
                      BEGIN
                         nums := table_nums(4, 8, 15, 16, 23, 42);
                         FOR ELEM in 1 .. nums.COUNT LOOP
                            num :=nums(ELEM);
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

String objects:

DECLARE
                         TYPE table_varchars is TABLE of VARCHAR2(size);
                         strings table_varchars;
                         str VARCHAR2;
                      BEGIN
                         strings := table_varchars('string1', 'string2', 'string3');
                         FOR ELEM in 1 .. strings.COUNT LOOP
                            str :=strings(ELEM);
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

This also allows you to generate the list upon which to iterate from a select statement, giving you power to do a lot of data manipulation.  You can select values from one table and create entries into other tables using those values.

Creating the list from a query:

DECLARE
                         TYPE table_nums is TABLE of NUMBER;
                         nums table_nums;
                         num NUMBER;
                      BEGIN
                         SELECT column INTO nums FROM data_table WHERE condition;
                         FOR ELEM in 1 .. nums.COUNT LOOP
                            num :=nums(ELEM);
                            // do something
                         END LOOP;
                      END;
                      /

Select all Open in new window

These simple loops should be enough to handle most of your PL/SQL looping needs. I've often used PL/SQL loops to create a lot of accurate test data or to correct a lot of data gone wrong. Looping in PL/SQL can save a considerable amount of time and give you simple, legible code that you can store and use easily.