老馬驛站
If you want to go fast, go alone; if you want to go far, go together.

隨筆-167 評論-8 文章-0 trackbacks-0

Sql Server的存儲過程是一個被命名的存儲在服務器上的Transacation-Sql語句集合,是封裝重復性工作的一種方法,它支持用戶聲明的變量、條件執行和其他強大的編程功能。

存儲過程相對于其他的數據庫訪問方法有以下的優點：

（1）重復使用。存儲過程可以重復使用，從而可以減少數據庫開發人員的工作量。

（2）提高性能。存儲過程在創建的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次，所以使用存儲過程提高了效率。

（3）減少網絡流量。存儲過程位于服務器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網絡傳輸的數據量。

（4）安全性。參數化的存儲過程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權限應用于存儲過程。

存儲過程一共分為了三類：用戶定義的存儲過程、擴展存儲過程以及系統存儲過程。

其中，用戶定義的存儲過程又分為Transaction-SQL和CLR兩種類型。

Transaction-SQL 存儲過程是指保存的Transaction-SQL語句集合，可以接受和返回用戶提供的參數。

CLR存儲過程是指對.Net Framework公共語言運行時(CLR)方法的引用，可以接受和返回用戶提供的參數。他們在.Net Framework程序集中是作為類的公共靜態方法實現的。（本文就不作介紹了）

創建存儲過程的語句如下：

Code

[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]: 存儲過程中的參數，除非將參數定義的時候有默認值或者將參數設置為等于另一個參數，否則用戶必須在調用存儲過程的時候為參數賦值。

存儲過程最多有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