DB2 10.5 for Linux, UNIX, and Windows

REPEAT statement

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

Invocation

This statement can be embedded in an:

SQL procedure definition
Compound SQL (compiled) statement
Compound SQL (inlined) statement

The compound statements can be embedded in an SQL procedure definition, SQL function definition, or SQL trigger definition. It is not an executable statement and cannot be dynamically prepared.

Authorization

No privileges are required to invoke the REPEAT statement. However, the authorization ID of the statement must hold the necessary privileges to invoke the SQL statements and search condition that are embedded in the REPEAT statement.

Syntax


>>-+--------+--REPEAT--| SQL-routine-statement |---------------->
   '-label:-'                                      

>--UNTIL--search-condition--END REPEAT--+-------+--------------><
                                        '-label-'   

SQL-routine-statement

     .----------------------------.        
     V                            |        
|--+---SQL-procedure-statement--;-+----+------------------------|
   | .-------------------------------. |   
   | V                               | |   
   '---| SQL-function-statement |--;-+-'

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 statements to execute within the loop. SQL-procedure-statement is only applicable when in the context of an SQL procedure or a compound SQL (compiled) statement. See SQL-procedure-statement in "Compound SQL (compiled)" statement.
SQL-function-statement: Specifies the SQL statements to execute within the loop. SQL-function-statement is only applicable when in the context of an SQL trigger, SQL function, or SQL method. See SQL-function-statement in "FOR".
search-condition: The search-condition is evaluated after each execution of the REPEAT loop. If the condition is true, the loop will exit. If the condition is unknown or false, the looping continues.

Example

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