DECLARE
s
int
DEFAULT
0
;
DECLARE
fix_role
bigint
(
20
);
DECLARE
fix_menu
bigint
(
20
);
--
定义游标，并将sql结果集赋值到游标中
DECLARE
role_menu
CURSOR
FOR
select
role_id,menu_id
from
system_role_menu
GROUP
BY
role_id,menu_id
HAVING
count
(menu_id)
>
1
;
--
声明当游标遍历完后将标志变量置成某个值
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
s
=
1
;
--
打开游标
open
role_menu;
--
将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch
role_menu
into
fix_role,fix_menu;
--
当s不等于1，也就是未遍历完时，会一直循环
while
s
<>
1
do
--
执行业务逻辑
delete
from
system_role_menu
where
role_id
=
fix_role
and
menu_id
=
fix_menu;
INSERT
INTO
system_role_menu (role_id,menu_id,tick)
VALUES
(fix_role,fix_menu,
1
);
--
将游标中的值再赋值给变量，供下次循环使用
fetch
role_menu
into
fix_role,fix_menu;
--
当s等于1时表明遍历以完成，退出循环
end
while
;
--
关闭游标
close
role_menu;
END
;
--
执行存储过程
call fix_role_menu();
