SQLServer2005：存储过程状态值的管理

使用存储过程的另外一个常见的功能是为调用者返回一个整型状态值，通过该状态值，调用者可以查看该存储过程是否执行成功或失败的原因。有关存储过程的部分状态值及其含义见表2-14。

表2-14 存储过程的部分状态值及其含义

SQL Server规定状态值0表示存储过程执行成功，-1～-99为执行不成功。另外用户还可以自己定义大于0或者小于-99的整数作为自己的返回状态值，以表示不同的执行结果。

SQL Server为用户提供了RETURN关键字来自定义存储过程的返回状态值，其语法格式如下。

其中的参数说明如下。

●status：整数表达式。在建立存储过程的时候，需要定义任意出错的条件，并把它们与整型的出错代码联系起来。

RETURN用于存储过程时不能返回空值。如果存储过程试图返回空值，系统将产生警告信息并返回0值。

例2-109 用户自定义返回值的实例，如图2-138所示。

本例中学习RETURN语句的表达式书写格式，其中值10表示用户没有提供必需的姓名，值-120表示按指定的姓名没有找到该学生的信息，值0表示存储过程执行正常。

图2-138 用户自定义返回值

若要正确捕获存储过程的返回状态值，应该使用EXECUTE语句，其语法格式如下。

其中的参数说明如下。

●@return_status：用来存储返回状态值的变量，使用之前必须事先声明。

●procedure_name：调用的存储过程的名称，必须符合SQL Server标识符的命名规则，

并且必须有该对象存在。

例2-110 演示捕获返回值的实例，如图2-139所示。

本例中学习用EXECUTE捕获返回值的方法。

图2-139 捕获返回值

读一读

1.存储过程

存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理，是独立存在于表之外的数据库对象。存储过程存储在数据库内，用户通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行它，并允许用户声明变量有条件执行。

在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL语句和流程控制语句的集合。存储过程在运算时生成执行方式，以后再运行时其执行速度很快。

在SQL Server的系列版本中存储过程分为两类：系统存储过程和用户自定义存储过程。系统存储过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。通过系统存储过程，SQL Serv-er中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中，但是仍可以在其他数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能的存储过程。在本任务中所涉及的存储过程主要是指用户自定义存储过程。

存储过程具有以下优点：

●可以在单个存储过程中执行一系列SQL语句。

●存储过程允许标准组件式编程：存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

●存储过程能够实现较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

●存储过程能够减少网络流量：对于同一个针对数据库对象的操作，如果这一操作所涉及的Transaction-SQL语句被组织成一个存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL语句，从而大大增加了网络流量。

●存储过程可被作为一种安全机制来充分利用：系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。

●可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。

存储过程的用途几乎是无限的。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；可以向数据库中插入和修改数据；其返回状态值给调用者指明调用是成功或是失败；存储过程还可以在一个存储过程中调用另一存储过程。目前，在数据库的应用领域，存储过程使用非常广泛。

在创建一个存储过程前，首先要编写及检测一个要在存储过程中执行的SQL语句，然后用这些没有错误的语句作为存储过程的程序体来创建存储过程，否则存储过程在被调用时就会出现错误，修改存储过程时也需要进行这样的检测，以确保存储过程内部没有问题。

2.CREATE PROCEDURE语法格式

其中的参数说明如下。

●procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。

要创建局部临时过程，可以在procedure_name前面加一个#符号，要创建全局临时过程，可以在procedure_name前面加两个#符号。完整的名称（包括#或##）不能超过128个字符。指定过程所有者的名称是可选的。

●number：可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的过程可以命名为orderproc；1、orderproc；2等。DROP PROCEDURE orderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。

●@parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2100个参数。

使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。

●data_type：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。对于可以是cursor数据类型的输出参数，没有最大数目的限制。

●VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化），仅适用于游标参数。

●default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行存储过程。默认值必须是常量或NULL。如果存储过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。

●OUTPUT：表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUT-PUT参数可将信息返回给调用过程。text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。

●n：表示最多可以指定2100个参数的占位符。(www.xing528.com)

●{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}：RECOMPILE表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，可使用RECOMPILE选项。

ENCRYPTION表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION可防止将过程作为SQL Server复制的一部分发布。

在升级过程中，SQL Server利用存储在syscomments中的加密注释来重新创建加密过程。

●FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用于存储过程筛选，且只能在复制过程中执行。本选项不能和WITH RECOMPILE选项一起使用。

●AS：指定过程要执行的操作。

●sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句，但有一些限制。

用户自定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb中创建），并且存储过程最大为128 MB。在单个批处理中，CREATE PROCEDURE语句不能与其他Transact-SQL语句组合使用。

默认情况下，参数可为空。如果传递NULL参数值并且该参数在CREATE或ALTER TABLE语句中使用，而该语句中引用的列又不允许使用NULL，则SQL Server会产生一条错误信息。为了防止向不允许使用NULL的列传递NULL参数值，应向过程中添加编程逻辑或为该列使用默认值（使用CREATE或ALTER TABLE的DEFAULT关键字）。

在创建或更改存储过程时，SQL Server将保存SET QUOTED_IDENTIFIER和SET ANSI_NULLS的设置。执行存储过程时，将使用这些原始设置。因此，所有客户端会话的SET QUOTED_IDENTIFIER和SET ANSI_NULLS设置在执行存储过程时都将被忽略。在存储过程中出现的SET QUOTED_IDENTIFIER和SET ANSI_NULLS语句不影响存储过程的功能。

其他SET选项在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置，应在过程开头添加一条SET语句，以确保设置正确。从存储过程中执行SET语句时，该设置只在存储过程完成之前有效。之后，设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项，而不会影响存储过程的逻辑。

3.EXECUTE语法格式

其中的参数说明如下。

●@return_status：一个可选的整型变量，保存存储过程的返回状态。这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。

在用于唤醒调用标量值用户定义函数时，@return_status变量可以是任何标量数据类型。

●procedure_name：拟调用的存储过程的名称。存储过程名称必须符合标识符规则。无论服务器的代码页或排序方式如何，扩展存储过程的名称总是区分大小写。

用户可以执行在另一数据库中创建的存储过程，只要该用户拥有此存储过程或有在该数据库中执行它的适当的权限。用户可以在另一台运行SQL Server的服务器上执行存储过程，只要该用户有适当的权限使用该服务器（远程访问），并能在数据库中执行该过程。如果指定了服务器名称但没有指定数据库名称，SQL Server会在用户默认的数据库中寻找该过程。

●number：可选的整数，用于将相同名称的过程进行组合，使得它们可以用一条DROP PROCEDURE语句除去。该参数不能用于扩展存储过程。

●@procedure_name_var：局部定义变量名，代表存储过程名称。

●@parameter：存储过程的参数，在CREATE PROCEDURE语句中定义。参数名称前必须加上符号@。在以@parameter_name=value格式使用时，参数名称和常量不一定按照CREATE PROCEDURE语句中定义的顺序出现。但是，如果有一个参数使用@pa-rameter_name=value格式，则其他所有参数都必须使用这种格式。

●value：过程中参数的值。如果参数名称没有指定，参数值必须以CREATE PROCE-

DURE语句中定义的顺序给出。

如果参数值是一个对象名称、字符串或通过数据库名称或所有者名称进行限制，则整个名称必须用单引号括起来。如果参数值是一个关键字，则该关键字必须用双引号括起来。

如果在CREATE PROCEDURE语句中定义了默认值，用户执行该过程时可以不必指定参数。如果该过程使用了带LIKE关键字的参数名称，则默认值必须是常量，并且可以包含%、_、[]及[^]通配符。

默认值也可以为NULL。通常，过程定义会指定当参数值为NULL时应该执行的操作。

●@variable：用来保存参数或者返回参数的变量。

●OUTPUT：指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字

OUTPUT创建。使用游标变量作为参数时使用该关键字。

如果使用OUTPUT参数，目的是在调用批处理或过程的其他语句中使用其返回值，则参数值必须作为变量传递（即@parameter=@variable）。如果一个参数在CREATE PROCEDURE语句中不是定义为OUTPUT参数，则对该参数指定OUTPUT的过程不能执行。不能使用OUTPUT将常量传递给存储过程。在执行存储过程之前，必须声明变量的数据类型并赋值。返回参数需要变量名称，其数据类型可以是text或image以外的任意数据类型。

●DEFAULT：根据过程的定义，提供参数的默认值。当过程需要的参数没有事先定义好的默认值，或缺少参数，或指定了DEFAULT关键字，就会出错。

●n：占位符，表示在它前面的项目可以多次重复执行。例如，EXECUTE语句可以指定一个或者多个@parameter、value或@variable。

●WITH RECOMPILE：强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，则需要使用该选项。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。

4.ALTER PROCEDURE语法格式

其中的参数说明如下。

●procedure_name：要更改的存储过程的名称。存储过程名称必须符合标识符规则。

●；number：现有的可选整数，该整数用来对具有同一名称的存储过程进行分组，以便可以用一条DROP PROCEDURE语句全部除去它们。

●@parameter：存储过程中的参数。

●data_type：参数的数据类型。

●VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化），仅适用于游标参数。

●default：参数的默认值。

●OUTPUT：表明参数是返回参数。

●n：表示最多可指定2100个参数的占位符。

●{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}：RECOMPILE表明SQL Server不会高速缓存该过程的计划，该过程将在运行时重新编译。ENCRYPTION表示SQL Server加密syscomments表中包含ALTER PROCEDURE语句文本的条目。使用ENCRYPTION可防止将过程作为SQL Server复制的一部分发布。

在升级过程中，SQL Server利用存储在syscomments中的加密注释来重新创建加密过程。

●FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用于存储过程筛选，且只能在复制过程中执行。本选项不能和WITH RECOMPILE选项一起使用。

●AS：过程将要执行的操作。

●sql_statement：过程中要包含的任意数目和类型的Transact-SQL语句，但有一些限制。归纳总结

通过这次任务的实践，了解了存储过程的作用和使用存储过程的优点，掌握了复杂存储过程的设计，熟悉了存储过程的管理，为复杂的数据处理奠定了坚实的基础。