先说一下需求：需要用SQL实现用户权限的添加。大体思路如下：
- 通过具体的权限获取到拥有此权限的菜单
- 通过菜单获取到拥有此菜单的用户
- 在权限表中使用上面两步查询到的菜单和用户，添加新的权限（action_dispatch_car）
分布实现的话如下：
-- 汽车调度按钮
SELECT menu_no FROM menu_action WHERE action_no = 'action_dispatch_car'
-- cheliangyudiaodu_50
-- cheliangyudiaodu_34
-- 拥有车辆预调度页面的用户
SELECT role_no FROM role_menu WHERE menu_no = 'cheliangyudiaodu_34'
SELECT role_no FROM role_menu WHERE menu_no = 'cheliangyudiaodu_50'
-- 添加 派车/改派的按钮
INSERT INTO role_menu_action(role_no,menu_no,action_no) VALUES(role_no,'cheliangyudiaodu_34','action_dispatch_car')
缺点：增加只能一条一条的添加。
改进：使用子查询批量插入
-- 汽车调度按钮
SELECT menu_no FROM menu_action WHERE action_no = 'action_dispatch_car'
-- cheliangyudiaodu_50
-- cheliangyudiaodu_34
INSERT INTO role_menu_action(role_no,menu_no,action_no)
SELECT role_no,'cheliangyudiaodu_34' menu_no ,'action_dispatch_car' action_no FROM role_menu WHERE menu_no = 'cheliangyudiaodu_34'
INSERT INTO role_menu_action(role_no,menu_no,action_no)
SELECT role_no,'cheliangyudiaodu_50' menu_no ,'action_dispatch_car' action_no FROM role_menu WHERE menu_no = 'cheliangyudiaodu_50'
优点：三条sql做一个简单的修改就能实现需求
缺点：查询菜单的sql还是要手动修改参数
再改进：使用存储过程
-- 创建插入数据的存储过程
DROP procedure IF EXISTS `addAction`;
CREATE PROCEDURE addAction(IN action_no VARCHAR(50))
BEGIN
INSERT INTO role_menu_action(role_no,menu_no,action_no)
SELECT role_no, action_no menu_no ,'action_dispatch_car' action_no FROM role_menu WHERE menu_no = action_no;
END
-- CALL addAction('cheliangyudiaodu_34')
-- 创建添加查询菜单的sql和调调用上面定义的插入的存储过程
DROP procedure IF EXISTS `addActionEnd`;
CREATE PROCEDURE addActionEnd(IN action_no_name VARCHAR(50))
BEGIN
DECLARE s int DEFAULT 0;
DECLARE menu_no_cur varchar(255);
DECLARE report CURSOR FOR SELECT menu_no FROM menu_action WHERE `action_no` = action_no_name;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open report;
fetch report into menu_no_cur;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
CALL addAction(menu_no_cur);
-- 将游标中的值再赋值给变量，供下次循环使用
fetch report into menu_no_cur;
end while;
END
-- 直接调用
CALL addActionEnd('action_dispatch_car')优点：实现一体化操作
缺点：部分参数写死，不具有通用性
最终改进：参数传递，具有通用性
DROP procedure IF EXISTS `addAction`;
CREATE PROCEDURE addAction(IN menu_name VARCHAR(50),IN action_name VARCHAR(50))
BEGIN
INSERT INTO role_menu_action(role_no,menu_no,action_no)
SELECT role_no, menu_name menu_no ,action_name action_no FROM role_menu WHERE menu_no = menu_name;
END
-- CALL addAction('cheliangyudiaodu_34')
DROP procedure IF EXISTS `addActionEnd`;
CREATE PROCEDURE addActionEnd(IN action_no_name VARCHAR(50))
BEGIN
DECLARE s int DEFAULT 0;
DECLARE menu_no_cur varchar(255);
DECLARE report CURSOR FOR SELECT menu_no FROM menu_action WHERE `action_no` = action_no_name;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
open report;
fetch report into menu_no_cur;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
CALL addAction(menu_no_cur,action_no_name);
-- 将游标中的值再赋值给变量，供下次循环使用
fetch report into menu_no_cur;
end while;
END
CALL addActionEnd('action_dispatch_car')优点：只需要传递 权限参数，就可以给所有用户添加 当前权限
附加，参考的游标相关的代码：
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS init_reportUrl;
-- 创建存储过程
CREATE PROCEDURE init_reportUrl()
BEGIN
-- 定义变量
DECLARE s int DEFAULT 0;
DECLARE report_id varchar(255);
DECLARE report_url varchar(256);
-- 定义游标，并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select reportId,reportUrl from patrolReportHistory;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 打开游标
open report;
-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
fetch report into report_id,report_url;
-- 当s不等于1，也就是未遍历完时，会一直循环
while s<>1 do
-- 执行业务逻辑
update patrolreporthistory set reportUrl = CONCAT('patrolReport.html?monitorId=',substring(report_url,15,1),'&reportId=',report_id) where reportId=report_id;
-- 将游标中的值再赋值给变量，供下次循环使用
fetch report into report_id,report_url;
-- 当s等于1时表明遍历以完成，退出循环
end while;
-- 关闭游标
close report;
END;存储过程输出测试代码：
-- 测试
DROP procedure IF EXISTS `test`;
CREATE PROCEDURE test(OUT temp VARCHAR(50))
BEGIN
SELECT 80 INTO temp;
-- set temp=2;
SELECT temp;
END
CALL test(@temp)
Comments | NOTHING