SQL server 2005案例教程 第6章 存储过程与触发器.pdf

第6 章 存储过程与触发器 6.1 【案例19】存储过程 相关知识 1．存储过程的类型 （1）存储过程特点 存储过程是存储在SQL Server 2005 服务器上、一种有效的封装重复性工作的方法，并具有支持用户声明的 变量、条件执行和其他强大的编程功能。与其他编程语言中的存储过程类似，SQL Server 2005 中的存储过程具 有如下特点。  接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。  包含用于在数据库中执行操作的编程语句。  向调用过程或批处理返回状态值，以指明成功或失败及失败的原因。 （2 ）用户自定义存储过程 用户自定义存储过程是由用户创建并能完成某一种特定功能的存储过程。SQL Server 2005 可以使用的两种 自定义存储过程的类型为Transact-SQL 和CLR ，具体说明如表6-1-1 所示。 （3）扩展存储过程 扩展存储过程是以在SQL Server 2005 环境外执行的动态链接库（Dynamic-Link Libraries ，DLL ）来实现。 一般以xp_为前缀标识。 （4 ）系统存储过程 在安装SQL Server 2005 时，系统创建了很多系统存储过程，存储在master 和msdb 数据库中，并以sp_为 前缀，系统存储过程主要是从系统表中获取信息，为系统管理员管理SQL Server 2005 提供支持。通过系统存储 过程，SQL Server 2005 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以顺利有效地 完成。 在SQL Server 2005 中，许多管理活动和信息活动都可以使用系统存储过程来执行，系统存储过程的分类如 表6-1-2 所示。 （5）临时存储过程 存储在tempdb 数据库中，以#和##为前缀的过程，#表示本地临时存储过程，##表示全局临时存储过程。 （6）远程存储过程 是在远程服务器的数据库中创建和存储过程，可被各种服务器访问，向具有相应许可权限的用户提供服务。 2 ．创建存储过程的规则 在设计和创建存储过程时，应该满足一定的约束和规则，只有满足了这些约束和规则才能创建有效的存储过 程。设计存储过程应遵守以下规则。 ①所有数据库对象（除存储过程）均可在存储过程中创建，只要该对象被创建就可被引用。 ②可以在存储过程内引用临时表。 ③如果在存储过程内创建了本地临时表，则该临时表仅为该存储过程存在，退出该存储过程后，临时表将 消失。 ④如果执行的存储过程中调用另一个存储过程，则被调用的存储过程可以访问由一个存储过程创建的所有 对象，包括临时表在内。 ⑤远程存储过程不参与事务处理，如果执行对远程SQL Server 2005 实例进行更改的远程存储过程，不能 回滚这些更改。 ⑥存储过程中的参数的最大数目为2100 。 ⑦存储过程中的局部变量的最大数目仅受可用内存的权限。 ⑧根据可用内存的不同，存储过程最大为128MB。 ⑨使用CREATE PROCEDURE 定义存储过程，可以包括任意数量和类型的SQL 语句，但不能在存储过程中使用 以下语句，如表6-1-3 所示。 3 ．使用Transact-SQL 语句创建存储过程 在SQL Server 2005 系统中，可以使用Transact-SQL 语句中的CREATE PROCEDURE 创建存储过程。具体操作 步骤如下：首先编写并测试一个要在存储过程内执行的SQL 查询语句。然后使用CREATE PROCEDURE 语句创建， 可以使用ALTER PROCEDURE 语句修改。 存储过程的定义包括两个主要内容：过程名和参数的说明以及过程体，即包含执行存储过程操作的 Transact-SQL 语句在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过 程或批处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。 （1）语法 使用CREATE PROCEDURE 语句创建存储过程的语法如下所示。 CREATE [PROCEDURE] 存储过程名[; number] [{@参数1 数据类型}[VARYING][= 默认值][OUTPUT], … {@参数n 数据