1、第第10章章 存儲過程與觸發器存儲過程與觸發器本章內容本章內容10.1 存儲過程概述存儲過程概述10.2 存儲過程的創建與使用存儲過程的創建與使用10.3 觸發器概述觸發器概述10.4 觸發器的創建與使用觸發器的創建與使用10.5 事務處理事務處理10.6 SQL Server的鎖機制的鎖機制10.1 存儲過程概述存儲過程概述 n存儲過程是存儲過程是SQL Server服務器上一組預編服務器上一組預編譯的譯的Transact-SQL語句，用于完成某項任語句，用于完成某項任務，它可以接受參數、返回狀態值和參數務，它可以接受參數、返回狀態值和參數值，并且可以嵌套調用。值，并且可以嵌套調用。10.1

2、 10.1 存儲過程概述存儲過程概述nSQL Server存儲過程的類型包括：存儲過程的類型包括：系統存儲過程系統存儲過程用戶定義存儲過程用戶定義存儲過程臨時存儲過程臨時存儲過程擴展存儲過程。擴展存儲過程。1. 存儲過程的類型存儲過程的類型10.1 10.1 存儲過程概述存儲過程概述(1)系統存儲過程系統存儲過程 n是指由系統提供的存儲過程，主要存儲在是指由系統提供的存儲過程，主要存儲在master數據庫中數據庫中并以并以sp_為前綴，它從系統表中獲取信息，從而為系統管為前綴，它從系統表中獲取信息，從而為系統管理員管理理員管理SQL Server提供支持。提供支持。n通過系統存儲過程，通過系統

3、存儲過程，SQL Server中的許多管理性或信息性中的許多管理性或信息性的活動的活動(例如使用例如使用sp_depends、sp_helptexts可以了解數據可以了解數據數據庫對象、數據庫信息數據庫對象、數據庫信息)都可以順利有效地完成。盡管都可以順利有效地完成。盡管系統存儲過程被放在系統存儲過程被放在master數據庫中，仍可以在其他數據數據庫中，仍可以在其他數據庫中對其進行調用庫中對其進行調用(調用時，不必在存儲過程名前加上數調用時，不必在存儲過程名前加上數據庫名據庫名)。當創建一個新數據庫時，一些系統存儲過程會。當創建一個新數據庫時，一些系統存儲過程會在新數據庫中被自動創建。在新數據

4、庫中被自動創建。10.1 10.1 存儲過程概述存儲過程概述(2)用戶定義存儲過程用戶定義存儲過程n是由用戶創建并能完成某一特定功能是由用戶創建并能完成某一特定功能(例如例如查詢用戶所需數據信息查詢用戶所需數據信息)的存儲過程。它處的存儲過程。它處于用戶創建的數據庫中，存儲過程名前沒于用戶創建的數據庫中，存儲過程名前沒有前綴有前綴sp_。10.1 10.1 存儲過程概述存儲過程概述(3)臨時存儲過程臨時存儲過程n臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加局臨時存儲過程，且可以分別向該過程名稱

5、前面添加“#”或或“#”前綴表示。前綴表示。“#”表示本地臨時存儲過程，表示本地臨時存儲過程，“#”表表示全局臨時存儲過程。使用臨時存儲過程必須創建本地連示全局臨時存儲過程。使用臨時存儲過程必須創建本地連接，當接，當SQL Server關閉后，這些臨時存儲過程將自動被刪關閉后，這些臨時存儲過程將自動被刪除。除。n由于由于SQL Server支持重新使用執行計劃，所以連接到支持重新使用執行計劃，所以連接到SQL Server 2000的應用程序應使用的應用程序應使用sp_executesql系統存儲過程，系統存儲過程，而不使用臨時存儲過程。而不使用臨時存儲過程。10.1 10.1 存儲過程概述存

6、儲過程概述(4)擴展存儲過程擴展存儲過程n擴展存儲過程是擴展存儲過程是SQL Server可以動態裝載可以動態裝載和執行的動態鏈接庫和執行的動態鏈接庫(DLL)。當擴展存儲過。當擴展存儲過程加載到程加載到SQL Server中，它的使用方法與中，它的使用方法與系統存儲過程一樣。擴展存儲過程只能添系統存儲過程一樣。擴展存儲過程只能添加到加到master數據庫中，其前綴是數據庫中，其前綴是xp_。10.1 10.1 存儲過程概述存儲過程概述2. 存儲過程的功能特點存儲過程的功能特點nSQL Server的存儲過程可實現以下功能：的存儲過程可實現以下功能：(1)接收輸入參數并以輸出參數的形式為調用過

7、程接收輸入參數并以輸出參數的形式為調用過程或批處理返回多個值。或批處理返回多個值。(2)包含執行數據庫操作的編程語句，包括調用其包含執行數據庫操作的編程語句，包括調用其他過程。他過程。(3)為調用過程或批處理返回一個狀態值，以表示為調用過程或批處理返回一個狀態值，以表示成功或失敗成功或失敗(及失敗原因及失敗原因)。10.1 10.1 存儲過程概述存儲過程概述存儲過程具有以下優點存儲過程具有以下優點(1)模塊化編程。模塊化編程。(2)快速執行。快速執行。 (3)減少網絡通信量。減少網絡通信量。(4)提供安全機制。提供安全機制。(5)保證操作一致性。保證操作一致性。10.2.1 創建存儲過程創建存

8、儲過程10.2.2 執行存儲過程執行存儲過程10.2.3 修改存儲過程修改存儲過程10.2.4 刪除存儲過程刪除存儲過程10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值10.2 存儲過程的創建與使用存儲過程的創建與使用10.2.1 創建存儲過程創建存儲過程1. 使用企業管理器創使用企業管理器創建存儲過程建存儲過程(1)啟動企業管理器，啟動企業管理器，登錄到要使用的服登錄到要使用的服務器。務器。(2)選擇要創建存儲過選擇要創建存儲過程的數據庫，在左程的數據庫，在左窗格中單擊窗格中單擊“存儲存儲過程過程”文件夾。文件夾。10.2 10.2 存儲過程的創建與使用存儲過程的創建與使用(3)右擊右

9、擊“存儲過程存儲過程”文件文件夾，在彈出菜單中選擇夾，在彈出菜單中選擇“新建存儲過程新建存儲過程”選項，選項，此時打開創建存儲過程此時打開創建存儲過程對話框。對話框。10.2.1 10.2.1 創建存儲過程創建存儲過程(4)在在“文本文本”編輯框中輸編輯框中輸入存儲過程正文。入存儲過程正文。(5)單擊單擊“檢查語法檢查語法”按鈕，按鈕，檢查語法是否正確。檢查語法是否正確。(6)單擊單擊“確定確定”按鈕，保按鈕，保存存儲過程。存存儲過程。(7)在圖在圖10-1的右窗格中，的右窗格中，右擊該存儲過程，在彈右擊該存儲過程，在彈出菜單中選擇出菜單中選擇“所有任所有任務務”，選擇，選擇“管理權管理權限限

10、”，在，在“對象屬性對象屬性”對話框中設置權限（如對話框中設置權限（如設置設置PUBLIC用戶有用戶有EXEC權限）。權限）。10.2.1 10.2.1 創建存儲過程創建存儲過程2. 使用向導創建存儲過程使用向導創建存儲過程(1)在企業管理器中選擇當前在企業管理器中選擇當前服務器，然后執行服務器，然后執行“工具工具向導向導”菜單命令，彈出菜單命令，彈出“選擇向導選擇向導”對話框。對話框。(2)在在“選擇向導選擇向導”對話框中對話框中展開展開“數據庫數據庫”項，雙擊項，雙擊“創建存儲過程向導創建存儲過程向導”項，項，彈出彈出“創建存儲過程創建存儲過程”對對話框。話框。10.2.1 10.2.1

11、創建存儲過程創建存儲過程(3)單擊單擊“下一步下一步”按鈕，按鈕，進入進入“選擇數據庫選擇數據庫”對話框對話框 10.2.1 10.2.1 創建存儲過程創建存儲過程(4)單擊單擊“下一步下一步”按鈕，按鈕，進入進入“選擇存儲過程選擇存儲過程”對話框。在該對話框中，對話框。在該對話框中，設置該存儲過程中的表設置該存儲過程中的表要執行的操作，可同時要執行的操作，可同時設置一個或多個操作設置一個或多個操作(用用于插入、刪除和更新于插入、刪除和更新)。如果選擇多個操作，每如果選擇多個操作，每個操作都將創建一個存個操作都將創建一個存儲過程。這里為儲過程。這里為customer表創建插入存表創建插入存儲過

12、程。儲過程。10.2.1 10.2.1 創建存儲過程創建存儲過程(5)單擊單擊“下一步下一步”按鈕，按鈕，進入進入“完成存儲過程創完成存儲過程創建建”對話框。在該對話對話框。在該對話框中顯示將要創建的存框中顯示將要創建的存儲過程的名稱及其操作，儲過程的名稱及其操作，這里的存儲過程名稱是這里的存儲過程名稱是insert_customer_1。10.2.1 10.2.1 創建存儲過程創建存儲過程(6)在圖在圖10-7中選擇一個中選擇一個存儲過程，單擊存儲過程，單擊“編編輯輯”按鈕，進入按鈕，進入“編編輯存儲過程屬性輯存儲過程屬性”對對話框。在此可以重新話框。在此可以重新設置存儲過程的名稱設置存儲過

13、程的名稱和要操作的字段。和要操作的字段。10.2.1 10.2.1 創建存儲過程創建存儲過程(7)如果要通過如果要通過SQL語句對語句對正在創建的存儲過程進正在創建的存儲過程進行修改，單擊行修改，單擊“編輯編輯SQL”按鈕，彈出按鈕，彈出“編輯編輯存儲過程存儲過程SQL”對話框。對話框。(8)SQL語句編輯完成后，語句編輯完成后，單擊單擊“分析分析”按鈕，將按鈕，將對存儲過程進行分析。對存儲過程進行分析。分析成功后，單擊分析成功后，單擊“確確定定”按鈕，返回圖按鈕，返回圖10-7“完成存儲過程完成存儲過程”對話對話框，再單擊框，再單擊“完成完成”按按鈕，開始創建存儲過程，鈕，開始創建存儲過程，

14、并彈出創建成功對話框。并彈出創建成功對話框。10.2.1 10.2.1 創建存儲過程創建存儲過程3. 使用使用CREATE PROCEDURE語句創建存儲語句創建存儲過程過程n使用使用CREATE PROCEDURE語句創建存儲過程語句創建存儲過程應該考慮以下幾個方面：應該考慮以下幾個方面：(1)在一個批處理中，在一個批處理中，CREATE PROCEDURE語句不能與語句不能與其他其他SQL語句合并在一起。語句合并在一起。(2)數據庫所有者具有默認的創建存儲過程的權限，它可數據庫所有者具有默認的創建存儲過程的權限，它可把該權限傳遞給其他的用戶。把該權限傳遞給其他的用戶。(3)存儲過程作為數據

15、庫對象其命名必須符合標識符的命存儲過程作為數據庫對象其命名必須符合標識符的命名規則。名規則。(4)只能在當前數據庫中創建屬于當前數據庫的存儲過程。只能在當前數據庫中創建屬于當前數據庫的存儲過程。10.2.1 10.2.1 創建存儲過程創建存儲過程創建存儲過程語句的語法格式如下：創建存儲過程語句的語法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR

16、 REPLICATION AS sql_statement ,.n 10.2.1 10.2.1 創建存儲過程創建存儲過程例例10-1 創建存儲過程，從表創建存儲過程，從表goods和表和表goods_classification的聯接中返回商品名、商的聯接中返回商品名、商品類別、單價。品類別、單價。10.2.1 10.2.1 創建存儲過程創建存儲過程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification

17、 gcON g.classification_id = gc.classification_idn存儲過程創建后，存儲過程的名稱存放在存儲過程創建后，存儲過程的名稱存放在sysobject表中，表中，文本存放在文本存放在syscomments表中。表中。10.2.2 執行存儲過程執行存儲過程n執行存儲過程的語法格式：執行存儲過程的語法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2

18、10.2 存儲過程的創建與使用存儲過程的創建與使用例如，執行例例如，執行例10-1的存儲過程的存儲過程goods_infon在在SQL查詢分析器中輸入命令：查詢分析器中輸入命令：EXEC goods_infon運行的結果：運行的結果：10.2.2 10.2.2 執行存儲過程執行存儲過程10.2 10.2 存儲過程的創建與使用存儲過程的創建與使用10.2.3 修改存儲過程修改存儲過程n修改存儲過程可以通過企業管理器和修改存儲過程可以通過企業管理器和Transact SQL語語句實現。句實現。1. 使用企業管理器修改存儲過程使用企業管理器修改存儲過程(1)在企業管理器中展開服務器組，再展開服務器。

19、在企業管理器中展開服務器組，再展開服務器。(2)展開展開“數據庫數據庫”文件夾，再展開要修改存儲過程的數文件夾，再展開要修改存儲過程的數據庫。據庫。(3)在要修改的存儲過程上右擊，并在彈出的快捷菜單中在要修改的存儲過程上右擊，并在彈出的快捷菜單中選擇選擇“屬性屬性”項，或者雙擊要修改的存儲過程，彈出項，或者雙擊要修改的存儲過程，彈出“存儲過程屬性存儲過程屬性”窗口。窗口。10.2.3 10.2.3 修改存儲過程修改存儲過程(4)在在“文本文本”框中直接框中直接對其代碼進行修改，對其代碼進行修改，修改完成后，先檢查修改完成后，先檢查語法，正確后單擊語法，正確后單擊“確定確定”按鈕。按鈕。10.2

20、.3 10.2.3 修改存儲過程修改存儲過程2. 使用使用ALTER PROCEDURE語句修改存儲語句修改存儲過程過程nALTER PROCEDURE的語法規則是：的語法規則是：ALTER PROCEDURE procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 10.2.3 10.2.3 修改存儲過程修改存儲過程例例10-2 使用使用

21、ALTER PROCEDURE語句更改語句更改存儲過程。存儲過程。(1)創建存儲過程創建存儲過程employee_dep，以獲取總經理辦的，以獲取總經理辦的男員工。男員工。CREATE PROCEDURE employee_dep AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男男 AND e.department_id=D001GO執行存儲過程執行存儲過程empl

22、oyee_dep，結果如圖，結果如圖 10.2.3 10.2.3 修改存儲過程修改存儲過程(2) 查看查看employee_dep存儲過程的文本信息存儲過程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = P AND = employee_depGO10.2.3 10.2.3 修改存儲過程修改存儲過程(3)使用使用ALTER PROCEDURE語句對語句對employee_dep過程進行修改，使其能夠顯示出過程進行修改，使其能夠顯示出所有男

23、員工，并使所有男員工，并使employee_dep過程以加密方過程以加密方式存儲在表式存儲在表syscomments中中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男男 GO10.2.3 10.2.3 修改存儲過程修改存儲過程執行修改后的存儲過程執行修改后的存儲過程employ

24、ee_dep，結果，結果如圖：如圖：10.2.3 10.2.3 修改存儲過程修改存儲過程(4)從系統表從系統表sysobjects和和syscomments提取修提取修改后的存儲過程改后的存儲過程employee_dep的文本信息可的文本信息可以運行步驟（以運行步驟（2）中的代碼，結果如圖）中的代碼，結果如圖 n這是由于在這是由于在ALTER PROCEDURE語句中使用語句中使用WITH ENCRYPTION關鍵字對存儲過程關鍵字對存儲過程employee_dep的文本進行了加密，其文本信息顯的文本進行了加密，其文本信息顯示為亂碼。示為亂碼。10.2 10.2 存儲過程的創建與使用存儲過程的

25、創建與使用10.2.4 刪除存儲過程刪除存儲過程 n存儲過程可以被快速刪除和重建，因為它沒有存存儲過程可以被快速刪除和重建，因為它沒有存儲數據。儲數據。1. 使用企業管理器刪除存儲過程使用企業管理器刪除存儲過程(1)在企業管理器中展開服務器組，再展開相應的服在企業管理器中展開服務器組，再展開相應的服務器。務器。(2)展開展開“數據庫數據庫”文件夾，再展開要刪除存儲過程文件夾，再展開要刪除存儲過程的數據庫。的數據庫。10.2.4 10.2.4 刪除存儲過程刪除存儲過程(3)單擊單擊“存儲過程存儲過程”項，在項，在右窗格中右擊要刪除的存右窗格中右擊要刪除的存儲過程，選擇的同時按下儲過程，選擇的同時

26、按下Ctrl鍵可以同時選擇多個鍵可以同時選擇多個存儲過程，在彈擊的快捷存儲過程，在彈擊的快捷菜單中選擇菜單中選擇“刪除刪除”項，項，彈出彈出“除去對象除去對象”對話框。對話框。(4)在在“除去對象除去對象”對話框中對話框中單擊單擊“顯示相關性顯示相關性”按鈕，按鈕，可以顯示依附該存儲過程可以顯示依附該存儲過程的對象，在確定不影響其的對象，在確定不影響其他對象后單擊他對象后單擊“全部除去全部除去”按鈕，刪除所有選擇的存按鈕，刪除所有選擇的存儲過程。儲過程。10.2.4 10.2.4 刪除存儲過程刪除存儲過程2. 使用使用DROP PROCEDURE刪除存儲過程刪除存儲過程nDROP PROCED

27、URE的語法如下：的語法如下：DROP PROCEDURE procedure_name ,.n n例如刪除例例如刪除例10-2創建的存儲過程創建的存儲過程employee_dep：DROP PROCEDURE employee_depGO10.2 10.2 存儲過程的創建與使用存儲過程的創建與使用10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值n存儲過程和調用者之間通過參數交換數據，可以存儲過程和調用者之間通過參數交換數據，可以按輸入的參數執行，也可由參數輸出執行結果。按輸入的參數執行，也可由參數輸出執行結果。調用者通過存儲過程返回的狀態值對存儲過程進調用者通過存儲過程返回的狀態值對存

28、儲過程進行管理。行管理。1. 參數參數n存儲過程的參數在創建過程時聲明。存儲過程的參數在創建過程時聲明。nSQL Server支持兩類參數：輸入參數和輸出參數。支持兩類參數：輸入參數和輸出參數。10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值(1)輸入參數輸入參數n輸入參數允許調用程序為存儲過程傳送數輸入參數允許調用程序為存儲過程傳送數據值。要定義存儲過程的輸入參數，必須據值。要定義存儲過程的輸入參數，必須在在CREATE PROCEDURE語句中聲明一個語句中聲明一個或多個變量及類型。或多個變量及類型。10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值例

29、例10-3 創建帶參數的存儲過程，從表創建帶參數的存儲過程，從表employee、sell_order、goods、goods_classification的連接中返的連接中返回輸入的員工名、該員工銷售的商品名、商品類別、回輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。銷售量等信息。CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name, goods_name,classification_name, order_numFROM employee e INNER JOIN sell_order s

30、 ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE employee_name10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值n存儲過程存儲過程sell_info以以employee_name變量作為輸變量作為輸入參數，執行時，可以省略參數名，直接給參數入參數，執行時，可以省略參數名，直接給參數值

31、。在值。在SQL查詢分析器中輸入命令：查詢分析器中輸入命令：EXEC sell_info 東方牧東方牧n運行結果如圖。運行結果如圖。n參數值可以包含通配符參數值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“錢錢”的員工的銷售情況可以使用以下命令：的員工的銷售情況可以使用以下命令：nEXEC sell_info 錢錢%10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值執行時，參數可以由位置標識，也可以由名執行時，參數可以由位置標識，也可以由名字標識。字標識。n例如，定義一個具有例如，定義一個具有3個參數的存儲過程：個參數的存儲過程：CREATE PROC mypro

32、c val1 int, val2 int, val3 intAS .n參數以位置傳遞：參數以位置傳遞：EXEC myproc 10,20,15n參數以名字傳遞，每個值由對應的參數名引導：參數以名字傳遞，每個值由對應的參數名引導：EXEC myproc val2=20,val1=10,val3=15n按名字傳遞參數比按位置傳遞參數具有更大的靈活性。但按名字傳遞參數比按位置傳遞參數具有更大的靈活性。但是，按位置傳遞參數卻具有更快的速度。是，按位置傳遞參數卻具有更快的速度。10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值(2)輸出參數輸出參數n輸出參數允許存儲過程將數據值或游標變

33、輸出參數允許存儲過程將數據值或游標變量傳回調用程序。量傳回調用程序。nOUTPUT關鍵字用以指出能返回到調用它關鍵字用以指出能返回到調用它的批處理或過程中的參數。為了使用輸出的批處理或過程中的參數。為了使用輸出參數，在參數，在CREATE PROCEDURE和和EXECUTE語句中都必須使用語句中都必須使用OUTPUT關關鍵字。鍵字。10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值例例10-4 創建存儲過程創建存儲過程price_goods，通過輸入，通過輸入參數在參數在goods表中查找商品，以輸出參數獲取表中查找商品，以輸出參數獲取商品單價。商品單價。CREATE PR

34、OC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值n執行執行price_goods存儲過程的代碼如下：存儲過程的代碼如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT pricen運行結果是商品名為運行結果是商品名為Can

35、on LBP2900的商的商品單價：品單價：1380.0EXECUTE語句還需要關鍵字語句還需要關鍵字OUTPUT以允以允許參數值返回給變量。許參數值返回給變量。10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值(1)用用RETURN語句定義返回值語句定義返回值n存儲過程可以返回整型狀態值，表示過程是否成存儲過程可以返回整型狀態值，表示過程是否成功執行，或者過程失敗的原因。功執行，或者過程失敗的原因。n如果存儲過程沒有顯式設置返回代碼的值，則如果存儲過程沒有顯式設置返回代碼的值，則SQL Server返回代碼為返回代碼為 0，表示成功執行；若返，表示成功執行；若返回回-1-9

36、9之間的整數，表示沒有成功執行。也可之間的整數，表示沒有成功執行。也可以使用以使用RETURN語句，用大于語句，用大于0或小于或小于-99的整數的整數來定義自己的返回狀態值，以表示不同的執行結來定義自己的返回狀態值，以表示不同的執行結果。果。2. 返回存儲過程的狀態返回存儲過程的狀態10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值例例10-5 創建存儲過程，輸入商品類別，返回各種商創建存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值品名稱。在存儲過程中，用值15表示用戶沒有提供表示用戶沒有提供參數；值參數；值-l01表示沒有輸入商品類別；值表示沒有輸入商品類別；

37、值0表示過程表示過程運行沒有出錯。運行沒有出錯。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=cl_name) RETURN -101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND

38、gc.classification_name=cl_nameRETURN 010.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值n在執行過程時，要正確接收返回的狀態值，在執行過程時，要正確接收返回的狀態值，必須使用以下語句；必須使用以下語句；EXECUTE status_var=procedure_name(2)捕獲返回狀態值捕獲返回狀態值10.2.5 10.2.5 存儲過程參數與狀態值存儲過程參數與狀態值DECLARE return_status intEXEC return_status=cl_goods 筆記本計算機筆記本計算機IF return_status=15 SE

39、LECT 語法錯誤語法錯誤ELSE IF return_status=-101 SELECT 沒有找到該商品類別沒有找到該商品類別n執行時，將對不同的輸入值返回不同的狀態值及執行時，將對不同的輸入值返回不同的狀態值及處理結果。處理結果。例例10-5的存儲過程的存儲過程cl_goods執行時使用以下語執行時使用以下語句：句：n觸發器是一種特殊類型的存儲過程。觸發器是一種特殊類型的存儲過程。n觸發器主要是通過事件進行觸發而被執行的，而觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過過程名字直接調用。當對某一存儲過程可以通過過程名字直接調用。當對某一表進行表進行UPDATE、INSERT、

40、DELETE操作時，操作時，SQL Server就會自動執行觸發器所定義的就會自動執行觸發器所定義的SQL語語句，從而確保對數據的處理必須符合由這些句，從而確保對數據的處理必須符合由這些SQL語句所定義的規則。語句所定義的規則。n觸發器的主要作用就是能夠實現由主鍵和外鍵所觸發器的主要作用就是能夠實現由主鍵和外鍵所不能保證的參照完整性和數據的一致性。不能保證的參照完整性和數據的一致性。 10.3 觸發器概述觸發器概述10.4.1 創建觸發器創建觸發器10.4.2 刪除觸發器刪除觸發器10.4.3 修改觸發器修改觸發器10.4 觸發器的創建與使用觸發器的創建與使用10.4 10.4 觸發器的創建與

41、使用觸發器的創建與使用1. 使用企業管理器創建觸發器使用企業管理器創建觸發器(1)啟動企業管理器，登錄到指定的服務器上。啟動企業管理器，登錄到指定的服務器上。(2)展開數據庫，選擇要在其上創建觸發器的表所在展開數據庫，選擇要在其上創建觸發器的表所在的數據庫，單擊該表（如的數據庫，單擊該表（如customer）。）。10.4.1 創建觸發器創建觸發器10.4.1 10.4.1 創建觸發器創建觸發器(3)在選擇的數據表上在選擇的數據表上右擊鼠標，在彈出右擊鼠標，在彈出菜單上選擇菜單上選擇“所有所有任務任務”選項。單擊選項。單擊“管理觸發器管理觸發器”，彈出彈出“觸發器屬性觸發器屬性”對話框。對話框

42、。10.4.1 10.4.1 創建觸發器創建觸發器(4)在在“觸發器屬性觸發器屬性”對對話框的名稱列表框中話框的名稱列表框中選擇選擇“新建新建”項，在項，在文本框中輸入觸發器文本框中輸入觸發器文本。單擊文本。單擊“檢查語檢查語法法”檢查語句是否正檢查語句是否正確。確。(5)在在“觸發器屬性觸發器屬性”對對話框中單擊話框中單擊“應用應用”按鈕，在名稱下拉列按鈕，在名稱下拉列表中會有新創建的觸表中會有新創建的觸發器名字。單擊發器名字。單擊“確確定定”按鈕，關閉窗口，按鈕，關閉窗口，觸發器創建成功。觸發器創建成功。10.4.1 10.4.1 創建觸發器創建觸發器2. 使用使用CREATE TRIGG

43、ER語句創建觸發器語句創建觸發器nCREATE TRIGGER語句的語法格式如下：語句的語法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement . n 10.4.1 10.4.1 創建觸發器創建觸發器例例10-6 在在employee表上創建一個表上創建一個DELETE類類型的觸發器，該觸發器的名稱為型的觸發器，該觸發器的名稱為tr_employee。(1)創建觸發器創建觸發

44、器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個員工被刪除個員工被刪除SELECT msgRETURN10.4.1 10.4.1 創建觸發器創建觸發器(2)執行觸發器執行觸發器tr_employeen觸發器不能通過名字來執行，而是在相應的觸發器不能通過名字來執行，而是在相應的SQL語句被執行時自動觸發的。例如執行以下語句被執行時自動觸發的。例如執行以下DELETE語句：語句：DELETE FROM employeeWH

45、ERE employee_name = 張三張三n該語句要刪除員工姓名為該語句要刪除員工姓名為“張三張三”記錄，由此激記錄，由此激活了表活了表employee 的的DELETE類型的觸發器類型的觸發器tr_employee，系統執行，系統執行tr_employee觸發器中觸發器中AS之后的語句，并顯示以下信息：之后的語句，并顯示以下信息：1個員工被刪除個員工被刪除10.4.1 10.4.1 創建觸發器創建觸發器n在觸發器的執行過程中，在觸發器的執行過程中，SQL Server建立和管理兩個臨時建立和管理兩個臨時的虛擬表：的虛擬表：Deleted表和表和Inserted表。這兩個表包含了在激表。

46、這兩個表包含了在激發觸發器的操作中插入或刪除的所有記錄。在執行發觸發器的操作中插入或刪除的所有記錄。在執行INSERT或或UPDATE語句之后所有被添加或被更新的記錄語句之后所有被添加或被更新的記錄都會存儲在都會存儲在Inserted表中。表中。n在執行在執行DELETE或或UPDATE語句時，從觸發程序表中被刪語句時，從觸發程序表中被刪除的行會發送到除的行會發送到Deleted表。表。n對于更新操作，對于更新操作，SQL Server先將要進行修改的記錄存儲到先將要進行修改的記錄存儲到Deleted表中，然后再將修改后的數據復制到表中，然后再將修改后的數據復制到Inserted表以表以及觸發

47、程序表。及觸發程序表。3. Deleted表和表和Inserted表表10.4.1 10.4.1 創建觸發器創建觸發器例例10-7 為表為表customer創建一個名為創建一個名為test_tr的的觸發器，當執行添加、更新或刪除時，激活觸發器，當執行添加、更新或刪除時，激活該觸發器。該觸發器。n創建創建test_tr觸發器：觸發器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM inserted SELECT * FROM deletedncustomer表執行以下插入操作：表執行以下插入操作

48、：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,張三張三,1234567)10.4.1 10.4.1 創建觸發器創建觸發器nINSERT操作激活觸發器操作激活觸發器test_tr，輸出如圖，輸出如圖10-19所示的表格。所示的表格。10.4 10.4 觸發器的創建與使用觸發器的創建與使用10.4.2 刪除觸發器刪除觸發器1. 使用企業管理器刪除觸發器使用企業管理器刪除觸發器(1)啟動企業管理器，登錄到指定的服務器。啟動企業管理器，登錄到指定的服務器。(2)選擇數據庫和表，其操作過程與創建觸發選擇數據庫和

49、表，其操作過程與創建觸發器相似，參見圖器相似，參見圖10-17。10.4.2 10.4.2 刪除觸發器刪除觸發器(3)從快捷菜單項中選從快捷菜單項中選擇擇“所有任務所有任務管管理觸發器理觸發器”選項，選項，打開打開“觸發器屬性觸發器屬性”對話框。對話框。(4)在在“觸發器屬性觸發器屬性”對話框的對話框的“名稱名稱”列表中選擇要刪除列表中選擇要刪除的觸發器名稱，再的觸發器名稱，再選擇選擇“刪除刪除”按鈕按鈕完成刪除操作。完成刪除操作。10.4.2 10.4.2 刪除觸發器刪除觸發器2. 使用使用DROP TRIGGER刪除指定觸發器刪除指定觸發器n刪除觸發器語句的語法格式如下：刪除觸發器語句的語

50、法格式如下：DROP TRIGGER trigger_name , .n n例如，刪除例例如，刪除例10-6的觸發器的觸發器tr_employee：DROP TRIGGER tr_employee10.4.3 修改觸發器修改觸發器n通過企業管理器、系統存儲過程或通過企業管理器、系統存儲過程或Transact_SQL語句，可以修改觸發器的名語句，可以修改觸發器的名字和正文。字和正文。1. 使用使用sp_rename系統存儲過程修改觸發器的系統存儲過程修改觸發器的名字：名字：sp_rename oldname, newname10.4 10.4 觸發器的創建與使用觸發器的創建與使用10.4.3 1

51、0.4.3 修改觸發器修改觸發器2. 使用企業管理器修改觸發器的正文使用企業管理器修改觸發器的正文操作步驟如下：操作步驟如下：(1)啟動企業管理器，登錄到指定的服務器。啟動企業管理器，登錄到指定的服務器。(2)選擇數據庫和表，其操作過程與創建觸發器相似，選擇數據庫和表，其操作過程與創建觸發器相似，參見圖參見圖10-17。(3)從快捷菜單項中選擇從快捷菜單項中選擇“所有任務所有任務管理觸發器管理觸發器”選項，打開選項，打開“觸發器屬性觸發器屬性”對話框，參見圖對話框，參見圖10-20。(4)在在“觸發器屬性觸發器屬性”對話框的對話框的“名稱名稱”列表中選擇列表中選擇要修改的觸發器名，在要修改的觸

52、發器名，在“文本文本”編輯框中可以修編輯框中可以修改選擇的觸發器正文。再使用改選擇的觸發器正文。再使用“檢查語法檢查語法”選項選項對語法進行檢查。對語法進行檢查。10.4.3 10.4.3 修改觸發器修改觸發器3. 使用使用ALTER TRIGGER語句修改觸發器語句修改觸發器n修改觸發器的語法如下：修改觸發器的語法如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 10.4.3 10.

53、4.3 修改觸發器修改觸發器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個員工數據個員工數據被插入被插入SELECT msgRETURNn對對employee表執行以下插入語句：表執行以下插入語句：INSERT employee(employee_id,employee_name)VALUES (E016,王五王五)n激活激活INSERT觸發器觸發器tr_employee，顯示信息：，顯示信息：1個員工數據被插入個員工數據被插入例如，將例例如

54、，將例10-6的觸發器的觸發器tr_employee修改為修改為INSERT操作后進行。操作后進行。10.5.1 事務概述事務概述10.5.2 事務管理事務管理10.5 事務處理事務處理10.5 10.5 事務處理事務處理n事務事務(Transaction)是是SQL Server中的一個邏中的一個邏輯工作單元，該單元將被作為一個整體進輯工作單元，該單元將被作為一個整體進行處理。行處理。n事務保證連續多個操作必須全部執行成功，事務保證連續多個操作必須全部執行成功，否則必須立即回復到未執行任何操作的狀否則必須立即回復到未執行任何操作的狀態，即執行事務的結果要不全部將數據所態，即執行事務的結果要不

55、全部將數據所要執行的操作完成，要不全部數據都不修要執行的操作完成，要不全部數據都不修改。改。10.5.1 事務概述事務概述10.5.1 10.5.1 事務概述事務概述n例如，企業取消了倉儲部，需要將例如，企業取消了倉儲部，需要將“倉儲部倉儲部”從從department表中刪除，而表中刪除，而employee表中的部門編號與倉儲表中的部門編號與倉儲部相對應的員工也應刪除。部相對應的員工也應刪除。假設倉儲部編號為假設倉儲部編號為D004，第一條，第一條DELETE語句修改語句修改department表表為：為：DELETE FROM department WHERE department_id =

56、 D004第二條第二條DELETE語句修改語句修改employee表為：表為：DELETE FROM employee WHERE department_id = D004n因此，必須保證這兩條因此，必須保證這兩條DELETE語句同時執行，或都不執語句同時執行，或都不執行。這時可以使用數據庫中的事務行。這時可以使用數據庫中的事務(Transaction)技術來實技術來實現。現。1事務的由來事務的由來2事務屬性事務屬性n由于事務作為一個邏輯工作單元，當事務由于事務作為一個邏輯工作單元，當事務執行遇到錯誤時，將取消事務所做的修改。執行遇到錯誤時，將取消事務所做的修改。一個邏輯單元必須具有一個邏輯單

57、元必須具有4個屬性：個屬性：原子性原子性(Atomicity)、一致性一致性(Consistency)隔離性隔離性(Isolation)持久性持久性(Durability)，n這些屬性稱為這些屬性稱為ACID。10.5.1 10.5.1 事務概述事務概述3事務模式事務模式nSQL Server以以3種事務模式管理事務。種事務模式管理事務。(1) 自動提交事務模式：每條單獨的語句都是一個事務。自動提交事務模式：每條單獨的語句都是一個事務。在此模式下，每條在此模式下，每條Transact-SQL語句在成功執行完成語句在成功執行完成后，都被自動提交，如果遇到錯誤，則自動回滾該語后，都被自動提交，如果

58、遇到錯誤，則自動回滾該語句。該模式為系統默認的事務管理模式。句。該模式為系統默認的事務管理模式。(2) 顯式事務模式：該模式允許用戶定義事務的啟動和結顯式事務模式：該模式允許用戶定義事務的啟動和結束。事務以束。事務以BEGIN TRANSACTION語句顯式開始，以語句顯式開始，以COMMIT或或ROLLBACK語句顯式結束。語句顯式結束。(3) 隱性事務模式：在當前事務完成提交或回滾后，新事隱性事務模式：在當前事務完成提交或回滾后，新事務自動啟動。隱性事務不需要使用務自動啟動。隱性事務不需要使用BEGIN TRANSACTION語句標識事務的開始，但需要以語句標識事務的開始，但需要以COMM

59、IT或或ROLLBACK語句來提交或回滾事務。語句來提交或回滾事務。10.5.1 10.5.1 事務概述事務概述10.5 10.5 事務處理事務處理1啟動和結束事務啟動和結束事務n啟動事務語句的語法格式如下：啟動事務語句的語法格式如下：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description n結束事務語句的語法格式如下：結束事務語句的語法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事務管理事務管理10.

60、5.2 10.5.2 事務管理事務管理例例10-8 建立一個顯式事務以顯示建立一個顯式事務以顯示Sales數據庫數據庫的的employee表的數據。表的數據。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTIONn本例創建的事務以本例創建的事務以BEGIN TRANSACTION語句語句開始，以開始，以COMMIT TRANSACTION語句結束。語句結束。10.5.2 10.5.2 事務管理事務管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_d