数据库系统SSD7 实验5 《触发器与游标》(MySQL)

一、实验目的

进一步熟悉SQL语句对数据库进行完整性控制的方法；

理解触发器的概念、定义方法和触发条件；

理解游标的定义、打开、使用、关闭与释放的方法。

二、实验内容

1、设置一个触发器，该触发器仅允许“dbo”用户可以删除学生表内数据。

2、针对学生表写一个DELETE触发器。

3、针对学生表写一个UPDATE触发器。

4、统计学生的平均成绩，输出低于平均分的成绩（使用游标）。

三、实验方法与实验步骤

1、设置一个触发器，该触发器仅允许“dbo”用户可以删除学生表内数据，否则出错。

2、针对学生表写一个DELETE触发器，删除数据，测试触发。

3、针对学生表写一个UPDATE触发器，更新数据，测试触发。

4、定义游标，统计学生的平均成绩，输出低于平均分的成绩。

大部分情况根据实验要求写出相应的SQL语句，新建查询并运行、调试即可。

对于触发器，可使用Navicat提供的mysql命令行界面（也可以是windows中MySQL提供的控制台界面）通过SQL语句进行创建，也可使用Navicat提供的可视化界面直接进行创建。

类似于存储过程的创建，由于MySQL以分号";"作为SQL语句的识别标志，因此需要在开始加入如下语句：

DELIMITER $

该语句设定"$"(或者其他非分号符)作为SQL语句的结束，最后再使用：

DELIMITER ;

该语句将识别标志还原为分号";"，示例如下：

四、实验结果

1、设置一个触发器，该触发器仅允许“dbo”用户可以删除学生表内数据，否则出错。

由于MySQL的触发器不允许以显示或隐式方式开始或结束事务的语句，即使用回滚等语句也不被允许，但是删除等操作失败后MySQL会自动进行回滚并终止之后的操作，因此只需要判断用户是否为dbo，如果不是则执行一个必会失败的语句触发自动回滚。

DELIMITER $
CREATE TRIGGER `test_02` BEFORE DELETE 
ON `s` FOR EACH ROW 
BEGIN
	SET @usr_local=CURRENT_USER();
	IF INSTR(@usr_local, 'dbo')<=0 THEN
		DELETE FROM s WHERE s.Sno IS NULL;
	END IF; 
END;
$
DELIMITER ;

结果是s表中形成一个触发器：

当前账户名字为root，尝试删除某个数据，结果如下：

切换当前账户为dbo，尝试删除某个数据，结果如下：

2、针对学生表写一个DELETE触发器，删除数据，测试触发。

创建触发器：

DELIMITER $
CREATE TRIGGER `test_03` BEFORE DELETE 
ON `s` FOR EACH ROW 
BEGIN
	SET @time=CURRENT_TIME();
	INSERT INTO log VALUES (@time);
END;
$
DELIMITER ;

对s表进行测试：

DELETE FROM s WHERE s.Sno='1234567890';

结果验证：

3、针对学生表写一个UPDATE触发器，更新数据，测试触发。

创建触发器：

DELIMITER $
CREATE TRIGGER `test_04` BEFORE UPDATE 
ON `s` FOR EACH ROW 
BEGIN
	SET @time=CURRENT_TIME();
	INSERT INTO log VALUES (@time);
END;
$
DELIMITER ;

对s表进行测试：

UPDATE s SET Sage=21 WHERE Sno='1234567890';

结果验证：

4、定义游标，通过存储过程exp5-1统计学生的平均成绩，输出低于平均分的成绩。

DROP PROCEDURE IF EXISTS `student_190222`.`exp5-1`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `exp5-1`()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE temp INT DEFAULT 0;
	-- 声明游标
	DECLARE cur CURSOR FOR 
		SELECT grade FROM sc 
		WHERE grade<(SELECT AVG(grade) FROM sc);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	TRUNCATE TABLE temp_tb;
	-- 开启游标
	OPEN cur;
	-- 开始循环
	read_loop:LOOP
		FETCH cur INTO temp;
		IF done THEN
			LEAVE read_loop;
		END IF;
		INSERT INTO temp_tb VALUES(temp);
	END LOOP read_loop;
	CLOSE cur;
	SELECT * FROM temp_tb;
END;
-- 调用测试
CALL `exp5-1`();

结果：

五、实验小结

主要是学习了如何使用create trigger创建触发器，以及创建触发器过程中的一些基本语法，还有如何使用循环控制语句LOOP、LEAVE结束循环、创建游标DECLARE cur CURSOR FOR、DECLARE CONTINUE HANDLER FOR NOT FOUND，以及游标的开启OPEN、关闭CLOSE、获取数据FETCH等等，主要还是MySQL的语法，与SQL Server还是有区别的。

约束和触发器在特殊情况下各有优势。触发器的主要好处在于它们可以包含使用 Transact-SQL 代码的复杂处理逻辑。因此，触发器可以支持约束的所有功能；但它在所给出的功能上并不总是最好的方法。实体完整性总应在最低级别上通过索引进行强制，这些索引或是 PRIMARY KEY 和 UNIQUE 约束的一部分，或是在约束之外独立创建的。假设功能可以满足应用程序的功能需求，域完整性应通过 CHECK 约束进行强制，而引用完整性(RI) 则应通过 FOREIGN KEY 约束进行强制。在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。

关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见，游标允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。