SQL Server 的存储过程[转]

kaywang

　　
　　
　　本文从如下几个方面讲述一下存储过程：
　　●  存储过程的概念
　　●  存储过程的优点
　　●  存储过程的分类
　　●  存储过程的接口
　　●  存储过程的解析、编译过程
　　●  存储过程的安全性
　　●  如何查看存储过程
　　●  加密、解密存储过程
　　-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
　　
　　●  存储过程的概念
　　存储过程（Stored  Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。
　　用户可以通过指定存储过程的名字并给出参数（带参存储过程）来执行它。
　　
　　●  存储过程的优点
　　
　　概括起来大体如下：
　　1： 速度快、性能好。
　　         存储过程是一组已经编译过的SQL 脚本，它已经进过解析、编译、优化器优化。调用存储过程可以重复使用它的缓存执行计划。这样节省了解析、分析等所需消耗的CPU 资源和时间。
　　
　　2： 存储过程存储在数据库服务器上，这样减少了网络通信以及网络阻塞。
　　        调用存储过程只需存储过程名字和参数，从而避免了把长串的SQL 语句传送到SQL 服务器，大大减轻了网络负担。
　　
　　3： 实现了业务逻辑的封装。
　　        可以把相当一部分业务逻辑封装到存储过程中，当业务逻辑变更的时候，只要接口不变，则只需修改存储过程内部逻辑就OK 了，避免了业务逻辑放在代码层而导致的业务逻辑变动时，改动大的痛苦。
　　
　　4： 安全性
　　        参数化的存储过程可以减少SQL Injiection 攻击，而且可以通过检验参数、授予对象执行权限等手段来提高安全性。
　　
　　●  存储过程的分类
　　
　　存储过程按类型分为：
　　系统存储过程：一般以SP 为前缀；
　　扩展存储过程：一般以XP 为前缀；
　　用户存储过程(自定义存储过程)：它包括CLR 存储过程，习惯以USP 为前缀；
　　临时存储过程：其中又分为全局临时存储过程和局部临时存储过程。
　　
　　
　　●  存储过程的接口
　　
　　存储过程的参数包括：输入参数、输出参数。
　　
　　第一个存储过程：
　　有两个输入参数 @EmployeeID、@EmployeeName。
　　其中@EmployeeID 的默认值是 -1，@EmployeeName 的默认值是 NULL。
　　代码如下：

use pubs

go

if object_id(N'dbo.usp_getemployebyid') is not null

begin

drop proc dbo.USP_GetEmployeById;

end

go

--===========================================================

--    Function        :    按员工号获取员工信息

--    Author          :    Kerry

--    Create Date     :    2010-08-10

--    Description    :

----------------------------------------------

--    2010-08-13      :    修改.......增加.....

--    2010-08-14      :    修改.......增加.....

--===========================================================

create procedure dbo.USP_GetEmployeById

@EmployeeID      int  = -1,

@EmployeeName    nvarchar(30) = null

as

set nocount on;

begin

if(@EmployeeID = -1 and @EmployeeName is null)

begin

print '请输入员工ID号或是用户名字';

end

if @EmployeeID = -1

select * from dbo.Employee where fName = @EmployeeName;

else

select * from dbo.Employee where emp_id = @EmployeeID;

end

go

　　
　　第二个存储过程：
　　代码如下：

use pubs

go

if object_id(N'dbo.USP_AddEmploye') is not null

begin

drop proc dbo.USP_AddEmploye;

end

go

--===========================================================

--    Function      :    新增一条员工记录

--    Author        :    GangXu

--    Create Date   :    2010-08-10

--    Description   :

----------------------------------------------

--    2010-08-13    :    修改.......增加.....

--    2010-08-15    :    修改了输入参数以适应测试环境

--    2010-08-15    :    修改了insert 语句以适应测试环境

--===========================================================

create procedure dbo.USP_AddEmploye

@fName           nvarchar(20),

@lName           nvarchar(20),

@jobLevel        int,

@Success         nvarchar(4) output

as

set nocount on;

begin try

if ((@fName is null OR len(@fName) = 0) or

(@lName is null OR len(@lName) = 0))

begin

print (N'员工的姓氏和名字都不能为空!');

set  @Success = N'失败';

return;

end

insert into Employee(emp_id,fname,lname,job_id,job_lvl,pub_id,hire_date)

values(right(NEWID(),9),@fName, @lName, 9, @jobLevel,'0736',getdate())

if @@error = 0

set @Success = N'成功';

end try

begin catch

set  @Success = N'失败';

select

ERROR_NUMBER()          AS ErrorNumber

,ERROR_SEVERITY()        AS ErrorSevertiy

,ERROR_STATE()           AS ErrorState

,ERROR_LINE()            AS ErrorLine

,ERROR_PROCEDURE()       AS ErrorProcedure

,ERROR_MESSAGE()         AS ErrorMessage

end catch

go

　　注意：本人(xugang) 因为数据库及数据表的不同，修改了原作者的SQL 语句(insert 插入语句) 以适应测试环境。
　　本人(xugang) 为了适应测试环境，将微软的示例数据库pubs 中的employee 表的CHECK 约束CK_emp_id 删除，
　　并使用SQL 中的newID( ) 函数随机产生 emp_id 的值，即：right(NEWID(),9)
　　
　　调用或执行存储过程
　　以第二个存储过程为示例，进行测试：
　　测试一：

declare @Result nvarchar(4);

set @Result = '';

exec dbo.USP_AddEmploye

@fName = null,

@lName = 'gang',

@jobLevel = 12,

@Success = @Result output -- 输出参数

　　运行结果：员工的姓氏和名字都不能为空!
　　
　　测试二：

declare @Result nvarchar(4);

set @Result = '';

exec dbo.USP_AddEmploye

@fName = 'xu',

@lName = 'gang',

@jobLevel = 100,

@Success = @Result output -- 输出参数

select @Result;

go

　　运行结果：成功
　　
　　一般在执行存储过程时，最好加上架构名称。例如：dbo.USP_AddEmploye  
　　        这样可以减少不必要的系统开销，提高性能。 因为如果在存储过程名称前面没有加上架构名称，SQL SERVER 首先会从当前数据库sys schema 开始查找，如果没有找到，则会去其它schema 查找，最后在dbo 架构里面查找。
　　
　　●  存储过程的解析、编译过程
　　
　　创建存储过程时，首先会分析检查语法的正确性：
　　如果在过程定义中遇到语法错误，将会返回错误，创建存储过程失败；
　　如果语法正确，存储过程的文本将会存储在SYS.SQL_MODULES 目录视图中；
　　
　　从 SYS.SQL_MODULES 目录视图中获得存储过程的代码，SQL 语句如下：

select * from sys.sql_modules

where object_id = object_id(N'dbo.USP_GetEmployeById')

　　
　　测试存储过程的解析
　　首先，创建一个存储过程USP_GetTableTest，它里面引用了表Test ，但表Test 根本不存在。

create procedure USP_GetTableTest

as

begin

select * from test;

end

　　创建该存储过程时，并不会报错。
　　但是执行存储过程时，会报出如下所示的错误：

　　这是因为在存储过程创建时，它先做语法检查，如果通过了语法检查，它会尝试解析它包含的对象名，如果存在也会解析该对象引用的对象是否存在。如果引用的对象名不存在，解析会在存储过程首次执行时触发。即在首次执行存储过程时，查询处理器从 sys.sql_modules 目录视图中读取该存储过程的文本，并检查该过程所使用的对象名称是否存在。这一过程称为延迟名称解析。因为存储过程引用的表对象不需要在创建该存储过程时就存在，而只需在执行该存储过程时存在。
　　注意：
　　只有当引用的表对象不存在时才能使用延迟名称解析。所有其他对象在创建所存储的过程时必须存在。
　　例如，引用所存储的过程中的一个现有表时，不能列出该表不存在的列。
　　示例如下：
　　我们先创建表TEST(col1) ，然后在存储过程 USP_GetTableTest 中查询它不存在的列col2 进行测试。
　　代码如下：

-- 创建数据表

create table test (col1 int);

go

if object_id(N'USP_GetTableTest') is not null

begin

drop proc USP_GetTableTest;

end

go

-- 创建存储过程

create procedure USP_GetTableTest

as

begin

select col2 from TEST;

end

　　
　　创建存储过程时，它会报如下错误提示：
　　消息 207，级别 16，状态 1，过程 USP_GetTableTest，第 6 行
列名 'col2' 无效。
　　
　　在解析阶段，Microsoft SQL Server 2005 还执行其他验证活动（例如：检查字段的数据类型与变量的兼容性）。如果执行存储过程时，存储过程所引用的对象丢失，则存储过程在到达引用丢失对象的SQL 语句时将停止执行，并返回错误信息。
　　所以，在解析阶段发现相关错误时，将在错误的SQL 语句段停止执行，并返回错误信息。
　　
　　如果执行过程时，成功通过解析阶段，则Microsoft SQL Server 查询优化器将分析存储过程中的Transact-SQL 语句，并创建一个执行计划。
　　执行计划将描述执行存储过程的最快方法，所依据的信息包括：
　　○  表中的数据量；
　　○  表中索引的存在及特征，以及数据在索引列中的分布；
　　○  WHERE 子句条件所使用的比较运算符和比较值；
　　○  是否存在联接，以及 UNION、GROUP BY 和 ORDER BY 关键字。
　　------------------------------------------------------------------------------------------------------------------------------------------------------------------------
　　—  查询优化器在分析完存储过程中的这些因素后，将执行计划置于内存中。
　　—  分析存储过程和创建执行计划的过程称为编译。
　　—  内存中优化的执行计划将用来执行该查询。
　　—  执行计划将驻留在内存中，直到重新启动 SQL Server 或其他对象需要存储空间时为止。
　　—  如果随后又执行存储过程，而原有的执行计划仍留在内存中，则 SQL Server 将重用原有的执行计划。
　　—  如果原有的执行计划已经不在内存中，这时候再执行存储过程，则会创建新的执行计划。
　　
　　●  存储过程的安全性
　　
　　使用 sa 账号登录数据库，执行下面这两条SQL 语句：

-- 拒绝用户'Kerry'对'Employee'数据库进行插入操作

deny insert on dbo.Employee to Kerry;

-- 允许用户'Kerry'执行'USP_AddEmploye'存储过程

grant execute on dbo.USP_AddEmploye to Kerry;

　　
　　然后我们用 Kerry 这个账号登陆，往表 Employee 离插入一条数据：

　　
　　而如果调用存储过程，往表 Employee 中插入一条数据，则如图所示：

　　这种安全模式能让你很灵活的控制用户允许进行的活动。
　　
　　●  如何查看存储过程
　　查看存储过程的方式很多，你可以使用视图化的MSMS( Microsoft SQL Server Management Studio ) 工具中的选择“修改存储过程”或是“编写存储过程为”来查看存储过程。
　　也可以通过SQL 语句查询视图，或是系统存储过程来查看你想要看的存储过程。
　　代码如下：

--查看存储过程的基本信息，例如: 参数等。

sp_help 'dbo.USP_GetEmployeById';

--查看具体的存储过程

sp_helptext 'dbo.USP_GetEmployeById';

--查看具体的存储过程

select * from sys.sql_modules

where object_id = object_id(N'dbo.USP_GetEmployeById');

　　
　　●  加密、解密存储过程
　　
　　为存储过程加密一般是为了安全需要，或是保护源代码需要。
　　加密存储过程一般通过关键字ENCRYPTION 来实现。这样，SQL Server 将 create procedure 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。
　　但是，可通过DAC 端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。
　　此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。
　　
　　将前面的第一个存储过程进行加密，代码如下：

use pubs

go

if object_id(N'dbo.usp_getemployebyid') is not null

begin

drop proc dbo.USP_GetEmployeById;

end

go

--===========================================================

--    Function        :    按员工号获取员工信息

--    Author          :    Kerry

--    Create Date     :    2010-08-10

--    Description    :

----------------------------------------------

--    2010-08-13      :    修改.......增加.....

--    2010-08-14      :    修改.......增加.....

--===========================================================

create procedure dbo.USP_GetEmployeById

@EmployeeID      int  = -1,

@EmployeeName    nvarchar(30) = null

with encryption --通过关键字 ENCRYPTION 实现加密存储过程

as

set nocount on;

begin

if(@EmployeeID = -1 and @EmployeeName is null)

begin

print '请输入员工ID号或是用户名字';

end

if @EmployeeID = -1

select * from dbo.Employee where fName = @EmployeeName;

else

select * from dbo.Employee where emp_id = @EmployeeID;

end

go

　　附加说明：只是在第一个存储过程的SQL 语句基础上，多加了“with  encryption”这条命令语句。
　　
　　执行以上SQL 语句后，你可以看到加密过后的存储过程，它的图标多了个小锁，而且你再也不能通过前面介绍过的使用视图化查看存储过程的方式来查看存储过程了。
　　使用MSMS( Microsoft SQL Server Management Studio ) 工具查看则会弹出下面的错误提示：

　　使用 sp_help 'dbo.USP_GetEmployeById'  还是能查看到存储过程的基本信息；
　　使用 sp_helptext 'dbo.USP_GetEmployeById'  则显示：对象 'dbo.USP_GetEmployeById' 的文本已加密。
　　使用SQL 语句：select * from sys.sql_modules where object_id = object_id(N'dbo.USP_GetEmployeById');
　　则“definition”字段显示为 NULL 。
　　
　　SQL Server 2005 里使用 with  encryption 选项创建的存储过程仍然和SQL Server 2000 一样，都是使用XOR 进行了的加密。但是与SQL Server 2000 不一样的是，在SQL Server 2005 的系统表syscomments 里已经查不到加密过的密文了。要查密文必须使用 DAC（专用管理员连接）连接数据库。
　　
　　        如果你接手了数据库的管理，而里面有些存储过程加密了，你又没有创建加密存储过程的那些脚本，你是否为此而无能为力。幸亏网上有位叫“王成辉”的翻译整理了国外大牛写的“解密加密存储过程”的一个存储过程 usp_decrypt ，有兴趣的可以找来看看。我在SQL Server 2005 中实验过了，确实能解密已经被加密的存储过程。
　　
　　来源：http://www.iyunv.com/kerrycode/archive/2010/08/14/1799392.html
　　注意：本文的内容和代码已经通过本人重新编排。