SQL Server中存儲過程技術的研究

楊銘

1 存儲過程的概念

當開發一個應用程序時，為了易于修改和擴充，經常會將負責不同功能的語句集中起來而且按照用途分別獨立放置，以便能夠反復調用，而這些獨立放置且擁有不同功能的語句，即是“過程”（Procedure）。存儲過程包含一些Transact-SQL語句并以特定的名稱存儲在數據庫中（存儲過程也是一種數據庫對象）。存儲過程中可以聲明變量、有條件地執行以及其他各項強大的程序設計功能。

2 存儲過程的分類

2.1 系統存儲過程

系統存儲過程（System Stored Procedures）主要存儲在master數據庫中，并以sp_為前綴，并且系統存儲過程主要是從系統表中獲取信息。通過系統存儲過程，許多管理性或信息性的活動（如了解數庫對象、數據庫信息）都可以被有效地完成。盡管這些系統存儲過程被存儲在master數據庫中，但是仍可以在其他數據庫中對其進行調用。當創建一個數據庫時，一些系統存儲過程會在新的數據庫中被自動創建。

系統存儲過程所能完成的操作多達千百項。例如，提供幫助的系統存儲過程有sp_helpsql顯示關于SQL語句、存儲過程和其他主題的信息;sp_help提供關于存儲過程或其他數據庫對象的報告;sp_helptext顯示存儲過程和其他對象的文本;sp_depends列舉引用或依賴指定對象的所有存儲過程。事實上，在前面的學習中就已使用道不少的系統存儲過程，例如，sp_tables取得數據庫中關于表和視圖的相關信息;sp_renamedb更改數據庫的名稱等。

當系統存儲過程的參數是保留字或對象名，且對象名由數據庫或擁有者名字限定時，整個名字必須包含在單引號中。一個用戶可以在所有數據庫中執行一個系統存儲過程的許可權，否則在任何數據庫中都不能執行系統存儲過程。

2.2 本地存儲過程

本地存儲過程（Local Stored Procedures）也就是用戶自行創建并存儲在用戶數據庫中的存儲過程。事實上一般所說的存儲過程指的就是本地存儲過程。

用戶創建的存儲過程是由用戶創建并能完成某一特定功能（如查詢用戶所需的數據信息）的存儲過程。

2.3 臨時存儲過程

臨時存儲過程（Temporary Stored Procedures）可分為以下兩種：

2.3.1 本地臨時存儲過程

不論哪一個數據庫是當前數據庫，如果在創建存儲過程時，以井字號（#）作為其名稱的第一個字符，則該存儲過程將成為一個存放在tempdb數據庫中的本地臨時存儲過程（例如，CREATE ?PROCEDURE #book_proc …）。本地臨時存儲過程只有創建它的連接的用戶才能夠執行它，而且一旦這位用戶斷開與SQL Server的連接（也就是注銷SQL Server 2005），本地臨時存儲過程就會自動刪除，當然，這位用戶也可以在連接期間用DROP PROCEDURE命令刪除他所創建的本地臨時存儲過程。

2.3.2 全局臨時存儲過程

不論哪一個數據庫是當前數據庫，只要所創建的存儲過程名稱是以兩個井字號（##）開始，則該存儲過程將成為一個存儲在tempdb數據庫中的全局臨時存儲過程。全局臨時存儲過程一旦創建，以后連接到數據庫的任意用戶都能執行它，而且不需要特定的權限。因此，必須注意其名稱不能和其他連接所采用的名稱相同。

不論創建的是本地臨時存儲過程還是全局臨時存儲過程，只要服務器管理平臺停止運行，它們將不復存在。

2.4 遠程存儲過程

遠程存儲過程（Remote Stored Procedures）是位于遠程服務器上的存儲過程，通常可以使用分布式查詢和EXECUTE命令執行一個遠程存儲過程。

2.5 擴展存儲過程

擴展存儲過程（Extended Stored Procedures）是用戶可以使用外部程序語言編寫的存儲過程。為了區別，擴展存儲過程的名稱通常以xp_開頭。擴展存儲過程是以動態鏈接庫（DLLS）的形式存在，它要存儲在系統數據庫master中。

3 存儲過程的運行方式

（1）能夠包含執行各種數據庫操作的語句，并且可以調用其他的存儲過程。

（2）能夠接受輸入參數，并以輸出參數的形式將多個數據值返回給調用程序（Calling Procedure）或批處理（Batch）。

（3）向調用程序或批處理返回一個狀態值，以表明成功或失敗（以及失敗的原因）。

（4）存儲過程（Stored Procedures）是一組為完成特定功能的SQL語句集，經編譯后存儲在數據庫中。用戶通過指定存儲過程的名字給出參數（如果該存儲過程帶有參數）來執行它。

4 存儲過程的優點

（1）通過本地存儲、代碼預編譯和緩存技術實現高性能的數據操作。

（2）通過通用編程結構和過程實現編程框架。如果業務規則發生變化，可以通過修改存儲過程來適應新的業務規則，而不必修改客戶端的應用程序。這樣所有調用該存儲過程的應用程序就會遵循新的業務規則。

（3）通過隔離和加密的方法提高數據庫的安全性。數據庠用戶可以通過得到權限來執行存儲過程，而不必給予用戶直接訪問數據庫對象的權限。這些對象將由存儲過程來執行操作，另外，存儲過程可以加密，這樣用戶就無法閱讀存儲過程中的Transact-SQL語句。

【參考文獻】

[1]夏慧，李響，齊泉.存儲過程在復雜的數據統計查詢中的應用[J].醫學信息， 2009（08）.

[2]曾毅，王玉萍.SQL Server數據庫中存儲過程的實現[J].科技信息·科學教研， 2008（25）.

[3]鄢愛蘭，鹿江春.數據庫存儲過程應用研究[J].南華大學學報：自然科學版， 2006（02）.

[責任編輯：楊玉潔]