SQL Server存儲過程與其他開發(fā)對象的比較研究與應(yīng)用

歐陽艷階，祝豐菊

摘 要： 在數(shù)據(jù)庫項(xiàng)目開發(fā)過程中，如何避免應(yīng)用程序在客戶端頻繁地執(zhí)行數(shù)據(jù)訪問操作，提高程序的運(yùn)行效率，是軟件工程師需要考慮的重要問題。本文分析并闡述了SQL Server數(shù)據(jù)庫系統(tǒng)中存儲過程的執(zhí)行過程及優(yōu)勢，并通過案例介紹了SQL Server存儲過程在開發(fā)過程中的具體應(yīng)用。

關(guān)鍵詞： SQL Server；數(shù)據(jù)庫；存儲過程

中圖分類號： TP311.13 文獻(xiàn)標(biāo)識碼： A 文章編號： 2095-8153（2017）01-0110-03

SQL Server是目前企業(yè)普遍應(yīng)用的關(guān)系型數(shù)據(jù)庫系統(tǒng)之一，它功能強(qiáng)大，性能穩(wěn)定。它還提供眾多數(shù)據(jù)處理工具，包括存儲過程、規(guī)則、約束、事務(wù)、鎖等。這些開發(fā)工具對提高開發(fā)人員的開發(fā)效率提供了重要幫助。因此SQL Server成為管理信息系統(tǒng)、數(shù)據(jù)庫系統(tǒng)、網(wǎng)絡(luò)集成等項(xiàng)目中的主要數(shù)據(jù)庫平臺。

為了提高開發(fā)效率，微軟公司在SQL Server系統(tǒng)中提供了存儲過程、視圖、規(guī)則、約束、觸發(fā)器等開發(fā)工具，其中存儲過程因其高效的執(zhí)行過程而深受數(shù)據(jù)庫開發(fā)人員的青睞。SQL Server系統(tǒng)中存儲過程（Procedure）能將商業(yè)邏輯，比如固定的功能模塊、計算流程等封裝其中，大大提高整個軟件系統(tǒng)的可維護(hù)性。因此在大型管理信息系統(tǒng)開發(fā)過程中尤其注重存儲過程的開發(fā)與使用[1]。

1 存儲過程的分類及執(zhí)行過程

存儲過程是由SQL語句和流控制語句組成的過程性程序。微軟公司在SQL Server數(shù)據(jù)庫系統(tǒng)中將存儲過程分為五類，其中常用的開發(fā)與運(yùn)行存儲過程有三類，它們是：系統(tǒng)存儲過程、用戶存儲過程和擴(kuò)展存儲過程[2]。

（1）系統(tǒng)存儲過程：該類存儲過程由微軟公司設(shè)計，由SQL Server在安裝過程中創(chuàng)建。其主要功能是獲取數(shù)據(jù)庫系統(tǒng)、操作系統(tǒng)及硬件等底層信息，也可為系統(tǒng)管理員和授權(quán)用戶提供數(shù)據(jù)更新服務(wù)。系統(tǒng)存儲過程均以sp_為名稱前綴。

（2）用戶存儲過程：由用戶為完成某一特定功能而編寫的存儲過程。管理信息系統(tǒng)開發(fā)過程中重要開發(fā)的也是該類存儲過程。

（3）擴(kuò)展存儲過程，是對動態(tài)鏈接庫DLL 函數(shù)的調(diào)用。一般涉及底層軟件、硬件等開發(fā)時主要采用該類存儲過程。

存儲過程首次執(zhí)行時，SQL Server數(shù)據(jù)庫系統(tǒng)將其優(yōu)化、編譯，并存儲在SQL Server數(shù)據(jù)庫系統(tǒng)服務(wù)器端的高速緩存中。只要服務(wù)器持續(xù)運(yùn)行，存儲過程即一直存在，客戶端和服務(wù)器端程序均可調(diào)用執(zhí)行，且允許不同的用戶訪問同樣的代碼。

2 存儲過程與其他開發(fā)對象的比較優(yōu)勢

存儲過程的用途幾乎無任何限制。返回DDL語句查詢結(jié)果、執(zhí)行復(fù)雜的數(shù)據(jù)計算、有效性檢驗(yàn)、事務(wù)等均可用存儲過程實(shí)現(xiàn)。在企業(yè)實(shí)際數(shù)據(jù)庫系統(tǒng)項(xiàng)目開發(fā)過程中，筆者通過對存儲過程、視圖、規(guī)則、約束、觸發(fā)器等開發(fā)工具的應(yīng)用，總結(jié)了存儲過程與SQL Server數(shù)據(jù)庫其他開發(fā)對象的比較優(yōu)勢。

1.適合模塊化程序開發(fā)

存儲過程被創(chuàng)建后存儲在SQL Server數(shù)據(jù)庫服務(wù)器端，通過應(yīng)用程序能反復(fù)調(diào)用，其代碼也能被應(yīng)用程序獨(dú)立修改。因此存儲過程可以增強(qiáng)系統(tǒng)可維護(hù)性和代碼的共享性，提高開發(fā)的質(zhì)量和效率，適合模塊化程序開發(fā)。

2.提高執(zhí)行效率

存儲過程第一次執(zhí)行后即被編譯，并存儲在高速緩沖中。其后續(xù)執(zhí)行不需重新編譯，因此能大大提高程序的執(zhí)行效率。適合對系統(tǒng)運(yùn)行效率較高的項(xiàng)目開發(fā)，比如計費(fèi)系統(tǒng)。

3.降低網(wǎng)絡(luò)通信流量

微軟公司為存儲過程設(shè)計了一套優(yōu)化的執(zhí)行方案。存儲過程第一次執(zhí)行后即被編譯。只要服務(wù)器持續(xù)運(yùn)行，存儲過程即一直存在。后續(xù)執(zhí)行時不需客戶端頻繁地將命令和數(shù)據(jù)傳輸?shù)椒?wù)器端，只需發(fā)布調(diào)用命令，即可將存儲過程執(zhí)行后的返回結(jié)果傳輸至客戶端進(jìn)行本地計算，因而可以明顯降低網(wǎng)絡(luò)通信流量。

4.保證數(shù)據(jù)安全性

SQL Server進(jìn)行安全性授權(quán)時，可以僅授予客戶訪問存儲過程的權(quán)限，而不授予客戶訪問數(shù)據(jù)庫中表、視圖等涉及具體數(shù)據(jù)的權(quán)限。這樣既保證了客戶不能直接查看、修改、刪除表或視圖中的數(shù)據(jù)，又能讓客戶方便地通過存儲過程操縱數(shù)據(jù)庫中的數(shù)據(jù)，從而保證數(shù)據(jù)的安全性。

3 存儲過程應(yīng)用舉例

下面案例程序代碼的作用是通過存儲過程從商品表AjaxGoods中進(jìn)行查詢。該案例展示了存儲過程在與其他對象上的比較優(yōu)勢。其優(yōu)勢如下：

（1）涉及多語句，用存儲過程將其模塊化；

（2）在服務(wù)器端編譯并存儲與高速緩存，提高執(zhí)行效率；

（3）執(zhí)行時僅EXECUTE？MyGoods_Cursor一句話調(diào)用，將結(jié)果傳至客戶端，降低網(wǎng)絡(luò)通信量；

（4）通過對存儲過程MyGoods_Cursor授權(quán)，不對AjaxGoods表授權(quán)，保證數(shù)據(jù)安全性。

案例如下：

某超市進(jìn)行貨物盤存，現(xiàn)欲統(tǒng)計出各商品大類剩余商品的數(shù)量，如家電類剩余商品數(shù)量、日化類剩余商品數(shù)量，并將結(jié)果組成一個字符串顯示。如“151，209，28”。采用存儲過程開發(fā)代碼如下：

CREATE PROCEDURE MyGoods_Cursor //定義存儲過程

@MyResult varchar（255） output //聲明輸出變量

AS

Declare Goods_Cursor cursor //聲明商品游標(biāo)變量

For

Select Goods_ID from AjaxGoods

Set @MyResult=

Declare @Field int //聲明臨時存放GoodsID的變量

Open Goods_Cursor //打開游標(biāo)

Fetch Next From Goods_Cursor Into @Field //將實(shí)際ID賦給變量

While（@@Fetch_Status=0） //循環(huán)開始

Begin

If @MyResult =

Select @MyResult = Convert（nvarchar（2），Count（*））

From GoodsCounty

Where GoodsID=@Field

Else

Select @MyResult = @MyResult + '，' + convert（nvarchar（2），count（*））

From AjaxGoods

Where GoodsID=@Field

Fetch Next From Goods_Cursor Into @Field //下一個GoodsID

End

Close Goods_Cursor//關(guān)閉游標(biāo)

Deallocate Goods_Cursor //釋放游標(biāo)引用

GO

執(zhí)行存儲過程：EXECUTE MyGoods_Cursor4 存儲過程的應(yīng)用場所及應(yīng)用策略總結(jié)

通過對存儲過程與其他開發(fā)對象的比較分析可以看出，存儲過程具有提高系統(tǒng)的執(zhí)行效率、保證數(shù)據(jù)安全、增強(qiáng)系統(tǒng)可維護(hù)性、降低網(wǎng)絡(luò)通信量等諸多優(yōu)勢。下面結(jié)合筆者開發(fā)經(jīng)驗(yàn)，對存儲過程的應(yīng)用場所及應(yīng)用策略作如下總結(jié)。

1.需要反復(fù)調(diào)用的計算模塊與商業(yè)邏輯采用存儲過程

雖然客戶端也能通過代碼取代存儲過程的部分功能，但存儲過程運(yùn)行過程中，首次執(zhí)行即已在服務(wù)器端進(jìn)行了編譯與優(yōu)化，而客戶端代碼的每條SQL 語句都要通過前臺開發(fā)工具臨時送入服務(wù)器進(jìn)行編譯和優(yōu)化執(zhí)行，因而存儲過程運(yùn)行效率遠(yuǎn)高于在客戶端的處理。而且若客戶端代碼包含多條SQL 語句，客戶端則要通過網(wǎng)絡(luò)與數(shù)據(jù)庫服務(wù)器多次通信才能完成任務(wù)，運(yùn)行效率會進(jìn)一步降低。因此需要反復(fù)調(diào)用的計算模塊與商業(yè)邏輯采用存儲過程

2.經(jīng)常變化的業(yè)務(wù)規(guī)則采用存儲過程

假如要開發(fā)一個統(tǒng)計銷售利潤的計算程序，而統(tǒng)計銷售利潤的方法經(jīng)常會根據(jù)實(shí)際情況進(jìn)行變化調(diào)整，此時應(yīng)把統(tǒng)計銷售利潤程序編寫成存儲過程，讓客戶端代碼調(diào)用該存儲過程來計算出銷售利潤數(shù)據(jù)。當(dāng)統(tǒng)計銷售利潤的方法發(fā)生變化時，只需修改存儲過程即可，而客戶端應(yīng)用程序代碼不需作任何改變，這樣大大降低了應(yīng)用程序的維護(hù)難度和維護(hù)工作量。因此經(jīng)常變化的業(yè)務(wù)規(guī)則采用存儲過程。

3.保證表數(shù)據(jù)安全采用存儲過程

假如一個客戶對數(shù)據(jù)表沒有任何訪問權(quán)限，系統(tǒng)仍然可以授予該客戶調(diào)用存儲過程的權(quán)限，以執(zhí)行修改數(shù)據(jù)表的行、列及子集。這樣做可保證表數(shù)據(jù)的安全性。因此保證表數(shù)據(jù)安全建議采用存儲過程。

4.保證表數(shù)據(jù)完整采用存儲過程

數(shù)據(jù)完整性可通過規(guī)則、約束等對象來操作。但事務(wù)級的完整性最好通過存儲過來來實(shí)現(xiàn)。首先在存儲過程中定義事務(wù)，再將SQL語句包裹在事務(wù)中，通過觸發(fā)器或其他對象調(diào)用，可使相關(guān)的表數(shù)據(jù)操作按事務(wù)要求一起發(fā)生或一起回滾，從而保證數(shù)據(jù)的完整性。

5.復(fù)雜的數(shù)據(jù)操作采用存儲過程

在應(yīng)用程序需要對基本表進(jìn)行復(fù)雜的DDL、DML操作才能得到最終結(jié)果情況下，若將復(fù)雜的DDL、DML操作放在客戶端，其效率是非常低下的。若采用存儲過程實(shí)現(xiàn)，諸多問題都可迎刃而解。包括效率、簡單、方便等。

5 結(jié)語

存儲過程作為 SQL Server 數(shù)據(jù)庫中重要的技術(shù)與工具，在項(xiàng)目開發(fā)過程中如能用合理運(yùn)用，不僅能有效地提高了系統(tǒng)性能，大大提高系統(tǒng)的可維護(hù)性，降低通信流量，還可通過存儲過程對客戶的權(quán)限進(jìn)行限制，保證數(shù)據(jù)的安全。

[參考文獻(xiàn)]

[1]陳暢亮.SQL Server性能調(diào)優(yōu)實(shí)戰(zhàn)[M].北京：機(jī)械工業(yè)出版社，2015：70.

[2]衛(wèi) 琳.SQL Server 2012數(shù)據(jù)庫應(yīng)用與開發(fā)教程[M].北京：清華大學(xué)出版社，2014：159.

Research and Application on SQL Server Database Stored Process and

Other Development Objects

OUYANG Yan-jie，ZHU Feng-ju

Abstract： In the database project development process，how to avoid the applications access the client data frequently and improve the operational efficiency of the program is the important issue that software engineers need to consider. This paper analyzes the implementation process and advantages of SQL Server database system stored procedure，introduces the application of SQL Server stored procedure in the development process based on the case analysis.

Key words： SQL Server；database；stored procedure