Guru: SQL PL, WHILE And REPEAT Loops

January 18, 2017 Ted Holt

In earlier editions of this august publication, I covered the SQL PL looping structures that I consider to be the most useful. Today I cover the remaining looping structures. I cover them for completeness, but also because what I consider to be most useful may not be what you consider to be most useful.

To review, the FOR loop provides a simple way to process the rows of a result set. LOOP structure is versatile, providing the freedom to exit a loop from any point. The remaining loop structures are WHILE and REPEAT.

The WHILE loop is a top-tested loop. It runs as long as a condition is true. If the governing condition is false when control first reaches the loop, the loop will not execute at all.

The WHILE syntax is simple:

WHILE condition DO body END WHILE

The REPEAT loop is a bottom-tested loop. It runs until a condition is true. A REPEAT loop executes at least one time. The syntax of REPEAT is also simple.

REPEAT body UNTIL condition END REPEAT

RPG programmers will recognize these structures as the counterparts to the DOW and DOU op codes.

To illustrate, let me return to a topic I wrote about awhile back, namely the need to convert a list into a temporary table. Here’s a routine that creates such a procedure.

create or replace procedure BuildDepartmentList
   (in p_List   varchar(256))

begin
   declare v_Department      dec     (  3);
   declare v_List            varchar (257);
   declare v_Pos             integer;
   declare v_Sep             varchar (  1)   default ',';

   set  v_List = trim(p_List) concat v_Sep;

   declare global temporary table DeptList
     ( Department     dec(2) )
   with replace;

   while v_List <> ' ' do
      set v_Pos = Locate (v_Sep, v_List);
      set v_Department = dec (substr ( v_List, 1, v_Pos - 1 ) );
      insert into session.DeptList values(v_Department);
      set v_List = substr (v_List, v_Pos + 1);
   end while;
end

And here’s how I call the procedure:

call BuildDepartmentList ('3,4,9,11,69')

When BuildDepartmentList starts running, p_List has the value <i>3,4,9,11,69</i>. The first SET copies the value of parameter p_List into v_List and adds a comma to the end.

set  v_List = trim(p_List) concat v_Sep;

This provides a work variable that the program can modify and ensures that the first SET within the WHILE loop always loads a positive value into v_Pos.

The WHILE loop runs as long as v_List is not blank. After the first iteration:

• v_Pos is 2
• v_Department is 3
• the DeptList table has a row with the value 3</li>
• v_List has become 4,9,11,69

Each iteration removes the first number from v_List and writes that number into the DeptList table. The loop ends after the fifth iteration, when v_List has a blank value.

The body of the WHILE executes five times and DeptList has five rows (records). Now I can use the temporary table for row selection.

select * 
  from GLXACTS
 where Dept in (select Department from session.DeptList)

A WHILE loop is a WHILE loop is a WHILE loop, regardless of the syntax of a programming language. The SQL PL syntax for top-tested and bottom-tested loops is as simple as any.

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

RELATED STORIES

SQL PL–The LOOP Loop

SQL PL–The FOR Loop

Dynamic Lists In Static SQL Queries