存储过程

存储过程
在使用 Microsoft® SQL Server™ 2000 创建应用程序时，Transact-SQL 编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用 Transact-SQL 程序时，可用两种方法存储和执行程序。可以在本地存储程序，并创建向 SQL Server 发送命令并处理结果的应用程序；也可以将程序在 SQL Server 中存储为存储过程，并创建执行存储过程并处理结果的应用程序。

SQL Server 中的存储过程与其它编程语言中的过程类似，原因是存储过程可以：

接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。


包含执行数据库操作（包括调用其它过程）的编程语句。


向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。
可使用 Transact-SQL EXECUTE 语句运行存储过程。存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接用在表达式中。

使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 Transact-SQL 程序的优势有：

允许模块化程序设计。
只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。

允许更快执行。
如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。

减少网络流量。
一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

可作为安全机制使用。
即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。

SQL Server 存储过程是用 Transact-SQL 语句 CREATE PROCEDURE 创建的，并可用 ALTER PROCEDURE 语句进行修改。存储过程定义包含两个主要组成部分：过程名称及其参数的说明，以及过程的主体（其中包含执行过程操作的 Transact-SQL 语句）。

--------------------------------------------------------------
SQL之存储过程
--- 寂寞E族 （技术内容来源于书籍和网络及个人经验，写到这里来是以便大家学习参考，为了避免引起不必要的版权纠纷，请勿转载）

存储过程是预编译 SQL 语句的集合，这些语句存储在一个名称下并作为一个单元来处理。存储过程代替了传统的执行 SQL 语句的方式。一个存储过程中可包含查询、插入、删除、更新等操作的一系列 SQL 语句，当这个存储过程被调用执行时，这些操作也会同时执行。


使用 create procedure 语句创建存储过程

语法如下：
create { proc | procedure } [ schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ varying ] [ = default ] [ [ out [ put ] ]
[ ,…n ] [ with < procedure_option > [ ,…n ]
[ for replication ]
as { < sql_statement > [ ; ] [ …n ] | < method_specifier> }
[ ; ]
< procedure_option > :: =
[ encryption ]
[ recompile ]
[ execute_as_clause ]
< sql_statement > :: =
{ [ begin ] statements [ end ] }
< method_specifier > :: =
external name assembly_name.class_name.method_name

参数说明：
schema_name：存储过程所属架构的名称。
procedure_name：新存储过程的名称。该名称必须遵循有关标识符的规则，并且在架构中必须唯一。
; number：胜于对同名过程进行分组的可选整数。使用一个 drop procedure 语句可将这些分组过程一起删除。例如，称为 orders 的应用程序可能使用名为 orderproc;1、orderproc;2、则数字不应包含在标识符中；只应在 procedure_name 前后使用适当的分隔符。
@parameter ：存储过程中的参数。在 create procedure 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有 2100 个参数。
varying：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适应于 cursor 参数。
default：参数的默认值。如果定义了 default 值，则无需指定引参数的值即可执行过程。默认值必须是常量或 null　．如果过程使用带 like 关键字的参数，则可包含下列通配符%、_、[] 和 [^]。
out | put：指示参数是输出参数。此选项的值可以返回给调用 execute 的语句。使用 output 参数将值返回给过程的调用方。除非是 clr 存储过程，否则 text 、ntext 和 image 不能用途 output 参数。使用 output 关键字的输出参数可以为游标占位符， clr 存储过程除外。
encryption：指示 SQL Server 将 create procedure 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过 DAC 端口访问系统表的特权用户或直接访问数据库文件 的特权用户可使用此文本。此外，向服务器进程附加调试器的用户可以运行时从内存中检索已解密的过程。
execute as：指定在其中执行存储过程的安全上下文。
for replication：指定不能在订阅服务器上执行为复制创建的存储过程。使用 for replication 选项创建的存储过程可用作存储过程筛选器，能在复制过程中执行。如果指定了 for replication ，则无法声明参数。对于 clr 存储过程，不能指定 for replication 。对于使用 for replication 创建的过程，忽略 recomplie 选项。
sql_statement：要包含在过程中的一个或多个 SQL 语句。
external name，assembly_name.class_name.method_name：指定 .net framework 程序集的方法，以便 clr 存储过程引用。class_name 必须为有效的 SQl Server 标识符，并且该类必须存在程序集中。如果类包含一个使用句点（.）分隔命名空间各部分的限定命名空间的名称，则必须使用方括号（[]）
或引号（""）将类名称分隔开。指定的方法必须为该类的静态方法。
注：clr 存储过程是通过引用 Sql Server 程序集而伊娃的存储过程。 Clr 存储过程的被耍在程序集中定义。

简单举例：
create procedure pro_student -- 查询 学生成绩大于60分的学生成绩详细信息 （该表必须存在）
as
select *
from 成绩信息表 where 学生成绩 > 60
go


使用 return 语句从存储过程中返回值

Return 语句无条件终止查询、批处理以及存储过程。不执行存储过程或者批处理中 return 语句后面的语句。在存储过程中使用 return 时，return 语句可以指定返回给调用存储过程、应用程序以及批处理的整数值。
return 语句的语法规则如下：
return [ integer_expression ]
其中参数 integer_expression 为返回的整形值。在存储过程上，可以给调用应用程序或者调用存储过程返回整数值。如果没有指定 return 语句的返回值，则返回 0 。 （return 不能返回 空值）
简单举例：
create procedure pro_person @personID int,
@quantity int output
as
declare @errersave int
set @errersave=0
-- 执行 Select 语句
select * from employees
where 人员编号=@personID
-- 保存任何非0的@@error值
if ( @@error<>0 )
set @errersave=@@error
set @errersave=@@error
-- 如果所有的 Select 语句执行正确，则返回 0 ,否则就返回当前错误代码
return @errersave
go


创建带参数的存储过程

简单举例：
create procedure proc
@studentid int,
as
select * from 学生信息表
where 学生编号=@studentid
go

在存储过程中使用事务

简单举例：
use db_Sql -- 使用 db_Sql 数据库
go

-- 判断 pro_pro 存储过程是否存在，如果存在将它删除
if exists(select name from sysobjects
where name=' pro_pro ' and type=' p ' )
drop proc pro_pro -- 删除存储过程
go

create procedure pro_pro
as
declare @truc int
select @truc=@@trancount
if @truc=0
begin tran p1
else
save tran p1
if(@truc=2)
begin
rollback tran p1 -- 事务回滚
return 25
end
if (@truc=0)
commit tran p1 -- 提交事务 p1
return 0

（*………… 未完待续，大纲如下 …………*）


创建带返回参数的存储过程

简单举例：


创建并添加扩展存储过程

简单举例：


创建加密存储过程

简单举例：


****************************** 管理存储过程 *******************************

执行存储过程

存储过程的重新编译

执行具有回传参数的存储过程

查看存储过程代码

修改存储过程

重新命名存储过程

删除存储过程

使用存储过程调用 com 组件 （sp_OACreate）

重命名数据库中列的名称

监控存储过程

自动执行存储过程


****************************** 函数 *******************************

创建标量函数

创建内嵌表值函数

删除函数
-------------------------------------------------------------
执行存储过程（数据库引擎）

若要执行存储过程，可以使用 Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程。

执行系统存储过程
系统存储过程以字符 sp_ 开头。它们物理上存储在资源数据库中，但逻辑上出现在 SQL Server 实例的每个系统定义数据库和用户定义数据库的 sys 架构中。可以从任何数据库执行系统存储过程，而不必完全限定存储过程名称。非架构限定名称可以是由一个部分组成的名称（如 sp_someproc），也可以是由三个部分组成的名称（如 somedb..sp_someproc），第二部分是架构名称，这里并未指定。

建议使用 sys 架构名称对所有系统存储名称进行限定，以防止名称冲突。以下示例说明执行系统存储过程的推荐方法。

复制代码
EXEC sys.sp_who;
下列示例说明执行系统存储过程的向后兼容方法。

注意：
SQL Server 的未来版本中将删除下列执行系统存储过程的方法。请避免在新的开发工作中使用这些方法，并计划修改当前使用它们的应用程序。


复制代码
EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;
数据库排序规则匹配
SQL Server 2008 与系统过程名称匹配时调用数据库排序规则。因此，在应用程序中应始终使用系统过程名称的正确大小写形式。例如，如果在具有区分大小写的排序规则的数据库上下文中执行，以下代码将失败：

复制代码
exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help
使用 sys.system_objects 和 sys.system_parameters 目录视图可以显示确切的系统存储过程名称。

执行系统扩展存储过程
系统扩展存储过程以字符 xp_ 开头。它们物理上存储在资源数据库中，但逻辑上出现在 SQL Server 实例的每个系统定义数据库和用户定义数据库的 sys 架构中。以下示例说明执行系统扩展存储过程的推荐方法。

复制代码
EXEC sys.xp_subdirs 'c:\';
执行用户定义存储过程
执行用户定义存储过程（不管是在批处理中还是在模块内，例如在用户定义存储过程或函数中）时，极力建议至少用架构名称限定存储过程名称。

以下示例说明执行用户定义存储过程的推荐方法。

复制代码
USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;
-或-

复制代码
EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
GO
如果指定了非限定用户定义存储过程，则数据库引擎 按以下顺序搜索此过程：

当前数据库的 sys 架构。

调用方的默认架构（如果在批处理或动态 SQL 中执行）。或者，如果非限定的过程名称出现在另一个过程定义的主体中，则接着搜索包含这一过程的架构。有关默认架构的详细信息，请参阅用户架构分离。

当前数据库中的 dbo 架构。

重要提示：
如果用户创建的存储过程与系统存储过程同名，在使用非架构限定的名称引用的情况下，将永远不会执行用户创建的存储过程。有关详细信息，请参阅创建存储过程（数据库引擎）。



指定参数
如果将存储过程编写为可以接受参数值，那么可以提供参数值。

提供的值必须为常量或变量，不能将函数名称指定为参数值。变量可以是用户定义变量或系统变量，如 @@spid。

下列示例说明如何将参数值传递给存储过程 uspGetWhereUsedProductID。此过程需要两个输入参数值：一个产品 ID 和一个日期。下列示例说明了如何将参数作为常量和变量进行传递，以及如何使用变量传递函数值。

复制代码
USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO
如果希望以不同的顺序（而非参数在存储过程中的定义顺序）来指定参数，则需为参数命名。有关详细信息，请参阅指定参数名称。

若要指定参数必须将一个值返回到调用程序，请使用 OUTPUT 关键字。有关详细信息，请参阅指定参数的方向。

指定参数顺序
如果以 @parameter = value 格式提供多个参数，则可以按任何顺序提供参数。还可以省略那些已提供默认值的参数。如果以 @parameter = value 格式提供一个参数，则必须按此格式提供所有的后续参数。如果不以 @parameter = value 格式提供参数，则必须按照 CREATE PROCEDURE 语句中给出的顺序提供参数。

执行存储过程时，服务器将拒绝所有未包含在过程创建期间的参数列表中的参数。如果参数名称不匹配，通过引用传递（显式传递参数名称）的任何参数都不会被接受。

使用参数的默认值
虽然可以省略已提供默认值的参数，但只能截断参数列表。例如，如果一个存储过程有五个参数，那么您可以省略第四个和第五个参数，但不能跳过第四个参数而仍然包含第五个参数，除非以 @parameter = value 格式提供参数。

如果在存储过程中定义了参数的默认值，那么下列情况下将使用默认值：

执行存储过程时未指定参数值。

将 DEFAULT 关键字指定为参数值。