第12章 存储过程和触发器.ppt

数据库实用教程 —— SQL Server 2008 本章学习目标： 掌握存储过程的定义方法 掌握执行存储过程的方法 理解触发器的概念 掌握利用触发器实现数据完整性的方法 12.1 存储过程 12.1.1 存储过程概述 存储过程与其他编程语言中的过程类似，比如可以接受输入参数并以输出参数的形式向调用过程或批处理返回多个值；包含一组Transact-SQL语句，用于在数据库中执行操作（包括调用其他过程）；同时，存储过程也可以向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）等。 在 SQL Server 中使用存储过程有如下好处： 存储过程已在服务器注册。 存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。 存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受 SQL Injection（SQL 注入）攻击。 存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 存储过程是命名代码，允许延迟绑定。这提供了一个用于简单代码演变的间接级别。 存储过程可以减少网络通信流量。一个需要数百行 Transact-SQL 代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。 1. 用户定义的存储过程 存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句，然后返回输出参数。在 SQL Server 2008 中，用户定义的Transact-SQL存储过程中包含一组Transact-SQL 语句集合，可以接受和返回用户提供的参数。 2. 扩展存储过程 扩展存储过程是指 Microsoft SQL Server 的实例可以动态加载和运行的 DLL，是由用户使用编程语言（例如C）创建的自己的外部例程，扩展存储过程一般使用_xp前缀。 3. 系统存储过程 SQL Server 中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。 12.1.2 创建存储过程 1. 利用Create Procedure语句创建 Create Procedure语句的语法格式为： Create { Proc | Procedure } procedure_name [ ; number ] [ { @parameter data_type } [ Varying ] [ = default ] [ Out | Output ] [Readonly] ] [ ,...n ] [ With Encryption | Recompile] [ For Replication ] As { sql_statement [;][ ...n ] } [;] 说明： procedure_name：存储过程名。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。可在 procedure_name 前面使用一个数字符号 (#) (#procedure_name) 来创建局部临时过程，使用两个数字符号 (##procedure_name) 来创建全局临时过程。存储过程或全局临时存储过程的完整名称（包括 ##）不能超过 128 个字符。局部临时存储过程的完整名称（包括 #）不能超过 116 个字符。 ; number：是可选整数，用于对同名的过程分组。使用一个 Drop Procedure 语句可将这些分组过程一起删除。 @parameter：过程中的参数。通过将@用作第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。在 Create Procedure 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有 2,100 个参数。如果指定了 For Replication，则无法声明参数。 data_type：参数以的数据类型。所有数据类型都可以用作 Transact-SQL 存储过程的参数。可以使用用户定义表类型来声明表值参数作为 Transact-SQL 存储过程的参数。只能将表值参数指定为输入参数，这些参数必须带有 Readonly 关键字。cursor 数据类型只能用于 Output 参数。如果指定了 cursor 数据类型，则还必须指定 Varying 和 Output 关键字。可以为 cursor 数据类型指定多个输出