如何在 MySQL 存储过程中使用 FOR LOOP?
mysqlmysqli database
以下是在 MySQL 存储过程中使用 FOR LOOP 的语法 −
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 -> // Query OK, 0 rows affected (0.37 sec) 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) Query OK, 0 rows affected (0.01 sec)
