MySQL心得7-2-存储函数、触发器

qqwe

　　创建函数：
　　1.存储函数也是过程式对象之一，与存储过程很相似。
　　它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别：
　　（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；
　　（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表中学生的数目作为结果。
　　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;
　　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;
　　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_nametrigger_time trigger_event
　　 ON tbl_nameFOR EACH ROWtrigger_stmt
　　说明：
　　● trigger_name：触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
　　● trigger_time：触发器触发的时刻，有两个选项：AFTER和BEFORE，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用AFTER选项；如果想要验证新数据是否满足使用的限制，则使用BEFORE选项。在MySQL中区别不明显，before跟after用法差不多。
　　● trigger_event：触发事件，指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
　　INSERT：将新行插入表时激活触发器。例如，通过INSERT、LOAD DATA和REPLACE语句。
　　UPDATE：更改某一行时激活触发器。例如，通过UPDATE语句。
　　DELETE：从表中删除某一行时激活触发器。例如，通过DELETE和REPLACE语句。
　　● tbl_name：与触发器相关的表名，在该表上发生触发事件才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于某一表，不能有两个BEFORE UPDATE触发器，但可以有1个BEFORE UPDATE触发器和1个BEFOREINSERT触发器，或1个BEFOREUPDATE触发器和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表中与该学生有关的数据全部删除。
　　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.删除触发器
　　和其他数据库对象一样，使用DROP语句即可将触发器从数据库中删除。语法格式：DROP TRIGGER[schema_name.]trigger_name
　　说明：trigger_name：指要删除的触发器名称。schema_name为所在数据库的名称，如果在当前数据库，可以省略。
　　例： 删除触发器XS_DELETE： DROP TRIGGERXS_DELETE;