Loop in MySQL stored procedures

MySQL allows you to loop statement for executing a block repeatedly based on a condition. These are like WHILE, REPEAT and LOOP.

WHILE LOOP:

It checks the expression at the beginning of each iteration.

Example:

DELIMITER $$
CREATE PROCEDURE MyWhileLoop()
BEGIN
DECLARE x  INT;
SET x = 1;
WHILE x  <= 10 DO
SET  x = x + 1;
END WHILE;
SELECT x;
END$$
DELIMITER;

REPEAT LOOP:

It checks the expression after the execution of statements.

DELIMITER $$
CREATE PROCEDURE MyRepeatLoop()
BEGIN
DECLARE x  INT;
SET x = 1;
REPEAT
SET  x = x + 1;
UNTIL x  >= 10
END REPEAT;
SELECT x;
END$$
DELIMITER;

LOOP:

It execute a statement repeatedly with an additional option.

DELIMITER $$
CREATE PROCEDURE MyLOOPLoop()
BEGIN
DECLARE x  INT;
SET x = 1;
loop_label:  LOOP
IF  x >= 10 THEN
LEAVE  loop_label;
END  IF;
IF  (x mod 2) THEN
ITERATE  loop_label;
ELSE
SET  x = x + 1;
END  IF;

END LOOP;
SELECT x;
END$$
DELIMITER ;