歐陽(yáng)艷階，祝豐菊

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

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

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

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

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

1 存儲(chǔ)過(guò)程的分類及執(zhí)行過(guò)程

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

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

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

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

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

2 存儲(chǔ)過(guò)程與其他開(kāi)發(fā)對(duì)象的比較優(yōu)勢(shì)

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

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

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

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

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

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

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

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

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

3 存儲(chǔ)過(guò)程應(yīng)用舉例

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

（1）涉及多語(yǔ)句，用存儲(chǔ)過(guò)程將其模塊化；

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

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

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

案例如下：

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

CREATE PROCEDURE MyGoods_Cursor //定義存儲(chǔ)過(guò)程

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

AS

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

For

Select Goods_ID from AjaxGoods

Set @MyResult=

Declare @Field int //聲明臨時(shí)存放GoodsID的變量

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

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

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

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 //下一個(gè)GoodsID

End

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

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

GO

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

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

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

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

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

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

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

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

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

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

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

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

5 結(jié)語(yǔ)

存儲(chǔ)過(guò)程作為 SQL Server 數(shù)據(jù)庫(kù)中重要的技術(shù)與工具，在項(xiàng)目開(kāi)發(fā)過(guò)程中如能用合理運(yùn)用，不僅能有效地提高了系統(tǒng)性能，大大提高系統(tǒng)的可維護(hù)性，降低通信流量，還可通過(guò)存儲(chǔ)過(guò)程對(duì)客戶的權(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ù)庫(kù)應(yīng)用與開(kāi)發(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