LEAVE statement
The LEAVE statement continues execution by leaving a block or loop.
Syntax
Description
- label1
- Specifies the label for the LEAVE statement. The label name cannot be the same as the routine name or another label within the same scope. For more information, see References to SQL labels.
- label2
- Specifies the label of the compound, FOR, LOOP, REPEAT, or WHILE
statement to exit.
The LEAVE statement cannot be used to leave a handler.
Notes
Effect on open cursors: When a LEAVE statement transfers control out of a compound statement, all open cursors that are declared in the compound statement that contains the LEAVE statement are closed, unless they are declared to return result sets or unless *ENDACTGRP is specified.
Considerations for SQLSTATE and SQLCODE variables: The LEAVE statement does not affect the SQLSTATE and SQLCODE SQL variables. At the end of the LEAVE statement the SQLSTATE and SQLCODE SQL variables reflect the result of the last statement executed before that LEAVE statement.
Examples
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