MySQL函数编程简介

备注:测试数据库版本为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直接进行调用即可。