SQL PL–The FOR Loop

SQL PL has four looping structures. The most useful one, to my way of thinking, is the FOR structure. It is different from–and more powerful than–the RPG FOR op code, and it’s easy to learn and use. Here’s how it works.

FOR iterates over a read-only result set and terminates when there are no more rows to process. Think of a FOR loop as a read-only cursor without the messiness of OPEN, FETCH, and CLOSE. Let me illustrate with a stored procedure.

create or replace Procedure Billing (in p_BillingCycle dec(3))
for One_Customer as
   select c.AccountNumber
     from customers as c 
    where c.BillingCycle = p_BillingCycle
 do
   call Bill200R (One_Customer.AccountNumber);
end for

Stored procedure BILLING is a program object that is executed through an SQL interface. For example, you can call it using a GUI interface, such as Run SQL Scripts, or the green-screen Start SQL Interactive Session (STRSQL) command.

call billing (20)

If you want to run it from a CL command line, use RUNSQL.

runsql ('call billing (20)')

This stored procedure accepts one parameter, the billing cycle, which is a three-digit packed-decimal number. I follow the convention suggested by Yip, et al (see the reference below), by prefixing parameter names with p_ in order to distinguish them from columns from the tables and views in the query.

FOR is followed by a loop name, in this case One_Customer. Columns from the result set are qualified with this name. The FOR loop executes a SELECT statement that retrieves all customers for the desired billing cycle.

The body of the loop begins after the word DO and ends with END FOR. I can place one or more statements in the body. In this example, I call RPG program BILL200R, which prepares an invoice for a customer. Notice that I passed the customer’s account number from the result set to BILL200R. Notice that the parameter is qualified by the loop name, not the table name.

Here’s a different version of the same application. There’s nothing wrong with calling an RPG program, but let’s say that the application needs to load data into two work tables, which another program will read to generate invoices.

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

begin
   declare  v_ID          dec (5)   default 0;

   delete from BillingHeader;
   delete from BillingTransactions;

   for One_Customer as
      select c.AccountNumber, c.Name, c.StreetAddress, c.City,
             c.State, c.Zip
      from customers as c
      where c.BillingCycle = p_BillingCycle
   do
      set v_ID = v_ID + 1;

      insert into BillingTransactions
            (ID, Xact, Line, Date,
             Item, Quantity, Price, Extended)
         select v_ID, t.xact, t.line, t. Date,
                t.item, t.quantity, t.price, t.quantity * t.price
           from sales as t
          where t.customer = One_Customer.AccountNumber;

      insert into BillingHeader
         (ID, CustomerNumber, Name,
          StreetAddress, City, State, Zip)
      values
          (v_ID, One_Customer.AccountNumber, One_Customer.Name,
           One_Customer.StreetAddress, One_Customer.City,
           One_Customer.State, One_Customer.Zip);
   end for;

   update BillingHeader as h
      set h.ordertotal = (select sum(t.quantity * t.price)
                            from billingtransactions as t
                           where t.id = h.id);

end

The body of the stored procedure always consists of one statement. The first example contains one FOR statement. The second example contains one compound statement.

The DECLARE creates a variable called v_ID and gives it an initial value of zero. This variable will be used as a key field in the work tables.

Speaking of the work tables, they must be cleared before the billing process reloads them. In CL, I would use the Clear Physical File (CLRPFM) command. In this example, I use DELETE without a WHERE clause. Had I built this example with a later release of DB2, I would have used TRUNCATE TABLE.

The FOR loop differs slightly from the previous example. The SELECT requires more columns from the CUSTOMERS table. In the body, I replaced the CALL with a SET and two INSERTs. The SET increments the key field. The first insert loads all the open sales for the customers assigned to the selected billing cycle into a table of transactions. The second insert loads header information. I could have made this example a bit more realistic by including data from more tables (e.g., a table of discount rates), but I wanted to keep the code as simple as possible.

Again, notice that the insert statements use the loop name, One_Customer, to qualify the columns from the result set. The correlation name for the CUSTOMERS table, c, is only used in the SELECT statement.

After the FOR loop, I update the order total column in the header table with the sum of the transactions for each billing ID. The work tables are ready for the billing program to invoice the customers.

If you need to update or delete the rows that were fetched in the FOR loop, you’ll have to access the result set through a cursor. That topic must wait for another day.

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

RELATED STORIES

A Brief Introduction To The SQL Procedures Language

Book Excerpt: DB2 SQL Procedural Language for Linux, UNIX, and Windows