How to use FOR LOOP in MySQL stored procedure?

The following is the syntax for using FOR LOOP in a MySQL stored procedure -

delimiter //
CREATE procedure yourProcedureName()
wholeblock:BEGIN
   DECLARE anyVariableName1 INT ;
   Declare anyVariableName3 int;
   DECLARE anyVariableName2 VARCHAR(255);
   SET anyVariableName1 =1 ;
   SET anyVariableName3 =10;
   SET anyVariableName2 = '';
loop_label: FORLOOP
   IF anyVariableName1 > anyVariableName3 THEN
      LEAVE loop_label;
   END IF;
   SET anyVariableName2 = CONCAT(anyVariableName2 ,anyVariableName1 ,',');
   SET anyVariableName1 = anyVariableName1 + 1;
   ITERATE loop_label;
   END FORLOOP;
SELECT anyVariableName2;
END
//

Now you can implement the above syntax. The for loop query is as follows -

mysql> delimiter //
mysql> CREATE procedure ForLoop()
   -> wholeblock:BEGIN
   -> DECLARE start INT ;
   -> Declare maxLimit int;
   -> DECLARE result VARCHAR(255);
   -> SET start =1 ;
   -> SET maxLimit=10;
   -> SET result = '';
   -> loop_label: LOOP
   -> IF start > 10 THEN
   -> LEAVE loop_label;
   -> END IF;
   -> SET result = CONCAT(result,start,',');
   -> SET start = start + 1;
   -> ITERATE loop_label;   
   -> END LOOP;
   -> SELECT result;
   -> END
   -> //
Query OK, 0 rows affected (0.37 sec)
mysql> delimiter ;

The for loop above prints 1 to 10, that is, in the following form 1,2,3,4,...10. call stored Procedure for using the CALL command. The syntax is as follows -

call yourStoredProcedureName();

The query called is as follows -

mysql> call ForLoop();

Output

+-----------------------+
| result                |
+-----------------------+
| 1,2,3,4,5,6,7,8,9,10, |
+-----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

The above is the detailed content of How to use FOR LOOP in MySQL stored procedure?. For more information, please follow other related articles on the PHP Chinese website!