Looping in Store Procedure MySql

Summary: In this tutorial, you will learn how to use various loop statements including WHILE, REPEAT and LOOP to run a block of code repeatedly in MySQL.
MySQL stored programming language supports loop which allows you to process commands iteratively. The standard loops are discuss as follows

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 supports a LOOP loop which allows you to execute statements repeatedly and 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$$  
 DELIMITER ;  

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 below it and continues, if the variable x is even, the block after ELSE constructs strings with even numbers.

reference : http://www.mysqltutorial.org/stored-procedures-loop.aspx