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

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

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

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

4、OCEDURE ( = 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.studentNo=sNo GROUP BY a.studentN

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

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

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

9、tNo=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 int -查找姓名，并放入到输出参数sName中 SELECT sName=studentName FRO

10、M 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 SET count=count+1 SET sum=sum+score FETCH myCur INT

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

12、umeric(6, 2) OUTPUT )ASBEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=09.4.1 创建存储过程 -定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studen

13、tNo=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 END9.4.1 创建存储过程 CLOSE curGrade DEALLOCATE curGrade IF count=0

14、 SELECT avg=0 ELSE SELECT avg=sum/countEND 本例使用了SELECT语句来显示变量的值，即 SELECT sName, cName, grade 由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。 显示内容在调试存储过程时有作用，一旦过程调试正确，使用存储过程的修改命令将显示内容删除。9.4.1 创建存储过程 例9.26 输入某学院名称，统计该学院每个班级同学的选课信息，返回班级编号、班级名称、课程名称、课程选课人数、课程平均分。 本例使用嵌套游标，读者通过该例掌握嵌套游标的使用方法。 分析： 本例

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

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

17、URSOR 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变量的值是从外游标中获取的班级编号。 将查询出来的班级编号、班级名称、课程名称、课程选课人数、课程平均分插入到临时表#myTemp中。9.4.1 创建存储过程 存储过程为：CREATE PROCEDURE proInst

18、itute( institute varchar(30) )ASBEGIN DECLARE className varchar(30), courseName varchar(30) DECLARE classNo char(6), count tinyint, avg numeric(5, 1) /*定义一个临时表，存放每个班级的班级编号、班级名称、课程 名称、课程选课人数、课程平均分*/ CREATE TABLE #myTemp ( classNo char(6), className varchar(30), courseName varchar(30), classCount tiny

19、int, 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 classNo, className WHILE (FETCH_STATUS=0) BEGIN -定义游标curCourse，查找classNo班选课的课程名称、选课人数、平均分 D

20、ECLARE 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 courseName9.4.1 创建存储过程 OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN /*

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

22、ame END CLOSE curClass DEALLOCATE curClass -显示临时表的内容，同时将临时表的内容返回给调用者 SELECT * FROM #myTempEND9.4.1 创建存储过程 在本例中，获取班级编号、班级名称不能写成： SELECT classNo=classNo, className=className FROM Class WHERE institute=institute 因为： 一个学院有多个班级，该查询返回一个元组集合。 变量classNo和className仅分别接收一个数据。 必须使用游标，本例定义游标为curClass。9.4.2 执行存储过

23、程 使用存储过程时，必须执行命令EXECUTE 语法： EXECUTE = , = OUTPUT , = , = OUTPUT 注意：EXECUTE的参数必须与对应的PROCEDURE的参数相匹配。 例9.27 执行存储过程proStudentByNo1 EXECUTE proStudentByNo1 0800001 9.4.2 执行存储过程 例9.28 执行存储过程proStudentByNo2 DECLARE sName varchar(20), avg numeric(5, 1)EXECUTE proStudentByNo2 0800001, sName OUTPUT, avg OUTP

24、UTSELECT sName, avg 例9.29 执行过程proInstitute EXECUTE proInstitute 信息管理学院 也可以使用命令：DECLARE institute varchar(30)SET institute=信息管理学院EXECUTE proInstitute institute9.4.3 修改和删除存储过程 修改存储过程 语法为： ALTER PROCEDURE = OUTPUT , = OUTPUT AS 注意：由于存储过程是在服务器端执行，程序中不需要有输出命令SELECT，由SELECT引出的输出不会在客户端出现。例9.30 修改存储过程proStu

25、dentAvg，将显示结果内容删除。ALTER PROCEDURE proStudentAvg( sNo char(7), avg numeric(6, 2) OUTPUT )ASBEGIN DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=09.4.3 修改和删除存储过程 -定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, scor

26、e 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) BEGIN -业务处理 SET sum=sum+grade SET count=count+1 FETCH curGrade INTO sName, cName, grade END CLOSE curGrade DEAL

27、LOCATE 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数据库完整性 9.29.3游标 存储过程触发器应用与安全设计 9.59.69.5触发器 触发器(trigger)是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。 触发器可进行更为复杂的检查和操作，具有更精细和更强大

28、的数据控制能力。 触发器是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次，事务可用于触发器中。 事务定义： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION 有两个特殊的表用在触发器语句中，不同的数据库管理系统其名称不一样： 在SQL Server中使用deleted和inserted表； Oracle数据库使用old和new表。9.5触发器 注意： 这两张表的结构与作用的表结构完全一致； 当作用表的SQL语句开始时，自动产生这两张表的结构与内容； 当SQL语句执行完毕，这两张表

29、也随即删除。 下面以SQL Server为例介绍触发器： deleted表 存储DELETE和UPDATE语句执行时所影响的行的拷贝； 在DELETE和UPDATE语句执行前被作用的行转移到deleted表中。 将被删除的元组或修改前的元组值存入该表中 inserted表 存储INSERT和UPDATE语句执行时所影响的行的拷贝； 在INSERT和UPDATE语句执行期间，新行被同时加到inserted表和触发器表中。 将被插入的元组或修改后的元组值存入该表中，同时更新基本表。9.5触发器 实际上，UPDATE命令是删除后紧跟着插入，旧行首先拷贝到deleted表中，新行同时拷贝到insert

30、ed表和基本表中。 触发器仅在当前数据库中生成 触发器有三种类型，即插入、删除和更新； 插入、删除和更新可作为一种类型的触发器； 查询操作不会产生触发动作，没有查询触发器类型。 9.5.1 创建触发器 9.5.2 修改和删除触发器 9.5.3 触发器的作用 9.5.1 创建触发器 创建触发器的语法： CREATE TRIGGER ON FOR INSERT | UPDATE | DELETE AS 其中： ：触发器的名称，在数据库中必须唯一； ：触发器作用的基本表，该表也称为触发器的目标表； INSERT | UPDATE | DELETE ：触发器事件，触发器的事件可以是插入INSERT、更

31、新UPDATE和删除DELETE事件，也可以是这几个事件的组合。9.5.1 创建触发器 INSERT 类型的触发器是指：当对指定表执行了插入操作时系统自动执行触发器代码。 UPDATE 类型的触发器是指：当对指定表执行了更新操作时系统自动执行触发器代码。 DELETE类型的触发器是指：当对指定表执行了删除操作时系统自动执行触发器代码。 ：触发动作的执行体，即一段SQL语句块 如果该触发执行体执行失败，则激活触发器的事件就会终止，且触发器的目标表或触发器可能影响的其它表不发生任何变化，即执行事务的回滚操作。9.5.1 创建触发器 例9.32 创建触发器，保证学生表中的性别仅能取男和女。 分析：

32、本例需要使用插入和修改两个触发器，因为可能破坏约束 “性别仅能取男和女”的操作是插入和修改操作。 违约条件是： 如果在inserted表中存在有性别取值不为“男”或“女”的记录（由于inserted表保存了修改后的记录，只要对inserted表进行判断即可），则取消本次操作。 插入触发器CREATE TRIGGER sexIns ON StudentFOR INSERTAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK9.5.1 创建触发器 修改触发器CREATE TRIGGER sexUpt ON

33、 StudentFOR UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 该例也可以合并为一个触发器CREATE TRIGGER sexUptInsON StudentFOR INSERT, UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男, 女) ) ROLLBACK 本例的inserted表结构与Student表结构相同。9.5.1 创建触发器 例9.33 创建触发器，当输入某个同学选课成绩时，如果他是

34、少数民族人，其成绩自动加5分。CREATE TRIGGER ScoreIns ON ScoreFOR INSERTAS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND student.nation汉族 ) UPDATE Score SET Score.score=Score.score+5 FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND Student.nation汉族

35、AND inserted.studentNo=Score.studentNo AND inserted.courseNo=Score.courseNo9.5.1 创建触发器 例9.34 创建触发器 ，自动修改班级表中的班级人数，规定一次仅能修改一个学生记录。 分析： 该触发器的含义是： 当对学生表Student删除和插入记录时必须修改班级人数； 当修改学生表中某同学的所属班级时，也要修改班级表中的相应班级的人数； 分别为插入、删除和修改操作设计触发器。 由于规定一次仅能修改一个学生记录，因此在触发器中必须进行判断：如果执行DML语句作用的对象超过一条记录，则取消本次操作。 由于规定一次仅能修改

36、一个学生记录，因此可直接在SELECT语句中使用变量接收查询出来的属性值，不需要使用游标： SELECT classNo=classNo FROM inserted9.5.1 创建触发器/* 插入触发器，inserted表结构与Student表结构相同 */CREATE TRIGGER classInsON StudentFOR INSERTAS /* 定义一个变量classNo，用于接受所插入的学生所属的班级编号 */ DECLARE classNo char(6) /* 如果插入的记录数大于1条, 则回滚 */ IF ( SELECT count(*) FROM inserted )1 R

37、OLLBACK ELSE BEGIN /* 找出插入的学生所属的班级编号放到变量classNo中 */ SELECT classNo=classNo FROM inserted /* 更新班级表中对应班级编号为classNo的班级人数 */ UPDATE Class SET classNum=classNum+1 WHERE classNo=classNo END9.5.1 创建触发器/* 删除触发器，deleted表结构与Student表结构相同 */CREATE TRIGGER classUptON StudentFOR DELETEAS /* 定义一个变量classNo，用于接受所删除的

38、学生所属的班级编号 */ DECLARE classNo char(6) /* 如果删除的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /* 找出删除的学生所属的班级编号放到变量classNo中 */ SELECT classNo=classNo FROM deleted /* 更新班级表中对应班级编号为classNo的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=classNo END9.5.1 创建触发器/* 更新触发器，

39、deleted和inserted表结构与Student表结构相同 */CREATE TRIGGER classUptON StudentFOR UPDATEAS /* 定义一个变量oldClassNo，用于接受所修改前的学生所属的班级编号 */ /* 定义一个变量newClassNo，用于接受所修改后的学生所属的班级编号 */ DECLARE oldClassNo char(6) DECLARE newClassNo char(6) /* 如果修改的记录数大于1条，则回滚 */ IF ( SELECT count(*) FROM deleted )1 ROLLBACK ELSE BEGIN /

40、* 找出修改前的学生所属的班级编号放到变量oldClassNo中 */ SELECT oldClassNo=classNo FROM deleted 9.5.1 创建触发器 /* 找出修改后的学生所属的班级编号放到变量newClassNo中 */ SELECT newClassNo=classNo FROM inserted /* 更新班级表中对应班级编号的班级人数 */ UPDATE Class SET classNum=classNum-1 WHERE classNo=oldClassNo UPDATE Class SET classNum=classNum+1 WHERE classNo

41、=newClassNo END 本例在更新触发器中要同时使用deleted表和inserted表。9.5.2 修改和删除触发器 语法为： ALTER TRIGGER ON FOR INSERT | UPDATE | DELETE AS 9.5.2 修改和删除触发器 例9.35 修改例9.34中的更新触发器，只有更新了学生所属的班级时才触发，允许对多条更新记录进行操作。 分析： 触发条件是：只有更新了学生所属的班级时才触发。 在程序中使用语句“IF UPDATE(classNo)”来保证：如果仅修改了班级编号之外的属性，则不引发触发器工作。 本例允许对多条更新记录进行操作，必须使用游标。 找出更

42、新前后的班级编号，分别放入到变量oldClassNo和newClassNo中； 对这两个班的人数分别进行减一和增一操作。9.5.2 修改和删除触发器 要找出更新前后的班级编号，对inserted和deleted两张表进行连接操作，连接条件是学号相等，其连接语句为 SELECT a.classNo, b.classNo FROM inserted a, deleted b WHERE a.studentNo=b.studentNo inserted表中保存的是修改后的班级编号。 deleted表中保存的是修改前的班级编号。9.5.2 修改和删除触发器 l 触发器程序为：ALTER TRIGGER

43、 classUptON StudentFOR UPDATEAS IF UPDATE(classNo) BEGIN - 定义一个变量oldClassNo，用于接受所修改前的学生所属的班级编号 - 定义一个变量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.studentNo9.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 SE