详解MySQL中存储函数创建与触发器设置

这篇文章主要为大家详细介绍了MySQL中存储函数的创建与触发器的设置，文中的示例代码讲解详细，具有一定的学习价值，需要的可以参考一下

存储函数也是过程式对象之一，与存储过程相似。他们都是由SQL和过程式语句组成的代码片段，并且可以从应用程序和SQL中调用。当然，也有不同的地方：

1、存储函数没有输出参数，因为存储函数本身就是输出参数【有return】

2、不能用call语句来调用存储函数

3、存储函数必须包含一条return语句，而存储过程中不允许存在

一、创建存储函数

使用create function语句创建存储函数，语法格式如下:

CREATE FUNCTION 存储函数名 （[参数[,...]]）
RETURNS 类型
函数体

注意：存储函数不能拥有与存储过程相同的名字。函数体要有return语句

例如：创建一个存储函数，其返回值是Book表中图书数目作为结果

DELIMITER $$
CREATE FUNCTION num_book()
RETURNS INTEGER
BEGIN
RETURN(SELECT COUNT(*)FROM Book);
END$$
DELIMITER ;

注意：因为上面的returns是规定返回一个integer类型的值，所以下面的return子句包含select的时候，结果可想而知也只能返回一个数值。使用的时候用()，如num_book();

例如：创建一个存储函数来删除Sell表中有但Book表中不存在的记录

DELIMITER $$
CREATE FUNCTION del_sell(book_bh CHAR(20))
RETURNS BOOLEAN
BEGIN
DECLARE bh CHAR(20);
SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=book_bh;
IF bh IS NULL THEN
DELETE FROM Sell WHERE 图书编号=book_bh;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$$
DELIMITER ;

注意：该存储函数给定了图书编号作为输入参数，先按给定的图书编号到Book表中查询，如果没有，就返回false，如果有就返回true。同时还要到Sell表中删除该图书编号的书。要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。

二、调用存储函数

存储函数创建完成后，调用存储函数的方法和使用系统提供的内置函数相同，都是使用select关键字。语法格式如下：

SELECT 存储函数名（[参数[,...]]）

例如：创建一个存储函数publish_book，通过调用存储函数author_book获得图书的作者，并判断该作者是否姓"李",是否返回出版信息，不是则返回“不符合要求”

DELIMITER $$
CREATE FUNCTION publish_book(b_name CHAR(20))
RETURNS CHAR(20)
BEGIN
DECLARE name CHAR(20);
SELECT author_book(b_name)INTO name;
IF name like'李%' THEN
RETURN(SELECT 出版时间 FROM Book WHERE 书名=b_name);
ELSE
RETURN'不符合要求';
END IF;
END$$
DELIMITER ;

然后调用该存储函数publish_book查看结果：

SELECT publish_book('计算机网络技术');

删除存储函数的方法和删除存储过程的方法基本一样，使用drop function语句，语法格式：

DROP FUNCTION [IF EXISTS]存储函数名 //if exists子句是mysql的扩展，防止发生错误

三、创建触发器

前面的文章也有说到，语法是使用create trigger语句，语法如下：

CREATE TRIGGER 触发器名 触发时间 触发事件
ON 表名 FOR EACH ROW 触发器动作

注意：触发时间有两个选项：before和after，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句之后，通常使用after选项，如果想要验证新数据是否满足使用的限制，则使用before选项。

触发器不能返回任何结果到客户端，为了阻止从触发器返回结果，不要在触发器定义中包含select语句，同样，也不能调用将数据返回客户端的存储过程。

例如：创建一个表table1，其中只有一列a，在表中创建一个触发器，每次插入操作时，将用户变量str的值设为“hello”

CREATE TABLE table1(a INTEGER);
CREATE TRIGGER table1_insert AFTER INSERT
ON table1 FOR EACH ROW
SET@str='hello';

要查看数据库中有哪些触发器可以用show triggers命令。

在mysql触发器中的sql语句可以关联表中的任意列，但不能直接使用列的名称去标志，这样系统会混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的久名同时存在。因此必须用这样的语法来标志，new.column_name或者old_column_name。new.column_name用来引入新行的一列，old.column_name用来引入更新或删除它之前的已有行的一列。

对于insert语句，只有new是可以用，对于delete语句，只有old可以用。而update可以同时使用。

例如：创建一个触发器，当删除表Book中某图书的信息时候，同时将Sell表中与该图书有关的数据全部删除。

DELIMITER $$
CREATE TRIGGER book_del AFTER DELETE
ON Book FOR EACH ROW
BEGIN
DELETE FROM Sell WHERE 图书编号=OLD.图书编号;
END$$
DELIMITER ;

当触发器要触发的是表自身的更新操作时，只能使用BEFORE触发器，而AFTER触发器将不被允许。

四、在触发器中调用存储过程

例如：假设BookStore数据库中有一个与Members表结构完成一样的表member_b,创建一个触发器，在Member表中添加数据的时候，调用存储过程，将member_b表中的数据与Members表同步。

1、定义存储过程：闯将一个与Members表结构完全一样的表member_b

DELIMITER $$
CREATE PROCEDURE data_copy()
BEGIN
REPLACE member_b SELECT * FROM Members;
END$$

2、创建触发器：调用存储过程data_copy()

DELIMITER $$
CREATE TRIGGER members_ins AFTER INSERT
ON Members FOR EACH ROW
CALL data_copy();
DELIMITER ;

五、删除触发器

语法格式：

DROP TRIGGER 触发器名

例如：删除触发器members_ins

DROP TRIGGER members_ins;