MySQL存储过程简介

原创只是甲 2020-06-07

1437

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL存储过程，MySQL的存储过程比较其它如Oracle、SqlServer、PostgreSQL会弱很多，但是也能实现一些单纯sql语句不能实现，或者是实现起来比较复杂业务场景，有总比没有强，而且Oracle公司在加强对MySQL各方面的提升，也许后面MySQL的存储过程会有很大的提升。

一.语法相关

语法:

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] 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.DEFINER:
DEFINER模式下，默认DEFINER=CURRENT_USER，在存储过程执行时，mysql会检查DEFINER定义的用户’user_name’@'host_name’的权限；

2.proc_parameter:
参数列表：不同于函数的参数列表，需要指明参数类型
IN，表示输入型
OUT，表示输出型
INOUT，表示混合型,即可以输入，在存储过程里面也可以修改，最后输出

3.characteristic:
3.1 LANGUAGE SQL
存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言

3.2 NOT DETERMINISTIC
是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用

3.3 CONTAINS SQL
提供子程序使用数据的内在信息，这些特征值目前提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况，说白了就是没有使用的
包括以下四种选择
3.3.1 CONTAINS SQL表示子程序不包含读或者写数据的语句
3.3.2 NO SQL 表示子程序不包含sql
3.3.3 READS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句
3.3.4 MODIFIES SQL DATA 表示子程序包含写数据的语句。

3.4 SQL SECURITY DEFINER
用来指定存储过程是使用创建者的许可来执行，还是执行者的许可来执行，默认值是DEFINER
DEFINER 创建者的身份来调用，对于当前用户来说：如果执行存储过程的权限，且创建者有访问表的权限，当前用户可以成功执行过程的调用的
说白了就是当前用户调用存储过程，存储过程执行的具体操作是借助定义存储过程的user的权限执行的。
INVOKER 调用者的身份来执行，对于当前用户来说：如果执行存储过程的权限，以当前身份去访问表，如果当前身份没有访问表的权限，即便是有执行过程的权限，仍然是无法成功执行过程的调用的。
说白了就是当前用户调用存储过程，只有当前用户有执行存储过程中涉及的对象的操作的权限的时候，才能成功执行。

3.5 COMMENT
存储过程的注释性信息写在COMMENT里面，这里只能是单行文本，多行文本会被移除到回车换行等。

二.案例

上面看了存储过程的语法，下面测试几个存储过程的案例，来帮助我们熟悉MySQL的存储过程

需求:对于删除用户数据，需要保留用户数据到历史表，并保留一条删除的信息到审计表。
测试数据:

-- 用户表
create table user_info(id int not null auto_increment,name varchar(200),id_number varchar(50),primary key(id));
insert into user_info values (1,'张三','420123199001011234');
insert into user_info values (2,'李四','420123199001011235');
insert into user_info values (3,'王五','420123199001011236');

-- 用户历史数据表
create table user_info_history(id int not null auto_increment,type varchar(50),user_id int,name varchar(200),id_number varchar(50),create_time datetime,primary key(id));

-- 用户记录删除审计表
create table audit_log(id int not null auto_increment ,type varchar(50),login_user varchar(50),audit_date datetime,user_id int,primary key(id));
复制

存储过程:

delimiter //

create procedure p_del_users(in pi_user_id int,
                             in pi_login_user varchar(200) 
							)
begin

  -- 将用户数据录入用户历史表
  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;

end;
//

delimiter ;
复制

测试记录:

mysql> delimiter //
mysql>
mysql> create procedure p_del_users(in pi_user_id int,
    ->                              in pi_login_user varchar(200)
    ->                                                  )
    -> begin
    ->
    ->   -- 将用户数据录入用户历史表
    ->   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;
    ->
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql>
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  1 | 张三   | 420123199001011234 |
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
3 rows in set (0.00 sec)

mysql>
-- 调用存储过程，删除id为1的用户表记录
mysql> call p_del_users(1,'TX0001');
Query OK, 1 row affected, 1 warning (0.01 sec)
-- 查询表 发现已被删除
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)
-- 用户历史记录保留表也保留了记录
mysql> select * from user_info_history;
+----+--------+---------+--------+--------------------+---------------------+
| id | type   | user_id | name   | id_number          | create_time         |
+----+--------+---------+--------+--------------------+---------------------+
|  1 | DELETE |       1 | 张三   | 420123199001011234 | 2020-05-25 17:04:22 |
+----+--------+---------+--------+--------------------+---------------------+
1 row in set (0.00 sec)
-- 审计表也有删除审计的数据
mysql> select * from audit_log;
+----+--------+------------+---------------------+---------+
| id | type   | login_user | audit_date          | user_id |
+----+--------+------------+---------------------+---------+
|  1 | DELETE | TX0001     | 2020-05-25 17:04:22 |       1 |
+----+--------+------------+---------------------+---------+
1 row in set (0.00 sec)

mysql>

复制

这样看，是不是觉得好简单，存储过程也不怎么样啦，就是把3个sql拼接起来了而已，也没什么复杂的。
别慌，我下面慢慢来增加复杂程度。

MySQL默认是自动提交，这个时候，3个sql中如果有一个失败了，例如第2个sql成功的删除了用户数据，但是第3个sql增加审计记录的时候报错了，没有成功，这样就会导致审计数据的缺失，这个时候我们该如何处理呢?

我们来看下面的例子:

-- 把删除的用户从新录入用户表
insert into user_info values (1,'张三','420123199001011234');
-- 将audit_log表user_id设为唯一索引，这样第二次录入数据就会报错
create  unique index i_auditlog_userid on audit_log(user_id); 
复制

-- 运行存储过程报错
mysql> call p_del_users(1,'TX0001');
ERROR 1062 (23000): Duplicate entry '1' for key 'audit_log.i_auditlog_userid'
-- 用户表数据删除
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
2 rows in set (0.00 sec)
-- 历史记录表，也保留了历史数据
mysql> select * from user_info_history;
+----+--------+---------+--------+--------------------+---------------------+
| id | type   | user_id | name   | id_number          | create_time         |
+----+--------+---------+--------+--------------------+---------------------+
|  1 | DELETE |       1 | 张三   | 420123199001011234 | 2020-05-25 17:04:22 |
|  2 | DELETE |       1 | 张三   | 420123199001011234 | 2020-05-25 17:19:30 |
+----+--------+---------+--------+--------------------+---------------------+
2 rows in set (0.00 sec)

-- 审计表报错，审计记录缺失
mysql>
mysql> select * from audit_log;
+----+--------+------------+---------------------+---------+
| id | type   | login_user | audit_date          | user_id |
+----+--------+------------+---------------------+---------+
|  1 | DELETE | TX0001     | 2020-05-25 17:04:22 |       1 |
+----+--------+------------+---------------------+---------+
1 row in set (0.00 sec)

复制

此时，可以在存储过程里面，加入事务，这样3个sql语句，要么一起成功，要么一起失败

-- 把删除的用户从新录入用户表
insert into user_info values (1,'张三','420123199001011234');
复制

delimiter //

create procedure p_del_users(in pi_user_id int,
                             in pi_login_user varchar(200) 
							)
begin

  -- 开启事务
  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;
  
   -- 开启事务后需要主动提交
  commit; 
  
end;
//

delimiter ;
复制

运行结果如下:

-- 此时直接报错了，而且查询数据，3个sql均没有成功
mysql> p_del_users(1,'TX0001');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p_del
_users(1,'TX0001')' at line 1
mysql>
mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  1 | 张三   | 420123199001011234 |
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
3 rows in set (0.00 sec)

mysql> select * from audit_log;
+----+--------+------------+---------------------+---------+
| id | type   | login_user | audit_date          | user_id |
+----+--------+------------+---------------------+---------+
|  1 | DELETE | TX0001     | 2020-05-25 17:04:22 |       1 |
+----+--------+------------+---------------------+---------+
1 row in set (0.00 sec)

mysql>

复制

如果我不想开发人员调用我的存储过程的时候报错，成功就commit，失败就rollback，然后返回一个标识给开发人员
此时，程序要做如下调整:

delimiter //

create procedure p_del_users(in pi_user_id int,
                             in pi_login_user varchar(200),
                             out po_result varchar(200) 
                            )
begin
  
  # 定义一个变量，默认为0
  DECLARE l_error INTEGER DEFAULT 0;  
  # 当有SQL报错的时候，设置变量值为1
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;   
  
  # 给输出一个默认值
  SET po_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 po_result = concat('删除',pi_user_id,'失败'); 
     ELSE    
      COMMIT;    
   END IF;    
  
end;
//

delimiter ;
复制

执行结果如下:

-- 没有返回错误，通过返回结果知道这次删除数据失败
mysql> call p_del_users(1,'TX0001',@result);
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> select @result;
+---------------+
| @result       |
+---------------+
| 删除1失败     |
+---------------+
1 row in set (0.00 sec)
复制

现在我们又接到审计部门的一个需求，对于修改用户表的记录，也要做历史数据保留和审计记录

-- 删除 audit_log表唯一索引
drop index i_auditlog_userid on audit_log;
复制

存储过程代码如下:

delimiter //

create procedure p_upd_users(in pi_user_id int,
                             in pi_login_user varchar(200),
							 in pi_name varchar(50),
                             in pi_idnumber varchar(50),
							 out po_result varchar(200)
							)
begin
  
  -- 定义一个变量，初始值为0
  DECLARE l_error INTEGER DEFAULT 0;  
  -- 如果有SQL异常，则l_error为1  
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET l_error=1;   
  
  -- 默认设置为成功
  SET po_result = concat('修改',pi_user_id,'成功');
  
  -- 开启事务
  start transaction;  

  -- 将用户数据录入用户历史表
  insert into user_info_history(type,user_id,name,id_number,create_time)
  select 'UPDATE',id,name,id_number,now()
  from user_info 
  where id = pi_user_id;

  -- 修改用户数据
   update user_info 
  set name = pi_name,
      id_number = pi_idnumber
  where id = pi_user_id;

  -- 增加审计记录
  insert into audit_log(type,login_user,audit_date,user_id)
  select 'UPDATE',pi_login_user,now(),pi_user_id;
  
  -- 没有错误就提交，有错误进行回滚
  IF l_error = 1 THEN    
      ROLLBACK; 
      -- 如果有错误，返回删除失败  
      SET po_result = concat('修改',pi_user_id,'失败'); 
     ELSE    
      COMMIT;    
   END IF;    

  
end;
//

delimiter ;
复制

执行结果如下:

mysql> call p_upd_users(1,'TX0001','刘伟','420123199001011234',@result);
Query OK, 0 rows affected, 4 warnings (0.01 sec)

mysql> select @result;
+---------------+
| @result       |
+---------------+
| 修改1成功     |
+---------------+
1 row in set (0.00 sec)

mysql> select * from user_info;
+----+--------+--------------------+
| id | name   | id_number          |
+----+--------+--------------------+
|  1 | 刘伟   | 420123199001011234 |
|  2 | 李四   | 420123199001011235 |
|  3 | 王五   | 420123199001011236 |
+----+--------+--------------------+
3 rows in set (0.00 sec)

mysql> select * from user_info_history;
+----+--------+---------+--------+--------------------+---------------------+
| id | type   | user_id | name   | id_number          | create_time         |
+----+--------+---------+--------+--------------------+---------------------+
|  1 | DELETE |       1 | 张三   | 420123199001011234 | 2020-05-25 17:04:22 |
|  2 | DELETE |       1 | 张三   | 420123199001011234 | 2020-05-25 17:19:30 |
|  6 | UPDATE |       1 | 刘伟   | 420123199001011234 | 2020-05-25 17:36:39 |
+----+--------+---------+--------+--------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from audit_log;
+----+--------+------------+---------------------+---------+
| id | type   | login_user | audit_date          | user_id |
+----+--------+------------+---------------------+---------+
|  1 | DELETE | TX0001     | 2020-05-25 17:04:22 |       1 |
|  6 | UPDATE | TX0001     | 2020-05-25 17:36:39 |       1 |
+----+--------+------------+---------------------+---------+
2 rows in set (0.00 sec)

复制

假设此时开发不想调用2个存储过程，此时可以封装起来，让开发调用一个存储过程

delimiter //

create procedure p_main_users(in pi_type varchar(50),
                              in pi_user_id int,
                              in pi_login_user varchar(200),
							  in pi_name varchar(50),
                              in pi_idnumber varchar(50),
							  out po_result varchar(200)
							 )
begin
  
  if pi_type = 'UPDATE' then
    call p_upd_users(pi_user_id,pi_login_user,pi_name,pi_idnumber,po_result);
	 elseif pi_type = 'DELETE' then
	 call p_del_users(pi_user_id,pi_login_user,po_result);
	 else
	   -- 空的块,什么都不做
	   begin
	   end;
   end if;

end;
//

delimiter ;
复制

运行结果:

mysql> delimiter //
mysql>
mysql> create procedure p_main_users(in pi_type varchar(50),
    ->                               in pi_user_id int,
    ->                               in pi_login_user varchar(200),
    ->                                                    in pi_name varchar(50),
    ->                               in pi_idnumber varchar(50),
    ->                                                    out po_result varchar(200)
    ->                                                   )
    -> begin
    ->
    ->   if pi_type = 'UPDATE' then
    ->     call p_upd_users(pi_user_id,pi_login_user,pi_name,pi_idnumber,po_result);
    ->   elseif pi_type = 'DELETE' then
    ->   call p_del_users(pi_user_id,pi_login_user,po_result);
    ->   else
    ->     -- 空的块,什么都不做
    ->     begin
    ->     end;
    ->    end if;
    ->
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call p_main_users('DELETE',1,'TX0002',null,null,@result);
Query OK, 0 rows affected, 7 warnings (0.01 sec)

mysql> select @result;
+---------------+
| @result       |
+---------------+
| 删除1成功     |
+---------------+
1 row in set (0.00 sec)

mysql> call p_main_users('UPDATE',1,'TX0002','张伟','420123199001011235',@result);
Query OK, 0 rows affected, 4 warnings (0.01 sec)

mysql> select @result;
+---------------+
| @result       |
+---------------+
| 修改1成功     |
+---------------+
1 row in set (0.00 sec)

复制