While & Repeat loop in Stored Procedure

WHILE loop
------------------------------
The syntax of while loop is as follows:
WHILE expression DO
Statements
END WHILE
First the while loop checks the expression, if it is true it will executes statement until the expression become false. Because while loop checks the expression before statements executed, it is often known as pretest loop. Here is an example of using while loop in stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS WhileLoopProc$$
CREATE PROCEDURE WhileLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END$$
DELIMITER ;

In stored procedures above, we build string repeatedly until the variable x greater than 5 and then we output the built string into console screen by using SELECT statement. One of common trap almost developers encounter is if the variable x is not initialized, its default value is NULL so the condition in while loop is always true; the code block inside while loop is executed indefinitively until your database server crashed.

REPEAT loop
----------------------------------
The syntax of repeat loop is as follows:
REPEAT
Statements;
UNTIL expression
END REPEAT
First the statements are executed, and then the expression is evaluated. If the expression is evaluated as true the statements are executed again and again until its value become false. Because the repeat loop checks the expression after the execution of statements so it is also known as post-test loop. We can rewrite the stored procedure above by using repeat loop as follows:
DELIMITER $$
DROP PROCEDURE IF EXISTS RepeatLoopProc$$
CREATE PROCEDURE RepeatLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
REPEAT
SET str = CONCAT(str,x,',');
SET x = x + 1;
UNTIL x > 5
END REPEAT;
SELECT str;
END$$
DELIMITER ;

Be noted that there is no delimiter (;) after UNTIL expression
LOOP loop, LEAVE and ITERATE
Leave statement allows you to leave the loop. It is a bit like break in other languages such as Java, C#...
Iterate statement allows you to start the loop again. It is like continue in Java or C#.
MySQL also support a LOOP loop which allows you to execute statements repeatedly more flexible. Here is an example of using LOOP loop.


DELIMITER $$
DROP PROCEDURE IF EXISTS LOOPLoopProc$$
CREATE PROCEDURE LOOPLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,',');
END IF;

END LOOP;
SELECT str;
END$$
The stored procedure only constructs string with even numbers. First we define a loop label, if a variable x is greater than 10 the loop is ended because of leave statement. Otherwise if the variable x is odd, the ITERATE ignores everything bellow it and continues, if the variable x is even, the block after ELSE constructs strings with even numbers.