Add 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.

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

Figure 1. Routine to execute the WHILE loop as long as the condition specified in the WHILE statement is true.

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 the previous figure 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.

Be careful that you do not create an endless loop, as the following figure shows.

Figure 2. Routine to accept an integer as an argument and then insert an integer value into the numbers column.

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. The following figure 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 3. Defining iterations in the FOR loop.

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.

Tip: 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.