如何在MySQL存储过程中使用FOR LOOP？
以下是在MySQL存储过程中使用FORLOOP的语法-
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
//现在，您可以实现上述语法。for循环查询如下-
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 -> // mysql> delimiter ;
上面的for循环打印1至10，即以下形式的1,2,3,4，.....10。使用CALL命令调用存储过程。语法如下-
call yourStoredProcedureName();
要调用的查询如下-
mysql> call ForLoop();
输出结果
+-----------------------+ | result | +-----------------------+ | 1,2,3,4,5,6,7,8,9,10, | +-----------------------+ 1 row in set (0.00 sec)