第9章 数据库完整性与安全—2

1、第9章 数据库完整性与安全 目 录 9.4 数据库安全性 9.1 数据库完整性 9.2 9.3 游标 存储过程 触发器应用与安全设计 9.59.6 9.4 存储过程 存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合 该集合编译后存放在数据库中，可根据实际情况重新编译； 存储过程可直接运行，也可远程运行； 存储过程直接在服务器端运行。 使用存储过程具有如下优点： 将业务操作封装 可为复杂的业务操作编写存储过程，放在数据库中； 用户可调用存储过程执行，而业务操作对用户是不可见的； 若存储过程仅修改了执行体，没有修改接口（即调用参数）， 则用户程序不需要修改，达到业务封装的效果。 便于

2、事务管理 事务控制可以用在存储过程中； 用户可依据业务的性质定义事务，并对事务进行相应级别的 操作。 9.4 存储过程 实现一定程度的安全性保护 存储过程存放在数据库中，且在服务器端运行； 对于不允许用户直接操作的表或视图，可通过调用存储过程 来间接地访问这些表或视图，达到一定程度的安全性； 这种安全性缘于用户对存储过程只有执行权限，没有查看权 限； 拥有存储过程的执行权限，自动获取了存储过程中对相应表 或视图的操作权限； 这些操作权限仅能通过执行存储过程来实现，一旦脱离存储 过程，也就失去了相应操作权限。 注意：对存储过程只需授予执行权限，不需授予表或视图的操作权限。 特别适合统计和查询操作

3、 一般统计和查询，尤其是期末统计，往往涉及数据量大、表 多，若在客户端实现，数据流量和网络通信量较大； 很多情况下，管理信息系统的设计者，将复杂的查询和统计 用存储过程来实现，免去客户端的大量编程。 9.4 存储过程 减少网络通信量 存储过程仅在服务器端执行，客户端只接收结果； 由于存储过程与数据一般在一个服务器中，可减少 大量的网络通信量。 使用存储过程前，首先要创建存储过程。可对存储过程进行 修改和删除。 创建存储过程后，必须对存储过程授予执行EXECUTE的权限， 否则该存储过程仅可以供创建者执行。 9.4.1 创建存储过程 9.4.2 执行存储过程 9.4.3 修改和删除存储过程 9.

4、4.1 创建存储过程 语法： CREATE PROCEDURE ( = OUTPUT , = OUTPUT ) AS 其中： ：过程名，必须符合标识符规则，且在数据 库中唯一； ：参数名，存储过程可不带参数，参数可 以是变量、常量和表达式； OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省 时表示是输入参数。 9.4.1 创建存储过程 如果存储过程的输出参数取集合值，则该输出参数不在 存储过程的参数中定义，而是在存储过程中定义一个临 时表来存储该集合值。 临时表的表名前加一个#符号，如#myTemp 在存储过程尾部，使用语句： SELECT * FROM #myTemp 将结果集合返

5、回给调用者。 存储过程结束后，临时表自动被删除。 注意： 用户定义的存储过程只能在当前数据库中创建； 一个存储过程最大不能超过128MB。若超过128MB，可将超出 的部分编写为另一个存储过程，然后在存储过程中调用。 9.4.1 创建存储过程 例9.23 输入某个同学的学号，统计该同学的平均分。 CREATE PROCEDURE proStudentByNo1(sNo char(7) AS SELECT a.studentNo, studentName, avg(score) FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a

6、.studentNo=sNo GROUP BY a.studentNo, studentName 9.4.1 创建存储过程 例9.24 输入某个同学的学号，统计该同学的平均分，并返 回该同学的姓名和平均分。 分析： 该过程涉及三个参数： 一个输入参数，设为sNo，用于接收某同学的学号； 两个输出参数，用于返回查询到的同学姓名和平均分，设 为sName 和avg 实现方法一： 用一个查询，根据输入参数sNo，查询出该同学的姓 名并放到输出参数sName中 由于在学生表中学号是唯一的，使用命令： SELECT snName=studentName FROM Student WHERE studen

7、tNo=sNo 9.4.1 创建存储过程 用另一个查询，根据输入参数sNo，查询出该同学 的选课平均分并放到输出参数avg中 由于该同学的平均分也只有一个，使用命令： SELECT avg=avg(score) FROM Score WHERE studentNo=sNo GROUP BY studentNo 9.4.1 创建存储过程 存储过程为： CREATE PROCEDURE proStudentByNo21(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT ) AS BEGIN - 当中多于1个SQL语句时，

8、使用块定义 -查询同学的姓名放入输出参数sName中 SELECT sName=studentName FROM Student WHERE studentNo=sNo -查询同学选课的平均分放入输出参数avg中 SELECT avg=avg(score) FROM Score WHERE studentNo=sNo GROUP BY studentNo END 9.4.1 创建存储过程 实现方法二： 用一个查询，根据输入参数sNo，查询出该同学的姓名并放 到输出参数sName中，其命令同方法一 定义一个游标，根据输入参数sNo，查询该同学所有的选课 记录，使用命令： DECLARE myCu

9、r CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo 定义局部变量score，用于接收从游标集中获取的成绩； 定义局部变量count，用于统计选课门数； 定义局部变量sum，用于对成绩进行累加。 9.4.1 创建存储过程 其存储过程为： CREATE PROCEDURE proStudentByNo22(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT) AS BEGIN DECLARE score tinyint, count tinyint, sum i

10、nt -查找姓名，并放入到输出参数sName中 SELECT sName=studentName FROM Student WHERE studentNo=sNo -变量赋初值 SET count=0 SET sum=0 -统计学生选课门数count和总分sum，使用游标： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo 9.4.1 创建存储过程 OPEN myCur - 打开游标 FETCH myCur INTO score - 获取当前元组数据 WHILE (FETCH_STATUS=0) BEGIN

11、 SET count=count+1 SET sum=sum+score FETCH myCur INTO score - 获取下一元组数据 END CLOSE myCur DEALLOCATE myCur IF count0 SELECT avg=sum / count ELSE SELECT avg=0 END 9.4.1 创建存储过程 SQL Server数据库还可以返回一个数据集合 该数据集合在客户端的程序中可以被网格类的对象接收； 可以对其进行逐行处理； 游标中可以嵌套游标。 例9.25 输入某同学的学号，使用游标统计该同学的平均分， 并返回平均分，同时逐行显示该同学的姓名、选课名称

12、和选课 成绩。 CREATE PROCEDURE proStudentAvg(sNo char(7), avg numeric(6, 2) OUTPUT ) AS BEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=0 9.4.1 创建存储过程 -定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Sco

13、re a, Student b, Course c WHERE b.studentNo=sNo AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo OPEN curGrade FETCH curGrade INTO sName, cName, grade WHILE (FETCH_STATUS=0) BEGIN -业务处理 SELECT sName, cName, grade - 输出 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade E

14、ND 9.4.1 创建存储过程 CLOSE curGrade DEALLOCATE curGrade IF count=0 SELECT avg=0 ELSE SELECT avg=sum/count END 本例使用了SELECT语句来显示变量的值，即 SELECT sName, cName, grade 由于存储过程仅在服务器端执行，其显示的内容只在服务器端 出现，并不返回给客户端，这样的输出结果是没有价值的。 显示内容在调试存储过程时有作用，一旦过程调试正确，使用存 储过程的修改命令将显示内容删除。 9.4.1 创建存储过程 例9.26 输入某学院名称，统计该学院每个班级同学的选课信 息

15、，返回班级编号、班级名称、课程名称、课程选课人数、课 程平均分。 本例使用嵌套游标，读者通过该例掌握嵌套游标的使用方法。 分析： 本例涉及两个参数 一个是输入参数：学院名称，设为institute； 一个是输出参数，它为一个集合值，包含了该学院所有班级 的班级编号、班级名称、课程名称、课程选课人数、课程平 均分； 对于集合值输出参数，在过程中定义一个临时表来存储该集 合，设临时表为#myTemp 在过程尾部使用语句“SELECT * FROM #myTemp”将该集合返 回给调用者。 9.4.1 创建存储过程 定义5个临时变量，分别保存查询出来的班级编号 classNo、班级名称classNa

16、me、课程名称 courseName、选课人数count、选课平均分avg。 由于一个学院有多个班级，定义一个游标curClass， 根据输入的学院名称，查询该学院所有的班级编号和 班级名称。 将查询出的班级编号和班级名称放入变量classNo、 className中。定义游标语句为： DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute 9.4.1 创建存储过程 由于一个班级选修了多门课程，需依据查询出来的 班级号，按选课的课程名进行分组计算，统计该班 每门课程的选

17、课人数和选课平均分。 需要使用第二个游标，将查询出来的该班的选课人数和平均分 放入变量count和avg中。定义游标语句为： DECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=classNo GROUP BY courseName 注意：classNo变量的值是从外游标中获取的班级编号。 将查询出来的班级编

18、号、班级名称、课程名称、课 程选课人数、课程平均分插入到临时表#myTemp中。 9.4.1 创建存储过程 存储过程为： CREATE PROCEDURE proInstitute( institute varchar(30) ) AS BEGIN DECLARE className varchar(30), courseName varchar(30) DECLARE classNo char(6), count tinyint, avg numeric(5, 1) /*定义一个临时表，存放每个班级的班级编号、班级 名称、课程 名称、课程选课人数、课程平均分*/ CREATE TABLE #

19、myTemp ( classNo char(6), className varchar(30), courseName varchar(30), classCount tinyint, classAvg numeric(5, 1) ) 9.4.1 创建存储过程 -定义游标curClass，依据输入参数institute，查找班级编号和班级名称 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute OPEN curClass FETCH curClass INTO cl

20、assNo, className WHILE (FETCH_STATUS=0) BEGIN -定义游标curCourse，查找classNo班选课的课程名称、选课人数、平均分 DECLARE curCourse CURSOR FOR SELECT courseName, count(*), avg(score) FROM Student a, Score b, Course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND classNo=classNo GROUP BY courseName 9.4.1 创建存储过程

21、OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN /* 将班级编号、班级名称、课程名称、课程选课人数、课程平均分 插入到临时表#myTemp中 */ INSERT INTO #myTemp VALUES( classNo, className, courseName, count, avg ) - 获取下一游标值，取该班下一门课程的课程名、选课人数和平均分 FETCH curCourse INTO courseName, count, avg END CLOSE cur

22、Course DEALLOCATE curCourse -获取游标curClass的下一个值，即取下一个班级 FETCH curClass INTO classNo, className END CLOSE curClass DEALLOCATE curClass -显示临时表的内容，同时将临时表的内容返回给调用者 SELECT * FROM #myTemp END 9.4.1 创建存储过程 在本例中，获取班级编号、班级名称不能 写成： SELECT classNo=classNo, className=className FROM Class WHERE institute=institut

23、e 因为： 一个学院有多个班级，该查询返回一个元组集合。 变量classNo和className仅分别接收一个数据。 必须使用游标，本例定义游标为curClass。 9.4.2 执行存储过程 使用存储过程时，必须执行命令EXECUTE 语法： EXECUTE = , = OUTPUT , = , = OUTPUT 注意：EXECUTE的参数必须与对应的 PROCEDURE的参数相匹配。 例9.27 执行存储过程proStudentByNo1 EXECUTE proStudentByNo1 0800001 9.4.2 执行存储过程 例9.28 执行存储过程proStudentByNo2 DECL

24、ARE sName varchar(20), avg numeric(5, 1) EXECUTE proStudentByNo2 0800001, sName OUTPUT, avg OUTPUT SELECT sName, avg 例9.29 执行过程proInstitute EXECUTE proInstitute 信息管理学院 也可以使用命令： DECLARE institute varchar(30) SET institute=信息管理学院 EXECUTE proInstitute institute 9.4.3 修改和删除存储过程 修改存储过程 语法为： ALTER PROCEDU

25、RE = OUTPUT , = OUTPUT AS 注意：由于存储过程是在服务器端执行，程序中不需要有输出命令SELECT，由 SELECT引出的输出不会在客户端出现。 例9.30 修改存储过程proStudentAvg，将显示结果内容删除。 ALTER PROCEDURE proStudentAvg( sNo char(7), avg numeric(6, 2) OUTPUT ) AS BEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT

26、sum=0, count=0 9.4.3 修改和删除存储过程 -定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studentNo=b.studentNo AND a.courseNo=c.courseNo OPEN curGrade FETCH curGrade INTO sName, cName, grade WHILE (FETCH_STATUS=0) BEGI

27、N -业务处理 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END CLOSE curGrade DEALLOCATE curGrade 9.4.3 修改和删除存储过程 IF count=0 SELECT avg = 0 ELSE SELECT avg=sum/count END 删除存储过程 语法： DROP PROCEDURE 例9.31 删除存储过程proStudentAvg DROP PROCEDURE proStudentAvg 目 录 9.4 数据库安全性 9.1 数据库完整

28、性 9.2 9.3 游标 存储过程 触发器应用与安全设计 9.59.6 9.5触发器 触发器(trigger)是用户定义在关系表上的一类由事件驱动 的存储过程，由服务器自动激活。 触发器可进行更为复杂的检查和操作，具有更精细和更强 大的数据控制能力。 触发器是一种特殊的存储过程，不管什么原因造成的数据 变化都能自动响应，对于每条SQL语句，触发器仅执行一 次，事务可用于触发器中。 事务定义： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION 有两个特殊的表用在触发器语句中，不同的数据库管理系 统其名称不一样： 在SQL Serv

29、er中使用deleted和inserted表； Oracle数据库使用old和new表。 9.5触发器 注意： 这两张表的结构与作用的表结构完全一致； 当作用表的SQL语句开始时，自动产生这两张表的结构与内容； 当SQL语句执行完毕，这两张表也随即删除。 下面以SQL Server为例介绍触发器： deleted表 存储DELETE和UPDATE语句执行时所影响的行的拷贝； 在DELETE和UPDATE语句执行前被作用的行转移到deleted表中。 将被删除的元组或修改前的元组值存入该表中 inserted表 存储INSERT和UPDATE语句执行时所影响的行的拷贝； 在INSERT和UPDA

30、TE语句执行期间，新行被同时加到inserted表和触发 器表中。 将被插入的元组或修改后的元组值存入该表中，同时更 新基本表。 9.5触发器 实际上，UPDATE命令是删除后紧跟着插入， 旧行首先拷贝到deleted表中，新行同时拷 贝到inserted表和基本表中。 触发器仅在当前数据库中生成 触发器有三种类型，即插入、删除和更新； 插入、删除和更新可作为一种类型的触发器； 查询操作不会产生触发动作，没有查询触发器 类型。 9.5.1 创建触发器 9.5.2 修改和删除触发器 9.5.3 触发器的作用 9.5.1 创建触发器 创建触发器的语法： CREATE TRIGGER ON FOR

31、INSERT | UPDATE | DELETE AS 其中： ：触发器的名称，在数据库中必须唯一； ：触发器作用的基本表，该表也称为触发器 的目标表； INSERT | UPDATE | DELETE ：触发器事件，触发器的事 件可以是插入INSERT、更新UPDATE和删除DELETE事件， 也可以是这几个事件的组合。 9.5.1 创建触发器 INSERT 类型的触发器是指：当对指定表执行 了插入操作时系统自动执行触发器代码。 UPDATE 类型的触发器是指：当对指定表执 行了更新操作时系统自动执行触发器代码。 DELETE类型的触发器是指：当对指定表执行 了删除操作时系统自动执行触发器代

32、码。 ：触发动作的执行体，即一段SQL语句块 如果该触发执行体执行失败，则激活触发器的事件就 会终止，且触发器的目标表或触发器可 能影响的其它表不发生任何变化，即执行事务的回滚 操作。 9.5.1 创建触发器 例9.32 创建触发器，保证学生表中的性别仅能取男和女。 分析： 本例需要使用插入和修改两个触发器，因为可能破 坏约束 “性别仅能取男和女”的操作是插入和修 改操作。 违约条件是： 如果在inserted表中存在有性别取值不为“男”或“女” 的记录（由于inserted表保存了修改后的记录，只要对 inserted表进行判断即可），则取消本次操作。 插入触发器 CREATE TRIGGE

33、R sexIns ON Student FOR INSERT AS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 9.5.1 创建触发器 修改触发器 CREATE TRIGGER sexUpt ON Student FOR UPDATE AS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 该例也可以合并为一个触发器 CREATE TRIGGER sexUptIns ON Student FOR INSER

34、T, UPDATE AS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 本例的inserted表结构与Student表结构相同。 9.5.1 创建触发器 例9.33 创建触发器，当输入某个同学选课成绩时，如果 他是少数民族人，其成绩自动加5分。 CREATE TRIGGER ScoreIns ON Score FOR INSERT AS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.stude

35、ntNo AND student.nation汉族 ) UPDATE Score SET Score.score=Score.score+5 FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND Student.nation汉族 AND inserted.studentNo=Score.studentNo AND inserted.courseNo=Score.courseNo 9.5.1 创建触发器 例9.34 创建触发器 ，自动修改班级表中的班级人数，规 定一次仅能修改一个学生记录。 分析： 该触发器的含

36、义是： 当对学生表Student删除和插入记录时必须修改班级人数； 当修改学生表中某同学的所属班级时，也要修改班级表 中的相应班级的人数； 分别为插入、删除和修改操作设计触发器。 由于规定一次仅能修改一个学生记录，因此在 触发器中必须进行判断：如果执行DML语句作 用的对象超过一条记录，则取消本次操作。 由于规定一次仅能修改一个学生记录，因此可 直接在SELECT语句中使用变量接收查询出来的 属性值，不需要使用游标： SELECT classNo=classNo FROM inserted 9.5.1 创建触发器 /* 插入触发器，inserted表结构与Student表结构 相同 */ CR

37、EATE TRIGGER classIns ON Student FOR INSERT AS /* 定义一个变量classNo，用于接受所插入的学生所属的班级编号 */ DECLARE classNo char(6) /* 如果插入的记录数大于1条, 则回滚 */ IF ( SELECT count(*) FROM inserted )1 ROLLBACK ELSE BEGIN /* 找出插入的学生所属的班级编号放到变量classNo中 */ SELECT classNo=classNo FROM inserted /* 更新班级表中对应班级编号为classNo的班级人数 */ UPDATE

38、Class SET classNum=classNum+1 WHERE classNo=classNo END 9.5.1 创建触发器 /* 删除触发器，deleted表结构与Student表结构 相同 */ CREATE TRIGGER classUpt ON Student FOR DELETE AS /* 定义一个变量classNo，用于接受所删除的学生所属的班级编号 */ DECLARE classNo char(6) /* 如果删除的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /* 找

39、出删除的学生所属的班级编号放到变量classNo中 */ SELECT classNo=classNo FROM deleted /* 更新班级表中对应班级编号为classNo的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=classNo END 9.5.1 创建触发器 /* 更新触发器，deleted和inserted表结构与Student表结构相同 */ CREATE TRIGGER classUpt ON Student FOR UPDATE AS /* 定义一个变量oldClassNo，用于接受所修改前的学生所属的

40、班级编号 */ /* 定义一个变量newClassNo，用于接受所修改后的学生所属的班级编号 */ DECLARE oldClassNo char(6) DECLARE newClassNo char(6) /* 如果修改的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /* 找出修改前的学生所属的班级编号放到变量oldClassNo中 */ SELECT oldClassNo=classNo FROM deleted 9.5.1 创建触发器 /* 找出修改后的学生所属的班级编号放到变量newCla

41、ssNo中 */ SELECT newClassNo=classNo FROM inserted /* 更新班级表中对应班级编号的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=oldClassNo UPDATE Class SET classNum=classNum+1 WHERE classNo=newClassNo END 本例在更新触发器中要同时使用deleted表和 inserted表。 9.5.2 修改和删除触发器 语法为： ALTER TRIGGER ON FOR INSERT | UPDATE | DELET

42、E AS 9.5.2 修改和删除触发器 例9.35 修改例9.34中的更新触发器，只有更新了学生所 属的班级时才触发，允许对多条更新记录进行操作。 分析： 触发条件是：只有更新了学生所属的班级时才 触发。 在程序中使用语句“IF UPDATE(classNo)”来保证：如果仅 修改了班级编号之外的属性，则不引发触发器工作。 本例允许对多条更新记录进行操作，必须使用 游标。 找出更新前后的班级编号，分别放入到变量oldClassNo 和newClassNo中； 对这两个班的人数分别进行减一和增一操作。 9.5.2 修改和删除触发器 要找出更新前后的班级编号，对inserted和 deleted两

43、张表进行连接操作，连接条件是学号 相等，其连接语句为 SELECT a.classNo, b.classNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo inserted表中保存的是修改后的班级编号。 deleted表中保存的是修改前的班级编号。 9.5.2 修改和删除触发器 l 触发器程序为： ALTER TRIGGER classUpt ON Student FOR UPDATE AS IF UPDATE(classNo) BEGIN - 定义一个变量oldClassNo，用于接受所修改前的学生所属的班级编号 - 定义一

44、个变量newClassNo，用于接受所修改后的学生所属的班级编号 DECLARE oldClassNo char(6) DECLARE newClassNo char(6) -定义游标uptCur，找出更新前后的班级编号 DECLARE uptCur CURSOR FOR SELECT a.classNo, b.classNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo 9.5.2 修改和删除触发器 OPEN uptCur -打开游标 FETCH uptCur INTO newClassNo, oldClassNo -获取当前 游标值 WHILE ( FETCH_STATUS=0 ) BEGIN /* 更新班级表中对应班级编号的班级人数 */ UPDATE Class SET classNum=classNum-1 - 原班级人数 减1 WHERE classNo=oldClassNo UPDATE Class SET c