MySQL中存储函数、触发器的示例分析
这篇文章主要介绍了MySQL中存储函数、触发器的示例分析,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。
创新互联专注于松岭企业网站建设,响应式网站建设,商城建设。松岭网站建设公司,为松岭等地区提供建站服务。全流程按需定制,专业设计,全程项目跟踪,创新互联专业和态度为您提供的服务
创建函数:
1. 存储函数也是过程式对象之一,与存储过程很相似。
它们都是由SQL和过程式语句组成的代码片断,并且可以从应用程序和SQL中调用。然而,它们也有一些区别:
(1)存储函数不能拥有输出参数,因为存储函数本身就是输出参数;
www.2cto.com
(2)不能用CALL语句来调用存储函数;
(3)存储函数必须包含一条RETURN语句,而这条特殊的SQL语句不允许包含于存储过程中。
2.创建存储函数使用CREATEFUNCTION语句。
要查看中有哪些存储函数,可以使用show function satus命令(与存储过程类似)。CREATE function语法格式:
CREATE FUNCTION sp_name ([func_parameter[,...]])
returns type
[characteristic ...] routine_body
说明:存储函数的定义格式和存储过程相差不大。
● sp_name是存储函数的名称。存储函数不能拥有与存储过程相同的名字。
● func_parameter是存储函数的参数,参数只有名称和类型,不能指定IN、OUT和INOUT。RETURNS type子句声明函数返回值的数据类型。
● routine_body是存储函数的主体,也叫存储函数体,所有在存储过程中使用的SQL语句在存储函数中也适用,包括流程控制语句、游标等。但是存储函数体中必须包含一个RETURN value语句,value为存储函数的返回值。这是存储过程体中没有的。
例1: 创建一个存储函数,它返回XS表中学生的数目作为结果。
www.2cto.com
DELIMITER $$
CREATE FUNCTION NUM_OF_XS()
RETURNS INTEGER
BEGIN
RETURN (SELECT COUNT(*)FROM XS);
END$$
DELIMITER ;
例2: 创建一个存储函数来删除XS_KC表中有但XS表中不存在的学号。
DELIMITER $$
CREATE FUNCTION DELETE_STU(XH CHAR(6))
RETURNS BOOLEAN
BEGIN
DECLARE STU CHAR(6);
SELECT 姓名 INTO STU FROM XS WHERE 学号=XH;
IF STU IS NULL THEN
DELETE FROM XS_KCWHERE 学号=XH;
RETURN TRUE;
ELSE
RETURN FALSE;
www.2cto.com
END IF;
END$$
DELIMITER ;
3. 调用创建的函数
存储函数创建完后,就如同系统提供的内置函数(如version()),所以调用存储函数的方法也差不多,都是使用SELECT关键字。
语法格式为: SELECT sp_name ([func_parameter[,...]])
例: 调用例1中的存储函数 :SELECT NUM_OF_XS();
存储函数中还可以调用另外一个存储函数或者存储过程。
例:创建一个存储函数,通过调用存储函数NAME_OF_STU获得学号的姓名,判断姓名是否是“王林”,是则返回王林的出生日期,不是则返回“FALSE”。
DELIMITER $$
CREATE FUNCTION IS_STU(XH CHAR(6))
RETURNS CHAR(10)
BEGIN
DECLARE NAME CHAR(8);
SELECT NAME_OF_STU(XH)INTO NAME;
IF NAME= '王林' THEN
RETURN(SELECT 出生日期 FROM XS WHERE 学号=XH);
ELSE
RETURN 'FALSE';
END IF; www.2cto.com
END$$
DELIMITER ;
4. 删除与修改创建的函数
删除存储函数的方法与删除存储过程的方法基本一样,都使用DROP FUNCTION语句。
语法格式为: DROPFUNCTION [IF EXISTS] sp_name
例: 删除例1中的存储函数NUM_OF_XS。
DROP FUNCTION IF EXISTS NUM_OF_XS;
同样也是使用ALTER FUNCTION语句可以修改存储函数的特征。
语法格式为: ALTER FUNCTION sp_name [characteristic ...]
当然,要修改存储函数的内容则要采用先删除后定义的方法。
触发器
1. 创建触发器
创建触发器使用CREATEtrigger语句,要查看数据库中有哪些触发器可以使用show triggers命令。
CREATE TRIGGER语法格式:
CREATE TRIGGERtrigger_name trigger_time trigger_event
ON tbl_nameFOR EACH ROW trigger_stmt
说明:
● trigger_name:触发器的名称,触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建,名称前面应该加上数据库的名称。
● trigger_time:触发器触发的时刻,有两个选项:AFTER和BEFORE,以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变,通常使用AFTER选项;如果想要验证新数据是否满足使用的限制,则使用BEFORE选项。在MySQL中区别不明显,before跟after用法差不多。 www.2cto.com
● trigger_event:触发事件,指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:
INSERT:将新行插入表时激活触发器。例如,通过INSERT、LOAD DATA和REPLACE语句。
UPDATE:更改某一行时激活触发器。例如,通过UPDATE语句。
DELETE:从表中删除某一行时激活触发器。例如,通过DELETE和REPLACE语句。
● tbl_name:与触发器相关的表名,在该表上发生触发事件才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如,对于某一表,不能有两个BEFORE UPDATE触发器,但可以有1个BEFORE UPDATE触发器和1个BEFOREINSERT触发器,或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。
● FOR EACH ROW:这个声明用来指定,对于受触发事件影响的每一行,都要激活触发器的动作。例如,使用一条语句向一个表中添加一组行,触发器会对每一行执行相应触发器动作。
● trigger_stmt:触发器动作,包含触发器激活时将要执行的语句。如果要执行多个语句,可使用BEGIN... END复合语句结构。这样,就能使用存储过程中允许的相同语句。
注意:触发器不能返回任何结果到客户端,为了阻止从触发器返回结果,不要在触发器定义中包含SELECT语句。同样,也不能调用将数据返回客户端的存储过程。
2. new.列名、old.列名用法
在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志,那会使系统混淆,因为激活触发器的语句可能已经修改、删除或添加了新的列名,而列的旧名同时存在。因此必须用这样的语法来标志:“NEW.column_name”或者“OLD.column_name”。NEW.column_name用来引用新行的一列,OLD.column_name用来引用更新或删除它之前的已有行的一列。
对于INSERT语句,只有NEW是合法的;对于DELETE语句,只有OLD才合法;而UPDATE语句可以与NEW或OLD同时使用。
例: 创建一个触发器,当删除表XS中某个学生的信息时,同时将XS_KC表中与该学生有关的数据全部删除。 www.2cto.com
DELIMITER $$
CREATE TRIGGERXS_DELETE AFTER DELETE
ON XS FOR EACH ROW
BEGIN
DELETE FROM XS_KC WHERE学号=OLD.学号;
END$$
DELIMITER ;
现在验证一下触发器的功能:DELETE FROM XS WHERE学号='081101';
使用SELECT语句查看XS_KC表中的情况:SELECT * FROM XS_KC;
注意:当触发器涉及对触发表自身的更新操作时,只能使用BEFORE,AFTER触发器将不被允许。
3.删除触发器 www.2cto.com
和其他数据库对象一样,使用DROP语句即可将触发器从数据库中删除。语法格式:DROP TRIGGER[schema_name.]trigger_name
说明:trigger_name:指要删除的触发器名称。schema_name为所在数据库的名称,如果在当前数据库,可以省略。
例: 删除触发器XS_DELETE: DROP TRIGGERXS_DELETE;
感谢你能够认真阅读完这篇文章,希望小编分享的“MySQL中存储函数、触发器的示例分析”这篇文章对大家有帮助,同时也希望大家多多支持创新互联,关注创新互联行业资讯频道,更多相关知识等着你来学习!
当前标题:MySQL中存储函数、触发器的示例分析
网站地址:http://myzitong.com/article/ggddpd.html