DBA MySQL存储过程

功能概述

​ 存储过程包含一系列可执行的SQL语句，存储过程须存放于MySQL中，通过对存储过程名字的调用可执行其内部的SQL语句。

1. 存储过程用于替代应用程序开发过程中书写的SQL语句，以实现应用程序与SQL的解耦合
2. 如果是基于网络传输，远程直接输入执行存储过程的名字即可，数据传输量较小
3. 存储过程的缺点在于部门间沟通不便导致可扩展性降低

创建存储过程

基础语法

​ 创建存储过程的基础语法如下：

# delimiter是指自定义结束符，mysql中以;号结束，使用自定义结束符后则以自定义结束符为准
# 自定义结束符的意义在于有可能定制存储过程逻辑中的语句会使用分号，但是此时存储过程并未创建完成
# 每个存储过程创建完成之后都有一个$，代表该语句以正式结束，存储过程以创建

delimiter $
  -- 无参：
  CREATE PROCEDURE 存储过程名称()
    BEGIN
      存储过程逻辑功能
    END $

  -- 有参：
  CREATE PROCEDURE 存储过程名称(in 形参名 形参类型, out 返回值名 返回值类型)
    BEGIN
      存储过程逻辑功能
    END $ 
delimiter ;

无参示例与使用

​ 存储过程是对一系列SQL语句的封装，在执行这一组SQL语句时，可使用名字进行调用：

delimiter $
  -- 创建存储过程
  CREATE PROCEDURE p1()
    BEGIN
      -- 书写程序逻辑
      SELECT * FROM db1.userInfo;
      -- 其他逻辑
    END $
delimiter ;

# MySQL中进行调用
call p1();

有参示例与使用

​ 存储过程中的形参及返回值必须在传入时进行定义与设置类型约束，与Golang定义函数类似：

in 仅用于传入的参数

out 仅用于返回值使用

inout 即可作为传入值也可传入返回值

delimiter $
  -- 创建存储过程 参数1，传入参数，int类型，参数2，返回值，int类型
  CREATE PROCEDURE p2(in n1 int, out res int)
    BEGIN
      -- 书写程序逻辑
      select id from t1 where id = n1;
      set res = 1;  -- 设置返回值
      -- 逻辑完毕
    END $
delimiter ;

# MySQL中进行调用
# 先将接受返回值的变量进行定义
set @res = 0; 
# 参数1，传入值，参数2，返回值
call p2(1,@res); 
# 查看返回值
select @res;

删除语法

​ 删除存储过程的语法格式如下：

DROP PROCEDURE 存储过程名称;

异常处理

​ 存储过程逻辑处理中可使用异常处理，但是MySQL的异常处理并不是太完善。

delimiter $
  -- 创建存储过程 参数1，传入参数，int类型，参数2，返回值，int类型
  CREATE PROCEDURE p2(in n1 int, out res int)
    BEGIN
      -- 异常捕捉：SQLWARNING：01开头的异常码，NOT FOUND:02开头的异常码，SQLEXCEPTION：其他数字开头的异常码
      DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION
      BEGIN
        -- 异常逻辑书写区域，如果发生异常返回值为0
        select "错误了";
        set res = 0;
      END;
      -- 书写正常逻辑，当出现异常时跑到上面执行异常的处理逻辑
      select "正常了";
      -- 没有该表，触发异常
      select * from nohavetable;
      -- 设置返回值，如果为触发异常，返回值为1
      set res = 1;
    END $
delimiter ;

# MySQL中进行调用
# 先将接受返回值的变量进行定义
set @res = 0; 
# 参数1，传入值，参数2，返回值
call p2(1,@res); 
# 查看返回值
select @res;