第八章 存储过程与触发器.pptVIP

第八章 存储过程与触发器 存储过程与触发器是数据库中的一个重要组成部分，存储过程可以把数据库的复杂操作封装为独立的程序模块，便于程序的维护和减少网络通讯流量。而触发器是可以自动调用执行的程序模块，可以实现比较复杂的约束功能，本章主要介绍了存储过程与触发器的概念，以及如何创建、使用和管理存储过程与触发器。 8.1存储过程 8.1.1存储过程概述 存储过程(Stored Procedure)是一组预先编译好的T-SQL代码。存储过程可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。存储过程经过了一次创建后，可以被多次调用。由于是已经编译好的代码，所以在执行的时候不必再次进行编译，从而提高了程序的运行效率。使用存储过程具有以下优点： ●存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 ●当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 8.1存储过程 ●存储过程可以重复使用,可减少数据库开发人员的工作量。 ●使用存储过程可以完成所有数据库操作，并可通过编程方式控制对数据库信息访问的权限以提高安全性。 8.1.2存储过程的类型 SQL Server支持的存储过程可分为5类：在不同情况下需要执行不同的存储过程。 系统存储过程、 本地存储过程、 临时存储过程、 远程存储过程 扩展存储过程。 8.1存储过程 1．系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_,它们为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。 2．本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。 3．临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个#，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 8.1存储过程 如果本地存储过程的名称前有两个#，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。 4．远程存储过程 远程存储过程指从远程服务器上调用的存储过程。 5．扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。 8.1存储过程 8.1.3存储过程的创建 用户存储过程只能定义在当前数据库中，可以使用SQL Server的企业管理器或Transact-SQL语句创建存储过程。默认情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。 1．使用企业管理器创建存储过程 (1)启动企业管理器，登录到要使用的服务器。 (2)在企业管理器的左窗格中，展开要创建存储过程的数据库文件夹，单击“存储过程”文件夹，此时在右窗格中显示该数据库的所有存储过程。 (3)右击“存储过程”文件夹，在弹出的快捷菜单中选择“新建存储过程”，此时打开如图8-1所示的“新建存储过程”对话框。 8.1存储过程 新建存储过程如图: 8.1存储过程 (4)在“文本”编辑框中输入存储过程的正文内容。 (5)单击“检查语法”按钮，检查语法是否正确。 (6)单击“确定”按钮保存。 (7)在右窗格中，右击该存储过程，在弹出菜单中选择“所有任务”，选择“管理权限”设置权限。 8.1存储过程 2.使用Transact-SQL语句创建存储过程 CREATE PROCEDURE的语法形式如下： CREATE PROC procedure_name[;number] [{ @parameter data_type} [VARYING][=default][OUTPUT] 〕[，…n] WITH {RECOMPILE | ENCRYPTION|RECOMPILE} [FOR REPLICATION] AS sql_statements 8.1存储过程 其中: ● procedure_name:用于定义存储过程名，必须符合标识符规则，且对于数据库及其所有者必须惟一;创建局部临时过程，可以在procedure name前面加一个#：创建全局