陳瀟
摘 要:在應(yīng)用程序開(kāi)發(fā)過(guò)程中,如何避免客戶機(jī)執(zhí)行大量的數(shù)據(jù)訪問(wèn)工作,提高客戶端的工作效率,是程序設(shè)計(jì)者需要考慮的問(wèn)題。本文介紹了SQL Server 2008中存儲(chǔ)過(guò)程的含義及優(yōu)點(diǎn),并通過(guò)具體實(shí)例向讀者介紹存儲(chǔ)過(guò)程的具體應(yīng)用,從而在系統(tǒng)開(kāi)發(fā)時(shí)能夠靈活運(yùn)用存儲(chǔ)過(guò)程提高效率。
關(guān)鍵詞:SQLServer;數(shù)據(jù)庫(kù);存儲(chǔ)過(guò)程
中圖分類號(hào):TP311.13 文獻(xiàn)標(biāo)識(shí)碼:A
1 引言(Introduction)
當(dāng)應(yīng)用程序需要訪問(wèn)服務(wù)器上的數(shù)據(jù)時(shí),在不建立存儲(chǔ)過(guò)程的條件下。通過(guò)網(wǎng)絡(luò)將Transact-SQL語(yǔ)句發(fā)送至服務(wù)器,服務(wù)器對(duì)語(yǔ)句編譯后在傳遞給客戶端。這種方式對(duì)于大量數(shù)據(jù)處理的事務(wù),增加了網(wǎng)絡(luò)上的傳輸量,加重了客戶端的工作負(fù)擔(dān)[1]。
存儲(chǔ)過(guò)程是一組Transact-SQL語(yǔ)句,編譯在單個(gè)執(zhí)行計(jì)劃中。它在服務(wù)器端對(duì)數(shù)據(jù)庫(kù)記錄進(jìn)行處理,然后將結(jié)果發(fā)給客戶端。這樣,既充分利用了服務(wù)器強(qiáng)大的計(jì)算能力,也避免應(yīng)用程序執(zhí)行時(shí)需要將大量數(shù)據(jù)從服務(wù)器下載到客戶端,減少了網(wǎng)絡(luò)上的傳輸量,同時(shí)也提高了客戶端的工作效率。
存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn):
(1)通過(guò)本地存儲(chǔ)、代碼預(yù)編譯和緩存技術(shù)實(shí)現(xiàn)高性能的數(shù)據(jù)操作。
(2)如果業(yè)務(wù)規(guī)則發(fā)生了改變,可以通過(guò)修改存儲(chǔ)過(guò)程來(lái)適應(yīng)新的業(yè)務(wù)規(guī)則,而不必修改客戶端應(yīng)用程序。
(3)通過(guò)隔離和加密的方法提高了數(shù)據(jù)庫(kù)的安全性。數(shù)據(jù)庫(kù)用戶可以通過(guò)得到權(quán)限來(lái)執(zhí)行存儲(chǔ)過(guò)程,而不必給予用戶直接訪問(wèn)數(shù)據(jù)庫(kù)對(duì)象的權(quán)限。
存儲(chǔ)過(guò)程按返回的數(shù)據(jù)類型,可分為兩類:一類類似于SELECT語(yǔ)句,用于查詢數(shù)據(jù),查詢到的數(shù)據(jù)以結(jié)果集的形式給出;另一類存儲(chǔ)過(guò)程是通過(guò)輸出參數(shù)返回信息,或不返回信息只執(zhí)行一個(gè)動(dòng)作。
2 簡(jiǎn)單存儲(chǔ)過(guò)程的建立(The process of establishing
a simple stored procedure)
創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程的基本語(yǔ)法如下:
CREATE PROCEDURE存儲(chǔ)過(guò)程名
[WITH ENCRYPTION]
[WITH RECOMPILE]
AS
SQL語(yǔ)句
案例1:為了方便對(duì)客戶訂購(gòu)信息的查詢,我們建立一個(gè)存儲(chǔ)過(guò)程khdg,功能是查詢訂單信息表中客戶訂購(gòu)的相關(guān)信息。在查詢?cè)O(shè)計(jì)器中運(yùn)行如下命令:
Create procedure dbo.khdg
As
Select姓名,地址,訂購(gòu)數(shù)量=sum(數(shù)量)from訂單信息groupby客戶編號(hào)
執(zhí)行該存儲(chǔ)過(guò)程的具體代碼如下:
Execute procedure khdg
3 帶參數(shù)的存儲(chǔ)過(guò)程(The stored procedure with
parameters)
向存儲(chǔ)過(guò)程指定輸入、輸出參數(shù)的主要目的是通過(guò)參數(shù)向存儲(chǔ)過(guò)程輸入和輸出信息來(lái)擴(kuò)展存儲(chǔ)過(guò)程的功能[2]。通過(guò)使用參數(shù),可以多次使用同一存儲(chǔ)過(guò)程并按用戶要求查找所需要的結(jié)果。
3.1 建立帶參數(shù)存儲(chǔ)過(guò)程
聲明帶輸入?yún)?shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下。
CREATEPROCEDURE存儲(chǔ)過(guò)程名
@參數(shù)名 數(shù)據(jù)類型[=默認(rèn)值][,...n]
[WITHENCRYPTION]
[WITHRECOMPILE]
AS
SQL語(yǔ)句
其中“@參數(shù)名”和定義局部變量一樣,必須以符號(hào)@為前綴,要指定數(shù)據(jù)類型,多個(gè)參數(shù)定義要用“,”隔開(kāi)。
案例2:為了方便統(tǒng)計(jì)銷售員在訂單信息表中的簽單個(gè)數(shù),我們自定義一個(gè)存儲(chǔ)過(guò)程Qdgs,該存儲(chǔ)過(guò)程的功能是:接受客戶端傳遞的銷售人員編號(hào),在服務(wù)器端完成該銷售人員簽單個(gè)數(shù)的查詢后,將結(jié)果返回給用戶。在查詢?cè)O(shè)計(jì)器中運(yùn)行如下命令:
CreateprocedureQdgs
@xsghint
Select簽單個(gè)數(shù)=count(*)from訂單信息where銷售工
號(hào)=@xsgh
Groupby銷售工號(hào)
案例3:建立一個(gè)多參數(shù)存儲(chǔ)過(guò)程Khdg,功能是:接收用戶輸入的客戶姓名和年份,查詢?cè)摽蛻粼谥付ú樵兡攴輧?nèi)的訂購(gòu)信息,返回給客戶端。
CreateprocedureKhdg
@khxmvarchar(6),@nfint
Select客戶姓名=姓名,貨品名稱=名稱,訂貨日期,數(shù)量,總金額
From客戶信息,訂單信息,貨品信息
Where客戶信息.編號(hào)=訂單信息.客戶編號(hào)and訂單信息.貨品編碼=貨品信息.編碼and客戶信息.姓名=@khxmanddatepart(yy,訂購(gòu)日期)=@nf
3.2 執(zhí)行存儲(chǔ)過(guò)程
在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中,有兩種方式來(lái)傳遞參數(shù)值,分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。
使用參數(shù)名傳遞參數(shù)值,是通過(guò)語(yǔ)句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。
3.2.1 使用參數(shù)名傳遞參數(shù)
執(zhí)行案例2的存儲(chǔ)過(guò)程
Execute procedureQdgs @xsgh=1
執(zhí)行結(jié)果如圖1所示。
圖1 存儲(chǔ)過(guò)程執(zhí)行結(jié)果
Fig.1 Perform stored procedures
3.2.2 按位置傳遞參數(shù)
執(zhí)行案例3的存儲(chǔ)過(guò)程:Execute procedureKhdg‘李紅,‘2003
執(zhí)行結(jié)果如圖2所示。
圖2 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程執(zhí)行結(jié)果
Fig.2 The results of the stored procedure with the input
parameters are implemented
4 帶輸出參數(shù)的存儲(chǔ)過(guò)程
如果我們需要從存儲(chǔ)過(guò)程中返回一個(gè)或多個(gè)值,可以通過(guò)在創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句中定義輸出參數(shù)來(lái)實(shí)現(xiàn),為了使用輸出參數(shù),需要在CREATEPROCEDURE語(yǔ)句中指定OUTPUT關(guān)鍵字。
聲明帶輸出參數(shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下。
CREATEPROCEDURE 存儲(chǔ)過(guò)程名
@參數(shù)名 數(shù)據(jù)類型[VARYING][=默認(rèn)值]OUTPUT[,...n]
[WITHENCRYPTION]
[WITHRECOMPILE]
AS
SQL語(yǔ)句
案例4:建立一個(gè)存儲(chǔ)過(guò)程Rqcx,功能是通過(guò)接收用戶輸入的訂單號(hào),來(lái)查詢?cè)摴P訂單到從訂購(gòu)到發(fā)貨共歷時(shí)多少天。
CreateprocedureRqcx
@ddh output int,@ts bigint output
As
Declare @dhrq datetime,@jhrq datetime
Select@dhrq=訂貨日期,@jhrq=交貨日f(shuō)rom訂單信息where訂單號(hào)=@ddh
Set@ts=datediff(dd,@dhrq,@jhrq)
Print'訂單號(hào)為:'+@ddh+'的訂單,從簽單到發(fā)貨共歷時(shí):'+@ts+'天'
執(zhí)行案例4的存儲(chǔ)過(guò)程:Execute procedureKhdg‘1
執(zhí)行結(jié)果如圖3所示。
圖3 帶輸出參數(shù)的存儲(chǔ)過(guò)程執(zhí)行結(jié)果
Fig.3 The results of the stored procedure with output
parameters are implemented
5 結(jié)論(Conclusion)
通過(guò)存儲(chǔ)過(guò)程,提高了客戶端的工作效率,增強(qiáng)系統(tǒng)的可維護(hù)性。在保證服務(wù)器端數(shù)據(jù)安全的前提下,體現(xiàn)了數(shù)據(jù)的一致性和完整性[3]。在進(jìn)行程序設(shè)計(jì)和數(shù)據(jù)庫(kù)管理工作時(shí),可根據(jù)實(shí)際情況靈活運(yùn)用存儲(chǔ)。
參考文獻(xiàn)(References)
[1] 萬(wàn)波,周順平.SQL+SERVER擴(kuò)展存儲(chǔ)過(guò)程實(shí)現(xiàn)機(jī)制及應(yīng)用 方法初探[J].武漢科技大學(xué)學(xué)報(bào)(自然科學(xué)版),2000(3):294-296.
[2] 梁德勝,楊曉燕,陳春娥.基于SQL Server的擴(kuò)展存儲(chǔ)過(guò)程[J].現(xiàn) 代電子技術(shù),2004,27(21):22-23.
[3] 宋世斌,李存華.SQL Server中存儲(chǔ)過(guò)程技術(shù)的研究與應(yīng)用[J]. 電腦知識(shí)與技術(shù)(學(xué)術(shù)交流),2007,1(12):327-329.
作者簡(jiǎn)介:
陳 瀟(1983-),男,碩士,講師.研究領(lǐng)域:數(shù)據(jù)庫(kù)管理,管
理信息系統(tǒng).