<

Go Premium for a chance to win a PS4. Enter to Win

x

Simple Looping in PL/SQL

Published on
4,362 Points
1,162 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month