SQL Server——存储过程

ienki

我想从下面几个方面大概的讲述下存储过程，可能有些知识点是你没有注意的，也可能有些知识点我不知道，欢迎大家指点指点。如有不足，欢迎指教!　　存储过程概念
　　存储过程优点
　　存储过程的接口
　　存储过程的解析、编译过程
　　存储过程安全性
　　查看存储过程
　　加密、解密存储过程
　　

存储过程概念　　
　　存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(带参存储过程)来执行它。


　　存储过程优点
　　

开发过程中使用存储过程的优点， 概括起来大体有下面一些：


1： 速度快、性能好。存储过程是一组已经编译过的SQL脚本，它已经进过解析、编译、优化器优化。调用存储过程可以重复使用它的缓存执行计划。这样节省了解析、分析等所      需消耗的CPU资源。时间


2： 存储过程存储在数据库服务器，可以减少网络通信，减少网络阻塞。 调用存储过程只需存储过程名字和参数，从而避免了    把长串的SQL语句传送到SQL 服务器，可以大大减      轻网络负担。


3： 业务逻辑封装，可以把相当一部分业务逻辑封装到存储过程中，当业务逻辑变更的时候，只要接口不变，只需修改存储过程内部逻辑就OK了，避免了业务逻辑放在代码层，业务      逻辑变动改动大的痛苦。


4： 安全性  参数化的存储过程可以减少SQL Injiection攻击，而且可以通过检验参数、授予对象执行权限提高了安全性。




存储过程按类型分为：系统存储过程、扩展存储过程、用户存储过程(包括CLR存储过程)、临时存储过程(其中又分为全局临时存储过程、局部临时存储过程)。系统存储过程一般以SP做前缀开头。扩展存储过程一般以XP为前缀，自定义存储过程命名，我习惯以USP为前缀。




存储过程的接口


存储过程的参数可以是输入参数、输出参数。先看看下面两个存储过程，第一个存储过程：有两个输入参数@EmployeeID、@EmployeeName，其中@EmployeeID 的默认值是 -1， @EmployeeName 的默认值是 NULL。

USE MyAssistant;
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 EmployeeName = @EmployeeName;
    ELSE
        SELECT * FROM dbo.Employee WHERE EmployeeID = @EmployeeID;
END
GO　　
　　

代码

USE MyAssistant;
GO
IF OBJECT_ID(N'dbo.USP_AddEmploye') IS NOT NULL
BEGIN
    DROP PROC dbo.USP_AddEmploye;
END
GO
--==========================================================================================================
--            Function              :        新增一条员工记录。
--            Author                :        Kerry
--            Create Date           :        2010-08-10
--            Description           :        
-------------------------------------------------------------------------------------------------------------
--            2010-08-13            :        修改.......增加.....
--            2010-08-14            :        修改.......增加.....
--==========================================================================================================
CREATE PROCEDURE dbo.USP_AddEmploye
    @EmployeeName     NVARCHAR(30),
    @Sex              BIT = 1,
    @Department       NVARCHAR(20),
    @Success          NVARCHAR(4) OUTPUT
AS
SET NOCOUNT ON;
BEGIN TRY
   
    IF (@EmployeeName IS NULL OR LEN(@EmployeeName) =0)
    BEGIN
        PRINT ('员工姓名不能为空');
        SET  @Success ='失败';
        RETURN;
    END
    INSERT INTO Employee
    VALUES(@EmployeeName, @Sex, @Department)
   
    IF @@error = 0
        SET @Success ='成功';
END TRY
   
BEGIN CATCH
    SET  @Success ='失败';
    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　　
　　
　　你可以这样去调用、执行存储过程 ，也可以不用添加@EmployeeName这样的参数。
　　DECLARE @Result NVARCHAR(4);



SET @Result = '';
EXEC dbo.USP_AddEmploye
        @EmployeeName ='张飞',
        @Sex =1 ,
        @Department    = '市场部',
        @Success = @Result OUTPUT
SELECT @Result;
GO　　
　　
　　一般在执行存储过程是，最好加上架构名称，例如：dbo.USP_AddEmploye  这样可以可以减少不必要的系统开销，提高性能。 因为如果在存储过程名称前面没有加上架构名称、SQL SERVER 首先会从当前数据库sys schema开始查找，如果没有找到，则会去其它schema查找，最后在dbo架构里面查找。
　　

　　存储过程的解析、编译过程
　　

　　创建存储过程时，首先分析检查语法的正确性。如果在过程定义中遇到语法错误，将会返回错误，创建存储过程失败。如果语法正确，存储过程的文本将会存储在SYS.SQL_MODULES 目录视图中。
　　



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
　　
　　
　　创建存储过程时，它会报如下错误提示。

　　

　　在解析阶段，Microsoft SQL Server 2005 还执行其他验证活动（例如，检查列数据类型与变量的兼容性）。如果执行存储过程时存储过程所引用的对象丢失，则存储过程在到达引用丢失对象的语句时将停止执行。在这种情况下，或在解析阶段发现其他错误时，将返回错误信息。
　　

　　如果执行过程时成功通过解析阶段，则 Microsoft SQL Server 查询优化器将分析存储过程中的 Transact-SQL 语句并创建一个执行计划。执行计划描述执行存储过程的最快方法，所依据的信息包括：
　　

　　表中的数据量。
　　

　　

　　表的索引的存在及特征，以及数据在索引列中的分布。
　　

　　

　　WHERE 子句条件所使用的比较运算符和比较值。
　　

　　

　　是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。
　　

　　

　　查询优化器在分析完存储过程中的这些因素后，将执行计划置于内存中。分析存储过程和创建执行计划的过程称为编译。优化的内存中的执行计划将用来执行该查询。执行计划将驻留在内存中，直到重新启动 SQL Server 或其他对象需要存储空间时为止。
　　

　　如果随后执行了存储过程，而现有执行计划仍留在内存中，则 SQL Server 将重用现有执行计划。如果执行计划不再位于内存中，则创建新的执行计划。
　　
　　存储过程安全性
　　

　　在sa账号下面，执行了下面这条语句
　　


DENY INSERT ON dbo.Employee TO Kerry;
GRANT EXECUTE ON dbo.USP_AddEmploye TO Kerry;　　
　　
　　

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


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

　　
　　这种安全模式能让你很灵活的控制用户允许进行的活动.
　　
　　查看存储过程
　　查看存储过程的方式很多，你可以如图所示，你可以在MSMS工具中选择“修改存储过程”或是“编写存储过程为”来查看存储过程，也可以通过查询视图，或是系统存储过程来查看你想要看的存储过程
　　

代码

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 MyAssistant;
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
AS
SET NOCOUNT ON;
BEGIN
   
    IF (@EmployeeID = -1 AND @EmployeeName IS NULL)
        BEGIN
            PRINT '请输入员工ID号或是用户名字';
        END

    IF @EmployeeID = -1
        SELECT * FROM dbo.Employee WHERE EmployeeName = @EmployeeName;
    ELSE
        SELECT * FROM dbo.Employee WHERE EmployeeID = @EmployeeID;
END
GO　　
　　

　　执行以后，你可以看见加密过后的存储过程，它的图标多了个小锁，而且你再也不能通过上面查看存储过程的方式来查看存储过程了。使用MSMS查看则会弹出下面错误提示

　　而用SP_HELPTEXT 则提示：对象 'dbo.USP_GetEmployeById' 的文本已加密。
　　

　　

　　SQLServer2005里使用WITH ENCRYPTION选项创建的存储过程仍然和sqlserver2000里一样，都是使用XOR进行了的加密。和2000不一样的是，在2005的系统表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC（专用管理员连接）连接到数据库后，如果你接手了数据库管理，里面有些存储过程加密了，你又没有创建加密存储过程的那些脚本，你是否干着急啊。别急，呵呵，网上有位叫王成辉翻译整理了国外大牛写的解密加密存储过程的一个存储过程usp_decrypt。呵呵，有兴趣的可以找来看看，我在SQL SERVER 05下面实验过了，确实能解密已经被加密的存储过程。