Control Statements › LEAVE

LEAVE

The LEAVE statement continues execution with the statement that immediately follows the specified labeled statement.

Syntax

►►── LEAVE ── stmnt-label ────────────────────────────────────────────────────►◄

Parameters

stmnt-label

Specifies the begin label of a statement that contains the LEAVE statement, and identifies the statement that needs to be left.

Usage

Statements that may be left: The labeled statement referred in the LEAVE must be a LOOP, REPEAT, WHILE or compound statement that contains the LEAVE statement.

Example

set options command delimiter '++';
create procedure USER01.TLEAVE1
  ( TITLE     varchar(10) with default
  , P_FNAME   char(20)
  , P_COUNT   integer
  , RESULT    varchar(25)
  )
    EXTERNAL NAME TLEAVE1 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;

  open EMP1;
  fetch EMP1 into FNAME, LNAME;
  fetching_loop:
  loop
    if (SQLSTATE < > '00000')
      then leave fetching_loop;
    end if;
    set P_COUNT = P_COUNT + 1;
    fetch EMP1 into FNAME, LNAME;
  end loop fetching_loop;

  set RESULT = 'SQLSTATE: ' || SQLSTATE;
  close EMP1;
end
++
commit++
set options command delimiter default++

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