MySQL-触发器和自定义函数
一、自定义函数
自定义函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片段，并且可以从应用程序和SQL中调用。也有一些区别：
- 自定义函数不能拥有输出参数，因为自定义函数本身就是输出参数；
- 不能用call语句来调用自定义函数；
- 自定义函数必须包含一条return语句，而此特殊语句不允许包含在存储过程中；
RETURN子句中包含select子句时，select语句的返回结果只能是一行且只能有一列值
1、创建自定义函数的语法格式
|
CREATE FUNCTION 自定义函数名 ([参数[,..]]) RETURN 类型 函数体 |
范例：创建一个自定义函数，它返回EMP表中的工资总数作为结果
|
DELIMITER $$ CREATE FUNCTION aa() RETURNS INT BEGIN RETURN (SELECT SUM(sal) FROM emp); END $$ DELIMITER ; |
2、查看和调用自定义函数
|
SHOW FUNCTION STATUS; //查看数据库中有哪些自定义函数 |
|
SELECT 自定义函数名([参数[,..]]); //调用自定义函数 |
|
SELECT aa(); //调用刚才创建的自定义函数aa，()必须要写 |
范例：创建一个自定义函数，给定员工号返回员工姓名
|
DELIMITER $$ CREATE FUNCTION bb(no INT(4)) RETURNS VARCHAR(10) BEGIN RETURN (SELECT ename FROM emp WHERE empno=no); END $$ DELIMITER ; //创建自定义函数 SELECT bb(7499); //输入参数7499，返回ALLEN |
范例：创建一个自定义函数，删除emp表中存在，但dept表中没有的记录
|
DELIMITER $$ CREATE FUNCTION cc(dno INT(2)) RETURNS VARCHAR(10) BEGIN DECLARE dn INT(2); SELECT deptno INTO dn FROM emp WHERE deptno=dno; IF dn IS NULL THEN DELETE FROM dept WHERE deptno=dno; RETURN '已删除'; ELSE RETURN '均存在'; END IF; END $$ DELIMITER ; |
3、删除自定义函数
DROP FUNCTION 自定义函数名;
二、触发器
触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
1、创建触发器
语法格式：
|
CREATE TRIGGER 触发器名 触发时间 触发事件 ON 表名 FOR EACH ROW 触发器动作 |
|
触发时间：触发器触发的时刻，有两个选项after/before，表示触发器是在激活它的语句之前或之后触发。如果想要验证新数据是否满足使用的限制，则使用before选项。 |
|
触发事件：指明了激活触发程序的语句的类型，可以为以下值之一： INSERT：将新行插入表时激活触发器，例如通过insert、load data和replace语句； UPDATE：更改某一行时触发激活器，例如通过UPDATE语句； DELETE：从表中删除某一行时激活触发器，例如通过DELETE和REPLACE语句； |
|
触发器动作：包含触发器激活时将要执行的语句，如果要执行多个语句，可使用BEGIN…END复合语句结构，这样，就能使用存储过程中允许的相同语句。 |
范例：创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将用户变量str的值设为“TRIGGER IS WORKING”。
|
CREATE TABLE table1(a INT); //创建表 |
|
CREATE TRIGGER aa AFTER INSERT ON table1 FOR EACH ROW SET @str = ‘TRIGGER IS WORKING’; //创建触发器 |
|
INSERT INTO table1 VALUES(10); //向表中插入一行数据 |
|
SELECT @str; //查看str的值 |
在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.列名”或“OLD.列名”。“NEW.列名”用来引用新行的一列，“OLD.列名”用来引用更新或删除它之前的已有行的一列。
对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句，可以OLD和NEW同时使用。
范例：创建一个触发器，当删除dept表中某部门的信息时，同时将emp表中与该部门相关的数据同步删除。
|
DELIMITER $$ CREATE TRIGGER bb AFTER DELETE ON emp FOR EACH ROW BEGIN DELETE FROM dept WHERE deptno=OLD.deptno; END $$ DELIMITER ; |
|
DELETE FROM emp WHERE deptno = 40; //删除dept表上的20号部门，触发事件 |
|
SELECT * FROM dept WHERE deptno = 40; //查询，验证功能实现 |
触发器中调用存储过程
范例：假设数据库汇总有一个与emp表结构完全一样的表emp_copy，创建一个触发器，在emp表中添加数据的时候，调用存储过程，将emp_copy表中的数据与emp表同步。
|
DELIMITER $$ CREATE PROCEDURE data_copy() BEGIN REPLACE emp_cope SELECT * FROM emp; END $$ //定义存储过程，创建一个与emp表结构完全一样的表emp_copy |
|
DELIMITER $$ CREATE TRIGGER abc AFTER INSERT ON emp FOR EACH ROW CALL data_copy(); END $$ DELIMITER ; //创建触发器，调用存储过程data_copy() |
2、查看触发器
|
SHOW TRIGGER; //查看数据库中有哪些触发器 |
3、删除触发器
|
DROP TRIGGER 触发器名; |
作者：kerwin-chyl
文章链接：https:////www.cnblogs.com/kerwin-chyl
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利
