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