MySQL中存储函数、触发器的示例分析

共计 3845 个字符，预计需要花费 10 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 中存储函数、触发器的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

 
创建函数：
 
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;
 

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“MySQL 中存储函数、触发器的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!