CH9 数据库完整性与安全_2

1、1School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 第第9 9章章 数据库完整性与安全数据库完整性与安全 2School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 目目 录录9.4数据库安全性数

2、据库安全性 9.1数据库完整性数据库完整性 9.29.3游标游标 存储过程存储过程触发器触发器应用与安全设计应用与安全设计 9.59.63School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4 存储过程存储过程n 存储过程存储过程是为了完成特定功能汇集而成的一组命名了的是为了完成特定功能汇集而成的一组命名了的SQL语语句集合句集合l 该集合该集合编译后存放在数据库中编译后存放在数据库中，可

3、根据实际情况重新编译；，可根据实际情况重新编译；l 存储过程存储过程可直接运行可直接运行，也可远程运行；，也可远程运行；l 存储过程存储过程直接在服务器端运行直接在服务器端运行。n 使用存储过程具有如下优点：使用存储过程具有如下优点：l 将业务操作封装将业务操作封装可为复杂的业务操作编写存储过程，放在数据库中；可为复杂的业务操作编写存储过程，放在数据库中；用户可调用存储过程执行，而业务操作对用户是不可见的；用户可调用存储过程执行，而业务操作对用户是不可见的；若存储过程仅修改了执行体，没有修改接口，则用户程序不需要修若存储过程仅修改了执行体，没有修改接口，则用户程序不需要修改，达到业务封装的效果

4、。改，达到业务封装的效果。l 便于事务管理便于事务管理事务控制可以用在存储过程中；事务控制可以用在存储过程中；用户可依据业务的性质定义事务，并对事务进行相应级别的操作。用户可依据业务的性质定义事务，并对事务进行相应级别的操作。4School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4 存储过程存储过程 l 实现一定程度的安全性保护实现一定程度的安全性保护存储过程存放在数据库中，且在服务器端运

5、行；存储过程存放在数据库中，且在服务器端运行；对于不允许用户直接操作的表或视图，可通过调用存储过程来间接对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性；地访问这些表或视图，达到一定程度的安全性；这种安全性缘于用户这种安全性缘于用户对存储过程只有执行权限，没有查看权限；对存储过程只有执行权限，没有查看权限；拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限；的操作权限；这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，这些操作权限仅能通过执行存储过程来实现，一旦

6、脱离存储过程，也就失去了相应操作权限。也就失去了相应操作权限。l 注意：注意：对存储过程只需授予执行权限，不需授予表或视图的操作对存储过程只需授予执行权限，不需授予表或视图的操作权限权限。l 特别适合统计和查询操作特别适合统计和查询操作一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大；在客户端实现，数据流量和网络通信量较大；很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。过程来实现，免去客

7、户端的大量编程。5School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4 存储过程存储过程 l 减少网络通信量减少网络通信量存储过程仅在服务器端执行，客户端只接收结果；存储过程仅在服务器端执行，客户端只接收结果；由于存储过程与数据一般在一个服务器中，可减少大量的网络通信由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。量。l 使用存储过程前，首先要创建存储过程。可对存储过程进行

8、修改使用存储过程前，首先要创建存储过程。可对存储过程进行修改和删除。和删除。l 创建存储过程后，必须对存储过程创建存储过程后，必须对存储过程授予执行授予执行EXECUTE的权限的权限，否则该存储过程仅可以供创建者执行。否则该存储过程仅可以供创建者执行。n 9.4.1 创建存储过程创建存储过程n 9.4.2 执行存储过程执行存储过程n 9.4.3 修改和删除存储过程修改和删除存储过程6School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章

9、章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 语法：语法：CREATE PROCEDURE ( = OUTPUT , = OUTPUT ) AS l其中：其中：过程名过程名，必须符合标识符规则，且在数，必须符合标识符规则，且在数据库中唯一；据库中唯一；：参数名参数名，存储过程可不带参数，参数可，存储过程可不带参数，参数可以是变量、常量和表达式；以是变量、常量和表达式；OUTPUT：说明该参数是：说明该参数是输出参数输出参数，被调用者获取使用。，被调用者获取使用。缺缺省时表示是输入参数省时表示是输入参数。7School of Information Techn

10、ology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 如果存储过程的如果存储过程的输出参数输出参数取取集合值集合值，则该输出参数不在，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。时表来存储该集合值。l 临时表的表名前加一个临时表的表名前加一个#符号，如符号，如#myTempl 在存储过程尾部，使用语句：在存储过程尾部，使用

11、语句： SELECT * FROM #myTemp 将结果集合返回给调用者将结果集合返回给调用者。l 存储过程结束后，临时表自动被删除。存储过程结束后，临时表自动被删除。n 注意：注意：l 用户定义的存储过程只能在当前数据库中创建；用户定义的存储过程只能在当前数据库中创建；l 一个存储过程最大不能超过一个存储过程最大不能超过128MB。若超过。若超过128MB，可将超出，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。的部分编写为另一个存储过程，然后在存储过程中调用。8School of Information Technology, Jiangxi University of Fi

12、nance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 例例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=sN

13、o GROUP BY a.studentNo9School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 例例9.24 输入某个同学的学号，统计该同学的平均分，输入某个同学的学号，统计该同学的平均分，并返并返回该同学的姓名和平均分回该同学的姓名和平均分。n 分析：分析：l该过程涉及三个参数：该过程涉及三个参数：一个一个输入参数输入参数，设为，设为sNo，用于接收

14、某同学的学号；，用于接收某同学的学号；两个两个输出参数输出参数，用于返回查询到的同学姓名和平均分，设，用于返回查询到的同学姓名和平均分，设为为sName 和和avgl实现方法一：实现方法一：用一个查询，根据用一个查询，根据输入参数输入参数sNo，查询出该同学的姓，查询出该同学的姓名并放到名并放到输出参数输出参数sName中中由于在学生表中学号是唯一的，使用命令：由于在学生表中学号是唯一的，使用命令：SELECT snName=studentName FROM Student WHERE studentNo=sNo10School of Information Technology, Jiang

15、xi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程用另一个查询，根据用另一个查询，根据输入参数输入参数sNo，查询出该同学的，查询出该同学的选课平均分并放到选课平均分并放到输出参数输出参数avg中中由于该同学的平均分也只有一个，使用命令：由于该同学的平均分也只有一个，使用命令： SELECT avg=AVG(score) FROM Score WHERE studentNo=sNo GROUP BY studentNo11Schoo

16、l of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程存储过程为：存储过程为：CREATE PROCEDURE proStudentByNo21(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT )ASBEGIN -查询同学的姓名放入输出参数查询同学的姓名放入输出参数sName中中 SELEC

17、T sName=studentName FROM Student WHERE studentNo=sNo -查询同学选课的平均分放入输出参数查询同学选课的平均分放入输出参数avg中中 SELECT avg=AVG(score) FROM Score WHERE studentNo=sNo GROUP BY studentNoEND12School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.

18、1 创建存储过程创建存储过程l实现方法二：实现方法二：用一个查询，根据用一个查询，根据输入参数输入参数sNo，查询出该同学的姓名并放到，查询出该同学的姓名并放到输出参数输出参数sName中，其命令同方法一中，其命令同方法一定义一个游标，根据定义一个游标，根据输入参数输入参数sNo，查询该同学所有的选课记，查询该同学所有的选课记录，使用命令：录，使用命令： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo定义局部变量定义局部变量score，用于接收从游标集中获取的成绩；，用于接收从游标集中获取的成绩；定义局部

19、变量定义局部变量count，用于统计选课门数；，用于统计选课门数；定义局部变量定义局部变量sum，用于对成绩进行累加。，用于对成绩进行累加。13School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程其存储过程为：其存储过程为：CREATE PROCEDURE proStudentByNo22(sNo char(7), sName varchar(20) OUT

20、PUT, avg numeric(5, 1) OUTPUT)AS BEGIN DECLARE score tinyint, count tinyint, sum int -查找姓名，并放入到查找姓名，并放入到输出参数输出参数sName中中 SELECT sName=studentName FROM Student WHERE studentNo=sNo -变量赋初值变量赋初值 SET count=0 SET sum=0 -统计学生统计学生选课门数选课门数count和和总分总分sum，使用游标：，使用游标： DECLARE myCur CURSOR FOR SELECT score FROM S

21、core WHERE studentNo=sNo 14School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程 OPEN myCur FETCH myCur INTO score WHILE (FETCH_STATUS=0) BEGIN SET count=count+1 SET sum=sum+score FETCH myCur INTO score END

22、CLOSE myCur DEALLOCATE myCur IF count0 SELECT avg=sum/count ELSE SELECT avg=0END15School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n SQL Server数据库还可以返回一个数据库还可以返回一个数据集合数据集合l 该该数据集合数据集合在客户端的程序中可以被在客户端的程序中可

23、以被网格类网格类的对象接收；的对象接收；l 可以对其进行逐行处理；可以对其进行逐行处理；l 游标中可以嵌套游标。游标中可以嵌套游标。 n 例例9.25 输入某同学的学号，使用游标统计该同学的平均分，输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行显示该同学的姓名、选课名称和选课并返回平均分，同时逐行显示该同学的姓名、选课名称和选课成绩。成绩。CREATE PROCEDURE proStudentAvg(sNo char(7), avg numeric(6, 2) OUTPUT )ASBEGIN DECLARE sName varchar(20), cName varchar

24、(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=016School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程 -定义、打开、获取游标定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseNam

25、e, 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) BEGIN -业务处理业务处理 SELECT sName, cName, grade - 输出输出 SET sum=sum+grade SET count=count+1 FETCH curGrade INT

26、O sName, cName, grade END17School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程 CLOSE curGrade DEALLOCATE curGrade IF count=0 SELECT avg=0 ELSE SELECT avg=sum/countENDn 本例使用了本例使用了SELECT语句来显示变量的值，即语句来显示变量的值，

27、即 SELECT sName, cName, graden 由于由于存储过程仅在服务器端执行存储过程仅在服务器端执行，其，其显示的内容只在服务器端显示的内容只在服务器端出现出现，并不返回给客户端并不返回给客户端，这样的输出结果是没有价值的。，这样的输出结果是没有价值的。l 显示内容在调试存储过程时有作用，一旦过程调试正确，使用存显示内容在调试存储过程时有作用，一旦过程调试正确，使用存储过程的修改命令将显示内容删除。储过程的修改命令将显示内容删除。18School of Information Technology, Jiangxi University of Finance & Eco

28、nomics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 例例9.26 输入某学院名称，统计该学院每个班级同学的选课信输入某学院名称，统计该学院每个班级同学的选课信息，返回班级编号、班级名称、课程名称、课程选课人数、课息，返回班级编号、班级名称、课程名称、课程选课人数、课程平均分。程平均分。n 本例使用本例使用嵌套游标嵌套游标，读者通过该例掌握嵌套游标的使用方法。，读者通过该例掌握嵌套游标的使用方法。n 分析：分析：l本例涉及两个参数本例涉及两个参数一个是一个是输入参数输入参数：学院名称，设为：学院名称

29、，设为institute；一个是一个是输出参数输出参数，它为一个，它为一个集合值集合值，包含了，包含了该学院所有班级该学院所有班级的的班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人数课程选课人数、课程平课程平均分均分；对于对于集合值输出参数集合值输出参数，在过程中定义一个，在过程中定义一个临时表来存储该集临时表来存储该集合合，设临时表为，设临时表为#myTemp在过程尾部使用语句在过程尾部使用语句“SELECT * FROM #myTemp”将该集合将该集合返回给调用者。返回给调用者。19School of Information Technology, Jiangxi Un

30、iversity of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程l定义定义5个临时变量，分别保存查询出来的班级编号个临时变量，分别保存查询出来的班级编号classNo、班级名称班级名称className、课程名称、课程名称courseName、选课人数、选课人数count、选课平均分、选课平均分avg。l由于一个学院有多个班级，定义一个游标由于一个学院有多个班级，定义一个游标curClass，根据输入，根据输入的学院名称，查询该学院所有的班级编号和班级名称

31、。的学院名称，查询该学院所有的班级编号和班级名称。将查询出的班级编号和班级名称放入变量将查询出的班级编号和班级名称放入变量classNo、className中。定义游标语句为：中。定义游标语句为： DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute20School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章

32、数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程l由于由于一个班级选修了多门课程一个班级选修了多门课程，需依据查询出来的，需依据查询出来的班级班级号号，按选课的，按选课的课程名进行分组课程名进行分组计算，计算，统计该班统计该班每门课程每门课程的的选课人数选课人数和和选课平均分选课平均分。需要需要使用第二个游标使用第二个游标，将查询出来的该班的选课人数和平，将查询出来的该班的选课人数和平均分放入变量均分放入变量count和和avg中。定义游标语句为：中。定义游标语句为：DECLARE curCourse CURSOR FOR SELECT courseName, cou

33、nt(*), 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变量的值是从变量的值是从外游标外游标中获取的班级编号。中获取的班级编号。l将查询出来的班级编号、班级名称、课程名称、课程选将查询出来的班级编号、班级名称、课程名称、课程选课人数、课程平均分课人数、课程平均分插入到临时表插入到临时表#myTemp中。中。21School of In

34、formation Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程l存储过程为：存储过程为：CREATE PROCEDURE proInstitute( institute varchar(30) )ASBEGIN DECLARE className varchar(30), courseName varchar(30) DECLARE classNo char(6), count tinyin

35、t, avg numeric(5, 1) /*定义一个临时表，存放每个班级的班级编号、班级名称、课程定义一个临时表，存放每个班级的班级编号、班级名称、课程 名称、课程选课人数、课程平均分名称、课程选课人数、课程平均分*/ CREATE TABLE #myTemp ( classNo char(6), className varchar(30), courseName varchar(30), classCount tinyint, classAvg numeric(5, 1) )22School of Information Technology, Jiangxi University of

36、Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程 -定义游标定义游标curClass，依据，依据输入参数输入参数institute，查找，查找课程编号课程编号和和班级名称班级名称 DECLARE curClass CURSOR FOR SELECT classNo, className FROM Class WHERE institute=institute OPEN curClass FETCH curClass INTO classNo, className

37、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 courseName23School

38、of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程 OPEN curCourse FETCH curCourse INTO courseName, count, avg WHILE (FETCH_STATUS=0) BEGIN -将班级编号、班级名称、课程名称、课程选课人数将班级编号、班级名称、课程名称、课程选课人数 -课程平均分插入到临时表课程平均分插入到临时表#my

39、Temp中中 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, cl

40、assName END CLOSE curClass DEALLOCATE curClass -显示临时表的内容，同时将临时表的内容返回给调用者显示临时表的内容，同时将临时表的内容返回给调用者 SELECT * FROM #myTempEND24School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.1 创建存储过程创建存储过程n 在本例中，获取班级编号、班级名称不能写成：在本例中，获取班

41、级编号、班级名称不能写成： SELECT classNo=classNo, className=className FROM Class WHERE institute=institutel因为：因为：一个学院有多个班级一个学院有多个班级，该查询，该查询返回一个元组集合返回一个元组集合。变量变量classNo和和className仅接收一个数据仅接收一个数据。必须使用游标必须使用游标，本例定义游标为，本例定义游标为curClass。25School of Information Technology, Jiangxi University of Finance & Economics数

42、据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.2 执行存储过程执行存储过程n 使用存储过程时，必须执行命令使用存储过程时，必须执行命令EXECUTEn 语法：语法： EXECUTE = , = OUTPUT , = , = OUTPUT l注意：注意：EXECUTE的参数必须与对应的的参数必须与对应的PROCEDURE的的参数相匹配。参数相匹配。n 例例9.27 执行存储过程执行存储过程proStudentByNo1 EXECUTE proStudentByNo1 0800001 26School of Information Tech

43、nology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.2 执行存储过程执行存储过程n 例例9.28 执行存储过程执行存储过程proStudentByNo2 DECLARE sName varchar(20), avg numeric(5, 1)EXECUTE proStudentByNo2 0800001, sName OUTPUT, avg OUTPUTSELECT sName, avg n 例例9.29 执行过程执行过程proInst

44、itute EXECUTE proInstitute 信息管理学院信息管理学院l也可以使用命令：也可以使用命令：DECLARE institute varchar(30)SET institute=信息管理学院信息管理学院EXECUTE proInstitute institute27School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.3 修改和删除存储过程修改和删除存储过程 n 修改

45、存储过程修改存储过程n 语法为：语法为： ALTER PROCEDURE = OUTPUT , = OUTPUT AS l 注意：注意：由于存储过程是在服务器端执行，由于存储过程是在服务器端执行，程序中不需要有输出命令程序中不需要有输出命令SELECT，由由SELECT引出的输出不会在客户端出现。引出的输出不会在客户端出现。n 例例9.30 修改存储过程修改存储过程proStudentAvg，将显示结果内容删除。，将显示结果内容删除。ALTER PROCEDURE proStudentAvg( sNo char(7), avg numeric(6, 2) OUTPUT )ASBEGIN DEC

46、LARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=028School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.3 修改和删除存储过程修改和删除存储过程 -定义、打开、获取游标定义、打开、获取游标 DECLARE curGra

47、de 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) BEGIN -业务处理业务处理 SET sum=sum+grade SET count=count+1 FETCH cur

48、Grade INTO sName, cName, grade END CLOSE curGrade DEALLOCATE curGrade 29School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.4.3 修改和删除存储过程修改和删除存储过程 IF count=0 SELECT avg = 0 ELSE SELECT avg=sum/count ENDn 删除存储过程删除存储过程l语法：语法

49、： DROP PROCEDURE l例例9.31 删除存储过程删除存储过程proStudentAvg DROP PROCEDURE proStudentAvg30School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 目目 录录9.4数据库安全性数据库安全性 9.1数据库完整性数据库完整性 9.29.3游标游标 存储过程存储过程触发器触发器应用与安全设计应用与安全设计 9.59.631School

50、 of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5触发器触发器n 触发器触发器(trigger)是用户定义在是用户定义在关系表关系表上的一类由上的一类由事件驱动事件驱动的的存储过程，由服务器自动激活。存储过程，由服务器自动激活。n 触发器可进行更为复杂的检查和操作，具有更精细和更强大触发器可进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。的数据控制能力。n 触发器是一种特殊的存储过程，

51、不管什么原因造成的触发器是一种特殊的存储过程，不管什么原因造成的数据变数据变化化都能自动响应，对于每条都能自动响应，对于每条SQL语句，触发器仅执行一次，语句，触发器仅执行一次，事务事务可用于触发器中。可用于触发器中。n 事务定义：事务定义： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION n 有两个特殊的表用在触发器语句中，不同的数据库管理系统有两个特殊的表用在触发器语句中，不同的数据库管理系统其名称不一样：其名称不一样：l 在在SQL Server中使用中使用deleted和和inserted表；表；l Oracle数据库

52、使用数据库使用old和和new表。表。32School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5触发器触发器n 注意：注意：l 这两张表的结构与作用的表结构完全一致；这两张表的结构与作用的表结构完全一致；l 当作用表的当作用表的SQL语句开始时，自动产生这两张表的结构与内容；语句开始时，自动产生这两张表的结构与内容；l 当当SQL语句执行完毕，这两张表也随即删除。语句执行完毕，这两张表也随

53、即删除。n 下面以下面以SQL Server为例介绍触发器：为例介绍触发器：n deleted表表l 存储存储DELETE和和UPDATE语句执行时所影响的行的拷贝；语句执行时所影响的行的拷贝；l 在在DELETE和和UPDATE语句执行前被作用的行转移到语句执行前被作用的行转移到deleted表中。表中。将被删除的元组或修改前的元组值存入该表中将被删除的元组或修改前的元组值存入该表中n inserted表表l 存储存储INSERT和和UPDATE语句执行时所影响的行的拷贝；语句执行时所影响的行的拷贝；l 在在INSERT和和UPDATE语句执行期间，新行被同时加到语句执行期间，新行被同时加到

54、inserted表和表和触发器触发器表中。表中。将被插入的元组或修改后的元组值存入该表中，同时更新基本表。将被插入的元组或修改后的元组值存入该表中，同时更新基本表。33School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5触发器触发器n 实际上，实际上，UPDATE命令是删除后紧跟着插入，旧行命令是删除后紧跟着插入，旧行首先拷贝到首先拷贝到deleted表中，新行同时拷贝到表中，新行同时拷

55、贝到inserted表表和基本表中。和基本表中。n 触发器仅在当前数据库中生成触发器仅在当前数据库中生成l触发器有三种类型，即插入、删除和更新；触发器有三种类型，即插入、删除和更新；l插入、删除和更新可作为一种类型的触发器；插入、删除和更新可作为一种类型的触发器；l查询操作不会产生触发动作，没有查询触发器类型。查询操作不会产生触发动作，没有查询触发器类型。n 9.5.1 创建触发器创建触发器n 9.5.2 修改和删除触发器修改和删除触发器n 9.5.3 触发器的作用触发器的作用 34School of Information Technology, Jiangxi University of

56、Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5.1 创建触发器创建触发器n 创建触发器的语法：创建触发器的语法： CREATE TRIGGER ON FOR AS l其中：其中：触发器的名称，在数据库中必须唯一；：触发器的名称，在数据库中必须唯一；：触发器作用的基本表触发器作用的基本表，该表也称为触发器的，该表也称为触发器的目标表；目标表；：触发器事件触发器事件，触发器的，触发器的事件可以是插入事件可以是插入INSERT、更新、更新UPDATE和删除和删除DELETE事事件，也可以是这几个事

57、件的组合。件，也可以是这几个事件的组合。35School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5.1 创建触发器创建触发器INSERT 类型的触发器是指：当对指定表类型的触发器是指：当对指定表执行执行了插入操作时系统自动执行触发器代码。了插入操作时系统自动执行触发器代码。UPDATE 类型的触发器是指：当对指定表类型的触发器是指：当对指定表执执行了更新操作时系统自动执行触发器代码。行了更

58、新操作时系统自动执行触发器代码。DELETE类型的触发器是指：当对指定表类型的触发器是指：当对指定表执行执行了删除操作时系统自动执行触发器代码。了删除操作时系统自动执行触发器代码。：触发动作的执行体触发动作的执行体，即一段，即一段SQL语句块语句块如果该如果该触发执行体执行失败触发执行体执行失败，则激活触发器的事件就会，则激活触发器的事件就会终止，且触发器的目标表终止，且触发器的目标表或触发器可能影或触发器可能影响的其它表响的其它表不发生任何变化不发生任何变化，即执行事务的，即执行事务的回滚操作回滚操作。36School of Information Technology, Jiangxi U

59、niversity of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5.1 创建触发器创建触发器n 例例9.32 创建触发器，保证学生表中的性别仅能取男和女。创建触发器，保证学生表中的性别仅能取男和女。n 分析：分析：l本例需要使用插入和修改两个触发器，因为可能破坏约本例需要使用插入和修改两个触发器，因为可能破坏约束束“性别仅能取男和女性别仅能取男和女”的操作是插入和修改操作。的操作是插入和修改操作。l违约条件是：违约条件是：如果在如果在inserted表中表中存在有性别取值不为存在有性别

60、取值不为“男男”或或“女女”的记录的记录（由于（由于inserted表保存了修改后的记录，只要对表保存了修改后的记录，只要对inserted表进行判表进行判断即可），则断即可），则取消本次操作取消本次操作。l插入触发器插入触发器CREATE TRIGGER sexIns ON StudentFOR INSERTAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( 男男, 女女) ) ROLLBACK37School of Information Technology, Jiangxi University of Finance & Economics数据库系统原理与设计数据库系统原理与设计第第 9 9 章章 数据库完整性与安全数据库完整性与安全 9.5.1 创建触发器创建触发器l修改触发器修改触发器CREATE TRIGGER sexUpt ON StudentFOR UPDATEAS IF EXISTS (