LOOP statement

The LOOP statement executes a statement or group of statements multiple times.

Syntax


                     .----------------------------.             
                     V                            |             
>>-+--------+--LOOP----SQL-procedure-statement--;-+--END LOOP--->
   '-label:-'                                                   

>--+-------+---------------------------------------------------><
   '-label-'

Description

label: Specifies the label for the LOOP statement. If the ending label is specified, a matching beginning label must be specified. A label name cannot be the same as the name of the SQL routine or another label within the same scope. For additional information, see References to labels.
SQL-procedure-statement: Specifies an SQL statement to be executed in the loop. The statement must be one of the statements listed under SQL-procedure-statement.

Notes

Considerations for the diagnostics area: At the beginning of the first iteration of the LOOP statement, and with every subsequent iteration, the diagnostics area is cleared.

Considerations for the SQLSTATE and SQLCODE SQL variables: Prior to executing the first SQL-procedure-statement within that LOOP statement, the SQLSTATE and SQLCODE values reflect the last values that were set prior to the LOOP statement. If the loop is terminated with a GOTO or a LEAVE statement, the SQLSTATE and SQLCODE values reflect successful completion of that statement. When the LOOP statement iterates, the SQLSTATE and SQLCODE values reflect the result of the last SQL statement that is executed within the LOOP statement.

Examples

This procedure uses a LOOP statement to fetch values from the employee table. Each time the loop iterates, the OUT parameter counter is incremented and the value of v_midinit is checked to ensure that the value is not a single space (' '). If v_midinit is a single space, the LEAVE statement passes the flow of control outside of the loop.

CREATE PROCEDURE LOOP_UNTIL_SPACE(OUT counter INTEGER) 
   LANGUAGE SQL 
   BEGIN 
      DECLARE v_counter INTEGER DEFAULT 0; 
      DECLARE v_firstnme VARCHAR(12); 
      DECLARE v_midinit CHAR(1); 
      DECLARE v_lastname VARCHAR(15); 
      DECLARE c1 CURSOR FOR 
         SELECT firstnme, midinit, lastname 
           FROM employee; 
      DECLARE EXIT HANDLER FOR NOT FOUND 
         SET counter = -1; 
      OPEN c1; 
      fetch_loop: 
      LOOP 
        FETCH c1 INTO v_firstnme, v_midinit, v_lastname; 
        IF v_midinit = ' ' THEN 
           LEAVE fetch_loop; 
        END IF; 
        SET v_counter = v_counter + 1; 
      END LOOP fetch_loop; 
      SET counter = v_counter; 
      CLOSE c1; 
   END