LEAVE statement

The LEAVE statement transfers program control out of a FOR, LOOP, REPEAT, WHILE, or compound statement.

Syntax

Read syntax diagramSkip visual syntax diagramlabel:LEAVE target-label

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