Control Statements › WHILE

WHILE

The WHILE statement repeats the execution of a statement or a group of statements while a condition is met.

Syntax

►►─┬──────────────┬───── WHILE ─── search-condition ── DO ─────────────────────►
   └─ beg-label: ─┘

     ┌──────────────────────────────┐
 ►── ▼ ─── procedure-statement ─ ; ─┴───── END WHILE ───────┬─────────────┬────►◄
                                                            └─ end-label ─┘

Parameters

beg-label:

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

WHILE search-condition

Specifies the search condition to be evaluated. If the outcome is false, the statement after the WHILE statement is executed. Otherwise, an iteration of the group of statements enclosed by DO and END WHILE is started.

DO procedure-statement END WHILE

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

end-label

Specifies an SQL identifier that labels the end of the WHILE 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.TWHILE2
  ( TITLE     varchar(10) with default
  , P_FNAME   char(20)
  , P_COUNT   integer
  )
    EXTERNAL NAME TWHILE2 LANGUAGE SQL
Label_700:
begin not atomic
 /*
 ** Count number of employees with equal first name
 */
  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;
  set P_COUNT = 0;
  open EMP1;
  fetch EMP1 into FNAME, LNAME;
  fetching_loop_non_SQL:
  while (SQLSTATE = '00000')
    do
      set P_COUNT = P_COUNT + 1;
      fetch EMP1 into FNAME, LNAME;
    end while fetching_loop_non_SQL;

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

call USER01.TWHILE2('TWHILE2','Martin  ');
;
*+
*+ TITLE       P_FNAME                   P_COUNT
*+ -----       -------                   -------
*+ TWHILE2     Martin                          3