《第09章数据库完整性与安全性4-6节精编版》由会员分享，可在线阅读，更多相关《第09章数据库完整性与安全性4-6节精编版（55页珍藏版）》请在金锄头文库上搜索。
1、第9章 数据库完整性与安全,树立健康的人生观、世界观 胸怀正面的世界观，维持自己的人格 不靠别人，要靠自己，为自己的未来念书 不搞自我中心，具有团队精神，待人和善有礼 要看到客观场景（Context）: 要全面，不片面 不光看当前，也看过去与未来,数据库原理与设计,2020/9/17,目 录,数据库安全性,数据库完整性,游标,存储过程,触发器,应用与安全设计,2020/9/17,9.4 存储过程,存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合 该集合编译后存放在数据库中，可根据实际情况重新编译； 存储过程可直接运行，也可远程运行； 存储过程直接在服务器端运行。 使用存储过程具有
2、如下优点： 将业务操作封装 可为复杂的业务操作编写存储过程，放在数据库中； 用户可调用存储过程执行，而业务操作对用户是不可见的； 若存储过程仅修改了执行体，没有修改接口（即调用参数），则用户程序不需要修改，达到业务封装的效果。 便于事务管理 事务控制可以用在存储过程中； 用户可依据业务的性质定义事务，并对事务进行相应级别的操作。,2020/9/17,9.4 存储过程,实现一定程度的安全性保护 存储过程存放在数据库中，且在服务器端运行； 对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性； 这种安全性缘于用户对存储过程只有执行权限，没有查看权限；
3、拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限； 这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。 注意：对存储过程只需授予执行权限，不需授予表或视图的操作权限。 特别适合统计和查询操作 一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大； 很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。,2020/9/17,9.4 存储过程,减少网络通信量 存储过程仅在服务器端执行，客户端只接收结果； 由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。
4、 使用存储过程前，首先要创建存储过程。可对存储过程进行修改和删除。 创建存储过程后，必须对存储过程授予执行EXECUTE的权限，否则该存储过程仅可以供创建者执行。 9.4.1 创建存储过程 9.4.2 执行存储过程 9.4.3 修改和删除存储过程,2020/9/17,9.4.1 创建存储过程,语法： CREATE PROCEDURE ( = OUTPUT , = OUTPUT ) AS 其中： ：过程名，必须符合标识符规则，且在数据库中唯一； ：参数名，存储过程可不带参数，参数可以是变量、常量和表达式； OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省时表示是输入参数。,2020/9
5、/17,9.4.1 创建存储过程,如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。 临时表的表名前加一个#符号，如#myTemp 在存储过程尾部，使用语句： SELECT * FROM #myTemp 将结果集合返回给调用者。 存储过程结束后，临时表自动被删除。 注意： 用户定义的存储过程只能在当前数据库中创建； 一个存储过程最大不能超过128MB。若超过128MB，可将超出的部分编写为另一个存储过程，然后在存储过程中调用。,2020/9/17,9.4.1 创建存储过程,例9.23 输入某个同学的学号，统计该同学的平均分。 C
6、REATE 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.studentNo, studentName,2020/9/17,9.4.1 创建存储过程,例9.24 输入某个同学的学号，统计该同学的平均分，并返回该同学的姓名和平均分。 分析： 该过程涉及三个参数： 一个输入参数，设为sNo，用于接收某同学的学
7、号； 两个输出参数，用于返回查询到的同学姓名和平均分，设为sName 和avg 实现方法一： 用一个查询，根据输入参数sNo，查询出该同学的姓名并放到输出参数sName中 由于在学生表中学号是唯一的，使用命令： SELECT snName=studentName FROM Student WHERE studentNo=sNo,2020/9/17,9.4.1 创建存储过程,用另一个查询，根据输入参数sNo，查询出该同学的选课平均分并放到输出参数avg中 由于该同学的平均分也只有一个，使用命令： SELECT avg=avg(score) FROM Score WHERE studentNo=s
8、No GROUP BY studentNo,2020/9/17,9.4.1 创建存储过程,存储过程为： CREATE PROCEDURE proStudentByNo21(sNo char(7), sName varchar(20) OUTPUT, avg numeric(5, 1) OUTPUT ) AS BEGIN - 当中多于1个SQL语句时，使用块定义 -查询同学的姓名放入输出参数sName中 SELECT sName=studentName FROM Student WHERE studentNo=sNo -查询同学选课的平均分放入输出参数avg中 SELECT avg=avg(sc
9、ore) FROM Score WHERE studentNo=sNo GROUP BY studentNo END,2020/9/17,9.4.1 创建存储过程,实现方法二： 用一个查询，根据输入参数sNo，查询出该同学的姓名并放到输出参数sName中，其命令同方法一 定义一个游标，根据输入参数sNo，查询该同学所有的选课记录，使用命令： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo 定义局部变量score，用于接收从游标集中获取的成绩； 定义局部变量count，用于统计选课门数； 定义局部变量sum
10、，用于对成绩进行累加。,2020/9/17,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 FROM Student WHERE studentNo=sNo -变量赋初值 SET count=0 SET s
11、um=0 -统计学生选课门数count和总分sum，使用游标： DECLARE myCur CURSOR FOR SELECT score FROM Score WHERE studentNo=sNo,2020/9/17,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 CLOSE myCur DEALLOCAT
12、E myCur IF count0 SELECT avg=sum / count ELSE SELECT avg=0 END,2020/9/17,9.4.1 创建存储过程,SQL Server数据库还可以返回一个数据集合 该数据集合在客户端的程序中可以被网格类的对象接收； 可以对其进行逐行处理； 游标中可以嵌套游标。 例9.25 输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行显示该同学的姓名、选课名称和选课成绩。 CREATE PROCEDURE proStudentAvg(sNo char(7), avg numeric(6, 2) OUTPUT ) AS BEGIN
13、DECLARE sName varchar(20), cName varchar(20) DECLARE grade tinyint, sum int, count tinyint SELECT sum=0, count=0,2020/9/17,9.4.1 创建存储过程,-定义、打开、获取游标 DECLARE curGrade CURSOR FOR SELECT studentName, courseName, score FROM Score a, Student b, Course c WHERE b.studentNo=sNo AND a.studentNo=b.studentNo AN
14、D 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 END,2020/9/17,9.4.1 创建存储过程,CLOSE curGrade DEALLOCATE curGrade IF count=0 SELECT
15、 avg=0 ELSE SELECT avg=sum/count END 本例使用了SELECT语句来显示变量的值，即 SELECT sName, cName, grade 由于存储过程仅在服务器端执行，其显示的内容只在服务器端出现，并不返回给客户端，这样的输出结果是没有价值的。 显示内容在调试存储过程时有作用，一旦过程调试正确，使用存储过程的修改命令将显示内容删除。,2020/9/17,9.4.1 创建存储过程,例9.26 输入某学院名称，统计该学院每个班级同学的选课信息，返回班级编号、班级名称、课程名称、课程选课人数、课程平均分。 本例使用嵌套游标，读者通过该例掌握嵌套游标的使用方法。 分
16、析： 本例涉及两个参数 一个是输入参数：学院名称，设为institute； 一个是输出参数，它为一个集合值，包含了该学院所有班级的班级编号、班级名称、课程名称、课程选课人数、课程平均分； 对于集合值输出参数，在过程中定义一个临时表来存储该集合，设临时表为#myTemp 在过程尾部使用语句“SELECT * FROM #myTemp”将该集合返回给调用者。,2020/9/17,9.4.1 创建存储过程,定义5个临时变量，分别保存查询出来的班级编号classNo、班级名称className、课程名称courseName、选课人数count、选课平均分avg。 由于一个学院有多个班级，定义一个游标curClass，根据输入的学院名称，查询该学院所有的班级编号和班级名称。 将查询出的班级编号和班级名称
