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架構裡面尋找。

預存程序的解析、編譯過程

建立預存程序時，首先分析檢查文法的正確性。如果在流程定義中遇到語法錯誤，將會返回錯誤，建立預存程序失敗。如果文法正確，預存程序的文本將會儲存在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下面實驗過了，確實能解密已經被加密的預存程序。