Adding WHILE and FOR Loops

Both the WHILE and FOR statements create execution loops in SPL routines. A WHILE loop starts with a WHILE condition, executes a block of statements as long as the condition is true, and ends with END WHILE.

Figure 411 shows a valid WHILE condition. The routine executes the WHILE loop as long as the condition specified in the WHILE statement is true.

Figure 411.

CREATE PROCEDURE test_rows( num INT )

   DEFINE i INTEGER;
   LET i = 1;

   WHILE i < num
      INSERT INTO table1 (numbers) VALUES (i);
      LET i = i + 1;
   END WHILE;

END PROCEDURE;

The SPL routine in Figure 411 accepts an integer as an argument and then inserts an integer value into the numbers column of table1 each time it executes the WHILE loop. The values inserted start at 1 and increase to num - 1.

Figure 412.

CREATE PROCEDURE endless_loop()

   DEFINE i INTEGER;
   LET i = 1;
   WHILE ( 1 = 1 )            -- don't do this!
      LET i = i + 1;
      INSERT INTO table1 VALUES (i);
   END WHILE;

END PROCEDURE;

A FOR loop extends from a FOR statement to an END FOR statement and executes for a specified number of iterations, which are defined in the FOR statement. Figure 413 shows several ways to define the iterations in the FOR loop.

For each iteration of the FOR loop, the iteration variable (declared as i in the examples that follow) is reset, and the statements within the loop are executed with the new value of the variable.

Figure 413.

FOR i = 1 TO 10
. . .
END FOR;

FOR i = 1 TO 10 STEP 2
. . .
END FOR;

FOR i IN (2,4,8,14,22,32)
. . .
END FOR;

FOR i IN (1 TO 20 STEP 5, 20 to 1 STEP -5, 1,2,3,4,5)
. . .
END FOR:

In the first example, the SPL procedure executes the FOR loop as long as i is between 1 and 10, inclusive. In the second example, i steps from 1 to 3, 5, 7, and so on, but never exceeds 10. The third example checks whether i is within a defined set of values. In the fourth example, the SPL procedure executes the loop when i is 1, 6, 11, 16, 20, 15, 10, 5, 1, 2, 3, 4, or 5—in other words, 13 times.

The main difference between a WHILE loop and a FOR loop is that a FOR loop is guaranteed to finish, but a WHILE loop is not. The FOR statement specifies the exact number of times the loop executes, unless a statement causes the routine to exit the loop. With WHILE, it is possible to create an endless loop.