SQL Server中存儲過程技術(shù)的研究

楊銘

1 存儲過程的概念

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

2 存儲過程的分類

2.1 系統(tǒng)存儲過程

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

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

當系統(tǒng)存儲過程的參數(shù)是保留字或?qū)ο竺覍ο竺蓴?shù)據(jù)庫或擁有者名字限定時，整個名字必須包含在單引號中。一個用戶可以在所有數(shù)據(jù)庫中執(zhí)行一個系統(tǒng)存儲過程的許可權(quán)，否則在任何數(shù)據(jù)庫中都不能執(zhí)行系統(tǒng)存儲過程。

2.2 本地存儲過程

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

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

2.3 臨時存儲過程

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

2.3.1 本地臨時存儲過程

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

2.3.2 全局臨時存儲過程

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

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

2.4 遠程存儲過程

遠程存儲過程（Remote Stored Procedures）是位于遠程服務(wù)器上的存儲過程，通?？梢允褂梅植际讲樵兒虴XECUTE命令執(zhí)行一個遠程存儲過程。

2.5 擴展存儲過程

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

3 存儲過程的運行方式

（1）能夠包含執(zhí)行各種數(shù)據(jù)庫操作的語句，并且可以調(diào)用其他的存儲過程。

（2）能夠接受輸入?yún)?shù)，并以輸出參數(shù)的形式將多個數(shù)據(jù)值返回給調(diào)用程序（Calling Procedure）或批處理（Batch）。

（3）向調(diào)用程序或批處理返回一個狀態(tài)值，以表明成功或失?。ㄒ约笆〉脑颍?。

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

4 存儲過程的優(yōu)點

（1）通過本地存儲、代碼預(yù)編譯和緩存技術(shù)實現(xiàn)高性能的數(shù)據(jù)操作。

（2）通過通用編程結(jié)構(gòu)和過程實現(xiàn)編程框架。如果業(yè)務(wù)規(guī)則發(fā)生變化，可以通過修改存儲過程來適應(yīng)新的業(yè)務(wù)規(guī)則，而不必修改客戶端的應(yīng)用程序。這樣所有調(diào)用該存儲過程的應(yīng)用程序就會遵循新的業(yè)務(wù)規(guī)則。

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

【參考文獻】

[1]夏慧，李響，齊泉.存儲過程在復(fù)雜的數(shù)據(jù)統(tǒng)計查詢中的應(yīng)用[J].醫(yī)學(xué)信息， 2009（08）.

[2]曾毅，王玉萍.SQL Server數(shù)據(jù)庫中存儲過程的實現(xiàn)[J].科技信息·科學(xué)教研， 2008（25）.

[3]鄢愛蘭，鹿江春.數(shù)據(jù)庫存儲過程應(yīng)用研究[J].南華大學(xué)學(xué)報：自然科學(xué)版， 2006（02）.

[責(zé)任編輯：楊玉潔]