FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

LEAVE

Exits a loop

Syntax
[label:]
<loop_stmt>
BEGIN
  ...
  LEAVE [label];
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list> DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>)} DO
Table 1. LEAVE Statement Parameters
Argument Description

label

Label

select_stmt

SELECT statement

condition

A logical condition returning TRUE, FALSE or UNKNOWN

The LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement.Using the optional label parameter, LEAVE can also exit an outer loop, that is, the loop labelled with label.Code continues to be executed from the first statement after the terminated loop block.

LEAVE Examples

  1. Leaving a loop if an error occurs on an insert into the NUMBERS table.The code continues to be executed from the line C = 0.

    ...
WHILE (B < 10) DO
BEGIN
  INSERT INTO NUMBERS(B)
  VALUES (:B);
  B = B + 1;
  WHEN ANY DO
  BEGIN
    EXECUTE PROCEDURE LOG_ERROR (
      CURRENT_TIMESTAMP,
      'ERROR IN B LOOP');
    LEAVE;
  END
END
C = 0;
...

  2. An example using labels in the LEAVE statement.LEAVE LOOPA terminates the outer loop and LEAVE LOOPB terminates the inner loop.Note that the plain LEAVE statement would be enough to terminate the inner loop.

    ...
STMT1 = 'SELECT NAME FROM FARMS';
LOOPA:
FOR EXECUTE STATEMENT :STMT1
INTO :FARM DO
BEGIN
  STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
  LOOPB:
  FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
  INTO :ANIMAL DO
  BEGIN
    IF (ANIMAL = 'FLUFFY') THEN
      LEAVE LOOPB;
    ELSE IF (ANIMAL = FARM) THEN
      LEAVE LOOPA;
    SUSPEND;
  END
END
...
See also

[fblangref50-psql-break], [fblangref50-psql-continue], [fblangref50-psql-exit]