Loop with ITERATE Statement : ITERATE « Procedure Function « MySQL Tutorial

1. MySQL Tutorial
2. Procedure Function
3. ITERATE

ITERATE label

ITERATE can appear only within LOOP, REPEAT, and WHILE statements.

ITERATE means "do the loop again."

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

You can use ITERATE in a LOOP, WHILE, or REPEAT control to indicate that the control should iterate through the statements in the loop again.

mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE myProc (IN in_count INT)
    -> BEGIN
    ->     DECLARE count INT default 0;
    ->
    ->     increment: LOOP
    ->     SET count = count + 1;
    ->     IF count < 20 THEN ITERATE increment; END IF;
    ->     IF count > in_count THEN LEAVE increment;
    ->     END IF;
    ->     END LOOP increment;
    ->
    ->     SELECT count;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> call myProc(5);
+-------+
| count |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

mysql>