CH9 数据库完整性与安全_2概要1.pptVIP

9.4 存储过程 存储过程是为了完成特定功能汇集而成的一组命名了的SQL语句集合 该集合编译后存放在数据库中，可根据实际情况重新编译； 存储过程可直接运行，也可远程运行； 存储过程直接在服务器端运行。 使用存储过程具有如下优点： 将业务操作封装 可为复杂的业务操作编写存储过程，放在数据库中； 用户可调用存储过程执行，而业务操作对用户是不可见的； 若存储过程仅修改了执行体，没有修改接口，则用户程序不需要修改，达到业务封装的效果。 便于事务管理 事务控制可以用在存储过程中； 用户可依据业务的性质定义事务，并对事务进行相应级别的操作。 9.4 存储过程 实现一定程度的安全性保护 存储过程存放在数据库中，且在服务器端运行； 对于不允许用户直接操作的表或视图，可通过调用存储过程来间接地访问这些表或视图，达到一定程度的安全性； 这种安全性缘于用户对存储过程只有执行权限，没有查看权限； 拥有存储过程的执行权限，自动获取了存储过程中对相应表或视图的操作权限； 这些操作权限仅能通过执行存储过程来实现，一旦脱离存储过程，也就失去了相应操作权限。 注意：对存储过程只需授予执行权限，不需授予表或视图的操作权限。 特别适合统计和查询操作 一般统计和查询，尤其是期末统计，往往涉及数据量大、表多，若在客户端实现，数据流量和网络通信量较大； 很多情况下，管理信息系统的设计者，将复杂的查询和统计用存储过程来实现，免去客户端的大量编程。 9.4 存储过程 减少网络通信量 存储过程仅在服务器端执行，客户端只接收结果； 由于存储过程与数据一般在一个服务器中，可减少大量的网络通信量。 使用存储过程前，首先要创建存储过程。可对存储过程进行修改和删除。 创建存储过程后，必须对存储过程授予执行EXECUTE的权限，否则该存储过程仅可以供创建者执行。 9.4.1 创建存储过程 9.4.2 执行存储过程 9.4.3 修改和删除存储过程 9.4.1 创建存储过程 语法： CREATE PROCEDURE procedureName [(@parameterName datatype [= defaultValue] [OUTPUT] [, @parameterName datatype [= defaultValue] [OUTPUT] ] ) ] AS SQL-Statements 其中： procedureName：过程名，必须符合标识符规则，且在数据库中唯一； parameterName：参数名，存储过程可不带参数，参数可以是变量、常量和表达式； OUTPUT：说明该参数是输出参数，被调用者获取使用。缺省时表示是输入参数。 9.4.1 创建存储过程 如果存储过程的输出参数取集合值，则该输出参数不在存储过程的参数中定义，而是在存储过程中定义一个临时表来存储该集合值。 临时表的表名前加一个#符号，如#myTemp 在存储过程尾部，使用语句： SELECT * FROM #myTemp 将结果集合返回给调用者。 存储过程结束后，临时表自动被删除。 注意： 用户定义的存储过程只能在当前数据库中创建； 一个存储过程最大不能超过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.studentNo 9.4.1 创建存储过程 [例9.24] 输入某个同学的学号，统计该同学的平均分，并返回该同学的姓名和平均分。 分析： 该过程涉及三个参数： 一个输入参数，设为@sNo，用于接收某同学的学号； 两个输出参数，用于返回查询到的同学姓名和平均分，设为@sName 和@avg 实现方法一： 用一个查询，根据输入参数@sNo，查询出该同学的姓名并放到输出参数@sName中 由于在学生表中学号是唯一的，使用命令： SELECT @snName=studentName FROM Student WHERE studentNo=@sNo 9.4.1 创建存储过程 用另一个查询，根据输入参数@sNo，查询出该同学的选课平均分并放到输出参数@avg中 由于该同