第7章1 存储过程_new

当创建存储过程时，需要确定存储过程的3个组成部 当创建存储过程时 ，需要确定存储过程的 个组成部 分： 所有的输入参数以及传递给调用者的输出参数 输入参数以及传递给调用者的输出参数； ①所有的输入参数以及传递给调用者的输出参数； 调用其他 被执行的针对数据库的操作语句 包括调用 操作语句， ② 被执行的针对数据库的 操作语句 ， 包括 调用 其他 存储过程的语句； 存储过程的语句； 返回给调用者的状态值 状态值， ③ 返回给调用者的 状态值 ， 以指明调用是成功还是 失败。 失败。
（3）存储过程能够减少网络流量 ）存储过程能够减少网络流量 当在客户计算机上调用存储过程时， 当在客户计算机上调用存储过程时 ， 网络中传送的 只是该调用语句，否则将是多条SQL语句，从而增 语句， 只是该调用语句，否则将是多条 语句 大了网络流量，降低网络负载。 大了网络流量，降低网络负载。 安全机制来充分利用 （4）存储过程可被作为一种安全机制来充分利用 ）存储过程可被作为一种安全 系统管理员通过对执行 某一存储过程的权限 执行某一存储过程的 权限进行限 系统管理员通过对 执行 某一存储过程的 权限 进行限 从而实现对相应的数据访问全限的限制， 制，从而实现对相应的数据访问全限的限制，避免 非授权用户对数据的访问，保证数据的安全。 非授权用户对数据的访问，保证数据的安全。
使用CREATE PROCEDURE创建存储过程的语法形式 使用 创建存储过程的语法形式 如下： ） 如下： （P143） CREATE PROC[EDURE] procedure_name [ ;number] [{ @parameter data_type} [VARYING] [=default] [OUTPUT] ] [,...n] [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement [ .. 使用Transact-SQL语句创建存储过程前，应该考虑下 语句创建存储过程前， 使用 语句创建存储过程前 列几个事项： 列几个事项： 不能将 ①不能将 CREATE PROCEDURE 语句与其他 SQL 语句组合到一个批处理 批处理中 语句组合到一个批处理中。 创建存储过程的权限默认属于数据库的所有者 存储过程的权限默认属于数据库的所有者， ② 创建 存储过程的 权限 默认 属于 数据库的所有者 ， 该 所有者可将此权限授予其他用户。 所有者可将此权限授予其他用户。 存储过程是数据库对象， 名称必须遵守标识符规 ③ 存储过程是数据库对象 ， 其 名称 必须遵守标识符规 则。 只能在当前数据库中创建存储过程。 当前数据库中创建存储过程 ④只能在当前数据库中创建存储过程。
利用SQL Server创建一个应用程序时，运用 创建一个应用程序时， 利用 创建一个应用程序时 Transact-SQL来进行编程，有两种方法： 来进行编程， 来进行编程 有两种方法： 一种是，在本地存储 程序， 一种是，在本地存储Transact-SQL程序，并创建应 程序 用程序向SQL Server发送命令来对结果进行处理。 发送命令来对结果进行处理。 用程序向 发送命令来对结果进行处理 另一种是，可以把部分 另一种是，可以把部分Transact-SQL编写的程序作 编写的程序作 存储过程存储在 存储在SQL Server中，并创建应用程序 为存储过程存储在 中 调用存储过程 对数据结果进行处理。 存储过程， 来调用存储过程，对数据结果进行处理。
●RECOMPILE 不保存该存储过程的执行计划 表明 SQL Server 不保存该存储过程的执行计划 。该 存储过程每执行一次都要重新编译 重新编译。 存储过程每执行一次都要重新编译。 ●ENCRYPTION 加密了 表示 SQL Server 加密了 syscomments 表，该表的 text字段是包含 CREATE PROCEDURE 语句的存 字段是包含 储过程的文本(定义 使用该关键字无法 定义)， 无法通过查看 储过程的文本 定义 ， 使用该关键字无法 通过查看 syscomments表来查看存储过程内容。 表来查看存储过程内容 表来查看存储过程内容。
第7章1 存储过程 (P140) 章
7.1 存储过程概述 7.2 存储过程的基本操作 习题
7.1 存储过程概述
存储过程（ 存储过程（Store Procedure）是一组为了完成 ） 特定功能的SQL语句集，经编译后存储在数据库中。 语句集， 编译后存储在数据库中 在数据库中。 特定功能的 语句集 用户通过指定存储过程的名字并给出参数（ 用户通过指定存储过程的名字并给出参数（如果该 存储过程带参数）来执行它。 存储过程带参数）来执行它。 SQL中存储过程分为两类 系统存储过程和 中存储过程分为两类： 在SQL中存储过程分为两类：系统存储过程和 用户自定义存储过程 自定义存储过程。 用户自定义存储过程。 系统存储过程就是系统创建的存储过程 就是系统创建的存储过程。 系统存储过程就是系统创建的存储过程。目的 在于能够方便地从系统表中查询信息或完成系统管 在于能够方便地从系统表中查询信息或完成系统管 理任务。 理任务。 系统过程以“ 为前缀， 系统过程以“sp_”为前缀，主要存储在 为前缀 主要存储在master 数据库中，为数据库管理员所有。 数据库中，为数据库管理员所有。
● @parameter 是过程中的参数。 是过程中的参数。在 CREATE PROCEDURE 语句 可以声明一个或多个参数。 中，可以声明一个或多个参数。当调用该存储过程 时，用户必须给出所有的参数值 ，除非定义了参 数的默认值。一个存储过程至多有1024个参数。 个参数。 数的默认值。一个存储过程至多有 个参数 ●data_type 用于指定参数的数据类型。在存储过程中， 用于指定参数的数据类型。在存储过程中，所有的 数据类型包括text和image都可以被用作参数。但 都可以被用作参数。 数据类型包括 和 都可以被用作参数 游标cursor数据类型只能被用作 数据类型只能被用作OUTPUT参数。 参数。 是，游标 数据类型只能被用作 参数
尽管这些系统存储过程被放在master数据库中， 数据库中， 尽管这些系统存储过程被放在 数据库中 在其他数据库对其进行调用 但是仍可以在其他数据库对其进行调用， 但是仍可以在其他数据库对其进行调用，在调用时 不必在存储过程名前加上数据库名 在存储过程名前加上数据库名。 不必在存储过程名前加上数据库名。 而且在创建一个新的数据库时， 而且在创建一个新的数据库时 ， 一些系统存储 过程会在新的数据库中自动创建。 过程会在新的数据库中自动创建。 用户自定义存储过程是由用户创建并能完成某 用户自定义存储过程是由用户创建并能完成某 一特定功能（如查询用户所需数据信息） 一特定功能（如查询用户所需数据信息）的存储过 程。 在本章中所涉及到的存储过程， 在本章中所涉及到的存储过程，主要是指用户 自定义存储过程。 自定义存储过程。
新建SQL Server 组 图7.2 创建存储过程向导对话框 图7.1 新建
在图7.2的创建存储过程向导的帮助下，可以完成： 在图 的创建存储过程向导的帮助下，可以完成： 的创建存储过程向导的帮助下 选择存储过程所在的数据库； 选择存储过程所在的数据库； 生成插入、删除或更新的存储过程； 生成插入、删除或更新的存储过程； 编辑创建存储过程属性的Transact-SQL语句。 语句。 编辑创建存储过程属性的 语句
●VARYING 用于指定作为输出OUTPUT参数支持的结果集， 参数支持的结果集， 用于指定作为输出 参数支持的结果集 仅用于游标参数。 仅用于游标参数。 ●Defaul 用于指定参数的默认值 如果定义了默认值， 默认值。 用于指定参数的 默认值 。 如果定义了默认值 ， 那 则该存储过程仍能被调用。 么即使不给出参数值 ，则该存储过程仍能被调用。 默认值必须是常数，或者是空值。 默认值必须是常数，或者是空值。 ●OUTPUT 表明该参数是一个返回参数 返回参数。 表明该参数是一个返回参数。 用OUTPUT参数可 参数可 以 向 调 用 者 返 回 信 息 。 Text 类 型 参 数 不 能 用 作 OUTPUT参数。 参数。 参数
7.2.1 使用向导创建存储过程
在企业管理器中，选择工具菜单中的向导选项， 在企业管理器中，选择工具菜单中的向导选项，选 工具菜单中的向导选项 所示， 创建存储过程向导”如图7.1所示 择“创建存储过程向导”如图 所示，则出现欢 迎使用创建存储过程向导对话框，如图7.2所示 所示。 迎使用创建存储过程向导对话框，如图 所示。
用CREATE PROCEDURE创建存储过程的语法参数 创建存储过程的语法参数 的意义如下： 的意义如下： ●procedure_name 用于指定要创建的存储过程的名称,它后面跟一个可 用于指定要创建的存储过程的名称 它后面跟一个可 选项number， 它是一个整数 ， 它用来区别一组同 选项 ， 它是一个整数， 名的存储过程。存储过程的命名必须符合命名规则， 名的存储过程。存储过程的命名必须符合命名规则， 在一个数据库中或对其所有者而言， 在一个数据库中或对其所有者而言，存储过程的名 字必须惟一 。
7.2 存储过程的基本操作
在SQL Server 2000中，创建一个存储过程有三种 中 方法： 方法： 一是使用创建存储过程向导 向导， 一是使用创建存储过程向导， 二是利用Transact-SQL命令 命令Create Procedure, 二是利用 命令 三是使用企业管理器。 三是使用企业管理器。 在一个存储过程中，可以使用任何 语句， 在一个存储过程中，可以使用任何SQL语句，但 存储过程中 语句 是不包括下面的语句： 是不包括下面的语句： Create Default, Create Procedure, Create Rule, Create Trigger, Create View。 。
创建存储过程时需要注意的问题还有以下几点： 创建存储过程时需要注意的问题还有以下几点： 注意的问题还有以下几点 （1）存储过程所引用的对象 ） 允许在存储过程创建时 引用一个 SQL Server允许 在存储过程创建时 引用 一个 允许 在存储过程创建时引用 不存在的对象， 创建的时候，系统只检查 只检查创建存 不存在的对象，在创建的时候，系统只检查创建存 储过程的语法 语法。 储过程的语法。 但是，如果在执行的时候 执行的时候， 但是，如果在执行的时候，存储过程引用了 一个不存在的对象，这次执行操作将会失败。 一个不存在的对象，这次执行操作将会失败。