随笔记录
需求为:需要初始化表user_authority,从user表中读取user_name字段,并根据role_type对user_authority表中的authority字段进行赋值
drop procedure if exists init_user_authority_proc;
create procedure init_user_authority_proc()
begin
declare var1 varchar(128);
declare flag int default 0;
-- 定义一个游标来记录sql查询的结果
declare cur cursor for SELECT id FROM user;
-- 循环结束标识
declare continue handler for not found set flag = 1;
-- 打开游标
open cur;
-- 将游标中的值赋给定义好的变量
fetch cur into var1;
while flag <> 1
do
INSERT INTO user_authority(`user_name`,`module`,`authority`) select user_name, 'phy_node', role_type =1 from user where id =var1;
INSERT INTO user_authority(`user_name`,`module`,`authority`) select user_name, 'phy_device', role_type =1 from user where id =var1;
INSERT INTO user_authority(`user_name`,`module`,`authority`) select user_name, 'phy_link', role_type =1 from user where id =var1;
-- 游标往后移
fetch cur into var1;
end while;
-- 关闭游标
close cur;
end;
-- 执行
call init_user_authority_proc();
需要改成可重复执行