Mysql的基本用法

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;