-- 分隔标记
delimiter $$
-- 创建存储过程
create procedure testCur()
BEGIN
-- 声明变量
DECLARE uid varchar(40);
DECLARE pid varchar(40);
DECLARE pcode varchar(40);
DECLARE done INT DEFAULT 0;-- 创建游标,并设置游标所指的数据(这里设置ID不为1是因为ID为1的是总的大类)
DECLARE cur CURSOR for
select a.user_id AS uid,a.project_id AS pid,p.project_code AS pcode
from acl_role_users a
left join acl_projects p on a.project_id = p.id
where p.project_code is not null;-- 游标执行完,即遍历结束。设置done的值为1
DECLARE CONTINUE HANDLER for not FOUND set done=1;-- 开启游标
open cur;-- 执行循环
posLoop:LOOP
-- 从游标中取出uid,pid,pcode
FETCH cur INTO uid,pid,pcode;-- 如果done的值为1,即遍历结束,结束循环
IF done=1 THEN
LEAVE posLoop;-- 注意,if语句需要添加END IF结束IF
END IF;-- 以游标中取出的cid为索引,逐行更新分类表:某分类parent_ids值为它的父分类parent_ids值和它父类的id
INSERT INTO `acl_user_project` (
`id`,
`user_id`,
`project_id`,
`project_code`,
`create_dtme`,
`last_updtme`,
`create_user_id`,
`last_update_user_id`,
`cancelsign`
)
VALUES
(UUID(),
uid,
pid,
pcode,NOW(),NOW(),'d1a00cb2-8cec-11e9-aba6-005056b4a62f','d1a00cb2-8cec-11e9-aba6-005056b4a62f','n');-- 关闭循环
END LOOP posLoop;-- 关闭游标
CLOSE cur;-- 关闭分隔标记
END