REPEAT statement

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

Syntax

Description

label: Specifies the label for the REPEAT statement. If the ending label is specified, it must be the same as the beginning label. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
SQL-procedure-statement: Specifies an SQL statement to be executed in the REPEAT loop.
search-condition: The search-condition is evaluated after each execution of the REPEAT loop. If the condition is true, the REPEAT loop will exit. If the condition is unknown or false, the looping continues.

Notes

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

Considerations for SQLSTATE and SQLCODE SQL variables: With each iteration of the REPEAT statement, the SQLSTATE and SQLCODE SQL variables are cleared prior to executing the first SQL-procedure-statement within the REPEAT statement. At the beginning of the first iteration of the REPEAT statement, the SQLSTATE and SQLCODE values reflect the last values that were set prior to the REPEAT statement. At the beginning of iterations 2 through n of the REPEAT statement, the SQLSTATE and SQLCODE values reflect the result of evaluating the search condition in the UNTIL clause of that REPEAT statement. If the loop is terminated with a GOTO, ITERATE, or LEAVE statement, the SQLSTATE and SQLCODE values reflect the successful completion of that statement. Otherwise, after the END REPEAT of the REPEAT statement completes, the SQLSTATE and SQLCODE values reflect the result of evaluating the search condition in the UNTIL clause of that REPEAT statement.

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