第八章 存储过程与触发器
1.简介
在实际开发中,经常会遇到为了实现某一功能需要编写一组SQL语句的情况,为了提高SQL语句的重用性,MySQL提供了存储过程来帮助用户减少没有必要的重复操作,从而提高开发效率。另外,数据库处理某些特定的事务时,也可以通过触发器来实现。

2.存储过程概述
存储过程是一组可以实现特定功能的SQL语句的集合。在大型数据库系统中,为了避免开发人员重复地编写相同的SQL语句,可以事先将常用或者复杂的工作用SQL语句写好并指定一个名称,然后经过编译和优化后存储在数据库服务器中。当用户需要数据库提供与已经定义好的存储过程功能相同的服务时,可以直接使用CALL语句在内部调用。这样一来,不仅提高了代码的精简度和运行速率,还可以减少数据在数据库和应用服务器之间的传输,从而提高数据处理的效率。

3.存储过程的优缺点
存储过程编译完成后存储在数据库中,被调用时并不需要进行再次编译。在编译存储过程之后,MySQL将其放入缓存,并为每个连接维护相应的存储过程高速缓存。存储过程的优点如下。
(1)存储过程有助于减少应用程序和数据库服务器之间的流量,应用程序不必发送多个冗长的SQL语句,只需要发送存储过程的名称和参数即可。
(2)存储过程允许组件式编程,可以提高SQL语句的重用性、共享性和可移植性。另外,也可以将数据库接口暴露给所有应用程序,使开发人员不必重复实现存储过程中已支持的功能。
(3)存储的程序是安全的。数据库管理员可以向访问数据库的存储过程应用程序授予适当的权限,而不用再为数据库或表设置复杂的权限。
存储过程是一组SQL语句的集合,编写时比较复杂,所以需要用户具有丰富的经验。另外,在编写存储过程时需要设置数据库对象的权限。使用存储过程时应该注意以下几点。
(1)如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。另外,如果在存储过程中使用大量逻辑操作,则CPU使用率也会上升。
(2)存储过程很难调试,只有少数的数据库管理系统允许调试存储过程,而MySQL不提供调试存储过程的功能,这可能会导致应用程序开发和维护阶段的问题。

4.创建存储过程
为了使读者清楚地了解存储过程的相关操作,在此将分别创建学生表students 和用户表users并插入数据,用于后面的例题演示。
MariaDB [Chapter_Four]> create table student(sid char(6) comment '学生学号',sname varchar(50) comment '学生姓名',age int comment '学生年龄',gender varchar(50) comment '学生性别');
Query OK, 0 rows affected (0.017 sec)

MariaDB [Chapter_Four]>
MariaDB [Chapter_Four]> insert into student(sid,sname,age,gender) values('1001','liwei',22,'male'),('1002','zhaohang',23,'male'),('1003','liuyi',21,'female'),('1004','wangwu',24,'male'),('1005','sunrei',21,'female'),('1006','zhouyan',20,'female'),('1007','tianqiang',19,'male'),('1008','moyan',23,'female'),('1009','dinghao',19,'male'),('1010','ludashi',22,'male'),('1011','dongxiaojie',25,'female');
Query OK, 11 rows affected (0.003 sec)
Records: 11 Duplicates: 0 Warnings: 0

MariaDB [Chapter_Four]>
MariaDB [Chapter_Four]> select * from student;
+------+-------------+------+--------+
| sid | sname | age | gender |
+------+-------------+------+--------+
| 1001 | liwei | 22 | male |
| 1002 | zhaohang | 23 | male |
| 1003 | liuyi | 21 | female |
| 1004 | wangwu | 24 | male |
| 1005 | sunrei | 21 | female |
| 1006 | zhouyan | 20 | female |
| 1007 | tianqiang | 19 | male |
| 1008 | moyan | 23 | female |
| 1009 | dinghao | 19 | male |
| 1010 | ludashi | 22 | male |
| 1011 | dongxiaojie | 25 | remale |
+------+-------------+------+--------+
11 rows in set (0.001 sec)

MariaDB [Chapter_Four]>
MariaDB [Chapter_Four]> create table users (id int,name varchar(200),age int,email varchar(200));
Query OK, 0 rows affected (0.006 sec)

MariaDB [Chapter_Four]> insert into users(id,name,age,email) values(1,'jack',21,'jack@qq.com'),(2,'tom',22,'tom@qq.com'),(3,'lisa',20,'lisa@qq.com'),(4,'peter',21,'peter@qq.com');
Query OK, 4 rows affected (0.004 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [Chapter_Four]> select * from users;
+------+-------+------+--------------+
| id | name | age | email |
+------+-------+------+--------------+
| 1 | jack | 21 | jack@qq.com |
| 2 | tom | 22 | tom@qq.com |
| 3 | lisa | 20 | lisa@qq.com |
| 4 | peter | 21 | peter@qq.com |
+------+-------+------+--------------+
4 rows in set (0.001 sec)

MariaDB [Chapter_Four]>
在MySQL中可以使用CREATE PROCEDURE语句创建存储过程,需要注意,前提是用户具有创建存储过程的权限。以root用户为例,查看该用户是否具有创建存储过程的权限,具体的SQL语句如下所示。
MariaDB [Chapter_Four]> select create_routine_priv from mysql.user where user='root';
+---------------------+
| create_routine_priv |
+---------------------+
| Y |
| Y |
| Y |
+---------------------+
3 rows in set (0.003 sec)

MariaDB [Chapter_Four]>
读者在查看不同用户的权限时应该注意修改SELECT语句中的用户名参数。MySQL中创建存储过程的语法格式如下所示。
create procedure sp_name(proc_parameter) [characteristic] routine_body
创建存储过程的语法格式中,各参数的含义如下。
CREATE PROCEDURE:创建存储过程的关键字。
sp_name:存储过程的名称。
proc_parameter:存储过程的参数列表。
characteristic:用于指定存储过程的特性。
routine_body:表示存储过程的主体部分,包含了在过程调用的时候必须执行的SQL语句,以BEGIN开始,以END结束。如果存储过程主体中只有一条SQL语句,可以省略BEGIN-END标志。proc_parameter参数列表项的具体形式如下。
[IN|OUT|INOUT] param_name type
上方形式中各参数的含义如表所示。
图片 5.png
另外,创建存储过程的语法格式中的characteristic项也有5个可选值,如表8.2所示。
图片 6.png
接下来将通过具体的实例演示如何创建存储过程。
创建一个带IN的存储过程,用于通过传入用户名查询表users中的用户信息。
MariaDB [Chapter_Four]> delimiter //
MariaDB [Chapter_Four]> create procedure sp_search(in p_name char(20)) begin if p_name is null or p_name='' then select * from users; else select * from users where name like p_name; end if; end//
Query OK, 0 rows affected (0.007 sec)

MariaDB [Chapter_Four]>
由于MySQL默认的语句结束符为分号,为了避免与存储过程中的结束符产生冲突,上方语句中使用“delimiter //”语句将 MySQL 的结束符设置为//。当然,存储过程创建完成后,也可以使用“delimiter ;”语句恢复MySQL默认的结束符,具体如下所示。
MariaDB [Chapter_Four]> delimiter ;
存储过程创建完成后,可以使用CALL关键字调用该存储过程。例如,使用CALL调用sp_search存储过程的语句如下。
MariaDB [Chapter_Four]> call sp_search('lisa');
+------+------+------+-------------+
| id | name | age | email |
+------+------+------+-------------+
| 3 | lisa | 20 | lisa@qq.com |
+------+------+------+-------------+
1 row in set (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

MariaDB [Chapter_Four]>
从上方代码的执行结果可以看出,通过往存储过程sp_search中传入参数lisa,成功查询到了用户lisa的信息。
创建一个带OUT的存储过程,用于通过传入学生年龄查询表students中大于该年龄的学生信息,并且输出查询到的学生人数。
MariaDB [Chapter_Four]> delimiter //
MariaDB [Chapter_Four]> create procedure sp_search2(in age int,out num int) begin if age is null or age='' then select * from student; else select * from student where stu_age>age; end if; select found_rows() into num; end//
Query OK, 0 rows affected (0.002 sec)

MariaDB [Chapter_Four]>
从上方代码的执行结果可以看出,sp_search2存储过程创建成功,可以使用CALL调用该存储过程。

上方语句中,通过向sp_search2存储过程中传入参数22,查询到了年龄大于22岁的学生信息,随后将统计结果以@search_num 变量的形式输出。通过查询@search_num可以得到存储过程统计的用户个数。
MariaDB [Chapter_Four]> call sp_search('lisa');
+--------+----------+----------+---------+
| stu_id | stu_name | stu_sex | stu_age |
+--------+----------+----------+---------+
| 1 | aa | 女 | 23 |
| 5 | ee | 女 | 23 |
| 7 | gg | 男 | 23 |
+--------+----------+----------+---------+
1 row in set (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

MariaDB [Chapter_Four]>

MariaDB [Chapter_Four]> select @search_num;
+-------------+
| @search_num |
+-------------+
| 3 |
+-------------+
1 row in set (0.001 sec)

MariaDB [Chapter_Four]>
从以上代码的执行结果可以看出,统计结果与输出内容一致。

5.查看存储过程
在MySQL中可以使用3种方式查看存储过程。
1.使用SHOW STATUS语句查看存储过程
MySQL中使用SHOW STATUS语句可以查询存储过程的状态。例如,查看名称以sp_开头的存储过程的状态。
MariaDB [Chapter_Four]> show procedure status like 'sp_%'\G
*************************** 1. row ***************************
Db: Chapter_Four
Name: sp_search
Type: PROCEDURE
Definer: root@localhost
Modified: 2022-03-10 21:33:05
Created: 2022-03-10 21:33:05
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: Chapter_Four
Name: sp_search2
Type: PROCEDURE
Definer: root@localhost
Modified: 2022-03-10 21:37:14
Created: 2022-03-10 21:37:14
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
2 rows in set (0.002 sec)

MariaDB [Chapter_Four]>
从代码的执行结果可以看出,数据库中以sp_开头的存储过程一共有2个。另外,通过使用SHOWSTATUS语句也可以查看存储过程的Db、Name、Type等信息。
2.使用SHOW CREATE语句查看存储过程
使用SHOW CREATE语句可以查看存储过程的详细创建信息。例如,查询名为sp_search2的存储过程。
MariaDB [Chapter_Four]> show create procedure sp_search2\G
*************************** 1. row ***************************
Procedure: sp_search2
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=root@localhost PROCEDURE sp_search2(in age int,out num int)
begin if age is null or age='' then select * from student; else select * from student where stu_age>age; end if; select found_rows() into num; end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.000 sec)

MariaDB [Chapter_Four]>
从上方代码的执行结果可以看出,通过SHOW CREATE语句查看到了存储过程的CreateProcedure等信息。
3.通过information_schema.Routines表查看存储过程
存储过程信息的存储与用户信息的存储方式一样,都是存放在相关的表中。用户可以查询information_schema库下的Routines表来获取相应的存储过程信息。例如,通过查询表检索出sp_search存储过程的信息。
MariaDB [Chapter_Four]> select * from information_schema.routines where routine_name='sp_search' and routine_type='procedure'\G
*************************** 1. row ***************************
SPECIFIC_NAME: sp_search
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: Chapter_Four
ROUTINE_NAME: sp_search
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 if p_name is null or p_name='' then select * from users; else select * from users where name like p_name; end if; end
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2022-03-10 21:33:05
LAST_ALTERED: 2022-03-10 21:33:05
SQL_MODE: STRICT_TRANS_TABLES,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.002 sec)

MariaDB [Chapter_Four]>
从上面的输出结果可以看出,information_schema.Routines表不仅包含了存储过程sp_searche的基本信息,而且包含了存储过程详细的创建语句。

6.删除存储过程
MySQL中使用DROP语句即可删除存储过程。例如,将存储过程sp_search删除的SQL语句如下所示。
MariaDB [Chapter_Four]> drop procedure sp_search;
Query OK, 0 rows affected (0.001 sec)

MariaDB [Chapter_Four]>
从执行结果可以看出,存储过程删除成功。

7.触发器概述
触发器(trigger)是MySQL中与表事件有关的一种特殊存储过程,在满足定义条件或用户对表进行INSERT、DELETE、UPDATE操作时,会激活触发器并执行触发器中定义的语句集合。触发器类似于约束,但是比约束具有更强大的数据控制能力,它的存在可以保证数据的完整性,其优点如下所示。
(1)自动执行:当满足触发器条件时,系统会自动执行定义好的相关操作。
(2)级联更新:可以通过数据库中的相关表进行层叠更改。
(3)强化约束:能够引用其他表中的列,从而实现比CHECK约束更为复杂的约束。
(4)跟踪变化:可以阻止数据库中未经允许的更新和变化。
触发器基于行触发,用户的删除、新增或者修改操作都可能会激活触发器。触发器的应用场景主要有以下3项。
(1)提高安全性:基于时间或权限限制用户的操作,例如,不允许下班后和节假日修改数据库数据,不允许某个用户做修改操作,等等。
(2)操作审计:跟踪用户对数据库的操作,审计用户操作数据库的语句,把用户对数据库的更新写入审计表。
(3)实现复杂的数据修改和数据完整性规则:通过触发器产生比规则更加复杂的限制,从而实现表的连环更新和非标准化的完整性检查和约束。

8.创建触发器
MySQL中创建触发器的语法格式如下所示。
create trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt
上方语法格式中各参数的含义如表所示。
图片 7.png
用户可以创建6种不同类型的触发器,分别为BEFORE INSERT、BEFORE UPDATE、BEFOREDELETE、AFTER INSERT、AFTER UPDATE和AFTER DELETE。不同类型触发器的触发条件不同,另外在MySQL中还定义了LOAD DATA语句和REPLACE语句,用于激活触发器。LOAD DATA语句用于将文件中的数据加载到数据表中,相当于一系列的INSERT操作;REPLACE语句用于表中存在PRIMARY KEY或UNIQUE索引时,若插入的数据和原来的PRIMARYKEY或UNIQUE索引一致,会删除原来的数据进行更换。各种触发器的激活和触发时机如下。
(1)INSERT型触发器:当向表中插入数据时激活触发器,可以通过INSERT、LOAD DATA或REPLACE语句触发。
(2)UPDATE型触发器:当更改某一行数据时激活触发器,可以通过UPDATE语句触发。
(3)DELETE型触发器:当删除某一行数据时激活触发器,可以通过DELETE和REPLACE语句触发。
下面将通过test1表和test2表为读者演示不同类型触发器的使用方法,表结构如下。
图片 8.png
为test1表创建名为trigger_test1的触发器,实现向test1表添加记录后自动将新记录备份到test2表中,操作代码如下。
图片 9.png
从代码的执行结果可以看出触发器创建成功。尝试向test1表中插入一条数据。
图片 10.png
从以上执行结果可以看出数据插入完成,此时查看test2表中是否存在相应的数据。
图片 11.png
从以上执行结果可看出,系统已将test1表中新插入的数据自动备份到test2表中。这是因为在进行INSERT操作时,激活了触发器trigger_test1,触发器自动向test2表中插入了同样的数据。
需要注意,trigger_test1中使用到的NEW关键字表示新插入的数据,MySQL中与之相对的关键字为OLD,两者在不同类型触发器中代表的含义如下。
(1)在INSERT型触发器中, NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据。
(2)在UPDATE型触发器中,NEW用来表示将要或已经修改为的新数据,OLD用来表示将要或已经被修改的原数据。
(3)在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据。NEW关键字和OLD关键字的使用语法格式如下。
NEW[OLD].columnName
上方语法格式中,columnName表示相应数据表的某个列名。另外,NEW可以在触发器中使用SET进行赋值,以免造成触发器的循环调用,而OLD仅为可读。
下面演示OLD在DELETE型触发器中的使用方法。
创建触发器tri_del_test1,实现删除test1表中的一条记录后,将test2表中的对应记录删除。
图片 12.png
尝试将test1表中id等于1的数据删除。
图片 13.png
从执行结果可以看出数据删除完成,此时查看test2表中的数据是否存在。
select * from test2;
Empty set (0.00 sec)
可以看出 test2 表中对应的数据同样被删除了,这表明触发器被成功触发。

9.查看触发器
MySQL中使用SHOW TRIGGERS语句查看触发器,SQL语句如下所示。
图片 14.png
图片 15.png
使用SHOW TRIGGERS语句可以查看到两个触发器的Event、Table、Statement等信息。另外,用户也可以通过查看information_schema.triggers表来获取触发器信息,SQL语句如下所示。
information_schema.triggers表包含了触发器的Field、Type等信息。

10.删除触发器
MySQL 中删除触发器的语法格式如下。
图片 16.png
上方语法格式中,trigger_name表示需要删除的触发器名称,IF EXISTS是可选的,表示如果触发器不存在,不发生错误,而是产生一个警告。例如,将trigger_test1触发器删除的SQL语句如下。
图片 17.png
可以看出触发器删除成功。用户可以使用SHOW TRIGGERS语句进行验证,此处不再赘述。

上一篇 下一篇