mysql 进阶（二）函数的使用
函数相当于 Java 中的有返回值函数，可以被其它模块反复调用，在调用的时候, 可以被作为表达式的一部分。必须要有返回值。函数会在数据库中一直保留，直到被删除。
语法
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
说明：
RETURN 子句说明函数返回值的数据类型, 这是与存储过程的区别之一。
存在同名函数不会替换，只能删除已存在的函数才能创建。
函数列表查询
语法：
select * from mysql.proc where db=数据库名 and type='function';
例如：
SELECT * from mysql.proc where db= 'test' and type='function';
函数详情查询
语法：
SHOW CREATE FUNCTION 数据库名.函数名;
例如：
SHOW CREATE FUNCTION test.func_getLog;
函数修改
语法：
ALTER FUNCTION fun_name [characteristic ...]
fun_name 参数表示函数的名称；
characteristic 参数指定存储函数的特性，可能的取值有:
CONTAINS SQL，表示子程序包含 SQL 语句，但是，不包含读或写数据的语句
NO SQL，表示子程序中，不包含 SQL 语句
READS SQL DATA，表示子程序中，包含读数据的语句
MODIFIES DATA，表示子程序中，包含写数据的语句
SQL SECURITY {DEFINER | INVOKER}，指明谁有权限来执行
DEFINER，表示只有定义者，自己才能够执行
INVOKER，表示调用者可以执行
COMMENT ‘string’，表示注释信息
目前，MySQL 还不提供对已存在的函数的代码修改。
如果，一定要修改函数的内容，必须，先将函数删除之后，在重新编写代码，或者创建一个新的函数。
函数调用
mysql 函数可以像表的一个字段一样使用 select 调用。
函数删除
语法
DROP FUNCTION [ IF EXISTS ] fun_name;
其中，fun_name 是函数的名称。
示例
例 1
创建
DROP FUNCTION IF EXISTS func_getLog;
DELIMITER ;;
create function func_getLog ( )
returns mediumtext
begin
return (select msg from log limit 0,1);
end
;;
DELIMITER ;
调用
select func_getLog() from dual;
删除
drop function func_getLog;
例 2
-- ----------------------------
-- 时间：2019年4月25日
-- version：1.0.15
-- 此次更新
-- 添加一个函数，用于处理新用户注册的逻辑
-- 传入参数用户的id、租户id、角色模板Id
-- 返回状态码
-- 执行逻辑
-- 创建超级管理员角色，然后根据角色模板Id对应的应用、功能创建新租户对应的应用、功能、角色
-- 设置新用户的角色对应关系、应用对应关系、功能对应关系
-- 复制一份产品扩展信息给新用户
-- 1、分配应用
-- 2、分配功能模块
-- 3、分配产品扩展字段
-- 4、分配角色（默认添加超级管理员角色，不可修改）
-- 5、写入用户角色模块关系
-- ----------------------------
DROP FUNCTION IF EXISTS registerUserTemplate;
DELIMITER ;;
CREATE
definer = 'root'@'%'
FUNCTION registerUserTemplate (userId bigint(20),tenantId bigint(20),roleId bigint(20)) RETURNS varchar(50)
sql security invoker -- 以调用者的权限来执行
begin
-- 返回1成功或者0失败
declare _success_msg varchar(50) default '0';
-- 创建超级管理员角色
INSERT INTO jsh_role(Name, type, value, description, tenant_id, delete_Flag) VALUES ('超级管理员', NULL, NULL, '多租户超级管理员', tenantId, '0');
set @roleId= (SELECT LAST_INSERT_ID());
-- 根据角色模板id获取对应的应用列表,为新租户创建对应的应用列表
set @appIdList = (select left(replace(replace(value,'[',''),']',','),length(replace(replace(value,'[',''),']',','))-1) from jsh_userbusiness where 1=1 and type='RoleAPP' and KeyId=roleId and ifnull(delete_Flag,'0') !='1');
begin
set @i=length(@appIdList)-length(replace(@appIdList,',',''));
set @left_str=@appIdList;
while @i>0
do
set @sub_str=substr(@left_str,1,instr(@left_str,',')-1);
set @left_str=substr(@left_str,length(@sub_str)+length(',')+1);
set @n=trim(@sub_str);
-- 创建应用
insert into jsh_app (Number, Name, Type, Icon, URL, Width, Height, ReSize, OpenMax, Flash, ZL, Sort, Remark, Enabled,delete_Flag, tenant_id) select Number, Name, Type, Icon, URL, Width, Height, ReSize, OpenMax, Flash, ZL, Sort, Remark, Enabled,delete_Flag, tenantId from jsh_app where id=@n;
set @i=@i-1;
end while;
set @n=trim(@left_str);
insert into jsh_app (Number, Name, Type, Icon, URL, Width, Height, ReSize, OpenMax, Flash, ZL, Sort, Remark, Enabled,delete_Flag, tenant_id) select Number, Name, Type, Icon, URL, Width, Height, ReSize, OpenMax, Flash, ZL, Sort, Remark, Enabled,delete_Flag, tenantId from jsh_app where id=@n;
end;
-- 根据角色模板id获取对应的功能列表,为新租户创建对应的功能列表
set @functionIdList = (select left(replace(replace(value,'[',''),']',','),length(replace(replace(value,'[',''),']',','))-1) from jsh_userbusiness where 1=1 and type='RoleFunctions' and KeyId=roleId and ifnull(delete_Flag,'0') !='1');
begin
set @i=length(@functionIdList)-length(replace(@functionIdList,',',''));
set @left_str=@functionIdList;
while @i>0
do
set @sub_str=substr(@left_str,1,instr(@left_str,',')-1);
set @left_str=substr(@left_str,length(@sub_str)+length(',')+1);
set @n=trim(@sub_str);
-- 创建应用
insert into jsh_functions (Number, Name, PNumber, URL, State, Sort, Enabled, Type, PushBtn, delete_Flag, tenant_id) select Number, Name, PNumber, URL, State, Sort, Enabled, Type, PushBtn, delete_Flag, tenantId from jsh_functions where id=@n;
set @i=@i-1;
end while;
set @n=trim(@left_str);
insert into jsh_functions (Number, Name, PNumber, URL, State, Sort, Enabled, Type, PushBtn, delete_Flag, tenant_id) select Number, Name, PNumber, URL, State, Sort, Enabled, Type, PushBtn, delete_Flag, tenantId from jsh_functions where id=@n;
end;
-- 设置新用户的角色对应关系
INSERT INTO jsh_userbusiness ( Type, KeyId, Value, BtnStr, delete_Flag, tenant_id) VALUES ( 'UserRole', userId, CONCAT('[',@roleId,']') , NULL, '0', tenantId);
set @appStr=(select GROUP_CONCAT(id separator '][') from jsh_app where tenant_id=tenantId and ifnull(delete_Flag,'0') !='1');
-- 设置角色应用对应关系
INSERT INTO jsh_userbusiness ( Type, KeyId, Value, BtnStr, delete_Flag, tenant_id) VALUES ( 'RoleAPP', @roleId, CONCAT('[',@appStr,']') , NULL, '0', tenantId);
set @functionStr=(select GROUP_CONCAT(id separator '][') from jsh_functions where tenant_id=tenantId and ifnull(delete_Flag,'0') !='1');
set @functionBtnStr=(select GROUP_CONCAT((CONCAT('"funId":"',id,'","btnStr":"',PushBtn,'"')) separator '},{') from jsh_functions where 1=1 and number in ('01020101','01020102','01020103','050202','060301') and tenant_id=tenantId and ifnull(delete_Flag,'0') !='1');
-- 设置角色功能对应关系
INSERT INTO jsh_userbusiness ( Type, KeyId, Value, BtnStr, delete_Flag, tenant_id) VALUES ( 'RoleFunctions', @roleId, CONCAT('[',@functionStr,']') , CONCAT('[{',@functionBtnStr,'}]'), '0', tenantId);
-- 设置租户的产品扩展信息
INSERT INTO jsh_materialproperty( nativeName, enabled, sort, anotherName, delete_Flag, tenant_id) select nativeName, enabled, sort, anotherName, delete_Flag, tenantId from jsh_materialproperty where id in(1,2,3,4,5,6);
return _success_msg;
end
;;
DELIMITER ;