sql預存程序定義與預存程序好處-PHP开发-PHP中文網

重複使用。預存程序可以重複使用，從而可以減少資料庫開發人員的工作量。
        (2)提升效能。預存程序在創建的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執行一次就需要編譯一次，所以使用預存程序提高了效率。
        (3)網路流量減少。預存程序位於伺服器上，呼叫的時候只需要傳遞預存程序的名稱以及參數就可以了，因此降低了網路傳輸的資料量。
        (4)安全性。參數化的預存程序可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權限套用到預存程序。
預存程序一共分為了三類：使用者定義的預存程序、擴充預存程序、系統預存程序。
         其中，以使用者定義的預存程序又分為Transaction-SQL和CLR兩種。
Transaction-SQL 預存程序是指已儲存的Transaction-SQL語句集合，可以接受並傳回使用者提供的參數。
        CLR預存程序是指.Net Framework公共語言執行階段(CLR)方法的引用，可以接受並回傳使用者提供的參數。他們在.Net Framework程式集中是作為類別的公共靜態方法實現的。
      建立預存程序的語句如下：
      下列為所引用的內容：
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ type.     [ VARYING ] [ = default ] [ [ OUT [ PUT ]
    ] [ ,n ]
[ WITH [ ,n ]
[ FOR REPLICATION ]
AS { [;][ n ] | ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE_AS_Clause ]

> methstate_statement> [ EXECUTE_AS_Clause ]

. ::of. ::=

EXTERNAL NAME assembly_name.class_name. method_name

[schema_name]: 代表的是儲存程序所屬的架構的名稱

      例如：

              Go
            Create Proc yangyang8848.AllGoods

執行：Exec AllGoods 發生錯誤。

            執行：Exec yangyang8848.AllGoods 正確執行。
      [；Number]: 用於將同名流程分組的可選整數。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。
      例如：

            Create Proc S1 ;1
        Select * From Master_Goods
            Go
            Create Proc           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
report  ode
      Go
      所調用的代碼：
      Declare @Code varchar(10)
      Set @Code = '0004'
      Set @Code = '0004)
yangyang8848.OneGoods @Code
      在參數的後邊加入Output 表示參數為輸出參數。
      Create Proc yangyang8848.OneGoods
      @GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011'🠎   Select * From Master_Goods Where GoodsCode = @GoodsCode
             Set @GoodsCode2 = '0005'   ：
      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
Go

Create Proc yangyang8848.OneGoods

      @GoodsCode2 varchar(10) out, @GoodsCode varchar(10) = '0011'
      As
             Select * From Master_ '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     Go

Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'

      With Encryption
     Code
             Set @GoodsCode2 = '0005'

.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 NullMasterot
Go

Create Proc GetMasterGoods

      @MyCursor Cursor Varying Output
      With Encryp  or = Cursor
             For
                  Go

      --下邊建立另一個儲存過程，用於遍歷遊標輸出結果

      Create Proc GetAllGoodsIDAndName
      As

Declare @GoodsCode varchar(18)

Declare Declare @MasterGoodsCursor Cursor

Exec GetMasterGoods @MasterGoodsCursor out
Fetch Next From @MasterGoodsCursor

Fetch Next From @MasterGood,Cursor

      Fetch Next From @MasterGood,Cursor
      Fetch Next   @GoodsName
      While(@@Fetch_Status = 0)
      Begin
           @GoodsCode + ':' + @GoodsName
             End

      End
      Close @MasterGoodsCursor
      Deallocate @ MasterGoodsCursor
      Go

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

      0003:0003
     05:123123
      0006:品0006

0007:品0007

0010 :品0010

0011:品0011

      0012:品0012
      0013:品0013
      0014:品0014