SQL Reference

REPEAT Statement

The REPEAT statement executes a statement or group of statements until a search condition is true.

Syntax

                          .-------------------------------.
                          V                               |
>>-+---------+--REPEAT-------SQL-procedure-statement--;---+----->
   '-label:--'
 
>----UNTIL--search-condition---END REPEAT----+--------+--------><
                                             '-label--'
 

Description

label

Specifies the label for the REPEAT statement. If the beginning label is specified, that label can be specified on LEAVE and ITERATE statements. If an ending label is specified, a matching beginning label also must be specified.

SQL-procedure-statement

Specifies the SQL statement to execute with the loop.

search-condition

Specifies a condition that is evaluated before each execution of the SQL procedure statement. If the condition is false, DB2 executes the SQL procedure statement in the loop.

Examples

A REPEAT statement fetches rows from a table until the not_found condition handler is invoked.

     CREATE PROCEDURE REPEAT_STMT(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 at_end SMALLINT DEFAULT 0;
          DECLARE not_found CONDITION FOR SQLSTATE '02000';
          DECLARE c1 CURSOR FOR
             SELECT firstnme, midinit, lastname 
                FROM employee;
          DECLARE CONTINUE HANDLER FOR not_found
             SET at_end = 1;
          OPEN c1;
          fetch_loop:
          REPEAT
             FETCH c1 INTO v_firstnme, v_midinit, v_lastname;
             SET v_counter = v_counter + 1;
             UNTIL at_end > 0
          END REPEAT fetch_loop;
          SET counter = v_counter;
          CLOSE c1;
       END