SQL Server 2008中的存儲(chǔ)過程的研究及應(yīng)用

張梅梅++宋正明++李豪++王影

[摘要]存儲(chǔ)過程是一種重要的數(shù)據(jù)庫對象，恰當(dāng)?shù)厥褂么鎯?chǔ)過程可以簡化應(yīng)用程序的開發(fā)，提高系統(tǒng)的運(yùn)行性能。文章以SQL Server 2008的存儲(chǔ)過程為例，闡述了存儲(chǔ)過程的定義、特點(diǎn)及其參數(shù)的定義，主要研究了存儲(chǔ)過程中出口參數(shù)和入口參數(shù)的使用，并在Visual C#中實(shí)現(xiàn)調(diào)用存儲(chǔ)過程的方法。

[關(guān)鍵詞]SQL Server；存儲(chǔ)過程；出口參數(shù)；入口參數(shù)；Visual Studio C#

[DOI]10.13939/j.cnki.zgsc.2016.23.79

1引言

在以數(shù)據(jù)庫為核心的大中型應(yīng)用系統(tǒng)中，通常需要處理海量數(shù)據(jù)，數(shù)據(jù)的存儲(chǔ)和查詢速度直接影響應(yīng)用系統(tǒng)的性能。存儲(chǔ)過程是一組保存在數(shù)據(jù)庫中的，經(jīng)過預(yù)先編譯和優(yōu)化的，執(zhí)行數(shù)據(jù)庫操作的SQL語句。存儲(chǔ)過程可以作單獨(dú)的數(shù)據(jù)庫對象，也可以作為一個(gè)單元被用戶的應(yīng)用程序調(diào)用。存儲(chǔ)過程作為數(shù)據(jù)庫對象存儲(chǔ)在數(shù)據(jù)庫中，執(zhí)行一次后，其編譯好的代碼存儲(chǔ)在高速緩沖存儲(chǔ)器中，再次使用時(shí)直接調(diào)用，無需再次編譯即可執(zhí)行，使執(zhí)行速度大大提高。因此，使用存儲(chǔ)過程完成對數(shù)據(jù)庫的操作，可以有效提高數(shù)據(jù)存儲(chǔ)和查詢的速度。同時(shí)，存儲(chǔ)過程還可以降低整個(gè)應(yīng)用程序的復(fù)雜性，提高重用性、安全性和可伸縮性。本文詳細(xì)介紹在Visual C#開發(fā)平臺(tái)下，主要用Transact-SQL編寫存儲(chǔ)過程存儲(chǔ)在SQL Server中，并創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，返回結(jié)果，然后對數(shù)據(jù)結(jié)果進(jìn)行處理。且都提供對存儲(chǔ)過程直接訪問的功能。通過具體的例子，介紹了在SQL Server數(shù)據(jù)庫中如何使用存儲(chǔ)過程實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)處理功能。

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

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

2存儲(chǔ)過程的特點(diǎn)

第一，執(zhí)行速度快：存儲(chǔ)過程在創(chuàng)建時(shí)就經(jīng)過了語法檢查和性能優(yōu)化，因此在執(zhí)行時(shí)不必再重復(fù)這些步驟。存儲(chǔ)過程經(jīng)過第一次調(diào)用后，駐留在內(nèi)存中，不必再編譯和優(yōu)化，所以執(zhí)行速度很快。第二，模塊化的程序設(shè)計(jì)：存儲(chǔ)過程經(jīng)過了一次創(chuàng)建以后，可以被無數(shù)次調(diào)用。用戶可以獨(dú)立于應(yīng)用程序而對存儲(chǔ)過程進(jìn)行修改?？梢园凑展δ苣K的不同，設(shè)計(jì)不同的存儲(chǔ)過程以供使用。第三，減少網(wǎng)絡(luò)通信量：存儲(chǔ)過程中可以包含大量的Transact-SQL語句。但存儲(chǔ)過程在調(diào)用時(shí)只需一條語句就可以實(shí)現(xiàn)，所以大大減少了網(wǎng)絡(luò)上數(shù)據(jù)通信的傳輸。第四，保證系統(tǒng)的安全性：設(shè)置用戶通過存儲(chǔ)過程來對某些關(guān)鍵數(shù)據(jù)進(jìn)行訪問，但不允許用戶直接使用Transact-SQL或企業(yè)管理器來對數(shù)據(jù)進(jìn)行訪問。

3存儲(chǔ)過程

3.1存儲(chǔ)過程的創(chuàng)建

存儲(chǔ)過程是用過程頭定義的，過程頭包括關(guān)鍵詞Procedure、過程名和參數(shù)清單，緊接過程頭后可定義任何過程范圍的變量，代碼的主體被定義為一個(gè)或多個(gè)塊，塊使用Begin和End語句限定范圍，還可以附帶錯(cuò)誤處理Exception塊。所有過程的定義都必須用End語句作為結(jié)束。創(chuàng)建存儲(chǔ)過程可使用SQLPLUS、Transact-SQL、可視化數(shù)據(jù)庫工具（Visual Database Tools）、Oracle過程生成器（Oracle Procedure Builder）等工具。

3.2存儲(chǔ)過程的結(jié)構(gòu)和定義語法

3.3存儲(chǔ)過程的參數(shù)

參數(shù)用于在存儲(chǔ)過程和調(diào)用存儲(chǔ)過程的應(yīng)用程序或工具之間交換數(shù)據(jù)，存儲(chǔ)過程可以有零個(gè)或多個(gè)參數(shù)，這些參數(shù)會(huì)被用于程序邏輯中確定的某些動(dòng)作，參數(shù)名和參數(shù)的數(shù)據(jù)類型被定義于程序設(shè)計(jì)單元的首部。存儲(chǔ)過程的參數(shù)按其用途可分為兩類：入口參數(shù)和出口參數(shù)。入口參數(shù)可用來把值傳遞給存儲(chǔ)過程使用。入口參數(shù)的語法如下：

Create Procedure_name @param1 data_type，@paramN datatype

例如：create procedure usp_input @temp_name varchar（30） @total int，@current_date datetime

出口參數(shù)采用變量的形式向調(diào)用過程或程序返回結(jié)果值。聲明出口參數(shù)的語法如下：

Create procedure procedure_name @parameter_name datatype OUTPUT

例如：create procedure usp_test @p1 int OUT

Declare @v_prodcount INTEGER

存儲(chǔ)過程參數(shù)的傳遞方式主要有標(biāo)準(zhǔn)方式（按值傳遞）和使用參數(shù)名（按名傳遞）。

3.4存儲(chǔ)過程的執(zhí)行

用EXECUTE語句可以運(yùn)行一個(gè)存儲(chǔ)過程。語法如下：

3.5存儲(chǔ)過程的刪除

不再需要存儲(chǔ)過程時(shí)可將其刪除。語法如下：

3.6存儲(chǔ)過程的調(diào)用

存儲(chǔ)過程的應(yīng)用方法是多種多樣的，在不同的編程語言中使用的語法也各有差異。但調(diào)用存儲(chǔ)過程的思路是基本一致的，一般可歸納為：①連接數(shù)據(jù)庫；②定義輸入輸出參數(shù)；③執(zhí)行存儲(chǔ)過程；④關(guān)閉數(shù)據(jù)庫連接。

4存儲(chǔ)過程的應(yīng)用實(shí)例

為了提高數(shù)據(jù)的運(yùn)行效率，在我們設(shè)計(jì)的教務(wù)管理系統(tǒng)中，我們應(yīng)用了如下的存儲(chǔ)過程從管理員角度創(chuàng)建增、刪、改、查幾個(gè)存儲(chǔ)過程：

（1）增加管理員。

5結(jié)論

本文以SQL Server 2008數(shù)據(jù)庫為例，通過典型的實(shí)例，介紹了使用存儲(chǔ)過程進(jìn)行復(fù)雜數(shù)據(jù)處理的方法，開發(fā)人員稍加修改即可用于自己的應(yīng)用程序開發(fā)。此外，從以上應(yīng)用實(shí)例，我們可以體會(huì)到存儲(chǔ)過程技術(shù)在數(shù)據(jù)編程方面的強(qiáng)大功能。在實(shí)際網(wǎng)絡(luò)系統(tǒng)的開發(fā)工作中，由于存儲(chǔ)過程的引入，可以使開發(fā)者的編程效率大大提高。

在數(shù)據(jù)庫系統(tǒng)開發(fā)中，使用存儲(chǔ)過程可以提高整個(gè)系統(tǒng)與數(shù)據(jù)庫交互的性能，可以讓數(shù)據(jù)庫管理系統(tǒng)來優(yōu)化對數(shù)據(jù)庫的存取，但存儲(chǔ)過程中業(yè)務(wù)邏輯也并非用得越多越好。所以在實(shí)際工作中，要根據(jù)具體情況和實(shí)際工作經(jīng)驗(yàn)來確定如何編寫存儲(chǔ)過程。

參考文獻(xiàn)：

[1]馮洪峰.存儲(chǔ)過程在SQL Server2005中應(yīng)用[J].電腦編程技巧與維護(hù)，2010（14）.

[2]蔣社想，程翠平.基于SQL Server存儲(chǔ)過程的評(píng)標(biāo)專家系統(tǒng)的研究與實(shí)現(xiàn)[J].長沙通信職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2011（3）.

[3]韋晨艷，楊鍵鳴，姚斯立.SQL數(shù)據(jù)庫中存儲(chǔ)過程.觸發(fā)器的應(yīng)用研究[J].中國信息界，2011（6）.

[4]凃云杰.存儲(chǔ)過程在教學(xué)管理信息系統(tǒng)中的靈活應(yīng)用[J].電子制作，2014（18）.

[5]楊銘.SQL Server中存儲(chǔ)過程技術(shù)的研究[J].科技視界，2014（34）.