MySQL - ITERATE Statement

---

---

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets. You can also create functions in MYSQL.

Similar to other programming languages MySQL provides support for the flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. You can use these statements in the stored programs (procedures), and RETURN in stored functions. You can use one Flow Control Statement with in another.

MySQL ITERATE Statement

The ITERATE statement is used to restart the LOOP, REPEAT or, WHILE statements. It cannot be used outside these statements.

Syntax

Following is the syntax of the ITERATE statement is MySQL −

ITERATE label

Where, label is the label of the LOOP or, REPEAT or, WHILE statement.

Example 1

Following query demonstrates how to use the ITERATE statement with in a function −

DELIMITER //
CREATE FUNCTION Sample (bonus INT)
   RETURNS INT
   BEGIN
      DECLARE income INT;
      SET income = 0;
      myLabel: LOOP
         SET income = income + bonus;
         IF income < 10000 THEN
            ITERATE myLabel;
         END IF;
         LEAVE myLabel;
      END LOOP myLabel;
   RETURN income;
END; //
DELIMITER ;

You can call the above created function as shown below −

SELECT Sample(1000);

Output

Following is the output of the above query −

Sample(1000)
10000

Example 2

Following query demonstrates how to use the ITERATE statement with in a procedure −

delimiter //
CREATE procedure proc()
BEGIN
   DECLARE val INT default 15;
   DECLARE res VARCHAR(255) default '';
   label: LOOP
      IF val < 0 THEN
         LEAVE label;
      END IF;
      SET res = CONCAT(res, val, ',');
      SET val = val –1;
      ITERATE label;
   END LOOP;
   SELECT res;
END//

You can all the above procedure as shown below −

call proc//

Output

The above query produces the output shown below −

res
15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0,