第09章数据库完整性与安全性4-6节

1北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全第9章数据库完整性与安全树立健康的人生观、世界观胸怀正面的世界观，维持自己的人格不靠别人，要靠自己，为自己的未来念书不搞自我中心，具有团队精神，待人和善有礼要看到客观场景（Context）:要全面，不片面不光看当前，也看过去与未来数据库原理与设计2019/10/22北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全目录9.4数据库安全性9.1数据库完整性9.29.3游标存储过程触发器应用与安全设计9.59.62019/10/23北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4存储过程存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合该集合编译后存放在数据库中，可根据实际情况重新编译；存储过程可直接运行，也可远程运行；存储过程直接在服务器端运行。使用存储过程具有如下优点：将业务操作封装可为复杂的业务操作编写存储过程，放在数据库中；用户可调用存储过程执行，而业务操作对用户是不可见的；若存储过程仅修改了执行体，没有修改接口（即调用参数），则用户程序不需要修改，达到业务封装的效果。便于事务管理事务控制可以用在存储过程中；用户可依据业务的性质定义事务，并对事务进行相应级别的操作。2019/10/24北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4存储过程实现一定程度的安全性保护存储过程存放在数据库中，且在服务器端运行；对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性；这种安全性缘于用户对存储过程只有执行权限，没有查看权限；拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限；这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。注意：对存储过程只需授予执行权限，不需授予表或视图的操作权限。特别适合统计和查询操作一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大；很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。2019/10/25北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4存储过程减少网络通信量存储过程仅在服务器端执行，客户端只接收结果；由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。使用存储过程前，首先要创建存储过程。可对存储过程进行修改和删除。创建存储过程后，必须对存储过程授予执行EXECUTE的权限，否则该存储过程仅可以供创建者执行。9.4.1创建存储过程9.4.2执行存储过程9.4.3修改和删除存储过程2019/10/26北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程语法：CREATEPROCEDUREprocedureName[(@parameterNamedatatype[=defaultValue][OUTPUT][,@parameterNamedatatype[=defaultValue][OUTPUT]])]ASSQL-Statements其中：procedureName：过程名，必须符合标识符规则，且在数据库中唯一；@parameterName：参数名，存储过程可不带参数，参数可以是变量、常量和表达式；OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省时表示是输入参数。2019/10/27北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。临时表的表名前加一个#符号，如#myTemp在存储过程尾部，使用语句：SELECT*FROM#myTemp将结果集合返回给调用者。存储过程结束后，临时表自动被删除。注意：用户定义的存储过程只能在当前数据库中创建；一个存储过程最大不能超过128MB。若超过128MB，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。2019/10/28北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程[例9.23]输入某个同学的学号，统计该同学的平均分。CREATEPROCEDUREproStudentByNo1(@sNochar(7))ASSELECTa.studentNo,studentName,avg(score)FROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=@sNoGROUPBYa.studentNo,studentName2019/10/29北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程[例9.24]输入某个同学的学号，统计该同学的平均分，并返回该同学的姓名和平均分。分析：该过程涉及三个参数：一个输入参数，设为@sNo，用于接收某同学的学号；两个输出参数，用于返回查询到的同学姓名和平均分，设为@sName和@avg实现方法一：用一个查询，根据输入参数@sNo，查询出该同学的姓名并放到输出参数@sName中由于在学生表中学号是唯一的，使用命令：SELECT@snName=studentNameFROMStudentWHEREstudentNo=@sNo2019/10/210北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程用另一个查询，根据输入参数@sNo，查询出该同学的选课平均分并放到输出参数@avg中由于该同学的平均分也只有一个，使用命令：SELECT@avg=avg(score)FROMScoreWHEREstudentNo=@sNoGROUPBYstudentNo2019/10/211北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程存储过程为：CREATEPROCEDUREproStudentByNo21(@sNochar(7),@sNamevarchar(20)OUTPUT,@avgnumeric(5,1)OUTPUT)ASBEGIN--当SQL-Statements中多于1个SQL语句时，使用块定义--查询同学的姓名放入输出参数@sName中SELECT@sName=studentNameFROMStudentWHEREstudentNo=@sNo--查询同学选课的平均分放入输出参数@avg中SELECT@avg=avg(score)FROMScoreWHEREstudentNo=@sNoGROUPBYstudentNoEND2019/10/212北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程实现方法二：用一个查询，根据输入参数@sNo，查询出该同学的姓名并放到输出参数@sName中，其命令同方法一定义一个游标，根据输入参数@sNo，查询该同学所有的选课记录，使用命令：DECLAREmyCurCURSORFORSELECTscoreFROMScoreWHEREstudentNo=@sNo定义局部变量@score，用于接收从游标集中获取的成绩；定义局部变量@count，用于统计选课门数；定义局部变量@sum，用于对成绩进行累加。2019/10/213北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程其存储过程为：CREATEPROCEDUREproStudentByNo22(@sNochar(7),@sNamevarchar(20)OUTPUT,@avgnumeric(5,1)OUTPUT)ASBEGINDECLARE@scoretinyint,@counttinyint,@sumint--查找姓名，并放入到输出参数@sName中SELECT@sName=studentNameFROMStudentWHEREstudentNo=@sNo--变量赋初值SET@count=0SET@sum=0--统计学生选课门数@count和总分@sum，使用游标：DECLAREmyCurCURSORFORSELECTscoreFROMScoreWHEREstudentNo=@sNo2019/10/214北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程OPENmyCur--打开游标FETCHmyCurINTO@score--获取当前元组数据WHILE(@@FETCH_STATUS=0)BEGINSET@count=@count+1SET@sum=@sum+@scoreFETCHmyCurINTO@score--获取下一元组数据ENDCLOSEmyCurDEALLOCATEmyCurIF@count0SELECT@avg=@sum/@countELSESELECT@avg=0END2019/10/215北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程SQLServer数据库还可以返回一个数据集合该数据集合在客户端的程序中可以被网格类的对象接收；可以对其进行逐行处理；游标中可以嵌套游标。[例9.25]输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行显示该同学的姓名、选课名称和选课成绩。CREATEPROCEDUREproStudentAvg(@sNochar(7),@avgnumeric(6,2)OUTPUT)ASBEGINDECLARE@sNamevarchar(20),@cNamevarchar(20)DECLARE@gradetinyint,@sumint,@counttinyintSELECT@sum=0,@count=02019/10/216北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程--定义、打开、获取游标DECLAREcurGradeCURSORFORSELECTstudentName,courseName,scoreFROMScorea,Studentb,CoursecWHEREb.studentNo=@sNoANDa.studentNo=b.studentNoANDa.courseNo=c.courseNoOPENcurGradeFETCHcurGradeINTO@sName,@cName,@gradeWHILE(@@FETCH_STATUS=0)BEGIN--业务处理SELECT@sName,@cName,@grade--输出SET@sum=@sum+@gradeSET@count=@count+1FETCHcurGradeINTO@sName,@cName,@gradeEND2019/10/217北京理工大学珠海学院计算机学院数据库原理与设计张申勇第9章数据库完整性与安全9.4.1创建存储过程CLOSEcurGradeDEALLOCATEcurGradeIF@count=0SELECT@avg=0ELSESELECT@avg=@sum/@countEND本例使用了SELECT语句来显示变量的值，即SELECT@sName,@cName,@grade由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。显示内容在调试存储过程时有作用，一旦过程调试正确