SQL Server 2008中的存儲過程的研究及應用

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

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

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

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

1引言

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

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

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

2存儲過程的特點

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

3存儲過程

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

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

3.2存儲過程的結構和定義語法

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

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

Create Procedure_name @param1 data_type，@paramN datatype

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

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

Create procedure procedure_name @parameter_name datatype OUTPUT

例如：create procedure usp_test @p1 int OUT

Declare @v_prodcount INTEGER

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

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

用EXECUTE語句可以運行一個存儲過程。語法如下：

3.5存儲過程的刪除

不再需要存儲過程時可將其刪除。語法如下：

3.6存儲過程的調用

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

4存儲過程的應用實例

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

（1）增加管理員。

5結論

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

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

參考文獻：

[1]馮洪峰.存儲過程在SQL Server2005中應用[J].電腦編程技巧與維護，2010（14）.

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

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

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

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