SQL FOR Statement Delivers

 Christmas is near which means it is gift giving season, so I thought I’d highlight a powerful capability that the SQL procedural language delivers to programmers – the FOR statement.

 The SQL procedural language (SQL PL) itself was a powerful addition to the SQL Standard because it enables developers to code both their data access and application logic entirely in SQL.  No need to learn the syntax for embedding SQL in  a high-level programming language such as RPG or COBOL, all the coding can be done with SQL.

 

For the most part, the SQL PL support mirrors other programming languages with support for variables, assignment statements, conditional statements, and looping constructs. While other languages support a for loop, the SQL FOR statement is unique in the fact that it streamlines the use of a cursor to process the rows returned by a SELECT statement.

 

In a high-level programming language, the use of a cursor requires a specific logic flow that looks like the following:

DECLARE pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100;

OPEN pgm1cur;

FETCH pgm1cur INTO :host1, :host2;

 
while SQLCODE = 0
   /* application logic using colPK and col2 values 

      stored in host1 & host2 variables*/
   FETCH pgm1cur INTO :host1, :host2;
end;

CLOSE pgm1cur;

The cursor needs to be declared and opened. Next, the FETCH statement is used with a looping construct to process each row returned by the cursor and copy the values needed for the application logic into local variables.

The coding for cursor processing is nothing complex, but it is somewhat tedious. The SQL FOR statement stands out because it reduces the amount of code that needs to be created and maintained. As the following example shows, the coding load is reduced because the FOR statement eliminates the need for opening, advancing, and closing the cursor along with the need to copy column values into host variables.

FOR myloop AS pgm1cur CURSOR FOR
  SELECT colPK, col2 FROM table1 WHERE col3>100
DO
     /* application logic using 

        colPK and col2 values */ 

END FOR;

The FOR statement makes cursor usage so simple that one can easily overlook that they’re even using a cursor – let’s look at customer example from a recent IBM Expert Labs engagement which reinforces this. 

A client needed help optimizing the performance of their SQL stored procedures and one of their stored procedures contained the following FOR statement:

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

     UPDATE table1 SET rowProcessed='Y' WHERE colPK = currentPK;
END FOR;

The client’s developer coded a separate UPDATE statement to update the row which the loop was currently processing because they lost track of the fact the FOR statement provides a cursor that can be used for a positioned update.  While the client’s FOR statement functionally worked, performance was sub-optimal because the separate UPDATE statement must search and find the row that needs to be updated. 

 

The positioned update used in this optimized version of the FOR statement eliminates the search and find overhead by just updating the row that the cursor is  currently pointing to.

FOR myloop AS proc1Cur CURSOR FOR
  SELECT colPK, col1, col2 FROM table1 WHERE rowProcessed ='N'
   FOR UPDATE OF rowProcessed
DO
    SET currentPK = colPK;

     /* application logic using col1 and col2 values */

    UPDATE table1 SET rowProcessed='Y' WHERE CURRENT OF proc1Cur;

END FOR;

One performance test that I ran showed the positioned update to be two times faster. I’m guessing that a 2x performance increase would be pretty high on the Christmas wish list for most programmers!

 

That's a wrap for 2025 -  Merry Christmas and I look forward to interacting with all of you in the new year!