陳瀟

摘 要：在應(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).