LEAVE statement
The LEAVE statement transfers program control out of a FOR, LOOP, REPEAT, WHILE, or compound statement.
Syntax
Description
- label
- Specifies the label for the LEAVE statement. The label name cannot be the same as the name of the SQL routine or the same as another label that is within the same scope. For additional information, see References to labels.
- target-label
- Specifies the label of the compound, FOR, LOOP, REPEAT, or WHILE
statement to exit. target-label must be
defined as a label for a compound, FOR, LOOP, REPEAT, or WHILE statement,
or in a block of code that is directly or indirectly nested within
that statement, subject to the following rules:
- If the LEAVE statement is in a condition handler, target-label must be defined in that condition handler.
- If the LEAVE statement is not in a condition handler, target-label must not be defined in a condition handler.
Notes
Effect on open cursors: 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
Example 1: The 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 Example 2: A LEAVE statement can be issued from
a nested block to leave a statement at a higher level. In the following
example, the LEAVE statement within the LAB2 compound statement causes
the LAB1 LOOP statement to terminate:
LAB1: LOOP
...
LAB2: BEGIN
SET A = 0;
...
LAB3: BEGIN
...
LEAVE LAB1; -- Multilevel LEAVE
...
END LAB3;
...
LEAVE LAB1; -- Multilevel LEAVE
...
END LAB2;
END LOOP;S