(1)                       (2)
>>-WHILE--| Condition |-------| Statement Block |--------------->
 
>--END WHILE--+---+--------------------------------------------><
              '-;-'
 

1. See Condition
2. See Statement Block

The condition is evaluated before the statement block first runs and before each subsequent iteration. Iterations continue as long as the condition remains true. The loop terminates when the condition evaluates to not true.

If any expression within the condition evaluates to NULL, the condition becomes not true unless you are explicitly testing for NULL with the IS NULL operator.

If an expression within the condition has an UNKNOWN value because it references uninitialized SPL variables, an immediate error results. In this case, the loop terminates, raising an exception.

The following example illustrates the use of WHILE loops in an SPL routine. In the SPL procedure, simp_while, the first WHILE loop executes a DELETE statement. The second WHILE loop executes an INSERT statement and increments the value of an SPL variable.

CREATE PROCEDURE simp_while()
   DEFINE i INT;
   WHILE EXISTS (SELECT fname FROM customer
       WHERE customer_num > 400)
      DELETE FROM customer WHERE id_2 = 2;
   END WHILE;
   LET i = 1;
   WHILE i < 10
      INSERT INTO tab_2 VALUES (i);
      LET i = i + 1;
   END WHILE;
END PROCEDURE