Control Statements › LOOP

LOOP

The LOOP statement repeats the execution of a statement or a group of statements.

Syntax

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

 ►──┬─────────────┬───────────────────────────────────────────────────────────►◄
    └─ end-label ─┘

Parameters

beg-label:

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

LOOP procedure-statement END LOOP

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

end-label

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

Usage

How execution of a LOOP statement ends

To end the repeated execution of the procedure-statements contained in a LOOP statement, a LEAVE statement can be used or an exit handler can be driven.

Example

See the example for the LEAVE statement. The procedure USER01.TLOOP1, is similar to USER01.TLEAVE1 but it uses an exit handler to terminate the LOOP.

set options command delimiter '++';
create procedure USER01.TLOOP1
  ( TITLE     varchar(10) with default
  , P_FNAME   char(20)
  , P_COUNT   integer
  , RESULT    varchar(30)
  )
    EXTERNAL NAME TLOOP1 LANGUAGE SQL
Label_700:
 /*
 ** Count number of employees with equal Firstname
 */
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;
  declare exit handler for SQLEXCEPTION, SQLWARNING, NOT FOUND
      set RESULT = 'SQLSTATE: ' || SQLSTATE;
 /*
 ** Count number of employees with equal Firstname
 */
  open EMP1;
  fetch EMP1 into FNAME, LNAME;

  fetching_loop:
  loop
      set P_COUNT = P_COUNT + 1;
      fetch EMP1 into FNAME, LNAME;
    end loop fetching_loop;
end
++
commit++
set options command delimiter default++

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