《[精选]第09章数据库完整性与安全性4-6节7542.pptx》由会员分享，可在线阅读，更多相关《[精选]第09章数据库完整性与安全性4-6节7542.pptx（54页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1 1北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全第第9 9章章 数据库完整性与安全数据库完整性与安全树立健康的人生观、世界观u胸怀正面的世界观，维持自己的人格u不靠别人，要靠自己，为自己的未来念书u不搞自我中心，具有团队精神，待人和善有礼u要看到客观场景（Context）:要全面，不片面 不光看当前，也看过去与未来数据库原理与设计数据库原理与设计2023/3/202023/3/202 2北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库
2、原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全目目 录录9.4数据库安全性 9.1数据库完整性 9.29.3游标 存储过程触发器应用与安全设计 9.59.62023/3/202023/3/203 3北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4存储过程存储过程n存储过程存储过程是为了完成特定功能汇集而成的是为了完成特定功能汇集而成的一组命名了的一组命名了的SQL语语句集合句集合l该集合该集合编译后存放在数据库中编译后存放在数据库中，可根
3、据实际情况重新编译；，可根据实际情况重新编译；l存储过程存储过程可直接运行可直接运行，也可远程运行；，也可远程运行；l存储过程存储过程直接在服务器端运行直接在服务器端运行。n使用存储过程具有如下优点：使用存储过程具有如下优点：l将业务操作封装将业务操作封装可为复杂的业务操作编写存储过程，放在数据库中；可为复杂的业务操作编写存储过程，放在数据库中；用户可调用存储过程执行，而用户可调用存储过程执行，而业务操作对用户是不可见的业务操作对用户是不可见的；若存储过程仅修改了执行体，没有修改若存储过程仅修改了执行体，没有修改接口（即调用参数）接口（即调用参数），则用，则用户程序不需要修改，达到户程序不需要
4、修改，达到业务封装的效果业务封装的效果。l便于事务管理便于事务管理事务控制可以用在存储过程中；事务控制可以用在存储过程中；用户可依据业务的性质定义事务，并对事务进行相应级别的操作。用户可依据业务的性质定义事务，并对事务进行相应级别的操作。2023/3/202023/3/204 4北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4存储过程存储过程l实现一定程度的安全性保护实现一定程度的安全性保护存储过程存放在数据库中，且在存储过程存放在数据库中，且在服务器端服务器端运行；运行
5、；对于不允许用户直接操作的表或视图，可通过调用存储过程来间接对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的地访问这些表或视图，达到一定程度的安全性安全性；这种安全性缘于用户这种安全性缘于用户对存储过程只有执行权限，没有查看权限；对存储过程只有执行权限，没有查看权限；拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限；的操作权限；这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。也就失去
6、了相应操作权限。l注意：注意：对存储过程只需授予对存储过程只需授予执行权限执行权限，不需授予，不需授予表或视图表或视图的操作的操作权限权限。l特别适合统计和查询操作特别适合统计和查询操作一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在在客户端客户端实现，实现，数据流量和网络通信量较大数据流量和网络通信量较大；很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，过程来实现，免去客户端的大量编程免去客户端的大量编程。2023/3/202023/3/205
7、 5北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4存储过程存储过程l减少网络通信量减少网络通信量存储过程仅在存储过程仅在服务器端服务器端执行，执行，客户端只接收结果客户端只接收结果；由于存储过程与数据一般在一个服务器中，可减少大量的网络由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。通信量。l使用存储过程前，首先要使用存储过程前，首先要创建存储过程创建存储过程。可对存储过程进行。可对存储过程进行修改和删除修改和删除。l创建存储过程后，必须对存储过程创建存储
8、过程后，必须对存储过程授予执行授予执行EXECUTE的权的权限限，否则该存储过程仅可以供创建者执行。，否则该存储过程仅可以供创建者执行。n9.4.1创建存储过程创建存储过程n9.4.2执行存储过程执行存储过程n9.4.3修改和删除存储过程修改和删除存储过程2023/3/202023/3/206 6北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n语法：语法：CREATEPROCEDURE(=OUTPUT,=OUTPUT)ASl其中：其中：过程名
9、过程名，必须符合标识符规则，且在数据，必须符合标识符规则，且在数据库中唯一；库中唯一；：参数名参数名，存储过程可不带参数，参数可，存储过程可不带参数，参数可以是以是变量、常量和表达式变量、常量和表达式；OUTPUT：说明该参数是：说明该参数是输出参数输出参数，被调用者获取使用。，被调用者获取使用。缺缺省时表示是输入参数省时表示是输入参数。2023/3/202023/3/207 7北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n如果存储过程的如
10、果存储过程的输出参数输出参数取取集合值集合值，则，则该输出参数不在该输出参数不在存储过程的参数中定义存储过程的参数中定义，而是在存储过程中定义一个，而是在存储过程中定义一个临临时表时表来存储该集合值。来存储该集合值。l临时表的表名前加一个临时表的表名前加一个#符号，如符号，如#myTempl在存储过程尾部，使用语句：在存储过程尾部，使用语句：SELECT*FROM#myTemp将结果集合返回给调用者将结果集合返回给调用者。l存储过程结束后，临时表自动被删除。存储过程结束后，临时表自动被删除。n注意：注意：l用户定义的存储过程只能在当前数据库中创建；用户定义的存储过程只能在当前数据库中创建；l一
11、个存储过程最大不能超过一个存储过程最大不能超过128MB。若超过。若超过128MB，可将超出，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。的部分编写为另一个存储过程，然后在存储过程中调用。2023/3/202023/3/208 8北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n例例9.23输入某个同学的学号输入某个同学的学号，统计该同学的平均分统计该同学的平均分。CREATEPROCEDUREproStudentByNo1(sN
12、ochar(7)ASSELECTa.studentNo,studentName,avg(score)FROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=sNoGROUPBYa.studentNo,studentName2023/3/202023/3/209 9北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n例例9.24输入某个同学的学号输入某个同学的学号，统计该同学的，统
13、计该同学的平均分平均分，并返并返回该同学的姓名和平均分回该同学的姓名和平均分。n分析：分析：l该过程涉及三个参数：该过程涉及三个参数：一个一个输入参数输入参数，设为，设为sNo，用于接收某同学的学号；，用于接收某同学的学号；两个两个输出参数输出参数，用于返回查询到的同学，用于返回查询到的同学姓名和平均分姓名和平均分，设，设为为sName和和avgl实现方法一：实现方法一：用一个查询，根据用一个查询，根据输入参数输入参数sNo，查询出该同学的，查询出该同学的姓姓名名并放到并放到输出参数输出参数sName中中由于在学生表中学号是唯一的，使用命令：由于在学生表中学号是唯一的，使用命令：SELECT
14、snName=studentName FROM Student WHERE studentNo=sNo2023/3/202023/3/201010北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程用另一个查询，根据用另一个查询，根据输入参数输入参数sNo，查询出该同学的，查询出该同学的选课选课平均分平均分并放到并放到输出参数输出参数avg中中由于该同学的平均分也只有一个，使用命令：由于该同学的平均分也只有一个，使用命令：SELECT avg=av
15、g(score)FROM Score WHERE studentNo=sNo GROUP BY studentNo2023/3/202023/3/201111北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程存储过程为：存储过程为：CREATEPROCEDUREproStudentByNo21(sNochar(7),sNamevarchar(20)OUTPUT,avgnumeric(5,1)OUTPUT)ASBEGIN-当当中多于中多于1个个SQ
16、L语句时，使用语句时，使用块定义块定义-查询同学的查询同学的姓名姓名放入输出参数放入输出参数sName中中SELECTsName=studentNameFROMStudentWHEREstudentNo=sNo-查询同学选课的查询同学选课的平均分平均分放入输出参数放入输出参数avg中中SELECT avg=avg(score)FROMScoreWHEREstudentNo=sNoGROUPBYstudentNoEND2023/3/202023/3/201212北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库
17、完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程l实现方法二：实现方法二：用一个查询，根据用一个查询，根据输入参数输入参数sNo，查询出该同学的，查询出该同学的姓名姓名并放并放到到输出参数输出参数sName中，其命令同方法一中，其命令同方法一定义一个定义一个游标游标，根据，根据输入参数输入参数sNo，查询该同学所有的，查询该同学所有的选课选课记录记录，使用命令：，使用命令：DECLAREmyCurCURSORFORSELECTscoreFROMScoreWHEREstudentNo=sNo定义局部变量定义局部变量score，用于接收从游标集中获取的，用于接收从游标集中获取的成绩
18、成绩；定义局部变量定义局部变量count，用于统计选课门数；，用于统计选课门数；定义局部变量定义局部变量sum，用于对成绩进行累加。，用于对成绩进行累加。2023/3/202023/3/201313北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程其存储过程为：其存储过程为：CREATEPROCEDUREproStudentByNo22(sNochar(7),sNamevarchar(20)OUTPUT,avgnumeric(5,1)OUTPUT
19、)ASBEGINDECLAREscoretinyint,counttinyint,sumint-查找查找姓名姓名，并放入到输出参数，并放入到输出参数sName中中SELECTsName=studentNameFROMStudentWHEREstudentNo=sNo-变量赋初值变量赋初值SETcount=0SETsum=0-统计学生统计学生选课门数选课门数count和和总分总分sum，使用游标：，使用游标：DECLAREmyCurCURSORFORSELECTscoreFROMScoreWHEREstudentNo=sNo2023/3/202023/3/201414北京理工大学珠海学院北京理工
20、大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程OPENmyCur -打开游标打开游标FETCHmyCurINTOscore -获取当前元组数据获取当前元组数据WHILE(FETCH_STATUS=0)BEGINSETcount=count+1SETsum=sum+scoreFETCHmyCurINTOscore -获取下一元组数据获取下一元组数据ENDCLOSEmyCurDEALLOCATEmyCurIFcount0SELECTavg=sum/countELSESELE
21、CTavg=0END2023/3/202023/3/201515北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程nSQLServer数据库还可以返回一个数据库还可以返回一个数据集合数据集合l该该数据集合数据集合在客户端的程序中可以被在客户端的程序中可以被网格类网格类的对象接收；的对象接收；l可以对其进行逐行处理；可以对其进行逐行处理；l游标中可以嵌套游标。游标中可以嵌套游标。n例例9.25输入某同学的学号，输入某同学的学号，使用游标统计该同学的
22、平均分使用游标统计该同学的平均分，并返回平均分，并返回平均分，同时逐行显示该同学的姓名、同时逐行显示该同学的姓名、选课名称选课名称和和选课选课成绩成绩。CREATEPROCEDUREproStudentAvg(sNochar(7),avgnumeric(6,2)OUTPUT)ASBEGINDECLAREsNamevarchar(20),cNamevarchar(20)DECLAREgradetinyint,sumint,counttinyintSELECTsum=0,count=02023/3/202023/3/201616北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库
23、原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程-定义、打开、获取游标定义、打开、获取游标DECLAREcurGradeCURSORFORSELECTstudentName,courseName,scoreFROMScorea,Studentb,CoursecWHEREb.studentNo=sNoANDa.studentNo=b.studentNoANDa.courseNo=c.courseNoOPENcurGradeFETCHcurGradeINTOsName,cName,gradeWHILE(FETCH_S
24、TATUS=0)BEGIN-业务处理业务处理SELECTsName,cName,grade-输出输出SETsum=sum+gradeSETcount=count+1FETCHcurGradeINTOsName,cName,gradeEND2023/3/202023/3/201717北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程CLOSEcurGradeDEALLOCATEcurGradeIFcount=0SELECTavg=0ELSESELE
25、CTavg=sum/countENDn本例使用了本例使用了SELECT语句来显示变量的值，即语句来显示变量的值，即SELECTsName,cName,graden由于由于存储过程仅在服务器端执行存储过程仅在服务器端执行，其，其显示的内容只在服务器端显示的内容只在服务器端出现出现，并不返回给客户端并不返回给客户端，这样的输出结果是没有价值的。，这样的输出结果是没有价值的。l显示内容在调试存储过程时有作用显示内容在调试存储过程时有作用，一旦过程调试正确，使用存，一旦过程调试正确，使用存储过程的修改命令将显示内容删除。储过程的修改命令将显示内容删除。2023/3/202023/3/201818北京理
26、工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n例例9.26输入某学院名称，输入某学院名称，统计该学院每个班级同学的选课信统计该学院每个班级同学的选课信息息，返回，返回班级编号、班级名称、班级编号、班级名称、课程名称课程名称、课程选课人数课程选课人数、课课程平均分程平均分。n本例使用本例使用嵌套游标嵌套游标，读者通过该例掌握嵌套游标的使用方法。，读者通过该例掌握嵌套游标的使用方法。n分析：分析：l本例涉及两个参数本例涉及两个参数一个是一个是输入参数
27、输入参数：学院名称，设为：学院名称，设为institute；一个是一个是输出参数输出参数，它为一个，它为一个集合值集合值，包含了，包含了该学院所有班级该学院所有班级的的班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人数课程选课人数、课程平课程平均分均分；对于对于集合值输出参数集合值输出参数，在过程中定义一个，在过程中定义一个临时表来存储该集临时表来存储该集合合，设临时表为，设临时表为#myTemp在过程尾部使用语句在过程尾部使用语句“SELECT*FROM#myTemp”将该集合将该集合返回给调用者。返回给调用者。2023/3/202023/3/201919北京理工大学珠海学院
28、北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程l定义定义5个临时变量，分别保存查询出来的班级编号个临时变量，分别保存查询出来的班级编号classNo、班级名称班级名称className、课程名称、课程名称courseName、选课人数、选课人数count、选课平均分、选课平均分avg。l由于由于一个学院有多个班级一个学院有多个班级，定义一个游标，定义一个游标curClass，根据输入，根据输入的学院名称，查询该学院所有的班级编号和班级名称。的学院名称，查询该学院
29、所有的班级编号和班级名称。将查询出的班级编号和班级名称放入变量将查询出的班级编号和班级名称放入变量classNo、className中。定义游标语句为：中。定义游标语句为：DECLAREcurClassCURSORFORSELECTclassNo,classNameFROMClassWHEREinstitute=institute2023/3/202023/3/202020北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程l由于由于一个班级选修了
30、多门课程一个班级选修了多门课程，需依据查询出来的，需依据查询出来的班级号班级号，按选课的按选课的课程名进行分组课程名进行分组计算，计算，统计该班统计该班每门课程每门课程的的选课选课人数人数和和选课平均分选课平均分。需要需要使用第二个游标使用第二个游标，将查询出来的该班的选课人数和平均分，将查询出来的该班的选课人数和平均分放入变量放入变量count和和avg中。定义游标语句为：中。定义游标语句为：DECLAREcurCourseCURSORFORSELECTcourseName,count(*),avg(score)FROMStudent a,Scoreb,CoursecWHEREa.stude
31、ntNo=b.studentNoANDb.courseNo=c.courseNoANDclassNo=classNoGROUPBYcourseName注意：注意：classNo变量的值是从变量的值是从外游标外游标中获取的班级编号。中获取的班级编号。l将查询出来的将查询出来的班级编号班级编号、班级名称班级名称、课程名称课程名称、课程选课人课程选课人数数、课程平均分课程平均分插入到临时表插入到临时表#myTemp中。中。2023/3/202023/3/202121北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库
32、完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程l存储过程为：存储过程为：CREATEPROCEDUREproInstitute(institutevarchar(30)ASBEGINDECLAREclassNamevarchar(30),courseNamevarchar(30)DECLAREclassNochar(6),counttinyint,avgnumeric(5,1)/*定义一个临时表，存放每个班级的定义一个临时表，存放每个班级的班级编号、班级名称、班级编号、班级名称、课程课程名称名称、课程选课人数课程选课人数、课程平均分课程平均分*/CREATETABLE#myT
33、emp(classNochar(6),classNamevarchar(30),courseNamevarchar(30),classCounttinyint,classAvgnumeric(5,1)2023/3/202023/3/202222北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程-定义游标定义游标curClass，依据，依据输入参数输入参数institute，查找，查找班级编号班级编号和和班级名称班级名称DECLAREcurClas
34、sCURSORFORSELECTclassNo,classNameFROMClassWHEREinstitute=instituteOPENcurClassFETCHcurClassINTOclassNo,classNameWHILE(FETCH_STATUS=0)BEGIN-定义游标定义游标curCourse，查找，查找classNo班选课的班选课的课程名称课程名称、选课人数选课人数、平均分平均分DECLAREcurCourseCURSORFORSELECTcourseName,count(*),avg(score)FROMStudent a,Scoreb,CoursecWHEREa.stu
35、dentNo=b.studentNoANDb.courseNo=c.courseNoANDclassNo=classNoGROUPBYcourseName2023/3/202023/3/202323北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程OPENcurCourseFETCHcurCourseINTOcourseName,count,avgWHILE(FETCH_STATUS=0)BEGIN/*将将班级编号班级编号、班级名称班级名称、课程
36、名称课程名称、课程选课人数课程选课人数、课程平均分课程平均分插入到临时表插入到临时表#myTemp中中*/INSERTINTO#myTempVALUES(classNo,className,courseName,count,avg)-获取下一游标值，取该班下一门课程的获取下一游标值，取该班下一门课程的课程名课程名、选课人数选课人数和和平均分平均分FETCHcurCourseINTOcourseName,count,avgENDCLOSEcurCourseDEALLOCATEcurCourse-获取游标获取游标curClass的下一个值，即取下一个的下一个值，即取下一个班级班级FETCHcurC
37、lassINTOclassNo,classNameENDCLOSEcurClassDEALLOCATEcurClass-显示临时表的内容，同时将临时表的内容返回给调用者显示临时表的内容，同时将临时表的内容返回给调用者SELECT*FROM#myTempEND2023/3/202023/3/202424北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.1创建存储过程创建存储过程n在本例中，获取在本例中，获取班级编号班级编号、班级名称班级名称不能写成：不能写成：SELECTc
38、lassNo=classNo,className=classNameFROMClassWHEREinstitute=institutel因为：因为：一个学院有一个学院有多个班级多个班级，该查询，该查询返回一个元组集合返回一个元组集合。变量变量classNo和和className仅分别接收一个数据仅分别接收一个数据。必须使用必须使用游标游标，本例定义游标为，本例定义游标为curClass。2023/3/202023/3/202525北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全
39、9.4.2执行存储过程执行存储过程n使用存储过程时，必须执行命令使用存储过程时，必须执行命令EXECUTEn语法：语法：EXECUTE=,=OUTPUT,=,=OUTPUTl注意：注意：EXECUTE的参数的参数必须与对应的必须与对应的PROCEDURE的的参数参数相匹配。相匹配。n例例9.27执行存储过程执行存储过程proStudentByNo1EXECUTEproStudentByNo108000012023/3/202023/3/202626北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全
40、数据库完整性与安全9.4.2执行存储过程执行存储过程n例例9.28执行存储过程执行存储过程proStudentByNo2DECLAREsNamevarchar(20),avgnumeric(5,1)EXECUTEproStudentByNo20800001,sNameOUTPUT,avgOUTPUTSELECTsName,avgn例例9.29执行过程执行过程proInstituteEXECUTEproInstitute信息管理学院信息管理学院l也可以使用命令：也可以使用命令：DECLAREinstitutevarchar(30)SETinstitute=信息管理学院信息管理学院EXECUTEp
41、roInstituteinstitute2023/3/202023/3/202727北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.3修改和删除存储过程修改和删除存储过程n修改存储过程修改存储过程n语法为：语法为：ALTERPROCEDURE=OUTPUT,=OUTPUTAS l注意：注意：由于存储过程是在服务器端执行，由于存储过程是在服务器端执行，程序中不需要有程序中不需要有输出命令输出命令SELECT，由由SELECT引出的输出不会在客户端出现引出的输出不会在客户端
42、出现。n例例9.30修改存储过程修改存储过程proStudentAvg，将显示结果内容删除将显示结果内容删除。ALTERPROCEDUREproStudentAvg(sNo char(7),avgnumeric(6,2)OUTPUT)ASBEGINDECLAREsName varchar(20),cName varchar(20)DECLAREgradetinyint,sum int,counttinyintSELECTsum=0,count=02023/3/202023/3/202828北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张
43、申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.3修改和删除存储过程修改和删除存储过程-定义、打开、获取游标定义、打开、获取游标DECLAREcurGradeCURSORFORSELECTstudentName,courseName,scoreFROMScorea,Studentb,Course cWHEREb.studentNo=sNoAND a.studentNo=b.studentNoANDa.courseNo=c.courseNoOPENcurGradeFETCHcurGradeINTOsName,cName,gradeWHILE(FETCH_STATUS=0)BE
44、GIN-业务处理业务处理SETsum=sum+gradeSETcount=count+1FETCHcurGradeINTOsName,cName,gradeENDCLOSEcurGradeDEALLOCATEcurGrade2023/3/202023/3/202929北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.4.3修改和删除存储过程修改和删除存储过程IFcount=0SELECTavg=0ELSESELECTavg=sum/countENDn删除存储过程删除存储过程l
45、语法：语法：DROPPROCEDURE l例例9.31删除存储过程删除存储过程proStudentAvgDROPPROCEDUREproStudentAvg2023/3/202023/3/203030北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全目目 录录9.4数据库安全性 9.1数据库完整性 9.29.3游标 存储过程触发器应用与安全设计 9.59.62023/3/202023/3/203131北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计
46、数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.5触发器触发器n触发器触发器(trigger)是用户定义在是用户定义在关系表关系表上的一类由上的一类由事件驱动事件驱动的的存储过程，由服务器自动激活。存储过程，由服务器自动激活。n触发器可进行更为复杂的检查和操作，具有更精细和更强大触发器可进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。的数据控制能力。n触发器是一种特殊的存储过程，不管什么原因造成的触发器是一种特殊的存储过程，不管什么原因造成的数据变数据变化化都能自动响应，对于每条都能自动响应，对于每条SQL语句，触发器仅执行一次，语句，触发
47、器仅执行一次，事务事务可用于触发器中。可用于触发器中。n事务定义：事务定义：BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTIONn有两个特殊的表用在触发器语句中，不同的数据库管理系统有两个特殊的表用在触发器语句中，不同的数据库管理系统其名称不一样：其名称不一样：l在在SQLServer中使用中使用deleted和和inserted表；表；lOracle数据库使用数据库使用old和和new表。表。2023/3/202023/3/203232北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学院 数据库原理与设计数据库原理与设计 张申勇张
48、申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.5触发器触发器n注意：注意：l这两张表的结构与作用的表结构完全一致；这两张表的结构与作用的表结构完全一致；l当作用表的当作用表的SQL语句开始时，自动产生这两张表的结构与内容语句开始时，自动产生这两张表的结构与内容；l当当SQL语句执行完毕，这两张表也随即删除。语句执行完毕，这两张表也随即删除。n下面以下面以SQLServer为例介绍触发器：为例介绍触发器：ndeleted表表l存储存储DELETE和和UPDATE语句执行时语句执行时所影响的行的拷贝所影响的行的拷贝；l在在DELETE和和UPDATE语句执行前被作用的行转移到语句
49、执行前被作用的行转移到deleted表中。表中。将被删除的元组或修改前的元组值存入该表中将被删除的元组或修改前的元组值存入该表中ninserted表表l存储存储INSERT和和UPDATE语句执行时语句执行时所影响的行的拷贝所影响的行的拷贝；l在在INSERT和和UPDATE语句执行期间，新行被同时加到语句执行期间，新行被同时加到inserted表和表和触发器触发器表中。表中。将被插入的元组或修改后的元组值存入该表中，同时更新基本表。将被插入的元组或修改后的元组值存入该表中，同时更新基本表。2023/3/202023/3/203333北京理工大学珠海学院北京理工大学珠海学院 计算机学院计算机学
50、院 数据库原理与设计数据库原理与设计 张申勇张申勇第第 9 9 章章 数据库完整性与安全数据库完整性与安全9.5触发器触发器n实际上，实际上，UPDATE命令是命令是删除后紧跟着插入删除后紧跟着插入，旧行，旧行首先拷贝到首先拷贝到deleted表中，新行同时拷贝到表中，新行同时拷贝到inserted表表和和基本表基本表中。中。n触发器仅在当前数据库中生成触发器仅在当前数据库中生成l触发器有三种类型，即触发器有三种类型，即插入插入、删除删除和和更新更新；l插入、删除和更新可作为一种类型的触发器；插入、删除和更新可作为一种类型的触发器；l查询操作不会产生触发动作，没有查询触发器类型。查询操作不会产