LOOP statement

The LOOP statement executes a statement or group of statements multiple times.

Syntax

Notes:

¹ Only one label: can be specified for each SQL-procedure-statement.

Description

label: Specifies the label for the LOOP statement. If the ending label is specified, the beginning label must be specified, and the two must match.
A label name cannot be the same as the name of the SQL procedure in which the label is used.
SQL-procedure-statement: Specifies the statements to be executed in the loop. The statement must be one of the statements listed under SQL-procedure-statement (external).

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 EXIT 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