ＳＱＬ?。樱澹颍觯澹虼鎯?chǔ)過(guò)程使用剖析

摘要：文章介紹了存儲(chǔ)過(guò)程的基本概念及其優(yōu)點(diǎn)，對(duì)存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)編程中的應(yīng)用作了詳細(xì)的論述，并討論了存儲(chǔ)過(guò)程的設(shè)計(jì)策略及使用策略。

關(guān)鍵詞：SQL Server存儲(chǔ)過(guò)程；數(shù)據(jù)庫(kù)應(yīng)用；應(yīng)用策略

中圖分類(lèi)號(hào)：TP393文獻(xiàn)標(biāo)識(shí)碼：A文章編號(hào)：1009-2374（2009）07-0118-03

一、SQL Server的存儲(chǔ)過(guò)程概述

存儲(chǔ)過(guò)程（Stored Procedure）是一組已被編輯好的，存儲(chǔ)在服務(wù)器上的能夠執(zhí)行某種功能的預(yù)編譯的Transact-SQL代碼。它是一種封裝重復(fù)任務(wù)操作的方法，支持用戶提供的參數(shù)變量，具有強(qiáng)大的編程能力。存儲(chǔ)過(guò)程通過(guò)參數(shù)傳遞、 進(jìn)行判斷、聲明變量，以及返回信息來(lái)擴(kuò)充標(biāo)準(zhǔn)SQL 語(yǔ)言的功能?？梢园汛鎯?chǔ)過(guò)程看成是以數(shù)據(jù)庫(kù)對(duì)象形式存儲(chǔ)在 SQL Server 中的一段程序或函數(shù)。當(dāng)執(zhí)行存儲(chǔ)過(guò)程時(shí)，該存儲(chǔ)過(guò)程是在 SQL Server上運(yùn)行，而不是在客戶端發(fā)送請(qǐng)求。存儲(chǔ)過(guò)程可以是一個(gè)簡(jiǎn)單的 SQL 語(yǔ)句，如 select * from gz。存儲(chǔ)過(guò)程也可以是由一系列用來(lái)對(duì)數(shù)據(jù)庫(kù)表實(shí)現(xiàn)復(fù)雜商務(wù)規(guī)則的SQL 語(yǔ)句和控制流語(yǔ)言語(yǔ)句所組成。存儲(chǔ)過(guò)程非常類(lèi)似于DOS系統(tǒng)中批處理文件（*.bat）。在批處理文件中，可以包含一組經(jīng)常執(zhí)行的命令，這一組命令可以通過(guò)批處理文件的執(zhí)行而執(zhí)行。同樣的，存儲(chǔ)過(guò)程也是把要完成某項(xiàng)任務(wù)的許多SQL語(yǔ)句寫(xiě)在一起，組成命令集合的形式，然后，通過(guò)執(zhí)行存儲(chǔ)過(guò)程執(zhí)行相應(yīng)的任務(wù)。

存儲(chǔ)過(guò)程與客戶端SQL命令操作的應(yīng)用程序相比，具有下列優(yōu)點(diǎn)：

1．可以大大提高系統(tǒng)效率。對(duì)于客戶端來(lái)說(shuō)，只要調(diào)用已在服務(wù)器中存放的存儲(chǔ)過(guò)程，并通過(guò)網(wǎng)絡(luò)發(fā)送該過(guò)程名和少量入口參數(shù)，數(shù)據(jù)庫(kù)服務(wù)器就可以執(zhí)行該過(guò)程，在執(zhí)行完成后只返回結(jié)果集數(shù)據(jù)給客戶端應(yīng)用程序，而無(wú)須再在網(wǎng)上傳送大量的命令和中間結(jié)果數(shù)據(jù)，這樣可以充分利用服務(wù)器的高性能來(lái)提高運(yùn)算速度，并減輕網(wǎng)絡(luò)負(fù)擔(dān)。

2．提高系統(tǒng)的可維護(hù)性。由于存儲(chǔ)過(guò)程是以代碼形式存在的一種數(shù)據(jù)庫(kù)對(duì)象，所以它的創(chuàng)建和刪除都很簡(jiǎn)單，并且不會(huì)影響到庫(kù)中的其他數(shù)據(jù)對(duì)象。例如在銀行報(bào)表管理系統(tǒng)中，有些計(jì)算規(guī)則往往會(huì)隨著時(shí)間和客戶要求的改變而改變，如果將這些業(yè)務(wù)規(guī)則的處理直接放到客戶應(yīng)用程序中去做，在規(guī)則發(fā)生改變時(shí)需要修改大量的客戶端源程序代碼，然后重新進(jìn)行編譯和鏈接，這樣不僅增加了客戶程序的維護(hù)難度，而且降低了系統(tǒng)的效率。但如果將這些規(guī)則放在服務(wù)器的存儲(chǔ)過(guò)程中，而由客戶端應(yīng)用程序來(lái)調(diào)用此過(guò)程，情況將大為改觀。當(dāng)某個(gè)業(yè)務(wù)的計(jì)算規(guī)則發(fā)生變化時(shí)，只需要修改或重寫(xiě)對(duì)應(yīng)的服務(wù)器存儲(chǔ)過(guò)程就可以了，從而提高了系統(tǒng)的可維護(hù)性。

3．增強(qiáng)系統(tǒng)的安全性。將存儲(chǔ)過(guò)程用在安全性上就是利用其“授權(quán)”的特性，因?yàn)榇鎯?chǔ)過(guò)程就像數(shù)據(jù)庫(kù)中的其他對(duì)象，如“表”和“字段”那樣可以被操作，所以只有當(dāng)一個(gè)用戶被授予某個(gè)權(quán)限后，才可以做與自身的權(quán)限相符合的動(dòng)作。

4．增強(qiáng)SQL語(yǔ)言的功能和靈活性。由于SQL語(yǔ)言自身的限制，它不能聲明變量，不能使用if/else結(jié)構(gòu)，不能使用循環(huán)語(yǔ)句。存儲(chǔ)過(guò)程利用流控制語(yǔ)句和內(nèi)部函數(shù)實(shí)現(xiàn)了SQL語(yǔ)言本身所不能做到的事，等于從另一個(gè)方面提高了SQL語(yǔ)言的功能和靈活性。

二、存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)編程中的應(yīng)用

存儲(chǔ)過(guò)程的應(yīng)用具有很大的靈活性，在具體的開(kāi)發(fā)過(guò)程中往往可以根據(jù)實(shí)際需要的不同而采用相應(yīng)的技術(shù)與方法。

1．存儲(chǔ)過(guò)程的嵌套調(diào)用。有時(shí)候可能要對(duì)一批數(shù)據(jù)進(jìn)行重復(fù)的處理，或者在一個(gè)存儲(chǔ)過(guò)程中要用到其他存儲(chǔ)過(guò)程的處理結(jié)果，這時(shí)就可以使用存儲(chǔ)過(guò)程的嵌套調(diào)用來(lái)實(shí)現(xiàn)。下面結(jié)合一個(gè)用于進(jìn)行數(shù)據(jù)統(tǒng)計(jì)和分析的具體實(shí)例來(lái)講述存儲(chǔ)過(guò)程嵌套調(diào)用的實(shí)現(xiàn)方法。首先創(chuàng)建一個(gè)用于數(shù)據(jù)統(tǒng)計(jì)的子存儲(chǔ)過(guò)程，把調(diào)用它的父存儲(chǔ)過(guò)程要傳給它的統(tǒng)計(jì)條件定義為輸入?yún)?shù)，而把父存儲(chǔ)過(guò)程想要得到的統(tǒng)計(jì)結(jié)果定義為輸出參數(shù)，并在子存儲(chǔ)過(guò)程中計(jì)算出它們的值作為返回結(jié)果。當(dāng)然，這個(gè)子存儲(chǔ)過(guò)程也可以作為一個(gè)獨(dú)立的存儲(chǔ)過(guò)程來(lái)使用。建立子存儲(chǔ)過(guò)程如下：

CREATEPROCEDUREup_jsszl

@zsbmnoinchar（3），// 定義輸入?yún)?shù)

@tjyfinint，

@ylfszlmoneyoutput// 定義輸出參數(shù)

AS

BEGIN

DECLARE@ylfszmoney，

@ylfyzmoney//聲明局部變量

......

SELECT@ylfszl=@ylfsz/@ylfyz，// 得到并返回結(jié)果

RETURN

END

然后創(chuàng)建一個(gè)父存儲(chǔ)過(guò)程，在父存儲(chǔ)過(guò)程中通過(guò)使用特定的參數(shù)值去調(diào)用子存儲(chǔ)過(guò)程來(lái)得到相應(yīng)的統(tǒng)計(jì)結(jié)果。

CREATEPROCEDUREup_bjszl

@zsbmnoinchar（3），// 定義輸入?yún)?shù)

@tjyfinint

AS

BEGIN

DECLARE

@zsbmnochar（3），// 聲明局部變量

@tjyfint，

@ylfszlmoney

... ...

EXECUTEup_jsszl

@zsbmno，// 調(diào)用子存儲(chǔ)過(guò)程

@tjyf，

@ylfszl

output

... ...

RETURN

END

由此可見(jiàn)，可以將那些實(shí)現(xiàn)不同功能的代碼模塊化，然后通過(guò)使用存儲(chǔ)過(guò)程的嵌套調(diào)用將它們組合起來(lái)去實(shí)現(xiàn)一些更復(fù)雜的功能。這樣既可以增強(qiáng)代碼的可重用性，又可以使整個(gè)應(yīng)用程序的結(jié)構(gòu)更加清晰，而且能夠有效地提高應(yīng)用程序的開(kāi)發(fā)效率和系統(tǒng)的可維護(hù)性。

2．在存儲(chǔ)過(guò)程中使用游標(biāo)。游標(biāo)提供了一種處理結(jié)果集中記錄的靈活手段，通過(guò)它可以根據(jù)需要對(duì)結(jié)果集中的各條記錄進(jìn)行一些相應(yīng)的處理。在存儲(chǔ)過(guò)程中需要對(duì)數(shù)據(jù)庫(kù)中的表或表中滿足一定條件的記錄集中的各條記錄進(jìn)行處理時(shí)就可以利用游標(biāo)來(lái)進(jìn)行。游標(biāo)的使用要經(jīng)過(guò)聲明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)、關(guān)閉游標(biāo)和釋放游標(biāo)這五個(gè)步驟。下面的存儲(chǔ)過(guò)程就是通過(guò)在滿足特定條件的記錄集上定義一個(gè)游標(biāo)來(lái)逐條處理記錄集中的每一條記錄的。

CREATEPROCEDUREup_fxszl

@zsbmnoinchar（3），// 定義輸入?yún)?shù)

@tjyfinint

AS

BEGIN

DECLARE@bmnochar（3），// 聲明局部變量

@ylfszlmoney

......

DECLAREcur_bmnoCURSORFOR// 聲明游標(biāo)

SELECT JZSNO FROM JZS WHERE FJNO = @zsbmnoin

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

FETCHcur_bmnoINTO@bmno//用游標(biāo)取一條記錄

WHILE@@sqlstatus=0// 處理記錄集中各記錄

BEGIN

EXECUTEup_jsszl@bmno，// 調(diào)用子存儲(chǔ)過(guò)程

@tjyfin，

@ylfszloutput

......

FETCHcur_bmnoINTO@bmno//用游標(biāo)取記錄

END

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

DEALLOCATECURSORcur_bmno// 釋放游標(biāo)

......

END

在上面的存儲(chǔ)過(guò)程中聲明了一個(gè)游標(biāo)來(lái)逐條取得從數(shù)據(jù)庫(kù)表中得到的結(jié)果集中的記錄，并在循環(huán)控制語(yǔ)句中將該記錄作為入口參數(shù)去調(diào)用子存儲(chǔ)過(guò)程來(lái)對(duì)每一條記錄進(jìn)行處理，然后再對(duì)處理結(jié)果作進(jìn)一步的分析?？梢钥吹酵ㄟ^(guò)將游標(biāo)和存儲(chǔ)過(guò)程調(diào)用結(jié)合起來(lái)，可以很方便地對(duì)結(jié)果集中的各條記錄進(jìn)行處理。

3．在存儲(chǔ)過(guò)程中使用臨時(shí)表。在使用存儲(chǔ)過(guò)程時(shí)，一般是通過(guò)定義輸出參數(shù)來(lái)得到它的計(jì)算結(jié)果。但是當(dāng)進(jìn)行一些復(fù)雜的數(shù)據(jù)處理要求返回的數(shù)據(jù)量比較大時(shí)，如果還是通過(guò)輸出參數(shù)來(lái)返回計(jì)算結(jié)果，則會(huì)因?yàn)槎x的參數(shù)過(guò)多而使存儲(chǔ)過(guò)程顯得很雜亂，而且在存儲(chǔ)過(guò)程中對(duì)可以定義的參數(shù)個(gè)數(shù)也是有一定限制的。另外，有時(shí)可能有多個(gè)存儲(chǔ)過(guò)程要用到一些相同的中間結(jié)果，而且連這些中間結(jié)果甚至也是由幾個(gè)存儲(chǔ)過(guò)程共同產(chǎn)生的。這時(shí)使用臨時(shí)表將會(huì)帶來(lái)很大的便利。在Sybase存儲(chǔ)過(guò)程中，可以用CREATETABLE命令來(lái)創(chuàng)建臨時(shí)表，并在表名前加上符號(hào)“#”作為臨時(shí)表的標(biāo)識(shí)，然后就可以像使用數(shù)據(jù)庫(kù)中其他的表一樣使用它。例如：

CREATEPROCEDUREup_cxszl

@zsbmnoinchar（3），// 定義輸入?yún)?shù)

@tjyfinint

AS

BEGIN

DECLARE@bmnochar（3），// 聲明局部變量

@ylfszlmoney

......

CREATETABLE#SFSZLCX（BMNOchar（3）null，//創(chuàng)建臨時(shí)表

YLFSZLmoneynull）

......

DECLAREcur_bmnoCURSORFOR// 聲明游標(biāo)

SELECT JZSNO FROM JZS WHERE FJNO = @zsbmnoin

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

FETCHcur_bmnoINTO@bmno //用游標(biāo)取一條記錄

WHILE@@sqlstatus=0// 處理記錄集中各記錄

BEGIN

EXECUTEup_jsszl@bmno，// 調(diào)用子存儲(chǔ)過(guò)程

@tjyfin

INSERT#SFSZLPX（BMNO，YLFSZL）// 結(jié)果存入臨時(shí)表

VALUES（@bmno，@ylfszl）

FETCHcur_bmnoINTO@bmno// 用游標(biāo)取記錄

END

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

DEALLOCATECURSORcur_bmno// 釋放游標(biāo)

RETURN

END

由于臨時(shí)表的生命周期與創(chuàng)建它的存儲(chǔ)過(guò)程相同，隨著存儲(chǔ)過(guò)程執(zhí)行的結(jié)束，臨時(shí)表也就不復(fù)存在了。所以如果想要在一個(gè)存儲(chǔ)過(guò)程中利用其他存儲(chǔ)過(guò)程產(chǎn)生的中間結(jié)果，則應(yīng)該在父過(guò)程中創(chuàng)建臨時(shí)表，在子過(guò)程中直接引用它，并把子過(guò)程所得的中間結(jié)果存入臨時(shí)表中。

三、存儲(chǔ)過(guò)程的應(yīng)用策略

使用存儲(chǔ)過(guò)程的目的是為了提高應(yīng)用系統(tǒng)的運(yùn)行效率，增強(qiáng)系統(tǒng)的可維護(hù)性，保證數(shù)據(jù)的完整性與一致性。下面給出了采用存儲(chǔ)過(guò)程的一些基本策略：

1．重復(fù)調(diào)用的、需要一定運(yùn)行效率的邏輯與運(yùn)算處理宜采用存儲(chǔ)過(guò)程實(shí)現(xiàn)。雖然客戶端應(yīng)用程序也能進(jìn)行這樣的邏輯與運(yùn)算處理，但存儲(chǔ)過(guò)程的運(yùn)行效率高。因?yàn)樗蔷幾g與優(yōu)化好的過(guò)程程序，而客戶端應(yīng)用程序的每個(gè)SQL語(yǔ)句都要臨時(shí)送入數(shù)據(jù)庫(kù)服務(wù)器進(jìn)編譯和優(yōu)化執(zhí)行。如果客戶端應(yīng)用程序包含多條SQL語(yǔ)句，客戶端應(yīng)用程序則要通過(guò)網(wǎng)絡(luò)與數(shù)據(jù)庫(kù)服務(wù)器多次通信才能完成任務(wù)，運(yùn)行效率進(jìn)一步降低。

2．易于變化的業(yè)務(wù)規(guī)則應(yīng)放入存儲(chǔ)過(guò)程中。例如，要編寫(xiě)一段計(jì)算獎(jiǎng)金的處理程序，而獎(jiǎng)金的發(fā)放辦法會(huì)經(jīng)常根據(jù)具體情況進(jìn)行調(diào)整，此時(shí)應(yīng)把獎(jiǎng)金業(yè)務(wù)程序?qū)懗纱鎯?chǔ)過(guò)程，讓客戶端應(yīng)用程序調(diào)用此過(guò)程來(lái)得到獎(jiǎng)金數(shù)據(jù)。當(dāng)計(jì)算獎(jiǎng)金的辦法發(fā)生變化時(shí)，只需修改存儲(chǔ)過(guò)程即可，而應(yīng)用程序不用任何改動(dòng)，這樣增強(qiáng)了應(yīng)用程序的可維護(hù)性。

3．需要集中管理和控制的邏輯與運(yùn)算處理應(yīng)放入存儲(chǔ)過(guò)程中。存儲(chǔ)過(guò)程只需在數(shù)據(jù)庫(kù)服務(wù)器中保存一份拷貝，所有的應(yīng)用子系統(tǒng)均可調(diào)用執(zhí)行該存儲(chǔ)過(guò)程，而無(wú)須每個(gè)應(yīng)用子系統(tǒng)編寫(xiě)相同的處理邏輯程序，這樣也便于應(yīng)用程序的維護(hù)與版本的管理。

4．存儲(chǔ)過(guò)程可作為保證系統(tǒng)數(shù)據(jù)安全性和數(shù)據(jù)完整性的一種實(shí)現(xiàn)機(jī)制。例如，一個(gè)用戶可以被授予權(quán)限去調(diào)用存儲(chǔ)過(guò)程執(zhí)行修改某特定表的行列子集，即使他對(duì)該表沒(méi)有任何其他權(quán)限，這樣可保證系統(tǒng)數(shù)據(jù)的安全性。同樣，通過(guò)特殊類(lèi)型的存儲(chǔ)過(guò)程——觸發(fā)器還可使相關(guān)的表數(shù)據(jù)操作在一起發(fā)生，從而維護(hù)數(shù)據(jù)的完整性。

5．需要對(duì)基本表的數(shù)據(jù)進(jìn)行較復(fù)雜的邏輯處理才能返回所需的結(jié)果數(shù)據(jù)集，應(yīng)采用存儲(chǔ)過(guò)程完成。在應(yīng)用程序開(kāi)發(fā)中，經(jīng)常會(huì)遇到這樣的情況，應(yīng)用程序報(bào)表數(shù)據(jù)、統(tǒng)計(jì)分析數(shù)據(jù)等很難直接從基本數(shù)據(jù)表處理得到，而需要對(duì)基本表進(jìn)行較復(fù)雜的邏輯操作處理或者需要建立若干過(guò)渡臨時(shí)表，才能得到最終的結(jié)果數(shù)據(jù)。如果這種處理操作均在客戶端應(yīng)用程序完成，效率是低下的。相反若采用存儲(chǔ)過(guò)程實(shí)現(xiàn)，諸多問(wèn)題都可迎刃而解。

四、結(jié)語(yǔ)

存儲(chǔ)過(guò)程的功能很強(qiáng)大，它可以根據(jù)用戶的設(shè)置完成從簡(jiǎn)單的查詢到應(yīng)用程序邏輯控制的任何事情。存儲(chǔ)過(guò)程在信息管理系統(tǒng)中的應(yīng)用非常廣泛，它為實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)應(yīng)用提供了很好的解決方法。它不僅能提高應(yīng)用系統(tǒng)的運(yùn)行效率增強(qiáng)系統(tǒng)的可維護(hù)性，還可以保證數(shù)據(jù)的完整性與可靠性。

參考文獻(xiàn)

[1]施伯樂(lè)．?dāng)?shù)據(jù)庫(kù)系統(tǒng)教程（第2版）[M].北京：北京高等教育出版社，2003．

[2]趙杰，李濤，朱慧．SQL Server數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)教程[M].北京：北京大學(xué)出版社，2003．

[3]杜軍平，黃杰．SQL Server 2000數(shù)據(jù)庫(kù)開(kāi)發(fā)[M]．北京：機(jī)械工業(yè)出版社，2001．

[4]溫春明．SQL Server 存儲(chǔ)過(guò)程研究[J]．福建電腦，2007．

[5]曹耀輝．SQL Server存儲(chǔ)過(guò)程在系統(tǒng)開(kāi)發(fā)中的應(yīng)用[J]．現(xiàn)代電子技術(shù)，2005．

[6]劉獨(dú)玉，羅彬．基于MS SQL Server的存儲(chǔ)過(guò)程技術(shù)的研究[J]．四川輕化工學(xué)院學(xué)報(bào)，2001．

[7]張義德，于西儉．基于SQL Server的存儲(chǔ)過(guò)程的研究[J]．航空計(jì)算技術(shù)，2001．

[8]劉云根，陳萌．存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)編程中的應(yīng)用[J]．科技廣場(chǎng)，2005．

[9]王雪媛．存儲(chǔ)過(guò)程研究[J]．內(nèi)江科技，2006．

[10]陸鑫．存儲(chǔ)過(guò)程及其應(yīng)用方法[J]．計(jì)算機(jī)應(yīng)用，1999．

作者簡(jiǎn)介：胡曉波（1979－），男（土家族），湘西民族職業(yè)技術(shù)學(xué)院教師，研究方向：計(jì)算機(jī)教學(xué)。