Sql Server系列：預存程序

標籤：style   blog   ar   io   color   使用   sp   for   strong   

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_GetAllProductsAS    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 INTEXECUTE USP_GetTotalRecordsByCategoryID @CategoryID = 1, @TotalRecords = @TotalProducts OUTPUTSELECT @TotalProducts

DECLARE @TotalProducts INTEXECUTE USP_GetTotalRecordsByCategoryID 1, @TotalProducts OUTPUTSELECT @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

Sql Server系列：預存程序