儲存過程的詳解

7.2.1 儲存過程基本知識

1．什麼是儲存過程

在開發SQL Server應用程式過程中，T-SQL語句是應用程式與SQL Server資料庫之間使用的主要程式設計介面。應用程式與SQL Server資料庫互動執行某些操作有兩種方法：一種是儲存在本地的應用程式記錄操作命令，應用程式向SQL Server傳送每一個命令，並對返回的資料進行處理；另一種是在SQL Server中定義某個過程，其中記錄了一系列的操作，每次應用程式只需呼叫該過程就可完成該操作。這種在SQL Server中定義的過程被稱為儲存過程。

2．儲存過程的功能

SQL Server中的儲存過程類似於程式語言中的過程和函式，它具有以下功能：

    · 接受輸入引數並返回多個輸出值。

· 包含T-SQL語句用以完成特定的SQL Server操作。

· 返回一個指示成功與否及失敗原因的狀態程式碼給呼叫它的過程。

儲存過程是一組預編譯的Transact-SQL語句，主體構成是標準SQL命令，同時包括SQL的擴充套件：語句塊、結構控制命令、變數、常量、運算子、表示式、流程控制等，所有這些組合在一起用於構造儲存過程。

3．儲存過程的優點

儲存過程有以下幾個優點：

    · 允許模組化程式設計，增強程式碼的重用性和共享性

    · 使用儲存過程可以加快執行速度

    · 使用儲存過程可以減少網路流量。

    · 儲存過程可以作為安全性機制。

4．儲存過程的分類

儲存過程有以下幾種型別：系統儲存過程、使用者儲存過程、臨時儲存過程、擴充套件儲存過程、遠端儲存過程。

系統儲存過程是由系統提供的過程，可以作為命令直接執行。系統儲存過程還可以作為模板儲存過程，指導使用者如何編寫有效的儲存過程。系統儲存過程儲存在master資料庫中，其字首為sp_。系統儲存過程可以在任意一個資料庫中執行。本書附錄B給出了常用的系統儲存過程。

使用者儲存過程是建立在使用者資料庫中的儲存過程。其名稱前面一般不加sp_字首。主要在應用程式中使用，以完成特定的任務。

臨時儲存過程屬於使用者儲存過程。如果使用者儲存過程前面加上符號“#”，則該儲存過程稱為區域性臨時儲存過程，只能在一個使用者會話中使用；如果使用者儲存過程前面加上符號“##”，則該過程稱為全域性儲存過程，可以在所有使用者會話中使用。

擴充套件儲存過程是在SQL Server環境之外執行的動態連結庫DLL，其字首為xp_。儘管這些動態連結庫在SQL Server環境之外，但它們可以被載入到SQL Server系統中，並且按照儲存過程的方式執行。

遠端儲存過程是指從遠端伺服器上呼叫的儲存過程，或者是從連線到另外一個伺服器上的客戶機上呼叫的儲存過程，是非本地伺服器上的儲存過程。

下面主要介紹使用者儲存過程的建立、修改、刪除等操作。

7.2.2 建立儲存過程

在SQL Server中建立儲存過程主要有以下3種方法：使用嚮導、使用企業管理器、 使用Transact-SQL命令。

1.使用嚮導建立儲存過程

使用嚮導建立儲存過程的步驟為：

(1)啟動企業管理器，展開資料庫目錄。

(2)在企業管理器的【工具】選單中選擇【嚮導】項。

(3)在【選擇嚮導】對話方塊中展開【資料庫】，單擊【建立儲存過程嚮導】項。

(4)單擊【確定】按鈕，開啟建立儲存過程的歡迎對話方塊(見圖7-6)，這個對話方塊向使用者介紹了利用建立嚮導建立儲存過程的主要步驟。

程的主要步驟。

 

圖7-6 建立儲存過程的歡迎對話方塊圖       7-7 選擇儲存過程對話方塊

(5)單擊【下一步】按鈕，開啟資料庫選擇對話方塊(儲存過程必須建立在一個資料庫中)，在資料庫下拉選單中選擇所需要的資料庫MyDb。

(6)單擊【下一步】按鈕，開啟選擇儲存過程對話方塊，對話方塊中共4個列表框，第1列為選定的資料庫中的所有表，第2列為插入核取方塊，第3列為刪除核取方塊，第4列為更新核取方塊。在這個對話方塊中選擇將要包含在儲存過程中的表【books】和對該表的操作【插入】，如圖7-7所示。

(7)單擊【下一步】按鈕，開啟完成建立儲存過程對話方塊，如圖7-8所示，對話方塊中顯示了系統賦予儲存過程的名稱及其描述資訊。單擊【編輯．．．】按鈕可以進入編輯儲存過程對話方塊，使用這個對話方塊可以修改儲存過程的名稱，列表框中設有 4列資訊，依次為列名、資料型別、長度和是否選擇核取方塊。預設情況下選擇所有列，單擊核取方塊來選擇需要的列，如圖7-9所示。

在編輯儲存過程對話方塊中單擊【編輯SQL…】按鈕，開啟編輯儲存過程SQL對話方塊，進行Transact-SQL的編輯，如圖7-10所示。編輯完成後，單擊【確定】按鈕，返回上一個對話方塊。

(8)在圖7-8所示對話方塊中，單擊【完成】按鈕，則儲存過程建立完畢。在選定的資料庫中，就會形成一個新的儲存過程。

注意利用建立嚮導的儲存過程功能有限，只適用於對錶的簡單操作，若須建立功能複雜的儲存過程，則要用CREATE PROCEDURE命令完成。

 

圖7-8 完成建立儲存過程對話方塊圖        7-9 編輯儲存過程對話方塊

圖7-10 編輯儲存過程SQL 對話方塊

2．使用企業管理器建立儲存過程

利用企業管理器建立儲存過程步驟如下：

(1)展開要在其中建立儲存過程的資料庫。

(2)右擊【儲存過程】圖示，在快捷選單中選擇【新建儲存過程．．．】命令，開啟新建儲存過程對話方塊，如圖7-11所示。

圖7-11 新建儲存過程對話方塊

(3)在新建儲存過程對話方塊的文字框中書寫儲存過程定義。【檢查語法】按鈕用於檢查儲存過程語法的正確性。定義完儲存過程後，單擊【確定】按鈕儲存儲存過程。

這種方法與下面講的利用Transact-SQL命令建立儲存過程幾乎完全相同。

3．使用Transact-SQL命令建立儲存過程

利用Transact-SQL命令建立儲存過程的語法結構如下：

    CREATE PROC[EDURE] procedure_name [；number] [{@parameter data_type}

    [VARYING] [= default] [OUTPUT]][，．．．n]

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

    [FOR REPLICATION] AS sql_statement [．．．n]

說明：

(1)     procedure_name 儲存過程名，其命名規則遵守 SQL Server 識別符號的命名規則，最長為 128個字元。

(2)     [；number] 可選整數，用於將同名的儲存過程分成組，以便用單獨的一條DROP PROCEDURE將其語句撤消。

(3)     @parameter 建立儲存過程時可以宣告一個或多個引數，最多為1024個。

(4)     VARYING只用於游標引數。

(5)     default 引數的預設值，可以為NULL，也可以包含萬用字元(％或_)。

(6)     OUTPUT 表明引數為一個輸出引數，當使用EXEC[UTE]執行時作為返回值，不能是TEXT型別。

(7)     WITH RECOMPILE 每次執行儲存過程時重新編譯，產生新的執行計劃，不能與 FOR REPLICATION同時使用。

(8)     WITH ENCRYPTION 將syscomments表中的儲存過程文字進行加密，使使用者不能利用sp_helptext檢視儲存過程內容。

(9)     FOR REPLICATION 表示該儲存過程不能在訂閱器上執行，只能在複製期間執行。

(10) sql_statement 作為儲存過程主體部分的Transact-SQL內容。

儲存過程只能在當前資料庫中建立，且只有具有System Administrators、db_owner或 db_ddladmin角色的成員才可以建立。在CREATE PROCEDURE語句中，可以包括任意數量的Transact-SQL語句，但是不能使用CREATE DEFAULT,CREATE PROCEDURE,CREATE RULE,CREATE TRIGGER和CREATE VIEW這些建立物件的語句。

【例7-18】 建立一個簡單的儲存過程。

USE MyDB

Go

CREATE PROCEDURE borrowed_num

As

SELECT 姓名,已借數量

FROM readers

WHERE 姓名='劉超'

Go

【例7-19】 通過多表連線查詢，建立較複雜的儲存過程。

Use MyDb

Go

CREATE PROCEDURE borrowed_books1

As

SELECT r.編號,r.姓名,b.圖書編號,k.書名,b.借期

FROM readers r inner join borrowinf b

ON r.編號=b.讀者編號 INNER JOIN books k

ON b.圖書編號=k.編號

WHERE 姓名='劉超'

7.2.3 儲存過程中的引數

SQL Server中儲存過程的引數包括輸入引數和輸出引數。引數擴充套件了SQL Server的功能，通過儲存過程每次執行時不同的引數值，實現其靈活性。

1．輸入引數

輸入引數用於把值傳入儲存過程。

【例7-20】 使用輸入引數，使得能夠顯示某人借閱書籍的情況。

Use MyDb

Go

CREATE PROCEDURE borrowed_books2

@name varchar(10)

As

SELECT r.編號,r.姓名,b.圖書編號,k.書名,b.借期

FROM readers r inner join borrowinf b

ON r.編號=b.讀者編號 INNER JOIN books k

ON b.圖書編號=k.編號

WHERE 姓名=@name

此例因為利用了輸入引數，使得儲存每次執行時都可以指定不同的查詢條件。

將值傳入儲存過程有以下幾種方法：

(1)直接將值傳入，如EXEC borrowed_books2 '張剛'。

(2)利用與宣告時相同型別的變數來傳遞。如EXEC borrowed_books2 @templ (此處，@templ為已宣告的字元型別變數，且已賦值)。

注意：指定的輸入引數值必須與其定義時的型別相同，並且輸入引數值必須與引數在儲存過程中宣告的順序相同。

(3)使用引數名進行傳遞。這種形式對於有多個輸入引數時，可以以任意的順序進行引數傳遞。但如果對一個引數使用了名字，則必須對隨後的引數都使用名字。

若沒有將值傳入相應的引數，並且在建立儲存過程時也沒有給引數賦預設值時，執行儲存過程會出錯。

【例7-21】 修改例7-20,使用預設引數。

Use MyDb

Go

CREATE PROCEDURE borrowed_books3

@name varchar(10)=NULL

As

IF @name IS NULL

SELECT r.編號,r.姓名,b.圖書編號,k.書名,b.借期

FROM readers r inner join borrowinf b

ON r.編號=b.讀者編號 INNER JOIN books k

ON b.圖書編號=k.編號

ELSE

SELECT r.編號,r.姓名,b.圖書編號,k.書名,b.借期

FROM readers r inner join borrowinf b

ON r.編號=b.讀者編號 INNER JOIN books k

ON b.圖書編號=k.編號

WHERE 姓名=@name

Go

請讀者自己分析該例子。

2．輸出引數

輸出引數用於把返回值賦予變數並傳給呼叫它的儲存過程或應用程式。宣告輸出引數時需在宣告引數的後面加上OUTPUT，以表明此引數為輸出引數。

【例7-22】 利用輸出引數計算階乘。

    USE MyDb

    IF EXISTS (SELECT name FROM sysobjects

               WHERE name='factorial' AND type='P')

        DROP PROCEDURE factorial

    GO

    CREATE PROCEDURE factorial

       @in float,

       @out float OUTPUT

    AS

    DECLARE @i int

    DECLARE @s float

    SET @i=1   

    SET @s=1

    WHILE @i<=@in

       BEGIN

SET @s=@s*@i

SET @i=@i+1

END

SET @out=@s

在查詢分析器中，用下面一段程式碼執行此儲存過程：

DECLARE @ou float

EXEC factorial 10,@ou OUT

PRINT '其階乘為：'+CAST(@ou AS varchar(20))

要執行一個帶有輸出引數的儲存過程，必須宣告一個變數來接受返回值（此變數不一定與建立儲存過程時宣告的輸出引數同名），並且在變數後必須使用關鍵字OUT（OUTPUT）。

7.2.4 修改儲存過程

1．使用企業管理器進行修改

修改儲存過程只需在相應資料庫的儲存過程物件項中找到要修改的儲存過程，雙擊儲存過程開啟儲存過程屬性視窗，如圖7-12，在視窗中直接進行修改，完成後單擊【確定】按鈕。

圖7-12 儲存過程屬性對話方塊

2．使用T-SQL命令進行修改

使用ALTER PROCEDURE可以修改儲存過程，其語法結構如下：

ALTER PROC[EDURE] procedure_name [;number]

[{@parameter data_type} [VARYING] [=default] [OUTPUT]][,…n]

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

{FOR REPLICATION}

AS 

sql-statement […n]

說明：ALTER PROCEDURE與CREATE PROCEDURE很類似。在CREATE PROCEDURE命令中使用的選項也必須在ALTER ROCEDURE中使用。ALTER ROCEDURE只能修改一個儲存過程。如果該儲存過程呼叫了其他儲存過程，則不影響被呼叫的儲存過程。只有具有db_owner和db_ddladmin許可權的成員才被允許執行ALTER ROCEDURE，執行ALTER ROCEDURE語句的許可權不能授予其他使用者。

3. 使用查詢分析器進行修改

開啟查詢分析器，按F8鍵（或單擊工具欄上的物件瀏覽器按鈕 ），在物件瀏覽器視窗中，開啟儲存過程所在的資料庫，開啟[儲存過程]資料夾，右擊要修改的儲存過程名（如test）,在彈出的選單中選擇【編輯】（如圖7-13所示），則儲存過程出現在查詢分析器的編輯視窗中，修改完畢儲存即可。

圖7-13 在查詢分析器中編輯儲存過程

7.2.5 執行儲存過程

1．儲存過程的編譯

首次執行儲存過程時，SQL Server編譯並檢驗其中的程式，如果發現錯誤，系統將拒絕執行此儲存過程。

即使只執行一條Transact-SQL語句，也要建立一份執行計劃，執行計劃包括儲存過程所需的錶行的索引。執行計劃保留在快取中，用於後續執行時完成儲存過程的查詢任務，提高執行速度。當出現下列幾種情況時，儲存過程被重新編譯：

（1）當SQL Server重新啟動，或儲存過程第一次被執行時。

（2）儲存過程修改後或其引用的表索引被刪除後，執行計劃被重新建立。

（3）當一個使用者在使用緩衝區中的執行計劃，重新編譯為第二個使用者建立第二個執行計劃。

（4)儲存過程刪除或重建後，緩衝區中所有的執行計劃備件都被刪除，執行時自動進行重新編譯，形成一份新的執行計劃。

2．儲存過程的執行

具有儲存過程執行許可EXECUTE的使用者，才可以執行儲存過程。在查詢分析器中，可以直接輸入儲存過程名，指定相應的輸入引數和輸出引數後執行。或者利用EXECUTE命令執行，這種方法同樣適用於應用程式中呼叫儲存過程。儲存過程與函式不同，它不能直接用過程名返回值，也不能直接在表示式中使用。EXECUTE的語法結構如下：

   [[EXEC[UTE]]

   {[@return status =]

    procedure_name[；number]| @procedure_name_var)

   [[ @parameter=] {value| @variable [OUTPUT] | [DEFAULT]} [,…n]

   [WITH RECOMPILE]

說明： 

(1)利用WITH RECOMPILE可選項可以在執行儲存過程時強制重新編譯。

(2)@return status：為一整型變數，用於儲存儲存過程的返回狀態。

7.2.6 刪除儲存過程

1．使用企業管理器刪除儲存過程

利用SQL Server 企業管理器刪除儲存過程是一種簡單有效的方法。展開資料庫後，單擊儲存過程圖示，在右邊視窗中出現的儲存過程中選擇要刪除的過程，單擊右鍵，在彈出的快捷選單中單擊【刪除】選項，即將儲存過程刪除。

2．使用T-SQL命令刪除儲存過程

利用DROP PROCEDURE命令刪除儲存過程,其語法結構如下：

    DROP PROCEDURE{procedure} [，．．．n]

【例7-23】 刪除上例中建立的儲存過程borrowed_num。

    USE MyDb

    GO

    DROP PROCEDURE borrowed_num

儲存過程被刪除後，其儲存在sysobjects和syscomments中的定義也被刪除掉，如果用系統過程sp_helptext來檢視儲存過程文字，則不會看到定義的內容。

7.2.7  檢視儲存過程的有關資訊

使用sp helptext系統儲存過程，可以檢視定義儲存過程的T-SQL語句。圖7-14顯示瞭如何使用查詢分析器檢視儲存過程borrowed_books1的定義。

圖7-14 顯示儲存討程的定義

使用企業管理器，用與上節介紹的修改儲存過程相同的步驟開啟儲存過程屬性對話方塊，也可以檢視儲存過程的定義。

如果儲存過程的定義是被加密的，即在定義或修改儲存過程的語句中使用了WITH ENCRYPTION子句，則儲存過程的定義以不可讀的形式儲存在syscomments表中。這時，將不能檢視儲存過程的定義。比如，用以下語句修改borrowed_books1的定義：

Use MyDb

Go

ALTER PROCEDURE borrowed_books1

WITH ENCRYPTION

As

SELECT r.編號,r.姓名,b.圖書編號,k.書名,b.借期

FROM readers r inner join borrowinf b

ON r.編號=b.讀者編號 INNER JOIN books k

ON b.圖書編號=k.編號

WHERE 姓名='劉超'

 

在查詢分析器中執行以上語句，結果視窗中顯示：

    命令已成功完成．

表示儲存過程修改成功，此時再執行sp_helptext borrowed_books1,將看不到borrowed_books1定義的文字，系統提示“物件備註已加密”。

使用系統儲存過程sp_help可以獲得有關儲存過程建立者、建立日期以及儲存過程使用的引數等資訊。

使用系統儲存過程sp_depends可以列出被該儲存過程使用的物件以及呼叫該儲存過程的其他儲存過程的名稱。這些資訊可以用來了解刪除該儲存過程時會影響哪些資料庫物件。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-446857/，如需轉載，請註明出處，否則將追究法律責任。