第10 存储过程与触发器.pptVIP

第10章 存储过程与触发器 本章内容 10.1 存储过程概述 10.2 存储过程的创建与使用 10.3 触发器概述 10.4 触发器的创建与使用 10.5 事务处理 10.6 SQL Server的锁机制 10.1 存储过程概述 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。 1. 存储过程的类型 SQL Server存储过程的类型包括： 系统存储过程 用户定义存储过程 临时存储过程 扩展存储过程。 (1)系统存储过程 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，它从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。 通过系统存储过程，SQL Server中的许多管理性或信息性的活动(例如使用sp_depends、sp_helptexts可以了解数据数据库对象、数据库信息)都可以顺利有效地完成。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。 (2)用户定义存储过程 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。 (3)临时存储过程 临时存储过程与临时表类似，分为局部临时存储过程和全局临时存储过程，且可以分别向该过程名称前面添加“#”或“##”前缀表示。“#”表示本地临时存储过程，“##”表示全局临时存储过程。使用临时存储过程必须创建本地连接，当SQL Server关闭后，这些临时存储过程将自动被删除。 由于SQL Server支持重新使用执行计划，所以连接到SQL Server 2000的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。 (4)扩展存储过程 扩展存储过程是SQL Server可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。 2. 存储过程的功能特点 SQL Server的存储过程可实现以下功能： (1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。 (2)包含执行数据库操作的编程语句，包括调用其他过程。 (3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。 存储过程具有以下优点 (1)模块化编程。 (2)快速执行。 (3)减少网络通信量。 (4)提供安全机制。 (5)保证操作一致性。 10.2 存储过程的创建与使用 10.2.1 创建存储过程 10.2.2 执行存储过程 10.2.3 修改存储过程 10.2.4 删除存储过程 10.2.5 存储过程参数与状态值 10.2.1 创建存储过程 1．使用SQL Server管理平台创建存储过程 （1）打开SQL Server管理平台，展开节点“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令 。 （2）在打开的SQL命令窗口中，系统给出了创建存储过程命令的模板，如图10-2所示。在模板中可以输入创建存储过程的Transact-SQL语句后，单击“执行”按钮即可创建存储过程。 （3）建立存储过程的命令被成功执行后，在“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”中可以看到新建立的存储过程 2. 使用CREATE PROCEDURE语句创建存储过程 使用CREATE PROCEDURE语句创建存储过程应该考虑以下几个方面： (1)在一个批处理中，CREATE PROCEDURE语句不能与其他SQL语句合并在一起。 (2)数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其他的用户。 (3)存储过程作为数据库对象其命名必须符合标识符的命名规则。 (4)只能在当前数据库中创建属于当前数据库的存储过程。 创建存储过程语句的语法格式如下： CREATE PROC[EDURE] procedure_name [; number ] [{@parameter data_type } [VARYING] [=default] [OUTPUT]] [, ...n ] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_st