作业帮 > SQLServer > 教育资讯

SQL Server教程：SQL Server—存储过程详解

来源：学生作业帮助网编辑：作业帮时间：2024/05/02 17:41:46 SQLServer

SQL Server教程：SQL Server—存储过程详解SQLServer

【无忧考网-SQL Server教程：SQL Server—存储过程详解】：

我想从下面几个方面大概的讲述下存储过程，可能有些知识点是你没有注意的，也可能有些知识点我不知道，欢迎大家指点指点。如有不足，欢迎指教!

存储过程概念

存储过程优点

存储过程的接口

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

存储过程安全性

查看存储过程

加密、解密存储过程

存储过程概念

存储过程(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架构里面查找。

SQLServer