MySQL笔记（五）——存储过程和函数、触发器

末蓝、 2023-07-04 08:44 9阅读 0赞

# 存储过程和函数 #

## 概览 ##

存储过程和函数是事先编译并存储在数据库中的一段SQL语句的集合。5.0版本开始支持。

**存储过程：**无返回值，参数可使用IN、OUT和INOUT类型；

**函数：**必须有返回值，参数只能是IN类型。

## 优点 ##

1、存储过程和函数可重复使用，调用存储过程和函数可简化开发人员的工作量；

2、调用存储过程和函数只需要传递存储过程/函数名称和参数即可，能够减少数据在数据库和应用服务器之间的传输；

3、安全性高，可设定用户的使用权限。

## 主要操作 ##

本笔记记录的例子都是使用官方提供的demo库sakila来进行练习的。

### 创建 ###

创建存储过程语法如下：

create
    
    [definer = {user | current_user}]
    
    procedure `sp_name` ([proc_parameter[, ...]])
    
    [characteristic ...] `routine_body`;

创建函数语法与存储过程类似，只是多了返回值类型的定义：

create
    
    [definer = {user | current_user}]
    
    function `func_name` ([func_parameter[, ...]])
    
    returns type
    
    [characteristic ...] `routine_body`;

**definer：**可选，指明存储过程/函数创建用户。

**characteristic：**特征值，可选取值如下

**language sql** 系统默认，说明过程/函数body是以MySQL编写。

**deterministic/not deterministic** 系统默认为not deterministic。deterministic意为确定的，即每次输出一样、输出也一样的程序。目前该特征值还未被优化程序使用。

**\{ contains sql | no sql | reads sql data | modifies sql data \}** 程序使用数据的内在信息，目前这些特征值只是提供给服务器，并没有被用于约束实际使用数据的情况：

contains sql：系统默认，表示程序不含读/写数据的语句；

no sql：表示程序不包含SQL语句；

reads sql data：表示程序只含有读数据的语句，而没有写数据的语句；

modifies sql data：表示程序含有写数据的语句。

sql security \{ definer | invoker \} 系统默认为definer。用来约束程序调用时使用的是程序创建者的权限还是调用者的权限。如调用者对程序中涉及的表没有相关权限，而该值为invoker，则会调用失败；若该值为definer，则可正常调用。举个例子：

先创建一个简单的存储过程，功能是向actor表中信息数据：

delimiter $$
    create procedure actor_insert(p_first_name varchar(45), p_last_name varchar(45))
    modifies sql data
    sql security definer
    begin
    insert into actor(first_name, last_name)
    values (p_first_name, p_last_name);
    end $$
    Query OK, 0 rows affected (0.00 sec)
    delimiter ；

使用当前用户测试一下存储过程actor\_insert：

call actor_insert('JIM', 'Carey');
    Query OK, 1 row affected (0.01 sec)

插入成功。现在我们再创建一个对actor表没有insert权限的用户test，来调用这个存储过程，因为当前设置了sql security definer，因此调用能够成功：

call sakila.actor_insert('xx', 'xx');
    Query OK, 1 row affected (0.00 sec)

接下来修改存储过程（alter procedure/function用于修改过程/函数的一些特征值，如果要修改routine\_body则需要删除重新创建）：

alter procedure actor_insert
    sql security invoker;
    Query OK, 0 rows affected (0.00 sec)

此时再调用一下：

call sakila.actor_insert('xx', 'xx');
    ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'actor'

由于调用者test没有actor表的insert权限，因此调用过程失败。

### 查看 ###

1、查看存储过程/函数的状态：*show \{ procedure | function \} status \[like 'pattern'\];*

例子：查看actor\_insert表状态

show procedure status like 'actor_insert'\G;
    *************************** 1. row ***************************
                      Db: sakila
                    Name: actor_insert
                    Type: PROCEDURE
                 Definer: root@localhost
                Modified: 2020-02-09 22:46:32
                 Created: 2020-02-09 17:53:05
           Security_type: INVOKER
                 Comment: 
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: latin1_swedish_ci
    1 row in set (0.00 sec)

2、查看存储过程/函数的定义：*show create \{ procedure | function \} \`sp\_name\`;*

例子：查看actor\_list表定义语句

show create procedure sakila.actor_insert\G;
    *************************** 1. row ***************************
               Procedure: actor_insert
                sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
        Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `actor_insert`(p_first_name varchar(45), p_last_name varchar(45))
        MODIFIES SQL DATA
        SQL SECURITY INVOKER
    begin
    insert into actor(first_name, last_name)
    values (p_first_name, p_last_name);
    end
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: latin1_swedish_ci
    1 row in set (0.00 sec)

3、所有的存储过程/函数，都存放在information\_schema库的routines表中，可以通过routines表查询存储过程/函数信息：

select * from information_schema.routines where routine_name='actor_insert'\G;
    *************************** 1. row ***************************
               SPECIFIC_NAME: actor_insert
             ROUTINE_CATALOG: def
              ROUTINE_SCHEMA: sakila
                ROUTINE_NAME: actor_insert
                ROUTINE_TYPE: PROCEDURE
                   DATA_TYPE: 
    CHARACTER_MAXIMUM_LENGTH: NULL
      CHARACTER_OCTET_LENGTH: NULL
           NUMERIC_PRECISION: NULL
               NUMERIC_SCALE: NULL
          DATETIME_PRECISION: NULL
          CHARACTER_SET_NAME: NULL
              COLLATION_NAME: NULL
              DTD_IDENTIFIER: NULL
                ROUTINE_BODY: SQL
          ROUTINE_DEFINITION: begin
    insert into actor(first_name, last_name)
    values (p_first_name, p_last_name);
    end
               EXTERNAL_NAME: NULL
           EXTERNAL_LANGUAGE: NULL
             PARAMETER_STYLE: SQL
            IS_DETERMINISTIC: NO
             SQL_DATA_ACCESS: MODIFIES SQL DATA
                    SQL_PATH: NULL
               SECURITY_TYPE: INVOKER
                     CREATED: 2020-02-09 17:53:05
                LAST_ALTERED: 2020-02-09 22:46:32
                    SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             ROUTINE_COMMENT: 
                     DEFINER: root@localhost
        CHARACTER_SET_CLIENT: utf8
        COLLATION_CONNECTION: utf8_general_ci
          DATABASE_COLLATION: latin1_swedish_ci
    1 row in set (0.01 sec)

### 删除 ###

删除存储过程/函数语法如下：

drop { procedure | function } [if exists] `sp_name`;

需要注意，删除存储过程/函数的用户需要拥有该存储过程/函数alter routine权限，且一次只能删除一个存储过程/函数。

## 变量 ##

**变量定义**

定义变量的语句如下：

declare var_name[, ...] var_type [default_value]

一次可定义多个相同类型的变量。变量的作用范围限于begin...end块中，且要位于块中所有语句之前。

**变量赋值**

1、直接赋值，使用set，可以赋常量或表达式：

set `var_name`=expr [, `var_name` expr] ...;

2、通过查询赋值（查询的返回结果必须只有一行）：

select `col_name`[, ...] into var_name[, ...] table_expr;

变量及其赋值可以参考sakila库的inventory\_in\_stock函数：

BEGIN
        DECLARE v_rentals INT;
        DECLARE v_out     INT;
    
    
        SELECT COUNT(*) INTO v_rentals
        FROM rental
        WHERE inventory_id = p_inventory_id;
        ...
    
        SELECT COUNT(rental_id) INTO v_out
        FROM inventory LEFT JOIN rental USING(inventory_id)
        WHERE inventory.inventory_id = p_inventory_id
        AND rental.return_date IS NULL;
        ...
    END

可以看到这个函数中，首先定义了变量v\_rentals和v\_out，因为它们数据类型相同，这一句也可以改写为：

declare v_rentals, v_out int;

后面的语句都是通过查询结果对这两个变量赋值。从出租表rental中统计出库存id与参数p\_inventory\_id相等的记录数量即指定货物出租的次数，并将其赋值给v\_rentals；从库存表inventory以及出租表rental中统计指定货物出租且未归还的次数，并赋值给v\_out。

## 条件 ##

条件的定义和处理，用于定义在语句执行过程中遇到问题时的处理方式，相当于Java中使用try...catch捕获、处理异常。条件的定义及处理语法如下：

-- 相当于为condition_value起别名为condition_name
    declare condition_name condition for condition_value;
    -- handler_type即对于条件发生后的处理方式，取值有以下两种：
    --  1、continue：跳过条件发生的语句，继续执行下一条
    --  2、exit：整个程序终止执行
    declare handler_type handler
        for condition_value[, ...]
        statement;

condition\_value的取值有以下几种：

*1、MySQL错误码*

*2、SQLState值*

*3、通过declare定义的condition\_name*

*4、SQLWARNING，即01开头的SQLState值，表示警告*

*5、NOT FOUND，即02开头的SQLState值，表示无数据*

*6、SQLEXCEPTION，表示所有没有被SQLWARNING和NOT FOUND捕获的SQLState值*

## 流程控制 ##

### if语句 ###

还是以上文中出现过的sakila中的函数inventory\_in\_stock为例：

BEGIN
        DECLARE v_rentals INT;
        DECLARE v_out     INT;
        ...
        IF v_rentals = 0 THEN
          RETURN TRUE;
        END IF;
        ...
        IF v_out > 0 THEN
          RETURN FALSE;
        ELSE
          RETURN TRUE;
        END IF;
    END

可以看到，if语句实现条件判断，与一般的编程语言类似，以if...end if包裹内容块，语法可以概括为：

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
    end if

### case语句 ###

case语句也用于实现条件判断，语法如下：

case case_value
    when when_value then statement_list
    [when when_value then statement_list] ...
    end case

尝试用case语句来改写上文if语句中的示例代码：

case v_rentals
    when 0 then return true;
    end case;
    
    case v_out
    when 0 the return true;
    else return false;
    end case;

在实际使用中，case更多被用于较为复杂的条件判断中，在有很多条件取值的情况下，case语句比if语句更为清晰。

### loop & leave语句 ###

loop语句可以实现简单的循环，退出循环则可以使用leave语句配合，语法如下：

[loop_label]:loop
    statement_list
    leave [loop_label]
    end loop [loop_label]

做个简单的练习，还是使用sakila库，构造一个存储过程，连续向actor表中插入10条数据：

USE `sakila`;
    DROP procedure IF EXISTS `actor_insert`;
    
    DELIMITER $$
    USE `sakila`$$
    CREATE PROCEDURE `actor_insert` ()
    modifies sql data
    BEGIN
    set @i = 0;
    ins: loop
    if @i = 10 then
    leave ins;
    end if;
    insert into actor(`first_name`, `last_name`) values ('test_loop', @i);
    set @i = @i + 1;
    end loop ins;
    END$$
    
    DELIMITER ;

最后可以看看过程中定义的@i的值是否为10，即可判断是否循环了10次：

select @i;
    +------+
    | @i   |
    +------+
    |   10 |
    +------+
    1 row in set (0.01 sec)

### iterate语句 ###

iterate用于循环中跳过当前循环剩下的语句、直接进入下一轮循环。重新定义一下上文中的过程actor\_insert，使@i为偶数时插入数据，否则不插入，@i最小为0、最大为10：

USE `sakila`;
    DROP procedure IF EXISTS `actor_insert`;
    
    DELIMITER $$
    USE `sakila`$$
    CREATE DEFINER=`root`@`%` PROCEDURE `actor_insert`()
        MODIFIES SQL DATA
    BEGIN
    set @i = 0;
    ins: loop
    set @i = @i + 1;
    if @i > 10 then
    leave ins;
    end if;
    if mod(@i, 2) = 0 then
    iterate ins;
    else
    insert into actor(`first_name`, `last_name`) values ('test_iterate', @i);
    end if;
    end loop ins;
    END$$
    
    DELIMITER ;

### repeat语句 ###

repeat语句用于实现有条件控制的循环，当满足条件时即可退出循环。语法如下：

[begin_label:] repeat
    statement_list
    util search_condition
    end repeat [end_label];

我们还是以上文的过程actor\_insert为例，将其改为用repeat语句控制循环：

USE `sakila`;
    DROP procedure IF EXISTS `actor_insert`;
    
    DELIMITER $$
    USE `sakila`$$
    CREATE DEFINER=`root`@`%` PROCEDURE `actor_insert`()
        MODIFIES SQL DATA
    BEGIN
    set @i = 0;
    ins: repeat
    insert into actor(`first_name`, `last_name`) values ('test_repeat', @i);
    set @i = @i + 1;
    until @i = 10
    end repeat ins;
    END$$
    
    DELIMITER ;

### while语句 ###

while语句也是实现有条件控制的循环。与repeat语句相比，while语句相当于编程语言中的while...do循环，而repeat相当于while...do循环。语法如下：

[begin_label:] while search_condition do
    statement_list
    end while [end_label];

我们还是以上文的过程actor\_insert为例，将其改为用while语句控制循环：

USE `sakila`;
    DROP procedure IF EXISTS `actor_insert`;
    
    DELIMITER $$
    USE `sakila`$$
    CREATE DEFINER=`root`@`%` PROCEDURE `actor_insert`()
        MODIFIES SQL DATA
    BEGIN
    set @i = 0;
    ins: while @i < 10 do
    insert into actor(`first_name`, `last_name`) values ('test_while', @i);
    set @i = @i + 1;
    end while ins;
    END$$
    
    DELIMITER ;

## 光标 ##

光标，用于存储过程/函数中，对结果集进行循环处理。

通过一个例子理解光标的使用。写一个存储过程，查询payment表中的记录，根据不同的staff\_id来对各个员工的销售额amount进行累加：

USE `sakila`;
    DROP procedure IF EXISTS `payment_amount_count`;
    
    DELIMITER $$
    USE `sakila`$$
    CREATE PROCEDURE `payment_amount_count` ()
    reads sql data
    BEGIN
    declare i_staff_id int;
    declare d_staff_amount decimal(5, 2);
    declare cur_payment cursor for 
        select staff_id, amount from payment;
    declare exit handler for not found
        close cur_payment;
    
    set @staff1 = 1;
    set @staff2 = 2;
    
    open cur_payment;
    repeat
    fetch cur_payment into i_staff_id, d_staff_amount;
    case i_staff_id
    when 1 then set @staff1 = @staff1 + d_staff_amount;
    when 2 then set @staff2 = @staff2 + d_staff_amount;
    end case;
    until 0
    end repeat;
    close cur_payment;
    END$$
    
    DELIMITER ;
    
    select @staff1, @staff2;
    +----------+----------+
    | @staff1  | @staff2  |
    +----------+----------+
    | 33490.47 | 33924.05 |
    +----------+----------+
    1 row in set (0.00 sec)

上述存储过程中，运用了很多上文记录的内容，如变量的定义与赋值、条件处理、if语句、case语句和repeat语句。另一方面，可以通过该存储过程，总结出光标的使用过程：

*1、declare cur\_name cursor for select\_statement：声明光标；*

*2、open cur\_name：打开光标；*

*3、fetch cur\_name into var\_name\[, ...\]：移动光标；*

*4、close cur\_name：关闭光标。*

## 事件调度器 ##

使用事件调度器，可以使数据库安自定义的时间周期触发某种操作，可以理解为时间触发器。

事件调度器适用于定期收集统计信息、定期清理历史数据、定期数据库检查，但是在繁忙且要求性能的数据库服务器上要慎重部署和启用，同时，开启和关闭事件调度器需要超级用户权限。

创建事件调度器的语法如下：

create event `event_name`
    on schedule <schedule>
    do
    <event_body>;

举个例子，创建一个test表，每隔两秒向表中插入一条数据，一分钟后停止插入:

首先创建test表：

create table test(
    id int primary key auto_increment comment '主键',
    last_update datetime not null comment '最后更新时间'
    ) charset utf8mb4;

接下来创建一个事件调度器，定时执行指定任务：

create event test_insert_event
    on schedule
    every 2 second ends current_timestamp + interval 1 minute
    do
    insert into test.test(last_update) value(now());

为了任务调度器能够执行，我们还需要开启调度器：

set global event_scheduler=1;

此时就可以看看test表中是否有数据增长了。

另外，我们可以通过show events;语句来查看当前事件调度器信息：

show events\G;
    *************************** 1. row ***************************
                      Db: test
                    Name: test_insert_event
                 Definer: root@localhost
               Time zone: SYSTEM
                    Type: RECURRING
              Execute at: NULL
          Interval value: 2
          Interval field: SECOND
                  Starts: 2020-02-12 00:42:46
                    Ends: 2020-02-12 00:44:46
                  Status: ENABLED
              Originator: 6
    character_set_client: utf8
    collation_connection: utf8_general_ci
      Database Collation: utf8mb4_general_ci
    1 row in set (0.00 sec)

当任务调度器执行结束，该event会自动销毁。如果想要提前结束事件，则可以通过以下语句：

-- 删除
    drop event event_name;
    -- 或者，禁用
    alter event event_name disable；

# 触发器 #

触发器是与表有关的数据库对象，我们可以为某张表定义触发器，当满足触发器定义的条件时执行触发器中定义的语句集合。相当于编程语言中的事件监听与回调，而这里的监听对象是一张表。

## 查看触发器 ##

官方示例库sakila中已经定义了数个触发器，我们可以先查看一下这些触发器。

**查看数据库中所有触发器**

show triggers\G;

这条语句可以用于查看数据库中所有的触发器信息，但不能查询指定的触发器。

**查看指定触发器**

类似于视图、存储过程/函数，information\_schema库中也存放了所有的触发器信息，具体是在triggers表中，可以通过指定触发器名称查询该表，来查询指定的触发器。如查询触发器ins\_film：

select * from information_schema.triggers where trigger_name='ins_film'\G;

查询结果如下：

*************************** 1. row ***************************
               TRIGGER_CATALOG: def
                TRIGGER_SCHEMA: sakila
                  TRIGGER_NAME: ins_film
            EVENT_MANIPULATION: INSERT
          EVENT_OBJECT_CATALOG: def
           EVENT_OBJECT_SCHEMA: sakila
            EVENT_OBJECT_TABLE: film
                  ACTION_ORDER: 1
              ACTION_CONDITION: NULL
              ACTION_STATEMENT: BEGIN
        INSERT INTO film_text (film_id, title, description)
            VALUES (new.film_id, new.title, new.description);
      END
            ACTION_ORIENTATION: ROW
                 ACTION_TIMING: AFTER
    ACTION_REFERENCE_OLD_TABLE: NULL
    ACTION_REFERENCE_NEW_TABLE: NULL
      ACTION_REFERENCE_OLD_ROW: OLD
      ACTION_REFERENCE_NEW_ROW: NEW
                       CREATED: 2020-02-03 23:36:41.96
                      SQL_MODE: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                       DEFINER: root@localhost
          CHARACTER_SET_CLIENT: utf8mb4
          COLLATION_CONNECTION: utf8mb4_general_ci
            DATABASE_COLLATION: latin1_swedish_ci
    1 row in set (0.01 sec)

## 创建触发器 ##

我们可以根据上文的ins\_film这个触发器，来总结创建触发器的语法。

首先，触发器需要一个作用对象，这个对象必须是一张表，即ins\_film中的event\_object\_table: film；然后，还需要一个触发的条件，即ins\_film中的event\_manipulation: insert；接着还要有触发的时机，是在触发条件之前呢还是之后，即ins\_film中的action\_timing: after；最后就是触发器的SQL语句集了，可以看到ins\_film的行为是在有新的数据插入film表之后，将相同的数据也插入到film\_text表。在SQL语句中，目标行为新产生的记录对象使用new表示，原先的记录对象用old表示。还需要注意，MySQL触发器仅支持行触发。

可以总结出创建触发器的语法如下：

create trigger trigger_name
    trigger_timing
    trigger_event
    on table_name
    for each row
    trigger_body;

**trigger\_timing **即触发的时机，取值有before和after，表示在目标行为之前执行SQL语句集还是之后；

**trigger\_event **即触发行为，取值有insert、update和delete。

作为练习，创建触发器test\_insert\_customer，当有新的记录插入customer表时，将新纪录的first\_name、last\_name和create\_date列值写入customer\_added表中。

先创建customer\_added表：

create table customer_added(
    id int primary key auto_increment comment '主键',
    first_name varchar(45) not null comment '名',
    last_name varchar(45) not null comment '姓',
    create_time datetime
    ) charset utf8mb4;

再创建触发器test\_insert\_customer：

delimiter $$
    create trigger test_insert_customer
    after insert
    on customer
    for each rows
    begin
    insert into customer_added(first_name, last_name, create_time)
    values(new.first_name, new.last_name, new.create_date);
    end; $$
    delimiter ;

最后验证一下，向customer表中插入一条记录：

检查一下customer\_added表：

select * from customer_added;
    +----+------------+-----------+---------------------+
    | id | first_name | last_name | create_time         |
    +----+------------+-----------+---------------------+
    |  1 | Tom        | Smith     | 2020-02-12 15:47:58 |
    +----+------------+-----------+---------------------+
    1 row in set (0.00 sec)

数据正确地插入了customer\_added表，因此我们创建的触发器无误。

## 删除触发器 ##

使用drop trigger一次可以删除一个触发器，未指定库名的情况下默认是当前库：

drop trigger [schema_name.]trigger_name;

例如删除上文创建的触发器test\_insert\_customer：

drop trigger sakila.test_insert_customer;
    Query OK, 0 rows affected (0.00 sec)