MySQL中存储函数创建与触发器设置的方法

MySQL中存储函数创建与触发器设置的方法

存储函数也是过程式对象之一，与存储过程相似。这些代码片段包含SQL和过程式语句，可以从应用程序和SQL中调用。然而，他们也有一些区别：

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

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

3、存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中

1、创建存储函数

使用CREATE FUNCTION语句创建存储函数

语法格式：

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

注：存储函数不能拥有与存储过程相同的名字。存储函数体中必须包含一个RETURN值语句，值为存储函数的返回值。

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

DELIMITER $$CREATE FUNCTION num_book()RETURNS INTEGERBEGINRETURN(SELECT COUNT(*)FROM Book);END$$DELIMITER ;登录后复制

RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。即使该存储函数不需要参数，调用时也需要使用()，例如：num_book()。

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

DELIMITER $$CREATE FUNCTION del_sell(book_bh CHAR(20))RETURNS BOOLEANBEGINDECLARE bh CHAR(20);SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=book_bh;IF bh IS NULL THENDELETE FROM Sell WHERE 图书编号=book_bh;RETURN TRUE;ELSERETURN FALSE;END IF;END$$DELIMITER ;登录后复制

该存储函数给定图书编号作为输入参数，先按给定的图书编号到Book表查找看有没有该图书编号的书，如果没有，返回false，如果有，返回true。同时还要到Sell表中删除该图书编号的书。要列举数据库中的存储过程，使用SHOW FUNCTION STATUS命令即可。

2、调用存储函数

存储函数创建完后，调用存储函数的方法和使用系统提供的内置函数相同，都是使用SELECT关键字。

语法格式：

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

例：创建一个存储函数publish_book，通过调用存储函数author_book获得图书的作者，并判断该作者是否姓“张”，是则返回出版时间，不是则返回“不合要求”。

DELIMITER $$CREATE FUNCTION publish_book(b_name CHAR(20))RETURNS CHAR(20)BEGINDECLARE name CHAR(20);SELECT author_book(b_name)INTO name;IF name like'张%' THENRETURN(SELECT 出版时间 FROM Book WHERE 书名=b_name);ELSERETURN'不合要求';END IF;END$$DELIMITER ;登录后复制

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

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

删除存储函数的方法和删除存储过程的方法基本一样，使用DROP FUNCTION语句

语法格式：

DROP FUNCTION [IF EXISTS]存储函数名

注：IF EXISTS子句是MySQL的扩展，如果函数不存在，它防止发生错误

例：删除存储函数a

DROP FUNCTION IF EXISTS a;登录后复制

3、创建触发器

使用CREATE TRIGGER语句创建触发器

语法格式：

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

触发器有两种触发选项：BEFORE和AFTER，分别表示触发器是在激活它的语句之前或之后被触发。通常使用AFTER选项来在激活触发器后执行语句。BEFORE选项用于验证新数据是否符合使用限制。

包含SELECT语句的触发器会返回结果到客户端，为了避免这种情况，应该避免在触发器定义中使用SELECT语句。同样，也不能调用将数据返回客户端的存储过程。

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

CREATE TABLE table1(a INTEGER);CREATE TRIGGER table1_insert AFTER INSERTON table1 FOR EACH ROWSET@str='TRIGGER IS WORKING';登录后复制

要查看数据库中有哪些触发器可以使用SHOW TRIGGERS命令。

在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。必须使用这种语法来标识：NEW.column_name或OLD.column_name。NEW.column_name用来引用新行的一列，OLD.column_name用来引用更新或删除它之前的已有行的一列。

对于INSERT语句，只有NEW是合法的，对于DELETE语句，只有OLD才合法。而UPDATE语句可以与NEW和OLD同时使用。

创建一个触发器，使得当删除表格“Book”中某本图书的信息时，同时删除所有与该图书有关的“Sell”表格中的数据。

DELIMITER $$CREATE TRIGGER book_del AFTER DELETEON Book FOR EACH ROWBEGINDELETE FROM Sell WHERE 图书编号=OLD.图书编号;END$$DELIMITER ;登录后复制

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

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

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

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

DELIMITER $$CREATE PROCEDURE data_copy()BEGINREPLACE member_b SELECT * FROM Members;END$$登录后复制

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

DELIMITER $$CREATE TRIGGER members_ins AFTER INSERTON Members FOR EACH ROWCALL data_copy();DELIMITER ;登录后复制

5、删除触发器

语法格式：

DROP TRIGGER 触发器名

例：删除触发器members_ins

DROP TRIGGER members_ins;登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系小编 edito_r@163.com 处理，核实后本网站将在24小时内删除侵权内容。