第12章 存儲過程和觸發(fā)器

1、數(shù)據(jù)庫實用教程數(shù)據(jù)庫實用教程 SQL Server 2008第第12章章 存儲過程和觸發(fā)器存儲過程和觸發(fā)器本章學(xué)習(xí)目標(biāo)： 掌握存儲過程的定義方法 掌握執(zhí)行存儲過程的方法 理解觸發(fā)器的概念 掌握利用觸發(fā)器實現(xiàn)數(shù)據(jù)完整性的方法12.1 存儲過程 12.1.1 存儲過程概述 存儲過程與其他編程語言中的過程類似，比如可以接受輸入?yún)?shù)并以輸出參數(shù)的形式向調(diào)用過程或批處理返回多個值；包含一組Transact-SQL語句，用于在數(shù)據(jù)庫中執(zhí)行操作（包括調(diào)用其他過程）；同時，存儲過程也可以向調(diào)用過程或批處理返回狀態(tài)值，以指明成功或失?。ㄒ约笆〉脑颍┑?。 在 SQL Server 中使用存儲過程有如下好處：存

2、儲過程已在服務(wù)器注冊。存儲過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加到它們的證書。用戶可以被授予權(quán)限來執(zhí)行存儲過程而不必直接對存儲過程中引用的對象具有權(quán)限。存儲過程可以強(qiáng)制應(yīng)用程序的安全性。參數(shù)化存儲過程有助于保護(hù)應(yīng)用程序不受 SQL Injection（SQL 注入）攻擊。存儲過程允許模塊化程序設(shè)計。存儲過程一旦創(chuàng)建，以后即可在程序中調(diào)用任意多次。這可以改進(jìn)應(yīng)用程序的可維護(hù)性，并允許應(yīng)用程序統(tǒng)一訪問數(shù)據(jù)庫。存儲過程是命名代碼，允許延遲綁定。這提供了一個用于簡單代碼演變的間接級別。存儲過程可以減少網(wǎng)絡(luò)通信流量。一個需要數(shù)百行 Transact-SQL 代碼的操作可以通過一條執(zhí)行過程

3、代碼的語句來執(zhí)行，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼。1. 用戶定義的存儲過程存儲過程是指封裝了可重用代碼的模塊或例程。存儲過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言 (DDL) 和數(shù)據(jù)操作語言 (DML) 語句，然后返回輸出參數(shù)。在 SQL Server 2008 中，用戶定義的Transact-SQL存儲過程中包含一組Transact-SQL 語句集合，可以接受和返回用戶提供的參數(shù)。2. 擴(kuò)展存儲過程擴(kuò)展存儲過程是指 Microsoft SQL Server 的實例可以動態(tài)加載和運(yùn)行的 DLL，是由用戶使用編程語言（例如C）創(chuàng)建的自己的外部例程，擴(kuò)展存儲過程一般使

4、用_xp前綴。3. 系統(tǒng)存儲過程SQL Server 中的許多管理活動都是通過一種特殊的存儲過程執(zhí)行的，這種存儲過程被稱為系統(tǒng)存儲過程。 12.1.2 創(chuàng)建存儲過程1. 利用Create Procedure語句創(chuàng)建Create Procedure語句的語法格式為：Create Proc | Procedure procedure_name ; number parameter data_type Varying = default Out | Output Readonly ,.n With Encryption | Recompile For Replication As ; .n ;說明：

5、procedure_name：存儲過程名。過程名稱必須遵循有關(guān)標(biāo)識符的規(guī)則，并且在架構(gòu)中必須唯一。可在 procedure_name 前面使用一個數(shù)字符號 (#) (#procedure_name) 來創(chuàng)建局部臨時過程，使用兩個數(shù)字符號 (#procedure_name) 來創(chuàng)建全局臨時過程。存儲過程或全局臨時存儲過程的完整名稱（包括 #）不能超過 128 個字符。局部臨時存儲過程的完整名稱（包括 #）不能超過 116 個字符。; number：是可選整數(shù)，用于對同名的過程分組。使用一個 Drop Procedure 語句可將這些分組過程一起刪除。parameter：過程中的參數(shù)。通過將用作第

6、一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關(guān)標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；其他過程中可以使用相同的參數(shù)名稱。在 Create Procedure 語句中可以聲明一個或多個參數(shù)。除非定義了參數(shù)的默認(rèn)值或者將參數(shù)設(shè)置為等于另一個參數(shù)，否則用戶必須在調(diào)用過程時為每個聲明的參數(shù)提供值。存儲過程最多可以有 2,100 個參數(shù)。如果指定了 For Replication，則無法聲明參數(shù)。data_type：參數(shù)以的數(shù)據(jù)類型。所有數(shù)據(jù)類型都可以用作 Transact-SQL 存儲過程的參數(shù)?？梢允褂糜脩舳x表類型來聲明表值參數(shù)作為 Transact-SQL 存儲過程的參數(shù)。只能將表值參數(shù)指定

7、為輸入?yún)?shù)，這些參數(shù)必須帶有 Readonly 關(guān)鍵字。cursor 數(shù)據(jù)類型只能用于 Output 參數(shù)。如果指定了 cursor 數(shù)據(jù)類型，則還必須指定 Varying 和 Output 關(guān)鍵字?？梢詾?cursor 數(shù)據(jù)類型指定多個輸出參數(shù)。Varying：指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲過程動態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變。僅適用于 cursor 參數(shù)。Default：參數(shù)的默認(rèn)值。如果定義了 default 值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程使用帶 LIKE 關(guān)鍵字的參數(shù)，則可包含下列通配符：%、_、 和 。Output：指示參數(shù)是輸出

8、參數(shù)。此選項的值可以返回給調(diào)用 Execute的語句。使用 Output 參數(shù)將值返回給過程的調(diào)用方。Readonly：指示不能在過程的主體中更新或修改參數(shù)。如果參數(shù)類型為用戶定義的表類型，則必須指定 Readonly。Encryption：指示 SQL Server 將 Create Procedure 語句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 的任何目錄視圖中都不能直接顯示。對系統(tǒng)表或數(shù)據(jù)庫文件沒有訪問權(quán)限的用戶不能檢索模糊文本。使用此選項創(chuàng)建的過程不能在 SQL Server 復(fù)制過程中發(fā)布。Recompile：指示數(shù)據(jù)庫引擎不緩存該過程的計劃，該過程在運(yùn)行時

9、編譯。如果指定了 For Replication，則不能使用此選項。For Replication：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。使用 For Replication 選項創(chuàng)建的存儲過程可用作存儲過程篩選器，且只能在復(fù)制過程中執(zhí)行。如果指定了 For Replication，則無法聲明參數(shù)。sql_statement：要包含在過程中的一個或多個 Transact-SQL 語句。2. 利用對象資源管理器創(chuàng)建具體的步驟為： 1）在選定的數(shù)據(jù)庫下打開【可編程性】節(jié)點。 2）找到【存儲過程】節(jié)點，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇【新建存儲過程】，如圖12.1所示。 3）在新建的查

10、詢窗口中可以看到關(guān)于創(chuàng)建存儲過程的語句模板，在其中添上相應(yīng)的內(nèi)容，單擊工具欄上的【執(zhí)行】即可。 12.1.3 修改和刪除存儲過程1.利用對象資源管理器修改存儲過程具體的步驟為： 1）在對象資源管理器中口中，找到需要修改的存儲過程節(jié)點，在其上單擊鼠標(biāo)右鍵，彈出快捷菜單，如圖12.3所示。 2）在快捷菜單中，單擊【修改】菜單，彈出修改窗口，可以在現(xiàn)有存儲過程定義的基礎(chǔ)上進(jìn)行修改。 3）修改完成后，單擊工具欄上的【執(zhí)行】按鈕，即可完成存儲過程的修改。2.利用Alter Procedure語句修改Alter Procedure語句的語法格式如下：Alter Proc | Procedure schem

11、a_name. procedure_name ; number parameter type_schema_name. data_type Varying = default Out Put ,.n With Encryption | Recompile For Replication As ; .n ;3. 利用對象資源管理器刪除存儲過程步驟為： 1）在對象資源管理器中口中，找到需要刪除的存儲過程節(jié)點，在其上單擊鼠標(biāo)右鍵，彈出快捷菜單，如圖12.6所示。 2）在快捷菜單中，單擊【刪除】菜單，彈出確認(rèn)刪除窗口，選擇【確定】即可刪除。 4.使用Drop Procedure語句刪除函數(shù) 使用Dro

12、p Procedure語句可以從當(dāng)前數(shù)據(jù)庫中刪除一個或多個存儲過程或過程組，具體的語法形式如下。 Drop Proc | Procedure schema_name. procedure ,.n 12.1.4 執(zhí)行存儲過程1. Execute語句Execute語句的語法格式為：Exec | Execute return_status = module_name ;number | module_name_var parameter = value | variable Output | Default ,.n With Recompile 說明：return_status：可選的整型變量，代表

13、存儲過程的返回狀態(tài)，該變量在用于 Execute 語句前，必須已經(jīng)聲明過。module_name：要調(diào)用的存儲過程名，必須符合標(biāo)識符命名規(guī)則。 ;number ：可選整數(shù)，用于對同名的過程分組。module_name_var：代表存儲過程名的局部變量。parameter：存儲過程的參數(shù)，必須和存儲過程定義中的相同，其中不能省略。在與 parameter_name=value 格式一起使用時，參數(shù)名和值不必按它們在存儲過程中定義的順序提供。但是，如果對任何參數(shù)使用了 parameter_name=value 格式，則對所有后續(xù)參數(shù)都必須使用此格式。Value：傳遞的參數(shù)值。如果參數(shù)名稱沒有指定，

14、參數(shù)值的順序必須和存儲過程中定義的順序相同。variable：存儲參數(shù)或返回參數(shù)的變量。Output：指定該參數(shù)為輸出參數(shù)，該參數(shù)在存儲過程定義時必須已經(jīng)使用了Output聲明為輸出參數(shù)，如果參數(shù)定義時沒有定義為輸出參數(shù)，則不能使用Output關(guān)鍵字。必須使用變量來接收輸出值，而且該變量必須事先聲明。Default：指明該參數(shù)使用默認(rèn)值。如果某參數(shù)定義時沒有指定默認(rèn)值，則不能使用Default關(guān)鍵字。With Recompile：一般情況下，存儲過程只有在第一次執(zhí)行時，系統(tǒng)對其進(jìn)行編譯，并將存儲起來，以后執(zhí)行時直接取出執(zhí)行計劃執(zhí)行，不再編譯。使用With Recompile，強(qiáng)制在執(zhí)行存儲過程

15、時重新對其進(jìn)行編譯。 2. 參數(shù)傳遞 如果存儲過程含有參數(shù)并且沒有指定默認(rèn)值，則調(diào)用存儲過程時必須對參數(shù)賦值，可以使用兩種方式傳遞參數(shù)：按順序賦值和按名賦值。 按順序給參數(shù)進(jìn)行賦值時，不需要給出參數(shù)的名稱，并且調(diào)用語句中值的順序必須和存儲過程定義中參數(shù)定義的順序保持一致。如果某參數(shù)有默認(rèn)值，可以使用Default指明該參數(shù)使用默認(rèn)值，如果該參數(shù)且位于參數(shù)列表的末尾，則Default可以省略。3. 用存儲過程返回數(shù)據(jù) 存儲過程可以用兩種方式向調(diào)用程序返回數(shù)據(jù)：利用輸出參數(shù)和利用Return語句。1）利用輸出參數(shù)返回數(shù)據(jù) 如果在存儲過程定義參數(shù)時使用了Output關(guān)鍵字，則存儲過程執(zhí)行結(jié)束時可以將

16、參數(shù)的當(dāng)前值返回給調(diào)用程序。 2）利用Return語句返回執(zhí)行狀態(tài) 在存儲過程中可以使用Return語句向調(diào)用程序返回一個整數(shù)（稱為返回代碼），指示存儲過程的執(zhí)行狀態(tài)。12.2 觸發(fā)器12.2.1 觸發(fā)器概念觸發(fā)器（Trigger）是SQL Server提供的除約束之外的另一種保證數(shù)據(jù)完整性的方法，它可以實現(xiàn)約束所不能實現(xiàn)的更復(fù)雜的完整性要求。觸發(fā)器是一種特殊的存儲過程，它不允許帶參數(shù)，不能由用戶直接通過名稱調(diào)用，而是由用戶的某一動作自動觸發(fā)。使用觸發(fā)器主要有以下優(yōu)點：觸發(fā)器是自動執(zhí)行的，在數(shù)據(jù)庫中定義了某個對象之后，或?qū)Ρ碇械臄?shù)據(jù)做了某種修改之后立即被激活。觸發(fā)器可以實現(xiàn)比約束更為復(fù)雜的完整

17、性要求，比如Check約束中不能引用其他表中的列，而觸發(fā)器可以引用；Check約束只是由邏輯符號連接的條件表達(dá)式，不能完成復(fù)雜的邏輯判斷功能。觸發(fā)器可以根據(jù)表數(shù)據(jù)修改前后的狀態(tài)，根據(jù)其差異采取相應(yīng)的措施。觸發(fā)器可以防止惡意的或錯誤的Insert、Update和Delete操作。12.2.2 DML觸發(fā)器DML觸發(fā)器在用戶對表中的數(shù)據(jù)進(jìn)行插入（Insert）、修改（Update）和刪除（Delete）時自動運(yùn)行。根據(jù)觸發(fā)器代碼執(zhí)行的時機(jī)，DML觸發(fā)器可以分為兩種：After觸發(fā)器和Instead of觸發(fā)器。After觸發(fā)器在在執(zhí)行了 Insert、Update 或 Delete 語句操作之后執(zhí)

18、行，只能在表上定義，不能在視圖上定義After觸發(fā)器。而Instead of觸發(fā)器則代替激活觸發(fā)器的DML操作執(zhí)行，即Insert、Update和Delete操作不再執(zhí)行，取而代之的是Instead of觸發(fā)器中的代碼。Instead of觸發(fā)器可以定義在表上和視圖上，通常使用Instead of觸發(fā)器擴(kuò)展視圖支持的可更新類型。 1.使用Create Trigger創(chuàng)建DML觸發(fā)器Create Trigger創(chuàng)建DML觸發(fā)器的語法格式為： Create Trigger trigger_name On table | view With Encryption For | After | Inst

19、ead Of Insert , Update , Delete As sql_statement ; 說明：trigger_name：觸發(fā)器名稱，必須遵守標(biāo)識符命名規(guī)則，并且不能以#或#開頭。table | view：對其執(zhí)行觸發(fā)器的表或視圖，視圖上不能定義For和After觸發(fā)器，只能定義Instead of觸發(fā)器。With Encryption：指定對觸發(fā)器進(jìn)行加密處理。For | After：指定觸發(fā)器中在相應(yīng)的DML操作（Insert、Update、Delete）成功執(zhí)行后才觸發(fā)。Instead Of：指定執(zhí)行 DML 觸發(fā)器而不是Insert、Update或Delete語句。在使用了

20、With Check Option語句的視圖上不能定義Instead of觸發(fā)器。Insert , Update , Delete：指定能夠激活觸發(fā)器的操作，必須至少指定一個操作。sql_statement：觸發(fā)器代碼，根據(jù)數(shù)據(jù)修改或定義語句來檢查或更改數(shù)據(jù)，通常包含流程控制語句，一般不應(yīng)向應(yīng)用程序返回結(jié)果。2. Inserted表和Deleted表在觸發(fā)器執(zhí)行的時候，會產(chǎn)生兩個臨時表：Inserted表和Deleted表。這兩個表的結(jié)構(gòu)和觸發(fā)器所在的表的結(jié)構(gòu)相同。在觸發(fā)器中可以使用這兩個臨時表測試某些數(shù)據(jù)修改的效果和設(shè)置觸發(fā)器操作的條件，但是這兩個表是只讀表，不能對表中的數(shù)據(jù)進(jìn)行修改。觸發(fā)器

21、執(zhí)行完成后，這兩個表就會被刪除。Inserted表用于存儲Insert語句和Update語句所影響行的副本。當(dāng)對觸發(fā)器表執(zhí)行Insert操作時，新行將被同時添加到觸發(fā)器表和Inserted表中，Inserted表中的行是觸發(fā)器表中新添加行的副本。Deleted表用于存儲Delete語句和Update語句所影響行的副本。當(dāng)對觸發(fā)器表執(zhí)行Delete操作時，行將從觸發(fā)器表中刪除，并存入Deleted表中。Deleted表和觸發(fā)器表沒有相同的行。當(dāng)對觸發(fā)器表執(zhí)行Update操作時，先從觸發(fā)器表中刪除舊行，然后再插入新行。其中被刪除的舊行被插入到Deleted表中，插入的新行的副本被插入到Insert

22、ed表中。3. 觸發(fā)器遞歸如果觸發(fā)器代碼中又包含對某個表的Insert、Update或Delete操作，而且在該表上定義了相應(yīng)的DML觸發(fā)器，則又會激活相應(yīng)的觸發(fā)器，這就是觸發(fā)器的遞歸。觸發(fā)器遞歸可以分為兩種類型：間接遞歸和直接遞歸。間接遞歸：一個應(yīng)用程序更新了表T1，觸發(fā)了觸發(fā)器TR1，在TR1中更新了表T2，激活觸發(fā)器TR2，以此類推。直接遞歸：應(yīng)用程序更新了表 T1，觸發(fā)了觸發(fā)器 TR1，在TR1中又更新了表 T1。由于表 T1 被更新，將再次觸發(fā)觸發(fā)器 TR1，依此類推。4. 使用對象資源管理器創(chuàng)建DML觸發(fā)器具體的步驟為： 1）打開對象資源管理器，找到希望創(chuàng)建DML觸發(fā)器的表，展開。

23、 2）找到【觸發(fā)器】節(jié)點，單擊鼠標(biāo)右鍵，在彈出的右鍵菜單中選擇【新建觸發(fā)器】。 3）在新建的查詢窗口中可以看到關(guān)于創(chuàng)建DML觸發(fā)器的語句模板，在其中添上相應(yīng)的內(nèi)容，單擊工具欄上的【執(zhí)行】即可。12.2.3 DDL觸發(fā)器與DML 觸發(fā)器不同的是，DDL觸發(fā)器不會被針對表或視圖的 Update、Insert 或 Delete 語句觸發(fā)。相反，它們將為了響應(yīng)各種數(shù)據(jù)定義語言 (DDL) 事件而激活，這些事件主要與以關(guān)鍵字Create、Alter 和Drop開頭的Transact-SQL 語句對應(yīng)。 創(chuàng)建DDL觸發(fā)器的Create Trigger語句的語法格式為：Create Trigger trig

24、ger_name On All Server | Database With Encryption For | After event_type | event_group ,.n As sql_statement ; 說明：trigger_name：觸發(fā)器名稱，必須符合標(biāo)識符命名規(guī)則。All Server：指定DDL觸發(fā)器的作用域為當(dāng)前服務(wù)器。如果指定了此參數(shù)，則只要當(dāng)前服務(wù)器中的任何位置上出現(xiàn) event_type 或 event_group，就會激活該觸發(fā)器。Database：指定DDL觸發(fā)器的作用域為當(dāng)前數(shù)據(jù)庫。如果指定了此參數(shù)，則只要當(dāng)前數(shù)據(jù)庫中的任何位置上出現(xiàn) event_type

25、 或 event_group，就會激活該觸發(fā)器。With Encryption：指定將觸發(fā)器的定義文本進(jìn)行加密處理。For | After：指定在DDL 觸發(fā)器僅在觸發(fā) SQL 語句中指定的所有操作都已成功執(zhí)行時才被觸發(fā)。event_type：將激活 DDL 觸發(fā)器的 Transact-SQL 語言事件的名稱。event_group：預(yù)定義的Transact-SQL 語言事件分組的名稱。執(zhí)行任何屬于 event_group 的 Transact-SQL 語言事件之后，都將激活 DDL 觸發(fā)器。sql_statement：觸發(fā)器代碼。12.2.4 登錄觸發(fā)器 登錄觸發(fā)器將是由登錄（LOGON）事

26、件而激活的觸發(fā)器，與 SQL Server 實例建立用戶會話時將引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗證階段完成之后且用戶會話實際建立之前激發(fā)。 創(chuàng)建登錄觸發(fā)器的Create Trigger語句的具體語法如下： Create Trigger trigger_name On All Server With Encryption For | After Logon As sql_statement ;12.2.5 修改和刪除觸發(fā)器1.利用對象資源管理器修改觸發(fā)器利用對象資源管理器修改觸發(fā)器，可以在已有的觸發(fā)器的基礎(chǔ)上進(jìn)行修改，不需要重新編寫，具體的步驟為：1）打開對象資源管理器，找到希望修改DML觸發(fā)器的表，展開。2）找到【觸發(fā)器】節(jié)點展開，在要修改的觸發(fā)器節(jié)點上單擊鼠標(biāo)右鍵，在彈出的右鍵菜單中選擇【修改】。3）這時將彈出修改觸發(fā)器的窗口，如圖12.11所示?？梢栽谠械幕A(chǔ)上進(jìn)行修改。修改完成后，單擊工具欄上的【執(zhí)行】按鈕，即可完成觸發(fā)器的修改。 2.利用Alter Trigger語句修改 修改DML觸發(fā)器的Alter Trigger語句的語法格式如下： Alter Trigger schema_name.trigger_name On ( table |