Examples

This procedure uses a LOOP statement to fetch values from the employee table. Each time the loop iterates, the OUT parameter counter is incremented and the value of v_midinit is checked to ensure that the value is not a single space (' '). If v_midinit is a single space, the LEAVE statement passes the flow of control outside of the loop.

 CREATE PROCEDURE LOOP_UNTIL_SPACE (OUT COUNTER INTEGER)
    LANGUAGE SQL
    BEGIN
       DECLARE v_counter INTEGER DEFAULT 0;
       DECLARE v_firstnme VARCHAR(12);
       DECLARE v_midinit CHAR(1);
       DECLARE v_lastname VARCHAR(15);
       DECLARE c1 CURSOR FOR
          SELECT firstnme, midinit, lastname
          FROM employee;
       DECLARE CONTINUE HANDLER FOR NOT FOUND
          SET counter = -1;
       OPEN c1;
       fetch_loop:
       LOOP
          FETCH c1 INTO v_firstnme, v_midinit, v_lastname;
          IF v_midinit = ' ' THEN
             LEAVE fetch_loop;
          END IF;
          SET v_counter = v_counter + 1;
       END LOOP fetch_loop;
       SET counter = v_counter;
       CLOSE c1;
    END