以下是在 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)
登录后复制
以上就是如何在MySQL存储过程中使用FOR LOOP?的详细内容,更多请关注每日运维网(www.mryunwei.com)其它相关文章!