1、修改自定义函数访问权限的方式
//security_type有个类型:DEFINER和INVOKER
//DEFINER:在执行存储过程前验证definer对应的用户如:root@192.168.2.%是否存在,以及是否具有执行存储过程的权限,若没有则报错
//INVOKER:在执行存储过程时判断inovker即调用该存储过程的用户是否有相应权限,若没有则报错
update mysql.proc set security_type='INVOKER' where db='数据库名' and name='自定义函数名';
2、存储过程和游标的使用
场景:将所有产品的单价都提高%10
-- 定义SQL输入的结束符为//
delimiter //
-- 定义存储过程
CREATE PROCEDURE change_price()
BEGIN
DECLARE var_id INT;
DECLARE var_price DOUBLE;
DECLARE var_new_price DOUBLE;
-- 定义游标结束标志
DECLARE done INT DEFAULT FALSE;
-- 定义游标
DECLARE cur CURSOR FOR SELECT id, price from commodity;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;
-- 循环游标值
cur_loop: LOOP
-- 获取游标值
FETCH FROM cur INTO var_id, var_price;
-- 判断游标是否结束
IF done THEN LEAVE cur_loop; END IF;
-- 设置新的价格
set var_new_price := var_price * 1.1;
-- 更新价格
UPDATE commodity SET price = var_new_price WHERE id=var_id;
END LOOP;
-- 关闭游标
CLOSE cur;
END;
//
-- 还原SQL输入的结束符
DELIMITER ;
-- 调用存储过程
CALL change_price();
-- 删除存储过程
DROP PROCEDURE change_price;
3、游标定义时表名可变的解决方案
游标定义中表名是不能为变量,但可以使用视图,间接实现游标中表名可变。
-----------------------------
-- 场景 修改id_path
-- 人员表中有org_id和id_path 例如:org_id='f9da5d54fe3248bf8282e9977b5f8a0b', id_path=''/413b2b183fc44b26a456224082f2190b/c32e0aa83ebe4ae8ba5b395006ab0308/f9da5d54fe3248bf8282e9977b5f8a0b
-- 角色表中有org_id和id_path 例如:org_id='4b07a404f59347118c0a69ef997cd82b',id_path='/413b2b183fc44b26a456224082f2190b/c32e0aa83ebe4ae8ba5b395006ab0308/f9da5d54fe3248bf8282e9977b5f8a0b/4b07a404f59347118c0a69ef997cd82b'
-- 需要将系统的所有org_id为c32e0aa83ebe4ae8ba5b395006ab0308修改成2993814831e34c7b8e00de24c25d0e4c,因此需要修改org_id和org_path
-----------------------------
-- 定义SQL输入的结束符为//
delimiter //
-- 定义存储过程
-- var_old_orgid 需要修改的机构ID
-- var_new_orgid 新机构ID
-- var_tablename 需要修改的表
CREATE PROCEDURE change_org(var_old_orgid VARCHAR(50), var_new_orgid VARCHAR(50), var_tablename VARCHAR(50))
BEGIN
-- 定义表的主键名
DECLARE var_objuid VARCHAR(50);
-- 定义idpath
DECLARE var_idpath VARCHAR(500);
-- 定义游标结束标志
DECLARE done INT DEFAULT FALSE;
-- 定义游标其中view_org_info为视图
DECLARE cur CURSOR FOR SELECT id, id_path FROM view_org_info;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 定义视图
DROP VIEW IF EXISTS view_org_info;
SET @view_sql = CONCAT("CREATE VIEW view_org_info AS SELECT objuid, org_id, id_path FROM ", var_tablename, " WHERE id_path LIKE '%", var_old_orgid, "%'");
-- 执行视图
PREPARE stmt FROM @view_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 打开游标
OPEN cur;
cur_loop: LOOP
FETCH FROM cur INTO var_objuid, var_idpath;
IF done THEN LEAVE cur_loop; END IF;
-- 替换id_path中的id
set @newIdPath := REPLACE(var_idpath, var_old_orgid, var_new_orgid);
-- 更新数据库
set @update_sql = CONCAT("UPDATE ", var_tablename, " SET id_path = '", @newIdPath, "' WHERE objuid = '", var_objuid, "'");
-- 执行sql
PREPARE stmt FROM @update_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
DROP VIEW IF EXISTS view_org_info;
END;
//
-- 还原SQL输入的结束符
DELIMITER ;
-- 调用存储过程
CALL change_org();
-- 删除存储过程
DROP PROCEDURE change_org;