面向考试数据库—存储函数与触发器入门使用

面向考试数据库系列博客以笔者的思维脑图为主线，博文内容为笔者对导图的具体分支所作的详细阐述，本篇博文，主要内容为数据库编程中的存储函数与触发器的知识，其中不足，望读者多加指正。

存储函数

 存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。它们之间的一些区别：
（1）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；
（2）不能用CALL语句来调用存储函数；
（3）存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。

创建存储函数一般使用CREATE FUNCTION语句。
语法格式：

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

创建

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

DELIMITER $$
     CREATE FUNCTION num_book()
        RETURNS INTEGER
        BEGIN
	RETURN (SELECT COUNT(*) FROM library);
        END$$
     DELIMITER ;

注意的点：

• RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。
  用法：
• 要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUS命令。

SHOW FUNCTION STATUS

• 调用存储函数

SELECT sp_name ([func_parameter[,...]])

例子： 创建一个存储函数，返回Book表中某本书的作者姓名。

DELIMITER $$
   CREATE FUNCTION author_student(b_sid CHAR(20))
   RETURNS CHAR(8)#将要返回的数值类型
   BEGIN
	RETURN (SELECT name FROM student WHERE sid= b_name);
   END$$
   DELIMITER ;
#参照格式，这里笔者无具体的表实现

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

 CREATE FUNCTION del_Sell1(b_bh CHAR(20))
	RETURNS BOOLEAN
      BEGIN
	DECLARE bh CHAR(20);
	SELECT 图书编号 INTO bh FROM Book WHERE 图书编号=b_bh;
	IF bh IS NULL THEN
		DELETE FROM Sell WHERE 图书编号=b_bh; 
		RETURN TRUE;
	ELSE 
		RETURN FALSE;
	END IF;
      END$$

触发器

一般的语法格式为：

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

使用姿势：

• 触发时间：触发器触发的时刻，有两个选项：AFTER和BEFORE，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用AFTER选项；如果想要验证新数据是否满足使用的限制，则使用BEFORE选项。
• 触发事件：指明了激活触发程序的语句的类型。触发事件可以是下述值之一：
  INSERT：将新行插入表时激活触发器。例如，通过INSERT、LOAD DATA和REPLACE语句。
  UPDATE：更改某一行时激活触发器。例如，通过UPDATE语句。
  DELETE：从表中删除某一行时激活触发器。例如，通过DELETE和REPLACE语句。
• 触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用BEGIN … END复合语句结构。这样，就能使用存储过程中允许的相同语句。

创建

第一个例子
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

DELIMITER |
create trigger tri_stuInsert after insert
on student for each row
begin
  declare c int;
  set c = (select stuCount from class where classID=new.classID);
  update class set stuCount = c + 1 where classID = new.classID;
end|
DELIMITER ; 

删除

语法格式：

DROP TRIGGER 触发器名

例子：删除触发器members_ins。

DROP TRIGGER members_ins;

课堂作业

第一部分

1、创建一个存储函数，返回学生的总人数EM_NUM()

DELIMITER $$
     CREATE FUNCTION EM_NUM()
        RETURNS INTEGER
        BEGIN
	RETURN (SELECT COUNT(*) FROM student);
        END$$
     DELIMITER ;

2、创建一个存储函数，判断某学生是否在计算机系，若是则返回其性别，若不是则返回“NO”。

CREATE FUNCTION S_C(XI CHAR(6))
	RETURNS CHAR(10)
BEGIN
	DECLARE Cid,Ssex
	CHAR(6);
	SELECT course.cid,student.Ssex	INTO  Cid,Ssex FROM cou
	,student
             WHERE cid =XI;
	IF cid=“004”#(计算机的课程号为004) THEN RETURN Ssex;
	ELSE 
		RETURN “NO”;
	END IF;
END

第二部分

3、创建触发器，在student表中当删除学生信息的同时将sc表中与该学生有关的数据全部删除。

DELIMITER $$
CREATE TRIGGER SC_DELETE AFTER DELETE
	ON sc FOR EACH ROW
BEGIN
	DELETE FROM sc WHERE 
          Sid=SC.Sid;
END$$
DELIMITER ;

4、创建触发器，实现当向Employees表插入一行数据时，对Salary表也插入一行，EmployeeID与Employees表中的EmployeeID相同，InCome和outcome为0.

DELIMITER $$
CREATE TRIGGER EM_INSERT AFTER INSERT
	ON Employees FOR EACH ROW
BEGIN
	INSERT INTO SALARY VALUES(NEW. EmployeeID,0,0);
END$$
DELIMITER ;

5、创建触发器，实现若将Employees表中员工的工作年限增加n年，收入增加n*500。

DELIMITER $$
CREATE TRIGGER EM_update Worker_Salary
	ON Employees FOR EACH ROW
BEGIN
	DECLARE years INT(1);
	set  years=NEW.workyear-OLD.workyear; 
    	IF years>=0 THEN
	   UPDATE salary SET income=income+500*years
              where employeeID=NEW.employeeid;
	END IF;
END$$
DELIMITER ;

小结

以上代码仅供参考，如有帮助不胜荣幸，同时不足之处望多多指正。

一段

程序猿学徒与程序猿大佬

学徒：大佬我最近努力敲代码，总还是有很多bug，不见长进啊？
大佬：你见过凌晨2点的北京吗？
学徒：见过啊
大佬：那你见过凌晨4点的月亮嘛？
学徒：也见过啊。
大佬：问题就在着啊，大晚上的你不好好敲代码，看这些干嘛？