SQL PL–The LOOP Loop

November 15, 2016 Ted Holt

In the previous episode of this exciting series, I wrote about FOR, arguably the most useful of the SQL PL looping structures. Next in line of usefulness, in my opinion, is the LOOP structure. Like the rest of SQL PL, this is an easy structure to master, and I am pleased to write about it.

In its simplest form, the syntax of the LOOP structure is as follows:

LOOP - body - END LOOP

Here is a more realistic version of the syntax:

label: LOOP - body - END LOOP label

The body contains any assortment of simple statements and/or compound statements.

Something in the body must force an exit from the loop. The recommended way to exit a loop is the LEAVE statement. You can also use RETURN, which exits a procedure or function, and GOTO, which I don’t recommend.

SQL PL allows you to label executable statements. That is, you can give a statement a name. A label is a group of alphanumeric characters. As with CL, a label is followed by a colon when used to identify a statement.

You may use a label to name a LOOP loop if you wish. In fact, if you use LEAVE to exit the loop, you must give the loop a label because LEAVE, unlike the RPG and CL counterparts, requires a label.

Let’s look at a simple example:

create or replace procedure Billing (in p_Company dec (3))

begin atomic

   declare   SqlState     char(5);
   declare   v_Company    dec (3);
   declare   v_Order      dec (9);
   declare   v_Line       dec (3);
   declare   v_Item       char(6);
   declare   v_Qty        dec (3);

   declare c_Bill cursor for
      select shp.Company, shp.SalesOrder, shp.Line,
             shp.Item, shp.Quantity
        from Shipments as shp
       where shp.Company = p_Company
         for update;

   open c_Bill;

Loop1:
   Loop
      fetch c_Bill into v_Company, v_Order, v_Line,
                     v_Item, v_Qty;
      if SqlState = '02000'
         then leave Loop1;
      end if; 

      update SalesOrderDetails as d
         set d.QtyShipped = d.QtyShipped + v_Qty
       where (d.company, d.order, d.line) =
                (v_Company, v_Order, v_Line);

      delete from Shipments
       where current of c_Bill;

   end loop;

   close c_Bill;

end

This example uses a cursor to bill customers for the company specified in the only parameter, p_Company. Since today’s topic is LOOP, I won’t explain the workings of the cursor. However, if you’ve used SQL cursors in other languages, you’ll readily understand this example.

The relevant parts of the LOOP structure are in red. The loop begins with the unimaginative label “Loop1” and ends with END LOOP. I could have included the loop name after END LOOP.

   end loop Loop1;

In a large routine, adding the loop name to LOOP might have contributed to greater clarity. Since the routine is so small, the loop name seemed to me to be unnecessary clutter.

SQL, like RPG, CL, COBOL, and some other languages, cannot determine the end of a data set without attempting to read. This is the classic case for a middle-tested loop. Part of the loop (the fetch) must be executed at least once, while the code that processes the fetched data may not be executed at all. The LOOP structure handles this requirement nicely.

When there is no more data to fetch, the database manager loads the value 02000 into the SQL state variable. This is the cue to exit the loop. Notice the LEAVE statement references the loop name.

I included two statements in the body of the loop: an UPDATE and a DELETE. Notice that the DELETE references the current row. This is the reason that I was not able to use a FOR loop. FOR loops are read-only.

I have written about my great fondness of middle-tested loops. RPG and CL let me fake them. COBOL has no structured way to implement them. (I could implement them with GO TO, but I won’t.) SQL PL directly supports them. Chalk up another one for SQL PL.

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

Table of Contents

Recent Posts

Subscribe

Pages

Search