SQL Server存儲(chǔ)過(guò)程是一個(gè)被命名的存儲(chǔ)在服務(wù)器上的Transacation-Sql語(yǔ)句集合,是封裝重復(fù)性工作的一種方法,它支持用戶聲明的變量、條件執(zhí)行和其他強(qiáng)大的編程功能。

SQL Server存儲(chǔ)過(guò)程相對(duì)于其他的數(shù)據(jù)庫(kù)訪問(wèn)方法有以下的優(yōu)點(diǎn)：

(1)重復(fù)使用。存儲(chǔ)過(guò)程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。

(2)提高性能。存儲(chǔ)過(guò)程在創(chuàng)建的時(shí)候就進(jìn)行了編譯，將來(lái)使用的時(shí)候不用再重新編譯。一般的SQL語(yǔ)句每執(zhí)行一次就需要編譯一次，所以使用存儲(chǔ)過(guò)程提高了效率。

(3)減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程位于服務(wù)器上，調(diào)用的時(shí)候只需要傳遞存儲(chǔ)過(guò)程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。

(4)安全性。參數(shù)化的存儲(chǔ)過(guò)程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權(quán)限應(yīng)用于存儲(chǔ)過(guò)程。

SQL Server存儲(chǔ)過(guò)程一共分為了三類：用戶定義的存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程以及系統(tǒng)存儲(chǔ)過(guò)程。

其中，用戶定義的存儲(chǔ)過(guò)程又分為T(mén)ransaction-SQL和CLR兩種類型。

1.Transaction-SQL 存儲(chǔ)過(guò)程是指保存的Transaction-SQL語(yǔ)句集合，可以接受和返回用戶提供的參數(shù)。

2.CLR存儲(chǔ)過(guò)程是指對(duì).Net Framework公共語(yǔ)言運(yùn)行時(shí)(CLR)方法的引用，可以接受和返回用戶提供的參數(shù)。他們?cè)?Net Framework程序集中是作為類的公共靜態(tài)方法實(shí)現(xiàn)的。(本文就不作介紹了)

創(chuàng)建SQL Server存儲(chǔ)過(guò)程的語(yǔ)句如下：

以下為引用的內(nèi)容：

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }   
        [ VARYING ] [ = default ] [ [ OUT [ PUT ]   
    ] [ ,n ]   
[ WITH < procedure_option> [ ,n ]  
[ FOR REPLICATION ]   
AS { < sql_statement> [;][ n ] | < method_specifier> }  
[;]  
< procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE_AS_Clause ]  
 
< sql_statement> ::=   
{ [ BEGIN ] statements [ END ] }  
 
< method_specifier> ::=  
EXTERNAL NAME assembly_name.class_name.method_name  
 

[schema_name]: 代表的是存儲(chǔ)過(guò)程所屬的架構(gòu)的名稱

例如：

Create Schema yangyang8848        
Go  
Create Proc yangyang8848.AllGoods  
As Select * From Master_Goods  
Go 

執(zhí)行：Exec AllGoods 發(fā)生錯(cuò)誤。

 執(zhí)行：Exec yangyang8848.AllGoods 正確執(zhí)行。

[；Number]: 用于對(duì)同名過(guò)程進(jìn)行分組的可選整數(shù)。使用一個(gè) DROP PROCEDURE 語(yǔ)句可將這些分組過(guò)程一起刪除。

例如：

Create Proc S1 ;1  
AS  
       Select * From Master_Goods  
Go  
Create Proc S1 ;2  
As   
       Select * From Master_Location  
Go 

創(chuàng)建完畢了兩個(gè)存儲(chǔ)過(guò)程。它們?cè)谕粋€(gè)組S1里，如果執(zhí)行Exec S1 則存儲(chǔ)過(guò)程默認(rèn)執(zhí)行 Exec S1 ;1 。如果我們想得到所有據(jù)點(diǎn)信息則需要執(zhí)行Exec S1 ;2。當(dāng)我們要?jiǎng)h除存儲(chǔ)過(guò)程的時(shí)候，只能執(zhí)行Drop Exec S1 則該組內(nèi)所有的存儲(chǔ)過(guò)程被刪除。

[@ parameter]: 存儲(chǔ)過(guò)程中的參數(shù)，除非將參數(shù)定義的時(shí)候有默認(rèn)值或者將參數(shù)設(shè)置為等于另一個(gè)參數(shù)，否則用戶必須在調(diào)用存儲(chǔ)過(guò)程的時(shí)候?yàn)閰?shù)賦值。

SQL Server存儲(chǔ)過(guò)程最多有2100個(gè)參數(shù)。

例如：

Create Proc yangyang8848.OneGoods  
@GoodsCode varchar(10)  
As   
       Select * From Master_Goods Where GoodsCode = @GoodsCode  
Go 

調(diào)用的代碼：

Declare @Code varchar(10)  
Set @Code = '0004' 
Exec yangyang8848.OneGoods @Code 

在參數(shù)的后邊加入Output 表明該參數(shù)為輸出參數(shù)。

Create Proc yangyang8848.OneGoods  
@GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011' 
As   
       Select * From Master_Goods Where GoodsCode = @GoodsCode  
       Set @GoodsCode2 = '0005' 
Go 

調(diào)用方法：

Declare @VV2 varchar(10)  
Exec yangyang8848.OneGoods  @Code out 

注意：如果存儲(chǔ)過(guò)程的兩個(gè)參數(shù)一個(gè)有默認(rèn)值一個(gè)沒(méi)有，那么我們要把有默認(rèn)值得放在后邊，不然會(huì)出問(wèn)題哦~~

細(xì)心的朋友，可能看到上邊的語(yǔ)句有一些不同，比如，存儲(chǔ)過(guò)程用的是output，而調(diào)用語(yǔ)句用的是out。我要告訴您，兩者是一樣的。

 [RECOMPILE]：指示數(shù)據(jù)庫(kù)引擎 不緩存該過(guò)程的計(jì)劃，該過(guò)程在運(yùn)行時(shí)編譯。如果指定了 FOR REPLICATION，則不能使用此選項(xiàng)。對(duì)于 CLR 存儲(chǔ)過(guò)程，不能指定 RECOMPILE。

這個(gè)說(shuō)一個(gè)非常好用的函數(shù) OBJECT_ID ：返回架構(gòu)范圍內(nèi)對(duì)象的數(shù)據(jù)庫(kù)對(duì)象標(biāo)識(shí)號(hào)。

例如：我們創(chuàng)建存儲(chǔ)過(guò)程時(shí)，可以如下寫(xiě)代碼

If Object_ID('yangyang8848.OneGoods') Is Not Null  
       Drop Proc yangyang8848.OneGoods  
Go  
 
Create Proc yangyang8848.OneGoods  
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011' 
As   
       Select * From Master_Goods Where GoodsCode = @GoodsCode  
       Set @GoodsCode2 = '0005' 
Go  

針對(duì)于上邊的這個(gè)存儲(chǔ)過(guò)程，我們調(diào)用以下SQL查詢

Select definition From sys.sql_modules  
      Where object_id = Object_ID('yangyang8848.OneGoods'); 

我們是可以查到結(jié)果的。

可是如果我們對(duì)該存儲(chǔ)過(guò)程加入[ ENCRYPTION ] 那么你將無(wú)法看到任何結(jié)果

If Object_ID('yangyang8848.OneGoods') Is Not Null  
         Drop Proc yangyang8848.OneGoods  
  Go  
 
  Create Proc yangyang8848.OneGoods  
  @GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011' 
 
  With Encryption  
  As   
         Select * From Master_Goods Where GoodsCode = @GoodsCode  
         Set @GoodsCode2 = '0005' 
  Go  

然后我們查詢 sys.sql_modules 目錄視圖，將返回給你Null。

然后我們執(zhí)行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods'

你將得到以下結(jié)果：The text for object 'yangyang8848.OneGoods' is encrypted.

說(shuō)到這里你應(yīng)該明白了，參數(shù)[ ENCRYPTION ]：是一種加密的功能， 將 CREATE PROCEDURE 語(yǔ)句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對(duì)系統(tǒng)表或數(shù)據(jù)庫(kù)文件沒(méi)有訪問(wèn)權(quán)限的用戶不能檢索模糊文本。但是，可通過(guò) DAC 端口訪問(wèn)系統(tǒng)表的特權(quán)用戶或直接訪問(wèn)數(shù)據(jù)庫(kù)文件的特權(quán)用戶可使用此文本。此外，能夠向服務(wù)器進(jìn)程附加調(diào)試器的用戶可在運(yùn)行時(shí)從內(nèi)存中檢索已解密的過(guò)程。

前兩天寫(xiě)了一篇關(guān)于游標(biāo)的介紹文章 ，下邊寫(xiě)一個(gè)例子，將游標(biāo)與存儲(chǔ)過(guò)程一起使用上： 

If Object_ID('dbo.GetMasterGoods') Is Not Null  
       Drop Proc dbo.GetMasterGoods  
 Go  
 
 Create Proc GetMasterGoods  
 @MyCursor Cursor Varying Output  
 With Encryption  
 As   
        Set @MyCursor = Cursor  
        For  
               Select GoodsCode,GoodsName From Master_Goods  
 Open @MyCursor  
 Go  

下邊建立另外一個(gè)存儲(chǔ)過(guò)程，用于遍歷游標(biāo)輸出結(jié)果

Create Proc GetAllGoodsIDAndName  
As  
 
Declare @GoodsCode varchar(18)  
Declare @GoodsName nvarchar(20)  
Declare @MasterGoodsCursor Cursor  
Exec GetMasterGoods @MasterGoodsCursor out 
Fetch Next From @MasterGoodsCursor  
InTo @GoodsCode,@GoodsName  
While(@@Fetch_Status = 0)  
Begin  
       Begin  
              Print @GoodsCode + ':' + @GoodsName  
       End  
       Fetch Next From @MasterGoodsCursor  
       InTo @GoodsCode,@GoodsName  
End  
Close @MasterGoodsCursor  
Deallocate @MasterGoodsCursor  
Go  

最后執(zhí)行Exec GetAllGoodsIDAndName結(jié)果為以下內(nèi)容 

0003:品0003  
 0004:品0004  
 0005:123123  
 0006:品0006  
 0007:品0007  
 0008:品0008  
 0009:品0009  
 0010:品0010  
 0011:品0011  
 0012:品0012  
 0013:品0013  
 0014:品0014  

【編輯推薦】

1. C# Thread類的應(yīng)用
2. C#線程：BeginInvoke和EndInvoke方法
3. 學(xué)習(xí)C#：Attribute與Property
4. C#編碼標(biāo)準(zhǔn)66條
5. C#委托事件及自定義事件的處理