注意每个版本的mysq的存储过程,触发器写法都会有些许区别,注意查看官方版本,不然你网上copy的语句可能执行无效,或者不成功
官方英文说明文档
https://dev.mysql.com/doc/refman/5.7/en/cursors.html
demo 数据版本mysql 5.7.22-log
如果需要debug你写的存储过程推荐:
dbForge Studio for MySQL 这个工具挺好使的
使用请参考
https://blog.csdn.net/weixin_42740530/article/details/94553996
CREATE DEFINER = "zx"@"%"
PROCEDURE `v2-20200212`.zx11 ()
BEGIN
-- 声明一些需要用到的变量
DECLARE done int DEFAULT FALSE;
DECLARE z_id int DEFAULT 0;
DECLARE z_admin_login_name varchar(255);
DECLARE z_remark varchar(255);
-- 声明游标
DECLARE admin_list CURSOR FOR
SELECT
`id`,
`admin_login_name`,
`remark`
FROM admin;
-- 声明 是否没有记录
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN admin_list;
-- 开始循环
read_loop:
LOOP
-- 注意声明变量类型和接受数据的顺序一致
FETCH admin_list INTO z_id, z_admin_login_name, z_remark;
-- 如果没有结果集就退出循环
IF done THEN
LEAVE read_loop;
END IF;
-- 具体执行的sql语句
UPDATE admin
SET remark = z_id
WHERE id = z_id;
-- 结束循环
END LOOP;
-- 关闭游标
CLOSE admin_list;
END
官方demo
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(16);
DECLARE b, c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
OPEN cur2;
read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END;