【数据库】五、MySQL过程式数据库对象（自定义函数、存储过程、游标、触发器）

最新推荐文章于 2024-07-06 13:51:47 发布

Suhan42

最新推荐文章于 2024-07-06 13:51:47 发布

阅读量803

点赞数 35

分类专栏：数据库MySQL 文章标签：数据库 mysql sql database

本文链接：https://blog.csdn.net/weixin_51350847/article/details/139900713

版权

数据库MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

五、MySQL过程式数据库对象

文章目录

五、MySQL过程式数据库对象
@[toc]
1.自定义函数function
1.1定义函数
1.2调用函数
1.3维护函数
1.3.1查看函数的定义
1.3.2删除函数定义
1.3.3修改

2.存储过程procedure
2.1创建
2.2调用
2.2.1不带输入参数存储过程的调用
2.2.2带输入参数存储过程的调用
2.2.3带输出参数存储过程的调用

2.3查看存储过程的定义
2.4删除存储过程
2.5修改存储过程

🙌存储过程与函数的比较
3.游标Cursor
3.1声明游标
3.2打开游标
3.3读取游标
3.4关闭游标

4.触发器Trigger
4.1创建
4.2使用触发器实现检查约束
4.3使用触发器实现外键级联选项
4.4查看
4.5删除
4.6注意事项

MySQL中的编程语言是一种非过程化的语言，用户或应用程序都是通过它来操作数据库的。当要执行的任务不能由单个SQL语句来完成时，可以通过某种方法将多条SQL语句组织到一起，来共同完成一项任务。

数据库开发人员在进行数据库开发时，为了实现一定的功能，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句块，称之为“存储程序”。

本章主要介绍函数、存储过程、触发器的实现。

MySQL的存储程序可分以下四大类：

存储函数(stored function)。返回一个计算结果，该结果可以用在表达式里。
存储过程(stored procedure)。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。
触发器(trigger)。与数据表相关联，当那个数据表被INSERT、DELETE、UPDATE语句修改时，触发器将自动执行。
事件(event)。根据时间表在预订时刻自动执行。

1.自定义函数function

自定义函数的创建及调用方法

1.1定义函数

MySQL允许数据库开发人员根据实际业务需要创建自定义函数。
创建自定义函数的语法格式如下：

CREATE FUNCTION 函数名(参数1,参数2,…) RETURNS 返回值的数据类型
[函数选项]
BEGIN
	[函数体]
	RETURN 语句
END;

函数选项是由以下一种或几种选项组合而成的：

language sql：默认选项，用于说明函数体使用SQL语言编写。
|[not] deterministic：确定性

当函数返回不确定值时，该选项是为了防止“复制”时的不一致性。如果函数总是对同样的输入参数产生同样的结果，则它被认为是“确定的”，否则就是“不确定”的。

例如，函数返回系统当前的时间，返回值是不确定的。如果既没有给定deterministic，也没有给定not deterministic，默认就是不确定的。
|{contains sql|no sql|reads sql data|modifies sql data}：
- contains sql：表示函数体中包含SQL语句，但不包含读或写数据的语句。
- no sql：表示函数体中不包含SQL语句。
- reads sql data：表示函数体中包含SELECT查询语句，但不包含更新语句。
- modifies sql data：表示函数体中包含更新语句。如果上述选项没有明确指定，默认是contains sql。
|sql security {definer|invoker}：用于指定函数的执行许可。
- Definer：表示该函数只能由创建者调用。
- Invoker：表示该函数可以被其他数据库用户调用，默认是definer。
|comment '注释'：为函数添加功能说明等注释信息。

例子：

-- 【例10-1】在jwgl数据库中，创建一个计算学生年龄的函数。该函数接收学生的学号，通过查询student表返回该学生的年龄。
USE jwgl;
DELIMITER $$
CREATE function fn_age(XH CHAR(10))  RETURNS INT
Reads sql data
	BEGIN
		DECLARE age int;
		SELECT YEAR(now())-YEAR(stu_birth) INTO age 
		FROM student WHERE stu_no=XH;
		RETURN age;
	END $$
DELIMITER ;

此处函数选项设置为Reads sql data，是因为在函数中使用了一条SELECT语句。

由于MySQL局部变量前面没有@符号，在命令局部变量时注意要与字段名称区别开。

另外，自定义函数的函数体使用SELECT语句不能产生结果集，否则将产生错误。

创建函数需要注意以下几点：

自定义函数是数据库的对象，因此，创建自定义函数时，需要指定该自定义函数隶属于哪个数据库。
同一个数据库内，自定义函数名不能与已有的函数名（包括系统函数名）重名。建议在自定义函数名中统一添加“fn_”前缀或“_fn”后缀。
函数的参数无需使用DECLARE命令定义，但它仍然是局部变量，且必须提供参数的数据类型。自定义函数如果没有参数，则使用空参数“( )”即可。
函数必须指定返回值数据类型，且须与RETURN语句中的返回值数据类型相近（长度可以不同）。

1.2调用函数

调用存储函数的方法就和使用系统的内置函数一样，使用SELECT语句就可以查看函数的返回值。

使用下面命令调用上面函数：

SELECT fn_age('1801010101') AS 年龄;

例子1：

-- 【例10-2】定义一个函数getAverScore，可以根据学生的姓名计算出学生的平均成绩。
DELIMITER $$
CREATE FUNCTION getAverScore(s_name varchar(10)) RETURNS int
Reads sql data
BEGIN
 DECLARE averScore int;
 SELECT avg(score) INTO averScore FROM score
 GROUP BY stu_no
 HAVING stu_no=
   (SELECT stu_no FROM student 
     WHERE  stu_name=s_name);
RETURN averScore;
END $$
DELIMITER ;

使用下面命令调用上面函数：

SELECT getAverScore('秦建兴') AS '秦建兴同学平均成绩';

例子2：

-- 【例】创建存储函数返回两个数相加之和。
-- 创建存储函数，代码如下：
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED) RETURNS SMALLINT no sql
BEGIN
	DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;
	SET a = x, b = y;
	RETURN a+b;
END;
-- 建立查询语句调用该自定义函数的语句如下：
set @num1=10;
set @num2=20;
set @result=addTwoNumber(@num1,@num2);
select @result;

1.3维护函数

1.3.1查看函数的定义

查看当前数据库中所有的自定义函数的信息：

show function status
-- 或
show  function  satus  like…

-- 例如：
show function status like 'fn_%';

使用命令show create function 函数名;可以查看指定函数的详细信息。

show create function fn_age;

函数的信息都保存在information_schema数据库中的routines表中，可以使用SELECT语句检索routines表，查询函数的相关信息。

SELECT * FROM information_schema.Routines
    WHERE ROUTINE_NAME = 'getAverScore';

1.3.2删除函数定义

使用MySQL命令DROP FUNCTION删除函数的定义，其语法格式如下：

DROP FUNCTION 函数名, …;
-- 例：
DROP FUNCTION fn_age;

1.3.3修改

可以使用drop function语句暂时将函数的定义删除，然后再使用create function语句重新创建相同名字的函数即可。

这种方法对于存储过程、视图、触发器的修改同样适用。

2.存储过程procedure

存储过程的创建及调用方法

在MySQL中，可以定义一段程序存放在数据库中，这样的程序称为存储过程，它是最重要的数据库对象之一。存储过程实质上就是一段代码，它可以由声明式SQL语句和过程式SQL语句组成。存储过程可以由程序、触发器或者另一个存储过程调用，从而激活它

它书写更简单，使用频率更高。一个数据库中，可能函数不多，但是存储过程很多。

2.1创建

创建存储过程命令格式如下：

CREATE PROCEDURE 存储过程名(参数1, 参数2, …)
[存储过程选项]
BEGIN
	存储过程语句块;
END;

存储过程的参数，格式如下：

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

当有多个参数的时候中间用逗号隔开，存储过程可以有0个、1个或多个参数。

关键字分别是IN、OUT和INOUT 分别为输入参数、输出参数和输入/输出参数。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入/输出参数既可以充当输入参数，也可以充当输出参数。

存储过程也可以不加参数，但是名称后面的括号是不可省略的。

参数的名字不要等于列的名字。

存储过程选项可能由以下一种或几种存储过程选项组合而成：

language sql
|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security {definer|invoker}
|comment ‘注释’

2.2调用

调用存储过程需使用CALL命令。另外，还要向存储过程传递IN参数、OUT参数或者INOUT参数。

2.2.1不带输入参数存储过程的调用

例子1：

-- 【例10-3】创建不带参数存储过程，从jwgl数据库的student、course、score三个表中查询，返回学生的学号、姓名、课程名和成绩。
DELIMITER $$
CREATE  PROCEDURE  stu_cj()
Reads sql data
BEGIN
	SELECT  student.stu_no AS 学号,stu_name AS 姓名, course_name AS 课程名,score AS 成绩
	FROM  student INNER  JOIN  score  
	ON  student.stu_no=score.stu_no INNER  JOIN  course
	ON  score.course_no=course.course_no;
END $$
DELIMITER ;

-- 调用：
call stu_cj();

例子2：

【例10-4】创建带输入参数的存储过程。从jwgl数据库的student、course、score三个表中查询任一同学指定课程的成绩。
DELIMITER $$
CREATE  PROCEDURE  proc_stu_cj(IN sname char(10),IN cname char(16))
Reads sql data
BEGIN
	SELECT student.stu_no,stu_name,course_name,score
	FROM  student INNER  JOIN  score ON  student.stu_no=score.stu_no 
	INNER  JOIN course ON score.course_no=course.course_no
	WHERE student.stu_name=sname AND course.course_name=cname;
END $$
DELIMITER ;

2.2.2带输入参数存储过程的调用

调用proc_stu_cj存储过程的命令如下：

方法一：

SET @sname='秦建兴';
SET @cname='数据结构';
CALL proc_stu_cj(@sname,@cname);

方法二：

CALL proc_stu_cj('秦建兴','数据结构');

例子：

【例10-5】创建带输出参数的存储过程。在jwgl数据库中创建一个存储过程用于计算指定学生（姓名）各科成绩的平均分，存储过程中使用了一个输入参数和一个输出参数。
DELIMITER $$
CREATE PROCEDURE  proc_avg_score(IN sname char(10),OUT avg_cj float)
Reads sql data
BEGIN
	SELECT avg(score) INTO avg_cj FROM student,score
	WHERE stu_name=sname AND student.stu_no=score.stu_no;
END $$
DELIMITER ;

2.2.3带输出参数存储过程的调用

调用proc_avg_score存储过程的命令如下：

SET @sname='秦建兴';
SET @avg_cj=0;
CALL proc_avg_score(@sname,@avg_cj);
SELECT round(@avg_cj,1);

创建存储过程需要注意以下几点：

存储过程是数据库的对象。在创建存储过程时，需要指定该存储过程隶属于哪个数据库。同一个数据库内，存储过程名不能与已经存在的存储过程重名，建议在存储过程名中统一添加前缀“proc_”或后缀“_proc”。
与函数相同之处在于，存储过程的参数也是局部变量，也需要提供参数的数据类型。与函数不同的是，存储过程有3种类型的参数IN参数、OUT参数以及INOUT参数：
1. IN 代表输入参数（默认情况下为IN参数），表示该参数的值必须由调用程序指定；
2. OUT 代表输出参数，表示该参数的值经存储过程计算后，将OUT参数的计算结果返回给调用程序；
3. INOUT 参数代表既是输入参数，又是输出参数，表示该参数可以由调用程序指定，又可以将参数的计算结果返回给调用程序。
参数的名字不要等于列的名字。
存储过程如果没有参数，使用空参数“( )”即可。

2.3查看存储过程的定义

查看当前数据库中所有的自定义函数的信息：

show procedure status
-- 或
show procedure status like ...;
-- 例：
show procedure status like 'proc_%';

使用命令“SHOW CREATE PROCEDURE 存储过程名;”可以查看指定函数的详细信息。

-- 【例】查看stu_cj存储过程的详细信息。
SHOW CREATE PROCEDURE stu_cj;

存储过程的信息保存在information_schema数据库中的routines表中，可以使用select语句查询存储过程的相关信息。

-- 【例】查看proc_avg_score存储过程信息。
SELECT * FROM information_schema.routines WHERE routine_name='proc_avg_score';

2.4删除存储过程

如果某个存储过程不再使用，可以使用drop procedure语句将其删除，其语法格式如下：

DROP PROCEDURE 存储过程名;

-- 【例】删除存储过程stu_cj。
DROP PROCEDURE stu_cj;

2.5修改存储过程

需要说明的是，由于存储过程保存的是一段存储程序，没有保存表数据。

因此，当需要修改存储过程的定义时，可以使用DROP PROCEDURE语句将存储过程删除，然后再使用CREATE PROCEDURE语句重新创建相同名字的存储过程即可。

🙌存储过程与函数的比较

MySQL的存储过程和函数统称为stored routines，它们都可以看作是一个“加工作坊”。什么时候需要定义函数，什么时候需要定义为存储过程，事实上没有严格的区分，不过一般而言，存储过程实现的功能要复杂一点，而函数实现的功能针对性更强一点。

存储过程和函数之间的共同特点有如下几点：

（1）应用程序调用存储过程或函数时，只需要提供存储过程名或者函数名，以及参数信息，无需将若干条MySQL语句发送到MySQL服务器上，从而节省了网络开销。

（2）存储过程或者函数可以重复使用，从而可以减少数据库开发人员，尤其是应用程序开发人员的工作量。

（3）使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。

存储过程和函数的不同之处，主要有以下几方面：

（1）函数必须有且仅有一个返回值，且必须指定返回值的数据类型。存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用OUT或者INOUT参数定义。

（2）在函数体内可以使用SELECT…INTO语句为某个变量赋值，但不能使用SELECT语句返回结果或者结果集；存储过程则没有这方面的限制。

（3）函数可以直接嵌入到SQL语句或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用，调用时需要使用CALL关键字。

（4）函数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，不能在函数体内使用预处理SQL语句。存储过程的限制相对比较少，基本上所有的SQL语句或者MySQL命令都可以在存储过程中使用。

（5）应用程序（例如Java、PHP等）调用函数时，通常将函数封装到SQL字符串中进行调用；应用程序调用存储过程时，必须使用CALL关键字进行调用。如果应用程序希望获取存储过程的返回值，必须给存储过程的OUT参数或者INOUT参数传递MySQL会话变量，这样应用程序才能通过该会话变量获取存储过程的返回值。

3.游标Cursor

数据库开发人员在编写存储过程（或者函数）等存储程序时，有时需要存储程序中的MySQL代码扫描SELECT结果集中的数据，并对结果集中的每条记录进行简单处理，通过MySQL的游标机制可以解决此类问题。

游标本质上是一种能从SELECT结果集中每次提取一条记录的机制，因此游标与SELECT息息相关。MySQL支持简单的游标。游标一定要在存储过程或函数中使用，不能单独在查询中使用。

使用一个游标需要用到4条特殊语句：

DECLARE CURSOR（声明游标）
OPEN CURSOR（打开游标）
FETCH CURSOR（读取游标）
CLOSE CURSOR（关闭游标）

3.1声明游标

声明游标的语法格式如下：

DECLARE 游标名 CURSOR FOR select语句

这个语句声明一个游标，也可以在存储过程中定义多个游标。但是一个块中的每个游标必须有唯一的名字。这里的SELECT语句不能有INTO子句。

3.2打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。其语法格式如下：

OPEN 游标名

3.3读取游标

游标打开后，就可以使用FETCH CURSOR语句从中读取数据，其语法格式如下：

FETCH 游标名 INTO 变量名…

FETCH…INTO语句与SELECT…INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。变量名指定是存放数据的变量。

第一次执行FETCH语句时，从结果集中提取第一条记录，再次执行FETCH语句时，从结果集中提取第二条记录，…，依此类推。FETCH语句每次从结果集中仅提取一条记录，因此，FETCH语句需要循环语句配合，这样才能实现对整个结果集的遍历。

当使用FETCH语句从游标中提取最后一条记录后，再次执行FETCH语句时，将产生“error 1329:NO data to FETCH”错误信息，数据库开发人员可以针对MySQL错误代码1329自定义错误处理程序，以便结束游标对结果集的遍历。
游标的自定义错误处理程序应该放在声明游标语句之后。游标通常结合自定义错误处理程序一起使用。

3.4关闭游标

游标使用完后，要及时关闭游标。其语法格式如下：

CLOSE 游标名

例子：

【例10-8】创建存储过程，使用游标在score表逐行检查某门课程的成绩，如果成绩在55~59之间，自动将成绩修改为60。
DELIMITER $$
CREATE PROCEDURE proc_update_score(IN kch char(6))
Modifies sql data
BEGIN 
	DECLARE xh char(10);
	DECLARE cj float;
	DECLARE state char(10);
	DECLARE update_cj_cursor CURSOR FOR SELECT stu_no,score FROM score WHERE course_no=kch;
	DECLARE continue HANDLER FOR 1329 SET state='遍历完成';
	OPEN update_cj_cursor;
	REPEAT
    	FETCH update_cj_cursor INTO xh,cj;
    	IF (cj>=55 AND cj<60) THEN SET cj=60;
    	END IF;
    	UPDATE score SET score=cj WHERE stu_no=xh AND course_no=kch;
    	UNTIL state='遍历完成'
	END REPEAT;
  CLOSE update_cj_cursor;
END $$
DELIMITER ;

4.触发器Trigger

触发器（Trigger）是一种特殊的存储过程，它也是嵌入到MySQL中的一段程序。和存储过程不同的是，触发器不需要使用CALL语句来调用，也不需要手工启动。触发器是由事件来触发某个操作过程，事件包括INSERT、UPDATE和DELETE语句。只要当一个预定义的事件发生时，触发器将会自动执行。

触发器是用来保护表中数据的。触发器基于一个表创建，但是可以针对多个表进行操作，所以触发器可以用来对表实施复杂的完整性约束。

触发器是通过事件触发而由系统自动执行的。

当对某一表进行插入(Insert)、修改(Update)、删除(Delete)操作时，MySQL会自动执行触发器所定义的SQL语句，从而实现业务规则和数据完整性。

触发器是一种与表操作（INSERT、UPDATE、DELETE）有关的数据库对象。触发器定义了一系列操作，这一系列操作称为触发程序。当触发器所在表上出现INSERT、UPDATE以及DETETE操作时，将激活触发器，即表的操作事件触发表上的触发程序的执行。

触发器具有以下优点：

触发器自动执行。当对表进行INSERT、 UPDATE以及DETETE操作，试图修改表中的数据时，相应操作的触发器立即自动执行。
触发器可以通过数据库中相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
触发器可以实现用CHECK约束实现不了的复杂约束。与CHECK约束相比，触发器可以引用其他表中的列。
触发器可以维护冗余数据，实现外键级联选项等。

4.1创建

CREATE TRIGGER语句语法格式如下：

CREATE TRIGGER 触发器名
AFTER/BEFORE INSERT/UPDATE/DELETE 
ON 表名
FOR EACH ROW 
BEGIN
	SQL语句;  #触发程序
END;

说明：

触发器是数据库对象，因此创建触发器时，需要指定该触发器属于哪一个数据库。
触发器是在表上创建的。这个表必须是基表，不能是临时表，也不能是视图。
MySQL的触发事件有三种：INSERT、UPDATE及DELETE。
- INSERT：将新记录插入表时激活触发程序。
- UPDATE：更改表中的记录时激活触发程序。
- DELETE：从表中删除记录叶激活触发程序。
触发器的触发时间有两种：BEFORE和AFTER。
- BEFORE表示在触发事件发生之前执行触发程序。
- AFTER表示在触发事件发生之后执行触发程序。
FOR EACH ROW表示行级触发器。
目前，MySQL仅支持行级触发器，不支持语句级别的触发器。FOR EACH ROW表示INSERT、UPDATE及DELETE操作影响的每一条记录都会执行一次触发程序。
触发程序中的SELECT语句不能产生结果集。
触发程序中可以使用old关键字与new关键字。
- ①当向表中插入新记录时，在触发程序中可以使用new关键字表示新记录。当需要访问新记录中的某个字段时，可以使用“new.字段名”进行访问。
- ②当从表中删除某条旧记录时，在触发程序中可以使用old关键字表示旧记录。当需要访问旧记录中的某个字段时，可以使用“old.字段名”进行访问。
- ③当修改表中的某条记录时，在触发程序中可以使用new关键字表示修改后的记录。使用old关键字表示修改前的记录。当需要访问修改后的记录中的某个字段时，可以使用“new.字段名”进行访问。当需要访问旧记录中的某个字段时，可以使用**“old.字段名**”进行访问。
- ④old记录是只读的，在触发程序中只能引用它，但不能更改它。在BEFORE触发程序中，可使用“SET new.字段名=值”更改new记录的值。但在AFTER触发程序中，不能使用“SET new.字段名=值”更改new记录的值。
- ⑤对于INSERT操作，只有new是合法的。
- 对于DELETE操作，只有old是合法的。
- 对于UPDATE操作，new和old都是合法的。

4.2使用触发器实现检查约束

-- 【例10-9】创建触发器实现向score表插入学生成绩值时，要求成绩在0~100之间（包括0和100）。
DELIMITER $$
CREATE TRIGGER score_insert_before BEFORE insert ON score for each row
BEGIN
  IF new.score<0 OR new.score>100 THEN
    INSERT INTO mytable values(0);
  END IF;
END $$
DELIMITER ;

与其他数据库管理系统不同，使用触发器实现检查约束时，MySQL触发器暂不支持撤销undo、退出exit等操作，触发程序正常运行期间无法中断其正常执行。为了阻止触发程序继续执行，可以在触发程序中定义一条出错的语句或者不可能完成的SQL语句。

上述例题仅仅实现了“插入”检查，也可以参照上述例题创建针对“修改”操作的触发器，负责对score表的update操作进行检查。

4.3使用触发器实现外键级联选项

对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、set null或者no action，外键约束关系可以交由InnoDB存储引擎自动维护。

例如，如果由于某原因删除student表中的某个学生记录，那么由于student表对于score表而言是外键约束的被参照表，为了保证数据的一致性，应该同时将score表中该学生的成绩数据一并删除。

这种操作可以通过创建外键时的级联删除选项实现，如果在外键约束中没有级联删除选项，也可以通过触发器实现。通常通过触发器实现级联删除操作。

-- 【例10-10】创建一个触发器，当从student表删除学生记录时，同时删除该学生在score表中的所有成绩记录。（为了实现这一操作，需要删除score表对于student的外键约束）
DELIMITER $$
CREATE TRIGGER student_delete_after AFTER delete ON student 
for each row
BEGIN
  DELETE FROM score WHERE stu_no=old.stu_no;
END $$
DELIMITER ;

接下来，执行下面命令从student表中删除学号为’1801010101’的学生信息，观察score表中该学生的成绩信息是否同时被删除。

SET FOREIGN_KEY_CHECKS = 0;  /*关闭外键检查*/
DELETE FROM student WHERE stu_no='1801010101';
SELECT * FROM score WHERE stu_no='1801010101';
SET FOREIGN_KEY_CHECKS = 1;  /*打开外键检查*/

从执行情况可以看到，当从student表中删除’1801010101’同学后，score表中该同学的成绩记录也同时被删除，这样就保证了score表中学生对student表中学生数据的参照完整性。

触发器除了可以实现检查约束和级联删除操作外，很多的时候还用来实现数据的级联修改操作。例如，在向score表中输入一条学生成绩记录后，如果学生的成绩在及格线以上，同时应当将该课程的学分累加到该学生的总学分字段上；再比如，对于一个销售管理系统，如果产生一个订单，那么首先应该检查商品的库存量是否满足订单需求，如果满足，订单可以生成，并应从库存信息中将该商品的库存量进行相应的减扣。

-- 例：创建触发器实现当修改course表中的课程号时，score表中的课程号自动修改。

DELIMITER $$
CREATE TRIGGER course_update_after AFTER update ON course for each row
BEGIN
  UPDATE score SET course_no=new.course_no
  WHERE course_no=old.course_no;
END$$
DELIMITER ;

测试触发器功能

SELECT * FROM course WHERE course_no='010002';
SELECT * FROM score WHERE course_no='010002';

SET FOREIGN_KEY_CHECKS = 0;
UPDATE course SET course_no='1111' WHERE course_no='010002';
SET FOREIGN_KEY_CHECKS = 1;

SELECT * FROM course WHERE course_no='1111';
SELECT * FROM score WHERE course_no='1111';

-- 【例10-11】创建触发器实现当向score表中插入一条成绩记录时，如果成绩及 格，则在student表中该学生总学分字段credit上累加该成绩记录对应课程的学分数。
DELIMITER $$
CREATE TRIGGER score_insert_after AFTER insert ON score for each row
BEGIN
  DECLARE xf int;
  SELECT course_credit INTO xf FROM course WHERE course_no=new.course_no;
  IF new.score>=60 THEN 
    UPDATE student SET credit=credit+xf WHERE stu_no=new.stu_no;
  END IF;
END$$
DELIMITER ;

执行下面命令，向score表插入一条成绩数据后，再检查学生’1902030101’的学分值。

INSERT INTO score VALUES('1902030101','010003',99);

从执行插入操作后的查询结果可以看到，向score表插入学生成绩数据后，相应地该学生的student表中的总学分credit值同时得到了修改，将课程的学分值累加到了学生的学分字段上。

4.4查看

通过SHOW TRIGGERS命令查看触发器。

show triggers;
-- 或
show triggers like 模式;

MySQL中所有触发器的定义都存放在information_schema数据库的triggers表中，可以通过SELECT命令来查看当前数据库中某个指定触发器的具体信息。

例如：

SELECT * FROM information_schema.triggers;

使用命令show create trigger 触发器名;可以查看指定数据库中指定触发器的详细信息

例如，查看score_insert_before触发器的详细信息可以使用：

show create trigger score_insert_before;

4.5删除

和其他数据库对象一样，可以使用DROP语句来删除触发器。其基本语法格式如下:

DROP TRIGGER [IF EXISTS] [数据库名.]触发器名
-- 例
DROP TRIGGER score_insert_after;

由于触发器保存的是一段触发程序，没有保存用户数据，当触发器的触发程序需要修改时，可以使用drop trigger语句暂时将该触发器删除，然后再使用create trigger语句重新创建触发器即可。

4.6注意事项

在MySQL中使用触发器有一些注意事项：

如果触发程序中包含SELECT语句，则该SELECT语句不能返回结果集。
同一个表不能创建两个相同触发时间、触发事件的触发器。
触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句。
MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致批量更新操作的性能降低。
在MyISAM存储引擎中，触发器不能保证原子性。例如，当使用一个更新语句更新一个表后，触发程序实现另一个表的更新，若触发程序执行失败，则不会回滚第一个表的更新。

Suhan42

关注

35
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
【数据库】五、MySQL过程式数据库对象（自定义函数、存储过程、游标、触发器）

自定义函数的创建及调用方法MySQL允许数据库开发人员根据实际业务需要创建自定义函数。CREATE FUNCTION 函数名(参数1,参数2,…) RETURNS 返回值的数据类型[函数选项]BEGIN[函数体]RETURN 语句END;函数选项：默认选项，用于说明函数体使用SQL语言编写。：确定性当函数返回不确定值时，该选项是为了防止“复制”时的不一致性。如果函数总是对同样的输入参数产生同样的结果，则它被认为是“确定的”，否则就是“不确定”的。
复制链接

扫一扫