第8章 存储过程和触发器-01.ppt

北京物资学院信息学院 8.1 存储过程的创建和使用 1 存储过程的定义 2 存储过程的优点 3 存储过程的创建 4 重新编译存储过程 5 自动执行存储过程 6 查看、修改和删除存储过程 7 扩展存储过程 1 存储过程的定义 存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。 存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。 系统存储过程 系统存储过程是指安装SQL Server时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。 扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如C或C++等）创建自己的扩展过程。 用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。 2 存储过程的优点 存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点： 存储过程提供了处理复杂任务的能力 存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。 增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率。 减少网络数据流量 存储过程是与数据库一起存放在服务器中并在服务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。 加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。 加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。 3 存储过程的创建 3.1 使用管理平台创建存储过程 3.2 使用T-SQL语句创建存储过程 3.3 创建带输入参数的存储过程 3.4 创建带输出参数的存储过程 3.1 使用管理平台创建存储过程 当创建存储过程时，需要确定存储过程的三个组成部分： 1) 所有的输入参数以及传给调用者的输出参数。 2) 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 3) 返回给调用者的状态值，以指明调用是成功还是失败。 【8-1】在SQL Server Management Studio中创建存储过程。 （1）打开SQL Server Management Studio，用鼠标单击“对象资源管理器”中“学生成绩管理系统”下的“可编程性”下的“存储过程”，在窗口的右侧显示当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单上选择“新建存储过程”，如图所示。 （2）在新打开的SQL命令窗口中，给出了创建存储过程命令的模板，输入代码。 CREATE PROCEDURE usp_GetAllStudents AS SELECT * FROM 学生表 （3）建立存储过程的命令成功完成后，在“对象资源管理器”中“学生成绩管理系统”下的“可编程性”下的“存储过程”中可以看到新建的存储过程。 （4）新建立的usp_GetAllStudents存储过程可通过以下代码执行，执行结果如图所示，显示所有学生的信息。 EXECUTE usp_GetAllStudents 3.2 使用T-SQL语句