Sql Server系列:存储过程

1 存储过程简介

  存储过程是使用T-SQL代码编写的代码段。在存储过程中,可以声明变量、执行条件判断语句等其他编程功能。在MS SQL Server 2012中存储过程主要分三类:系统存储过程、自定义存储过程和扩展存储过程。

  存储过程的优点:

  ◊ 存储过程加快系统允许速度,存储过程只在创建时编译,以后每次执行时不需要重新编译。

  ◊ 存储过程可以封装复杂的数据库操作,简化操作流程。

  ◊ 可实现模块化的程序设计,存储过程可以多次调用,提供统一的数据库访问接口,改进应用程序的可维护性。

  ◊ 存储过程可以增强代码的安全性。

  ◊ 存储过程可以降低网络流量,存储过程代码直接存储在数据库中,在客户端与服务器的通讯过程中,不会产生大量的T-SQL代码流量。

  存储过程的缺点:

  ◊ 数据库移植不方便,存储过程依赖于数据库管理系统,MS SQL Server 2012存储过程中封装的操作代码不能直接移植到其他数据库系统中。

  ◊ 不支持面向对象的设计,无法采用面向对象的方式将逻辑业务进行封装。

  ◊ 不易维护

  ◊ 不支持集群

1.1 系统存储过程

  系统存储过程是有MS SQL Server 2012系统自身提供的存储过程,可以作为命令执行各种操作。系统存储过程主要用来从系统表中获取信息,使用系统存储过程完成数据库服务器的管理工作。系统存储过程位于数据库服务器中,并以sp_开头,系统存储过程定义在系统定义和用户定义的数据库中,在调用时不必在存储过程前加数据库限定名。

  系统存储过程创建并存储于系统数据库master中。

1.2 自定义存储过程

  自定义存储过程即用户使用T-SQL语句编写的、为了实现某一特定业务需求,在用户数据库中编写的T-SQL语句集合,用户存储过程可以接受输入参数、向客户端返回结果和信息、返回输出参数等。

  创建自定义存储过程时,存储过程名前面加上##表示创建一个全局的临时存储过程;存储过程名前面加上#表示创建局部临时存储过程。局部临时存储过程只能在创建它的会话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。

1.3 扩展存储过程

  扩展存储过程是以在SQL Server 2012环境外执行的DLL来实现的。扩展存储过程以前缀xp_标识。

2 创建及执行存储过程

  CREATE PROCEDURE语句的语法格式:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

  EXECUTE存储过程的语法格式:

[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH <execute_option> [ ,...n ] ]
    }
[;]

  示例:

CREATE PROCEDURE USP_GetAllProducts
AS
    SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
    FROM [dbo].[Product]
EXECUTE USP_GetAllProducts

  带输入参数的存储过程:

CREATE PROCEDURE USP_GetByProductID
(
    @ProductID INT
)
AS
    SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock],[CreateDate]
    FROM [dbo].[Product]
    WHERE [ProductID] = @ProductID
EXECUTE USP_GetByProductID @ProductID = 1

  带输出参数的存储过程:

CREATE PROCEDURE USP_GetTotalRecordsByCategoryID
(
    @CategoryID INT,
    @TotalRecords INT OUTPUT
)
AS
    SELECT @TotalRecords = COUNT(1)    FROM [dbo].[Product]
    WHERE [CategoryID] = @CategoryID
DECLARE @TotalProducts INT
EXECUTE USP_GetTotalRecordsByCategoryID @CategoryID = 1, @TotalRecords = @TotalProducts OUTPUT
SELECT @TotalProducts
DECLARE @TotalProducts INT
EXECUTE USP_GetTotalRecordsByCategoryID 1, @TotalProducts OUTPUT
SELECT @TotalProducts

3 修改存储过程

  修改存储过程语法格式:

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

4 查看存储过程

  查看存储过程结构:

EXEC sp_help USP_GetTotalRecordsByCategoryID

  查看存储过程文本:

EXEC sp_helptext USP_GetTotalRecordsByCategoryID

5 删除存储过程

  删除存储过程语法:

DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]

  示例:

DROP PROCEDURE USP_GetTotalRecordsByCategoryID

6 使用WITH RECOMPILE选项

  使用WITH RECOMPILE选项可以确保为特定的某次运行创建新的计划,使用该选项的两种方式:

  ◊ 在运行时包含WITH RECOMPILE

EXECUTE USP_GetAllProducts WITH RECOMPILE

  ◊ 在存储过程中包含WITH RECOMPILE选项。

  在CREATE PROC或ALTER PROC语句中的AS语句前添加WITH RECOMPILE选项。

7 递归存储过程

  SQL Server最多可以进行32次递归,超出则会引发错误并停止处理。

CREATE PROC USP_Factorial
(
    @ValueIn INT,
    @ValueOut INT OUTPUT
)
AS
    DECLARE @InWorking INT
    DECLARE @OutWorking INT

    IF @ValueIn >= 1
        BEGIN
            SELECT @InWorking = @ValueIn - 1
            EXEC USP_Factorial @InWorking, @OutWorking OUTPUT
            SELECT @ValueOut = @ValueIn * @OutWorking
        END
    ELSE
        SET @ValueOut = 1
DECLARE @ValueOut INT
EXEC USP_Factorial 5, @ValueOut OUTPUT
SELECT @ValueOut
posted @ 2014-12-16 11:09  libingql  阅读(741)  评论(0编辑  收藏  举报