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

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

中圖分類號：TP393文獻標(biāo)識碼：A文章編號：1009-2374（2009）07-0118-03

一、SQL Server的存儲過程概述

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

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

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

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

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

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

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

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

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

CREATEPROCEDUREup_jsszl

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

@tjyfinint，

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

AS

BEGIN

DECLARE@ylfszmoney，

@ylfyzmoney//聲明局部變量

......

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

RETURN

END

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

CREATEPROCEDUREup_bjszl

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

@tjyfinint

AS

BEGIN

DECLARE

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

@tjyfint，

@ylfszlmoney

... ...

EXECUTEup_jsszl

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

@tjyf，

@ylfszl

output

... ...

RETURN

END

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

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

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// 打開游標(biāo)

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

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

BEGIN

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

@tjyfin，

@ylfszloutput

......

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

END

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

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

......

END

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

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

CREATEPROCEDUREup_cxszl

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

@tjyfinint

AS

BEGIN

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

@ylfszlmoney

......

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

YLFSZLmoneynull）

......

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

SELECT JZSNO FROM JZS WHERE FJNO = @zsbmnoin

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

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

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

BEGIN

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

@tjyfin

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

VALUES（@bmno，@ylfszl）

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

END

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

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

RETURN

END

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

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

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

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

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

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

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

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

四、結(jié)語

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

參考文獻

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

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

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

[4]溫春明．SQL Server 存儲過程研究[J]．福建電腦，2007．

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

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

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

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

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

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

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