SQL Reference

LEAVE Statement

The LEAVE statement transfers program control out of a loop or a compound statement.

Syntax

>>-LEAVE--label------------------------------------------------><
 

Description

label

Specifies the label of the compound, FOR, LOOP, REPEAT, or WHILE statement to exit.

Rules

When a LEAVE statement transfers control out of a compound statement, all open cursors in the compound statement, except cursors that are used to return result sets, are closed.

Examples

This example contains a loop that fetches data for cursor c1. If the value of SQL variable at_end is not zero, the LEAVE statement transfers control out of the loop.

     CREATE PROCEDURE LEAVE_LOOP(OUT counter INTEGER)
        LANGUAGE SQL
        BEGIN
           DECLARE v_counter INTEGER;
           DECLARE v_firstnme VARCHAR(12);
           DECLARE v_midinit CHAR(1);
           DECLARE v_lastname VARCHAR(15);
           DECLARE at_end SMALLINT DEFAULT 0;
           DECLARE not_found CONDITION FOR SQLSTATE '02000';
           DECLARE c1 CURSOR FOR 
              SELECT firstnme, midinit, lastname 
                 FROM employee;
           DECLARE CONTINUE HANDLER for not_found 
              SET at_end = 1;
           SET v_counter = 0;
           OPEN c1;
           fetch_loop:
           LOOP
              FETCH c1 INTO v_firstnme, v_midinit, v_lastname;
              IF at_end <> 0 THEN LEAVE fetch_loop;
              END IF;
              SET v_counter = v_counter + 1;
           END LOOP fetch_loop;
           SET counter = v_counter;
           CLOSE c1;
        END