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.