執行個體操作 教你玩轉SQL Server預存程序(一)

　首先介紹一下什麼是預存程序：預存程序就是將常用的或很複雜的工作，預先用SQL語句寫好並用一個指定的名稱儲存起來，並且這樣的語句是放在資料庫中的，還可以根據條件執行不同SQL語句， 那麼以後要叫資料庫提供與已定義好的預存程序的功能相同的服務時，只需調用execute,即可自動完成命令。

　　請大家來瞭解一下預存程序的文法。

　　create PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

　　[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

　　[ FOR REPLICATION ]

　　AS sql_statement [ ...n ]

　　參數：

　　procedure_name

　　新預存程序的名稱。過程名必須符合標識符規則，且對於資料庫及其所有者必須唯一。

　　要建立局部暫存處理序，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要建立全域暫存處理序，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字元。指定流程所有人的名稱是可選的。

　　預存程序的優點都有哪些呢？

　　1.預存程序只在創造時進行編譯即可，以後每次執行預存程序都不需再重新編譯，而我們通常使用的SQL語句每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。

　　2.經常會遇到複雜的商務邏輯和對資料庫的操作，這個時候就會用SP來封裝資料庫操作。當對資料庫進行複雜操作時(如對多個表進行update,insert,Query,delete時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。可以極大的提高資料庫的使用效率，減少程式的執行時間，這一點在較大資料量的資料庫的操作中是非常重要的。在代碼上看，SQL語句和程式碼語句的分離，可以提高程式碼的可讀性。

　　3.預存程序可以設定參數，可以根據傳入參數的不同重複使用同一個預存程序，從而高效的提高代碼的最佳化率和可讀性。

　　4.安全性高,可設定只有某此使用者才具有對指定預存程序的使用權預存程序的種類：

　　（1）系統預存程序：以sp_開頭,用來進行系統的各項設定.取得資訊.相關管理工作,如 sp_help就是取得指定對象的相關資訊。

　　（2）擴充預存程序 以XP_開頭,用來叫用作業系統提供的功能
exec master..xp_cmdshell ’ping 10.8.16.1’

　　（3）使用者自訂的預存程序,這是我們所指的預存程序常用格式

  　模版：create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement

　　解釋：output：表示此參數是可傳回的

　　with {recompile|encryption} recompile:表示每次執行此預存程序時都重新編譯一次;encryption:所建立的預存程序的內容會被加密。

　　;number

　　是可選的整數，用來對同名的過程分組，以便用一條 drop PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程式使用的過程可以命名為 orderproc;1、orderproc;2 等。drop PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前後使用適當的定界符。

　　@parameter

　　過程中的參數。在 create PROCEDURE 語句中可以聲明一個或多個參數。使用者必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的預設值）。預存程序最多可以有 2100 個參數。

　　使用@符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其它過程中。預設情況下，參數只能代替常量，而不能用於代替表名、列名或其它資料庫物件的名稱。

　　data_type

　　參數的資料類型。所有資料類型（包括 text、ntext 和 image）均可以用作預存程序的參數。不過，cursor 資料類型只能用於 OUTPUT 參數。如果指定的資料類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。

　　說明：對於可以是cursor 資料類型的輸出參數，沒有最大數目的限制。

　　VARYING

　　指定作為輸出參數支援的結果集（由預存程序動態構造，內容可以變化）。僅適用於遊標參數。

　　default

　　參數的預設值。如果定義了預設值，不必指定該參數的值即可執行過程。預設值必須是常量或 NULL。如果過程將對該參數使用 like 關鍵字，那麼預設值中可以包含萬用字元（%、_、[] 和 [^]）。

　　OUTPUT

　　表明參數是返回參數。該選項的值可以返回給 exec[UTE]。使用 OUTPUT 參數可將資訊返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是遊標預留位置。

　　n

　　表示最多可以指定 2100 個參數的預留位置。

　　{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不會緩衝該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩衝在記憶體中的執行計畫時，請使用 RECOMPILE 選項。

　　ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 create PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 複製的一部分發布。

　　說明：在升級過程中，SQL Server 利用儲存在 syscomments 中的加密注釋來重新建立加密過程。

　　FOR REPLICATION

　　指定不能在訂閱伺服器上執行為複製建立的預存程序。.使用 FOR REPLICATION 選項建立的預存程序可用作預存程序篩選，且只能在複製過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。

　　AS

　　指定過程要執行的操作。

　　sql_statement

　　過程中要包含的任意數目和類型的 Transact-SQL 陳述式。但有一些限制。

　　n

　　是表示此過程可以包含多條 Transact-SQL 陳述式的預留位置。

　　注釋

　　預存程序的最大大小為 128 MB。