SQLServer2008數(shù)據(jù)庫存儲過程的應(yīng)用

陳瀟

摘 要：在應(yīng)用程序開發(fā)過程中，如何避免客戶機執(zhí)行大量的數(shù)據(jù)訪問工作，提高客戶端的工作效率，是程序設(shè)計者需要考慮的問題。本文介紹了SQL Server 2008中存儲過程的含義及優(yōu)點，并通過具體實例向讀者介紹存儲過程的具體應(yīng)用，從而在系統(tǒng)開發(fā)時能夠靈活運用存儲過程提高效率。

關(guān)鍵詞：SQLServer；數(shù)據(jù)庫；存儲過程

中圖分類號：TP311.13 文獻標(biāo)識碼：A

1 引言（Introduction）

當(dāng)應(yīng)用程序需要訪問服務(wù)器上的數(shù)據(jù)時，在不建立存儲過程的條件下。通過網(wǎng)絡(luò)將Transact-SQL語句發(fā)送至服務(wù)器，服務(wù)器對語句編譯后在傳遞給客戶端。這種方式對于大量數(shù)據(jù)處理的事務(wù)，增加了網(wǎng)絡(luò)上的傳輸量，加重了客戶端的工作負擔(dān)[1]。

存儲過程是一組Transact-SQL語句，編譯在單個執(zhí)行計劃中。它在服務(wù)器端對數(shù)據(jù)庫記錄進行處理，然后將結(jié)果發(fā)給客戶端。這樣，既充分利用了服務(wù)器強大的計算能力，也避免應(yīng)用程序執(zhí)行時需要將大量數(shù)據(jù)從服務(wù)器下載到客戶端，減少了網(wǎng)絡(luò)上的傳輸量，同時也提高了客戶端的工作效率。

存儲過程具有以下優(yōu)點：

（1）通過本地存儲、代碼預(yù)編譯和緩存技術(shù)實現(xiàn)高性能的數(shù)據(jù)操作。

（2）如果業(yè)務(wù)規(guī)則發(fā)生了改變，可以通過修改存儲過程來適應(yīng)新的業(yè)務(wù)規(guī)則，而不必修改客戶端應(yīng)用程序。

（3）通過隔離和加密的方法提高了數(shù)據(jù)庫的安全性。數(shù)據(jù)庫用戶可以通過得到權(quán)限來執(zhí)行存儲過程，而不必給予用戶直接訪問數(shù)據(jù)庫對象的權(quán)限。

存儲過程按返回的數(shù)據(jù)類型，可分為兩類：一類類似于SELECT語句，用于查詢數(shù)據(jù)，查詢到的數(shù)據(jù)以結(jié)果集的形式給出；另一類存儲過程是通過輸出參數(shù)返回信息，或不返回信息只執(zhí)行一個動作。

2 簡單存儲過程的建立（The process of establishing

a simple stored procedure）

創(chuàng)建簡單存儲過程的基本語法如下：

CREATE PROCEDURE存儲過程名

[WITH ENCRYPTION]

[WITH RECOMPILE]

AS

SQL語句

案例1：為了方便對客戶訂購信息的查詢，我們建立一個存儲過程khdg，功能是查詢訂單信息表中客戶訂購的相關(guān)信息。在查詢設(shè)計器中運行如下命令：

Create procedure dbo.khdg

As

Select姓名，地址，訂購數(shù)量=sum（數(shù)量）from訂單信息groupby客戶編號

執(zhí)行該存儲過程的具體代碼如下：

Execute procedure khdg

3 帶參數(shù)的存儲過程（The stored procedure with

parameters）

向存儲過程指定輸入、輸出參數(shù)的主要目的是通過參數(shù)向存儲過程輸入和輸出信息來擴展存儲過程的功能[2]。通過使用參數(shù)，可以多次使用同一存儲過程并按用戶要求查找所需要的結(jié)果。

3.1 建立帶參數(shù)存儲過程

聲明帶輸入?yún)?shù)的存儲過程的語法格式如下。

CREATEPROCEDURE存儲過程名

@參數(shù)名 數(shù)據(jù)類型[=默認值][，...n]

[WITHENCRYPTION]

[WITHRECOMPILE]

AS

SQL語句

其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指定數(shù)據(jù)類型，多個參數(shù)定義要用“，”隔開。

案例2：為了方便統(tǒng)計銷售員在訂單信息表中的簽單個數(shù)，我們自定義一個存儲過程Qdgs，該存儲過程的功能是：接受客戶端傳遞的銷售人員編號，在服務(wù)器端完成該銷售人員簽單個數(shù)的查詢后，將結(jié)果返回給用戶。在查詢設(shè)計器中運行如下命令：

CreateprocedureQdgs

@xsghint

Select簽單個數(shù)=count（*）from訂單信息where銷售工

號=@xsgh

Groupby銷售工號

案例3：建立一個多參數(shù)存儲過程Khdg，功能是：接收用戶輸入的客戶姓名和年份，查詢該客戶在指定查詢年份內(nèi)的訂購信息，返回給客戶端。

CreateprocedureKhdg

@khxmvarchar（6），@nfint

Select客戶姓名=姓名，貨品名稱=名稱，訂貨日期，數(shù)量，總金額

From客戶信息，訂單信息，貨品信息

Where客戶信息.編號=訂單信息.客戶編號and訂單信息.貨品編碼=貨品信息.編碼and客戶信息.姓名=@khxmanddatepart（yy，訂購日期）=@nf

3.2 執(zhí)行存儲過程

在執(zhí)行存儲過程的語句中，有兩種方式來傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。

使用參數(shù)名傳遞參數(shù)值，是通過語句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。

3.2.1 使用參數(shù)名傳遞參數(shù)

執(zhí)行案例2的存儲過程

Execute procedureQdgs @xsgh=1

執(zhí)行結(jié)果如圖1所示。

圖1 存儲過程執(zhí)行結(jié)果

Fig.1 Perform stored procedures

3.2.2 按位置傳遞參數(shù)

執(zhí)行案例3的存儲過程：Execute procedureKhdg‘李紅，‘2003

執(zhí)行結(jié)果如圖2所示。

圖2 帶輸入?yún)?shù)的存儲過程執(zhí)行結(jié)果

Fig.2 The results of the stored procedure with the input

parameters are implemented

4 帶輸出參數(shù)的存儲過程

如果我們需要從存儲過程中返回一個或多個值，可以通過在創(chuàng)建存儲過程的語句中定義輸出參數(shù)來實現(xiàn)，為了使用輸出參數(shù)，需要在CREATEPROCEDURE語句中指定OUTPUT關(guān)鍵字。

聲明帶輸出參數(shù)的存儲過程的語法格式如下。

CREATEPROCEDURE 存儲過程名

@參數(shù)名 數(shù)據(jù)類型[VARYING][=默認值]OUTPUT[，...n]

[WITHENCRYPTION]

[WITHRECOMPILE]

AS

SQL語句

案例4：建立一個存儲過程Rqcx，功能是通過接收用戶輸入的訂單號，來查詢該筆訂單到從訂購到發(fā)貨共歷時多少天。

CreateprocedureRqcx

@ddh output int，@ts bigint output

As

Declare @dhrq datetime，@jhrq datetime

Select@dhrq=訂貨日期，@jhrq=交貨日from訂單信息where訂單號=@ddh

Set@ts=datediff（dd，@dhrq，@jhrq）

Print'訂單號為：'+@ddh+'的訂單，從簽單到發(fā)貨共歷時：'+@ts+'天'

執(zhí)行案例4的存儲過程：Execute procedureKhdg‘1

執(zhí)行結(jié)果如圖3所示。

圖3 帶輸出參數(shù)的存儲過程執(zhí)行結(jié)果

Fig.3 The results of the stored procedure with output

parameters are implemented

5 結(jié)論（Conclusion）

通過存儲過程，提高了客戶端的工作效率，增強系統(tǒng)的可維護性。在保證服務(wù)器端數(shù)據(jù)安全的前提下，體現(xiàn)了數(shù)據(jù)的一致性和完整性[3]。在進行程序設(shè)計和數(shù)據(jù)庫管理工作時，可根據(jù)實際情況靈活運用存儲。

參考文獻（References）

[1] 萬波，周順平.SQL+SERVER擴展存儲過程實現(xiàn)機制及應(yīng)用 方法初探[J].武漢科技大學(xué)學(xué)報（自然科學(xué)版），2000（3）：294-296.

[2] 梁德勝，楊曉燕，陳春娥.基于SQL Server的擴展存儲過程[J].現(xiàn) 代電子技術(shù)，2004，27（21）：22-23.

[3] 宋世斌，李存華.SQL Server中存儲過程技術(shù)的研究與應(yīng)用[J]. 電腦知識與技術(shù)（學(xué)術(shù)交流），2007，1（12）：327-329.

作者簡介：

陳 瀟（1983-），男，碩士，講師.研究領(lǐng)域：數(shù)據(jù)庫管理，管

理信息系統(tǒng).