mysql 存储程序

1 总览

存储程序可以封装一些语句，然后给用户提供一种简单的方式来调用这个存储程序，从而间接地执行某些语句。根据调用方式的不同，可以把存储程序分为存储例程、触发器和事件，存储例程又分为存储函数和存储过程，如☝️ 上图。

2 存储函数

存储函数只有一个返回值，可以从 mysql 内置的函数理解，所有 mysql 内置的函数都是只有一个返回值，比如：

存储函数很好理解，就是一个函数，跟普通函数一样有函数名，函数体，参数列表和返回值。创建存储函数语句如下：

CREATE FUNCTION 存储函数名称([参数列表])
    RETURNS 返回值类型
BEGIN
    函数体内容
END

2.1 示例

现在写一个存储函数，输入用户名 name，返回用户手机号 phone：

CREATE FUNCTION get_phone(qname VARCHAR (45))
    RETURNS VARCHAR(11)
BEGIN
RETURN (SELECT phone
        from t
        where name = qname);
END EOF

存储函数的调用跟普通函数的调用也是一样的👇

3 局部变量和自定义变量

在存储函数中可以使用局部变量和自定义变量，二者的区别是，局部变量用 DECLARE 申明，不用加 @符，局部变量随着函数调用结束，变量销毁且只能在存储函数中使用。自定义变量需要加 @ 符，且可以在函数外调用。

CREATE FUNCTION get_phone(qname VARCHAR (45))
    RETURNS VARCHAR(11)
BEGIN
    DECLARE ph varchar(11) default ""; # 局部变量
    SET @ii = 100; # 自定义变量
    SET ph = (select phone from t where name = qname); # 给局部变量赋值

    RETURN  ph ;
END EOF

☝️ 可知，在存储函数get_phone中有一个局部变量 ph和自定义变量 @ii，函数调用结束后 @ii 被赋值为100 且可以在函数执行完后访问，但是 @ph 是空的。

4 存储过程

存储函数侧重于执行某些语句并返回一个值，而存储过程更侧重于单纯的去执行这些语句。存储过程的定义不需要声明返回值类型。

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END

调用存储过程使用 CALL 关键字。

4.1 示例

以下示例定义了一个 my_operate 的存储过程：

CREATE PROCEDURE my_operate(pname varchar (45))
BEGIN
     SELECT * FROM t;
     INSERT INTO t(phone, name) VALUES("15214254125", "卢俊义");
     SELECT * FROM t;
     SELECT * from t where name = pname;
END EOF

☝️ my_operate 定义并执行了 4 条 sql，完美诠释了存储过程更侧重于单纯的去执行这些语句。

5 存储过程的参数前缀

存储过程在定义参数的时候可以选择添加一些前缀👇，如果不写，默认的前缀是IN：

参数类型 [IN | OUT | INOUT] 参数名 数据类型

👇以下的示例，综合了 in，out，inout 参数：

CREATE PROCEDURE my_arg(
            in pname varchar (45),
            out ophone char(11),
            inout io_name varchar(45)
          )
BEGIN
     SELECT * FROM t;
     INSERT INTO t(phone, name) VALUES("15225632145", "公孙胜");
     SELECT * FROM t;
     SELECT phone from t where name = pname into ophone;
     SET pname = "公孙胜";
     SET io_name = "公孙胜";
END EOF

由☝️可以看出，虽然在存储过程中修改了 pname 的值为 公孙胜，但是并没有生效，值依然是最初的宋江。IN参数只能被用于读取，对它赋值是不会被调用者看到的。

out 参数 ophone 最初是空的，通过存储过程赋值成功为公孙胜。

inout 参数 io_name 最初是空的，通过存储过程赋值成功为公孙胜，这里如果 io_name 不为空，也会被修改为公孙胜。

6 存储过程和存储函数的区别

• 存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，而存储过程不需要。
• 存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数。
• 存储函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果。
• 存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端。
• 存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。

7 游标

游标是为了方便访问结果集中的某一条记录，可以理解成循环。如果某个结果集中有 10 条记录，使用游标后，会一条一条的去访问这 10 条记录。

游标可以在存储函数和存储过程中使用。

使用游标分为 4 步：

1. 创建游标：DECLARE 游标名称 CURSOR FOR 查询语句;
2. 打开游标：OPEN 游标名称;
3. 通过游标访问记录
4. 关闭游标：CLOSE 游标名称;

不显式的使用CLOSE语句关闭游标的话，在该存储函数或存储过程执行完之后会自动关闭游标。

可以使用👇来获取结果集中的记录：

FETCH 游标名 INTO 变量1, 变量2, ... 变量n

7.1 示例

🤑以下创建一个存储过程，在存储过程中使用游标。

创建游标t_cursor，游标执行语句为 SELECT phone, name FROM t。

DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句; 的作用是结果集遍历结束后会自动执行这句，这里也可以使用 WHILE 循环遍历，但是 while 有个弊端是需要提前知道结束条件，比如结果集的总数是多少。这样写的好处是直接遍历，遍历结束自动处理，将 done 变量设置为 1，也就是说只要 done = 1 就说明遍历结束了，利用 LEAVE 关键字跳出循环。

CREATE PROCEDURE my_cursor()
BEGIN
    DECLARE v_phone char(11);
    DECLARE v_name varchar(45);

    DECLARE done INT DEFAULT 0;
    DECLARE t_cursor CURSOR FOR SELECT `phone`, `name` FROM t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN t_cursor;

    flag: LOOP
        FETCH t_cursor INTO v_phone, v_name;
        IF done = 1 THEN
            LEAVE flag;
        END IF;
        SELECT v_phone, v_name, done;
    END LOOP flag;

    CLOSE t_cursor;

END EOF

8 触发器和事件

存储例程是需要手动调用的，而触发器和事件是 MySQL 服务器在特定情况下自动调用的。

9 触发器

创建触发器

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

MySQL 目前只支持对INSERT、DELETE、UPDATE这三种类型的语句设置触发器。

FOR EACH ROW BEGIN ... END表示对具体语句影响的每一条记录都执行触发器内容。

对于INSERT语句来说，FOR EACH ROW影响的记录就是准备插入的那些新记录。

对于DELETE语句和UPDATE语句来说，FOR EACH ROW影响的记录就是符合条件的那些记录。

针对每一条受影响的记录，需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

• 对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。
• 对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。
• 对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

9.1 示例

🤦‍♂️以下示例，对表 t 创建一个 my_trigger触发器，表 t 有三个字段，name，phone，my_join，对于每条 insert 的语句，在执行 insert 之前判断如果 name = admin 那么将即将插入的 name 值改为 valid，如果 name 值为空，将即将插入的 name 值改为无名氏，除此之外将 name 和 phone 拼接后赋给 my_join 字段。

CREATE TRIGGER my_trigger
     BEFORE INSERT ON t
     FOR EACH ROW
     BEGIN
         IF NEW.name = 'admin' THEN
             SET NEW.name = 'valid';
         ELSEIF NEW.name = '' THEN
            SET NEW.name = '无名氏';
         ELSE
            SET NEW.my_join = CONCAT(NEW.name, "--", NEW.phone);
         END IF;
 END EOF

9.2 注意事项

• 触发器内容中不能有输出结果集的语句。
• 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。
• 在BEFORE触发器中，我们可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER 触发器的内容时记录已经被插入完成或者更新完成了。
• 如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

10 事件

事件可以让 MySQL 服务器在某个时间点或者每隔一段时间自动地执行一些语句。

默认情况下，MySQL服务器并不会帮助我们执行事件，需要手动开启该功能：

SET GLOBAL event_scheduler = ON;

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

10.1 某个时间点执行

CREATE EVENT insert_t1_event
ON SCHEDULE
AT '2022-01-03 11:20:11' # 或者 AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
    INSERT INTO t(phone, name) VALUES('15210214254', '宋江');
END

10.2 每隔一段时间执行

CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
DO
BEGIN
    INSERT INTO t(phone, name) VALUES('15210214254', '宋江');
END

在创建好事件之后，到了指定时间，MySQL 服务器会自动执行。