SQLserver2005案例教程第六章存储过程与触发器.pptVIP

l l l 第 6 章 存储过程与触发器 6.1 【案例 19】存储过程 相关知识 1．存储过程的类型 （1）存储过程特点 存储过程是存储在 SQL Server 2005 服务器上、一种有效的封装重复性工作的方法，并具有支持用户声明的 变量、条件执行和其他强大的编程功能。与其他编程语言中的存储过程类似，SQL Server 2005 中的存储过程具 有如下特点。 接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 包含用于在数据库中执行操作的编程语句。 向调用过程或批处理返回状态值，以指明成功或失败及失败的原因。 （2）用户自定义存储过程 用户自定义存储过程是由用户创建并能完成某一种特定功能的存储过程。SQL Server 2005 可以使用的两种 自定义存储过程的类型为 Transact-SQL 和 CLR，具体说明如表 6-1-1 所示。 在线教务辅导网： 教材其余课件及动画素材请查阅在线教务辅导网 QQ:349134187 或者直接输入下面地址： （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 数据类型}[= 默认值][OUTPUT] ] AS SQL 语句 (…n) l l l l l l （2）主要参数说明 过程名称在架构中必须唯一，可在存储过程名前面使用一个数字符合（#）来创建局部临时过