备注:测试数据库版本为MySQL 8.0
这个blog我们来聊聊MySQL函数,函数功能与存储过程大体类似,但是函数需要有一个返回值,可以被sql调用,复杂的sql查询可以通过自己定义的函数解决。
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
一.语法相关
语法:
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
语法基本同存储过程,只是多了一个返回值
二.案例
1.案例1:
需求:对用户表的修改和删除均要保留历史数据及审计记录
测试数据:
delimiter //
create function f_del_users(pi_user_id int,
pi_login_user varchar(200)
) returns varchar(50)
MODIFIES SQL DATA
begin
# 定义一个输出列
DECLARE o_result varchar(50);
# 定义一个变量,默认为0
DECLARE l_error INTEGER DEFAULT 0;
# 当有SQL报错的时候,设置变量值为1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;
# 给输出一个默认值
SET o_result = concat('删除',pi_user_id,'成功');
-- 开启事务
start transaction;
-- 将用户数据录入用户历史表
insert into user_info_history(type,user_id,name,id_number,create_time)
select 'DELETE',id,name,id_number,now()
from user_info
where id = pi_user_id;
-- 删除用户数据
delete from user_info where id = pi_user_id;
-- 增加审计记录
insert into audit_log(type,login_user,audit_date,user_id)
select 'DELETE',pi_login_user,now(),pi_user_id;
# 成功就删除,失败就回滚
IF l_error = 1 THEN
ROLLBACK;
# 如失败,修改输出
SET o_result = concat('删除',pi_user_id,'失败');
ELSE
COMMIT;
END IF;
return o_result;
end;
//
delimiter ;
运行结果:
运行报错了,提示函数里面是不能有事务的,事务只能存在于MySQL的存储过程
看来MySQL的函数与Oracle的函数还是存在差别,复杂的功能还是写存储过程,函数作为辅助。
函数与存储过程的差别:
1.函数有返回值而存储过程没有,是returns,return后面有一个s
2.函数只能定义入参,不能定义出参
3.函数里面不能开启事务,存储过程可以
mysql> delimiter //
mysql>
mysql> create function f_del_users(pi_user_id int,
-> pi_login_user varchar(200)
-> ) returns varchar(50)
-> MODIFIES SQL DATA
-> begin
->
-> # 定义一个输出列
-> DECLARE o_result varchar(50);
->
-> # 定义一个变量,默认为0
-> DECLARE l_error INTEGER DEFAULT 0;
-> # 当有SQL报错的时候,设置变量值为1
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;
->
-> # 给输出一个默认值
-> SET o_result = concat('删除',pi_user_id,'成功');
->
-> -- 开启事务
-> start transaction;
->
-> -- 将用户数据录入用户历史表
-> insert into user_info_history(type,user_id,name,id_number,create_time)
-> select 'DELETE',id,name,id_number,now()
-> from user_info
-> where id = pi_user_id;
->
-> -- 删除用户数据
-> delete from user_info where id = pi_user_id;
->
-> -- 增加审计记录
-> insert into audit_log(type,login_user,audit_date,user_id)
-> select 'DELETE',pi_login_user,now(),pi_user_id;
->
-> # 成功就删除,失败就回滚
-> IF l_error = 1 THEN
-> ROLLBACK;
-> # 如失败,修改输出
-> SET o_result = concat('删除',pi_user_id,'失败');
-> ELSE
-> COMMIT;
-> END IF;
->
-> return o_result;
->
-> end;
-> //
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.
mysql>
mysql> delimiter ;
mysql>
mysql>
案例2
需求:通过函数实现查询员工部门
CREATE DEFINER=`root`@`localhost` FUNCTION `f_get_dept`(`pi_empno` int) RETURNS varchar(200) CHARSET utf8mb4
DETERMINISTIC
BEGIN
#Routine body goes here...
declare o_dept_name varchar(200) default null;
select concat('工号:',pi_empno,',','姓名:',e.ename,',','部门:',d.dname)
into o_dept_name
from emp e
left join dept d
on e.deptno = d.deptno
where e.empno = pi_empno
;
RETURN o_dept_name;
END
运行结果:
mysql> select f_get_dept(empno) from emp;
+---------------------------------------------+
| f_get_dept(empno) |
+---------------------------------------------+
| 工号:7782,姓名:CLARK,部门:ACCOUNTING |
| 工号:7839,姓名:KING,部门:ACCOUNTING |
| 工号:7934,姓名:MILLER,部门:ACCOUNTING |
| 工号:7369,姓名:SMITH,部门:RESEARCH |
| 工号:7566,姓名:JONES,部门:RESEARCH |
| 工号:7788,姓名:SCOTT,部门:RESEARCH |
| 工号:7876,姓名:ADAMS,部门:RESEARCH |
| 工号:7902,姓名:FORD,部门:RESEARCH |
| 工号:7499,姓名:ALLEN,部门:SALES |
| 工号:7521,姓名:WARD,部门:SALES |
| 工号:7654,姓名:MARTIN,部门:SALES |
| 工号:7698,姓名:BLAKE,部门:SALES |
| 工号:7844,姓名:TURNER,部门:SALES |
| 工号:7900,姓名:JAMES,部门:SALES |
+---------------------------------------------+
14 rows in set (0.01 sec)
从上面例子可以看出,对于sql里面需要复杂拼接,连表等操作,可以通过自己定义的函数搞定,sql直接进行调用即可。