Sql Server系列：儲存過程

libingql發表於2014-12-16
SQLServer儲存過程

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

相關文章