SQL Server預存程序介紹

SQL Server預存程序是一個被命名的儲存在伺服器上的Transacation-Sql語句集合,是封裝重複性工作的一種方法,它支援使用者聲明的變數、條件執行和其他強大的編程功能。

SQL Server預存程序相對於其他的資料庫存取方法有以下的優點：

(1)重複使用。預存程序可以重複使用，從而可以減少資料庫開發人員的工作量。

(2)提高效能。預存程序在建立的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次，所以使用預存程序提高了效率。

(3)減少網路流量。預存程序位於伺服器上，調用的時候只需要傳遞預存程序的名稱以及參數就可以了，因此降低了網路傳輸的資料量。

(4)安全性。參數化的預存程序可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke許可權應用於預存程序。

SQL Server預存程序一共分為了三類：使用者定義的預存程序、擴充預存程序以及系統預存程序。

其中，使用者定義的預存程序又分為Transaction-SQL和CLR兩種類型。

1.Transaction-SQL 預存程序是指儲存的Transaction-SQL語句集合，可以接受和返回使用者提供的參數。

2.CLR預存程序是指對.Net Framework通用語言執行平台(CLR)方法的引用，可以接受和返回使用者提供的參數。他們在.Net Framework程式集中是作為類的公用靜態方法實現的。(本文就不作介紹了)

建立SQL Server預存程序的語句如下：

以下為引用的內容：

 

  
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]: 代表的是預存程序所屬的架構的名稱

例如：

 

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

執行：Exec AllGoods 發生錯誤。

執行：Exec yangyang8848.AllGoods 正確執行。

[；Number]: 用於對同名過程進行分組的可選整數。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。

例如：

 

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

建立完畢了兩個預存程序。它們在同一個組S1裡，如果執行Exec S1 則預存程序預設執行 Exec S1 ;1 。如果我們想得到所有據點資訊則需要執行Exec S1 ;2。當我們要刪除預存程序的時候，只能執行Drop Exec S1 則該組內所有的預存程序被刪除。

[@ parameter]: 預存程序中的參數，除非將參數定義的時候有預設值或者將參數設定為等於另一個參數，否則使用者必須在調用預存程序的時候為參數賦值。

SQL Server預存程序最多有2100個參數。

例如：

 

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

調用的代碼：

 

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

在參數的後邊加入Output 表明該參數為輸出參數。

 

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

調用方法：

 

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

注意：如果預存程序的兩個參數一個有預設值一個沒有，那麼我們要把有預設值得放在後邊，不然會出問題哦~~

細心的朋友，可能看到上邊的語句有一些不同，比如，預存程序用的是output，而調用語句用的是out。我要告訴您，兩者是一樣的。

[RECOMPILE]：指示資料庫引擎 不緩衝該過程的計劃，該過程在運行時編譯。如果指定了 FOR REPLICATION，則不能使用此選項。對於 CLR 預存程序，不能指定 RECOMPILE。

這個說一個非常好用的函數 OBJECT_ID ：返回架構範圍內對象的資料庫物件標識號。

例如：我們建立預存程序時，可以如下寫代碼

 

  
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  
 

針對於上邊的這個預存程序，我們調用以下SQL查詢

 

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

我們是可以查到結果的。

可是如果我們對該預存程序加入[ ENCRYPTION ] 那麼你將無法看到任何結果

 

 

  
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。

然後我們執行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods'

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

說到這裡你應該明白了，參數[ ENCRYPTION ]：是一種加密的功能， 將 CREATE PROCEDURE 語句的原始文本轉換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄檢視中都不能直接顯示。對系統資料表或資料庫檔案沒有存取權限的使用者不能檢索模糊文本。但是，可通過 DAC 連接埠訪問系統資料表的特權使用者或直接存取資料庫檔案的特權使用者可使用此文本。此外，能夠向伺服器處理序附加調試器的使用者可在運行時從記憶體中檢索已解密的過程。

前兩天寫了一篇關於遊標的介紹文章 ，下邊寫一個例子，將遊標與預存程序一起使用上： 

 

  
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  
 

下邊建立另外一個預存程序，用於遍曆遊標輸出結果

 

  
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  
 

最後執行Exec GetAllGoodsIDAndName結果為以下內容 

 

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