<

Simple Looping in PL/SQL

Published on
4,495 Points
1,295 Views
2 Endorsements
Last Modified:
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
Comment
0 Comments

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Join & Write a Comment

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month