Control Statements › REPEAT

REPEAT

The REPEAT statement repeats the execution of a statement or a group of statements until a condition is met.

Syntax

                                     ┌────────────────────────────┐
►►──┬──────────────┬──── REPEAT ──── ▼ ─ procedure-statement ─ ; ─┴────────────►
    └─ beg-label: ─┘

 ►── UNTIL ── search-condition ── END REPEAT ──┬─────────────┬─────────────────►◄
                                               └─ end-label ─┘

Parameters

beg-label:

Specifies a 1- through 32-character SQL identifier that labels the REPEAT statement. The value must be different from any other label used in the compound statement if the REPEAT statement is contained in a compound statement.

REPEAT procedure-statement

Specifies the statement or group of statements that are repeatedly executed.

UNTIL search-condition

Specifies the search condition that is evaluated after each iteration. If the outcome is true, the statement following the REPEAT statement is executed. Otherwise, a new iteration starts.

end-label

Specifies an SQL identifier that labels the end of the REPEAT statement. If specified, a beg-label must also have been specified and both labels must be equal.

Example

set options command delimiter '++';
create procedure USER01.TREPEAT1
  ( TITLE     varchar(10) with default
  , P_FNAME   char(20)
  , P_COUNT   integer
  , RESULT    varchar(25)
  )
    EXTERNAL NAME TREPEAT1 LANGUAGE SQL
Label_700:
 /*
 ** Count number of employees with equal First name using REPEAT
 */
begin not atomic
  declare FNAME   char(20);
  declare LNAME   varchar(20);
  declare EMP1 CURSOR FOR
        Select EMP_FNAME, EMP_LNAME
          From DEMOEMPL.EMPLOYEE
         where EMP_FNAME = P_FNAME;

  open EMP1;

  fetching_loop:
  repeat
    fetch EMP1 into FNAME, LNAME;

    if (SQLSTATE = '00000')
      then set P_COUNT = P_COUNT + 1;
    end if;
  until SQLSTATE < > '00000'
  end repeat fetching_loop;
  set RESULT = 'SQLSTATE: ' || SQLSTATE;

  close EMP1;
end
++
commit++
set options command delimiter default++

call USER01.TREPEAT1('TREPEAT1','Martin',0,'U');
*+
*+ TITLE       P_FNAME                   P_COUNT  RESULT
*+ -----       -------                   -------  ------
*+ TREPEAT1    Martin                          3  SQLSTATE: 02000