SQLServer2008數據庫存儲過程的應用

陳瀟

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

關鍵詞：SQLServer；數據庫；存儲過程

中圖分類號：TP311.13 文獻標識碼：A

1 引言（Introduction）

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

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

存儲過程具有以下優點：

（1）通過本地存儲、代碼預編譯和緩存技術實現高性能的數據操作。

（2）如果業務規則發生了改變，可以通過修改存儲過程來適應新的業務規則，而不必修改客戶端應用程序。

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

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

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

a simple stored procedure）

創建簡單存儲過程的基本語法如下：

CREATE PROCEDURE存儲過程名

[WITH ENCRYPTION]

[WITH RECOMPILE]

AS

SQL語句

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

Create procedure dbo.khdg

As

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

執行該存儲過程的具體代碼如下：

Execute procedure khdg

3 帶參數的存儲過程（The stored procedure with

parameters）

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

3.1 建立帶參數存儲過程

聲明帶輸入參數的存儲過程的語法格式如下。

CREATEPROCEDURE存儲過程名

@參數名 數據類型[=默認值][，...n]

[WITHENCRYPTION]

[WITHRECOMPILE]

AS

SQL語句

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

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

CreateprocedureQdgs

@xsghint

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

號=@xsgh

Groupby銷售工號

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

CreateprocedureKhdg

@khxmvarchar（6），@nfint

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

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

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

3.2 執行存儲過程

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

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

3.2.1 使用參數名傳遞參數

執行案例2的存儲過程

Execute procedureQdgs @xsgh=1

執行結果如圖1所示。

圖1 存儲過程執行結果

Fig.1 Perform stored procedures

3.2.2 按位置傳遞參數

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

執行結果如圖2所示。

圖2 帶輸入參數的存儲過程執行結果

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

parameters are implemented

4 帶輸出參數的存儲過程

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

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

CREATEPROCEDURE 存儲過程名

@參數名 數據類型[VARYING][=默認值]OUTPUT[，...n]

[WITHENCRYPTION]

[WITHRECOMPILE]

AS

SQL語句

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

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+'的訂單，從簽單到發貨共歷時：'+@ts+'天'

執行案例4的存儲過程：Execute procedureKhdg‘1

執行結果如圖3所示。

圖3 帶輸出參數的存儲過程執行結果

Fig.3 The results of the stored procedure with output

parameters are implemented

5 結論（Conclusion）

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

參考文獻（References）

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

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

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

作者簡介：

陳 瀟（1983-），男，碩士，講師.研究領域：數據庫管理，管

理信息系統.