Simple Looping in PL/SQL

Published:
Updated:
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;
}

Open in new window


PL/SQL


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

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
}

Open in new window


PL/SQL


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

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
}

Open in new window


PL/SQL


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

Open in new window


Java


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

Open in new window


PL/SQL


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

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
}

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;
/

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;
/

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;
/

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.
2
1,626 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community