第10章存儲(chǔ)過(guò)程與觸發(fā)器

1、第第10章章 存儲(chǔ)過(guò)程與觸發(fā)器存儲(chǔ)過(guò)程與觸發(fā)器本章內(nèi)容本章內(nèi)容10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用10.3 觸發(fā)器概述觸發(fā)器概述10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用10.5 事務(wù)處理事務(wù)處理10.6 SQL Server的鎖機(jī)制的鎖機(jī)制10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 n存儲(chǔ)過(guò)程是存儲(chǔ)過(guò)程是SQL Server服務(wù)器上一組預(yù)編服務(wù)器上一組預(yù)編譯的譯的Transact-SQL語(yǔ)句，用于完成某項(xiàng)任語(yǔ)句，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。值，并且可以嵌套調(diào)用。10.1

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

3、存儲(chǔ)過(guò)程，SQL Server中的許多管理性或信息性中的許多管理性或信息性的活動(dòng)的活動(dòng)(例如使用例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)可以了解數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息)都可以順利有效地完成。盡管都可以順利有效地完成。盡管系統(tǒng)存儲(chǔ)過(guò)程被放在系統(tǒng)存儲(chǔ)過(guò)程被放在master數(shù)據(jù)庫(kù)中，仍可以在其他數(shù)據(jù)數(shù)據(jù)庫(kù)中，仍可以在其他數(shù)據(jù)庫(kù)中對(duì)其進(jìn)行調(diào)用庫(kù)中對(duì)其進(jìn)行調(diào)用(調(diào)用時(shí)，不必在存儲(chǔ)過(guò)程名前加上數(shù)調(diào)用時(shí)，不必在存儲(chǔ)過(guò)程名前加上數(shù)據(jù)庫(kù)名據(jù)庫(kù)名)。當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)。當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)建。在新數(shù)據(jù)

4、庫(kù)中被自動(dòng)創(chuàng)建。10.1 10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述(2)用戶(hù)定義存儲(chǔ)過(guò)程用戶(hù)定義存儲(chǔ)過(guò)程n是由用戶(hù)創(chuàng)建并能完成某一特定功能是由用戶(hù)創(chuàng)建并能完成某一特定功能(例如例如查詢(xún)用戶(hù)所需數(shù)據(jù)信息查詢(xún)用戶(hù)所需數(shù)據(jù)信息)的存儲(chǔ)過(guò)程。它處的存儲(chǔ)過(guò)程。它處于用戶(hù)創(chuàng)建的數(shù)據(jù)庫(kù)中，存儲(chǔ)過(guò)程名前沒(méi)于用戶(hù)創(chuàng)建的數(shù)據(jù)庫(kù)中，存儲(chǔ)過(guò)程名前沒(méi)有前綴有前綴sp_。10.1 10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述(3)臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程n臨時(shí)存儲(chǔ)過(guò)程與臨時(shí)表類(lèi)似，分為局部臨時(shí)存儲(chǔ)過(guò)程和全臨時(shí)存儲(chǔ)過(guò)程與臨時(shí)表類(lèi)似，分為局部臨時(shí)存儲(chǔ)過(guò)程和全局臨時(shí)存儲(chǔ)過(guò)程，且可以分別向該過(guò)程名稱(chēng)前面添加局臨時(shí)存儲(chǔ)過(guò)程，且可以分別向該過(guò)程名稱(chēng)

5、前面添加“#”或或“#”前綴表示。前綴表示?！?”表示本地臨時(shí)存儲(chǔ)過(guò)程，表示本地臨時(shí)存儲(chǔ)過(guò)程，“#”表表示全局臨時(shí)存儲(chǔ)過(guò)程。使用臨時(shí)存儲(chǔ)過(guò)程必須創(chuàng)建本地連示全局臨時(shí)存儲(chǔ)過(guò)程。使用臨時(shí)存儲(chǔ)過(guò)程必須創(chuàng)建本地連接，當(dāng)接，當(dāng)SQL Server關(guān)閉后，這些臨時(shí)存儲(chǔ)過(guò)程將自動(dòng)被刪關(guān)閉后，這些臨時(shí)存儲(chǔ)過(guò)程將自動(dòng)被刪除。除。n由于由于SQL Server支持重新使用執(zhí)行計(jì)劃，所以連接到支持重新使用執(zhí)行計(jì)劃，所以連接到SQL Server 2000的應(yīng)用程序應(yīng)使用的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲(chǔ)過(guò)程，系統(tǒng)存儲(chǔ)過(guò)程，而不使用臨時(shí)存儲(chǔ)過(guò)程。而不使用臨時(shí)存儲(chǔ)過(guò)程。10.1 10.1 存儲(chǔ)過(guò)程概述存

6、儲(chǔ)過(guò)程概述(4)擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程n擴(kuò)展存儲(chǔ)過(guò)程是擴(kuò)展存儲(chǔ)過(guò)程是SQL Server可以動(dòng)態(tài)裝載可以動(dòng)態(tài)裝載和執(zhí)行的動(dòng)態(tài)鏈接庫(kù)和執(zhí)行的動(dòng)態(tài)鏈接庫(kù)(DLL)。當(dāng)擴(kuò)展存儲(chǔ)過(guò)。當(dāng)擴(kuò)展存儲(chǔ)過(guò)程加載到程加載到SQL Server中，它的使用方法與中，它的使用方法與系統(tǒng)存儲(chǔ)過(guò)程一樣。擴(kuò)展存儲(chǔ)過(guò)程只能添系統(tǒng)存儲(chǔ)過(guò)程一樣。擴(kuò)展存儲(chǔ)過(guò)程只能添加到加到master數(shù)據(jù)庫(kù)中，其前綴是數(shù)據(jù)庫(kù)中，其前綴是xp_。10.1 10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述2. 存儲(chǔ)過(guò)程的功能特點(diǎn)存儲(chǔ)過(guò)程的功能特點(diǎn)nSQL Server的存儲(chǔ)過(guò)程可實(shí)現(xiàn)以下功能：的存儲(chǔ)過(guò)程可實(shí)現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過(guò)

7、程接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過(guò)程或批處理返回多個(gè)值?；蚺幚矸祷囟鄠€(gè)值。(2)包含執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句，包括調(diào)用其包含執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句，包括調(diào)用其他過(guò)程。他過(guò)程。(3)為調(diào)用過(guò)程或批處理返回一個(gè)狀態(tài)值，以表示為調(diào)用過(guò)程或批處理返回一個(gè)狀態(tài)值，以表示成功或失敗成功或失敗(及失敗原因及失敗原因)。10.1 10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)(1)模塊化編程。模塊化編程。(2)快速執(zhí)行?？焖賵?zhí)行。 (3)減少網(wǎng)絡(luò)通信量。減少網(wǎng)絡(luò)通信量。(4)提供安全機(jī)制。提供安全機(jī)制。(5)保證操作一致性。保證操作一致性。10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存

8、儲(chǔ)過(guò)程10.2.2 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程10.2.3 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程10.2.4 刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程1. 使用企業(yè)管理器創(chuàng)使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程建存儲(chǔ)過(guò)程(1)啟動(dòng)企業(yè)管理器，啟動(dòng)企業(yè)管理器，登錄到要使用的服登錄到要使用的服務(wù)器。務(wù)器。(2)選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)選擇要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，在左程的數(shù)據(jù)庫(kù)，在左窗格中單擊窗格中單擊“存儲(chǔ)存儲(chǔ)過(guò)程過(guò)程”文件夾。文件夾。10.2 10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用(3)右擊右

9、擊“存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程”文件文件夾，在彈出菜單中選擇夾，在彈出菜單中選擇“新建存儲(chǔ)過(guò)程新建存儲(chǔ)過(guò)程”選項(xiàng)，選項(xiàng)，此時(shí)打開(kāi)創(chuàng)建存儲(chǔ)過(guò)程此時(shí)打開(kāi)創(chuàng)建存儲(chǔ)過(guò)程對(duì)話(huà)框。對(duì)話(huà)框。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(4)在在“文本文本”編輯框中輸編輯框中輸入存儲(chǔ)過(guò)程正文。入存儲(chǔ)過(guò)程正文。(5)單擊單擊“檢查語(yǔ)法檢查語(yǔ)法”按鈕，按鈕，檢查語(yǔ)法是否正確。檢查語(yǔ)法是否正確。(6)單擊單擊“確定確定”按鈕，保按鈕，保存存儲(chǔ)過(guò)程。存存儲(chǔ)過(guò)程。(7)在圖在圖10-1的右窗格中，的右窗格中，右擊該存儲(chǔ)過(guò)程，在彈右擊該存儲(chǔ)過(guò)程，在彈出菜單中選擇出菜單中選擇“所有任所有任務(wù)務(wù)”，選擇，選擇“管理權(quán)管理權(quán)限限

10、”，在，在“對(duì)象屬性對(duì)象屬性”對(duì)話(huà)框中設(shè)置權(quán)限（如對(duì)話(huà)框中設(shè)置權(quán)限（如設(shè)置設(shè)置PUBLIC用戶(hù)有用戶(hù)有EXEC權(quán)限）。權(quán)限）。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程2. 使用向?qū)?chuàng)建存儲(chǔ)過(guò)程使用向?qū)?chuàng)建存儲(chǔ)過(guò)程(1)在企業(yè)管理器中選擇當(dāng)前在企業(yè)管理器中選擇當(dāng)前服務(wù)器，然后執(zhí)行服務(wù)器，然后執(zhí)行“工具工具向?qū)驅(qū)А辈藛蚊?，彈出菜單命令，彈出“選擇向?qū)нx擇向?qū)А睂?duì)話(huà)框。對(duì)話(huà)框。(2)在在“選擇向?qū)нx擇向?qū)А睂?duì)話(huà)框中對(duì)話(huà)框中展開(kāi)展開(kāi)“數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)”項(xiàng)，雙擊項(xiàng)，雙擊“創(chuàng)建存儲(chǔ)過(guò)程向?qū)?chuàng)建存儲(chǔ)過(guò)程向?qū)А表?xiàng)，項(xiàng)，彈出彈出“創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程”對(duì)對(duì)話(huà)框。話(huà)框。10.2.1 10.2.1

11、創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(3)單擊單擊“下一步下一步”按鈕，按鈕，進(jìn)入進(jìn)入“選擇數(shù)據(jù)庫(kù)選擇數(shù)據(jù)庫(kù)”對(duì)話(huà)框?qū)υ?huà)框 10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(4)單擊單擊“下一步下一步”按鈕，按鈕，進(jìn)入進(jìn)入“選擇存儲(chǔ)過(guò)程選擇存儲(chǔ)過(guò)程”對(duì)話(huà)框。在該對(duì)話(huà)框中，對(duì)話(huà)框。在該對(duì)話(huà)框中，設(shè)置該存儲(chǔ)過(guò)程中的表設(shè)置該存儲(chǔ)過(guò)程中的表要執(zhí)行的操作，可同時(shí)要執(zhí)行的操作，可同時(shí)設(shè)置一個(gè)或多個(gè)操作設(shè)置一個(gè)或多個(gè)操作(用用于插入、刪除和更新于插入、刪除和更新)。如果選擇多個(gè)操作，每如果選擇多個(gè)操作，每個(gè)操作都將創(chuàng)建一個(gè)存?zhèn)€操作都將創(chuàng)建一個(gè)存儲(chǔ)過(guò)程。這里為儲(chǔ)過(guò)程。這里為customer表創(chuàng)建插入存表創(chuàng)建插入存儲(chǔ)過(guò)

12、程。儲(chǔ)過(guò)程。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(5)單擊單擊“下一步下一步”按鈕，按鈕，進(jìn)入進(jìn)入“完成存儲(chǔ)過(guò)程創(chuàng)完成存儲(chǔ)過(guò)程創(chuàng)建建”對(duì)話(huà)框。在該對(duì)話(huà)對(duì)話(huà)框。在該對(duì)話(huà)框中顯示將要?jiǎng)?chuàng)建的存框中顯示將要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名稱(chēng)及其操作，儲(chǔ)過(guò)程的名稱(chēng)及其操作，這里的存儲(chǔ)過(guò)程名稱(chēng)是這里的存儲(chǔ)過(guò)程名稱(chēng)是insert_customer_1。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(6)在圖在圖10-7中選擇一個(gè)中選擇一個(gè)存儲(chǔ)過(guò)程，單擊存儲(chǔ)過(guò)程，單擊“編編輯輯”按鈕，進(jìn)入按鈕，進(jìn)入“編編輯存儲(chǔ)過(guò)程屬性輯存儲(chǔ)過(guò)程屬性”對(duì)對(duì)話(huà)框。在此可以重新話(huà)框。在此可以重新設(shè)置存儲(chǔ)過(guò)程的名稱(chēng)設(shè)置存儲(chǔ)過(guò)

13、程的名稱(chēng)和要操作的字段。和要操作的字段。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程(7)如果要通過(guò)如果要通過(guò)SQL語(yǔ)句對(duì)語(yǔ)句對(duì)正在創(chuàng)建的存儲(chǔ)過(guò)程進(jìn)正在創(chuàng)建的存儲(chǔ)過(guò)程進(jìn)行修改，單擊行修改，單擊“編輯編輯SQL”按鈕，彈出按鈕，彈出“編輯編輯存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程SQL”對(duì)話(huà)框。對(duì)話(huà)框。(8)SQL語(yǔ)句編輯完成后，語(yǔ)句編輯完成后，單擊單擊“分析分析”按鈕，將按鈕，將對(duì)存儲(chǔ)過(guò)程進(jìn)行分析。對(duì)存儲(chǔ)過(guò)程進(jìn)行分析。分析成功后，單擊分析成功后，單擊“確確定定”按鈕，返回圖按鈕，返回圖10-7“完成存儲(chǔ)過(guò)程完成存儲(chǔ)過(guò)程”對(duì)話(huà)對(duì)話(huà)框，再單擊框，再單擊“完成完成”按按鈕，開(kāi)始創(chuàng)建存儲(chǔ)過(guò)程，鈕，開(kāi)始創(chuàng)建存儲(chǔ)過(guò)程，

14、并彈出創(chuàng)建成功對(duì)話(huà)框。并彈出創(chuàng)建成功對(duì)話(huà)框。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程3. 使用使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程過(guò)程n使用使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程應(yīng)該考慮以下幾個(gè)方面：應(yīng)該考慮以下幾個(gè)方面：(1)在一個(gè)批處理中，在一個(gè)批處理中，CREATE PROCEDURE語(yǔ)句不能與語(yǔ)句不能與其他其他SQL語(yǔ)句合并在一起。語(yǔ)句合并在一起。(2)數(shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，它可數(shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶(hù)。把該權(quán)限傳遞給其他的用戶(hù)。(3)存儲(chǔ)過(guò)程作為數(shù)據(jù)

15、庫(kù)對(duì)象其命名必須符合標(biāo)識(shí)符的命存儲(chǔ)過(guò)程作為數(shù)據(jù)庫(kù)對(duì)象其命名必須符合標(biāo)識(shí)符的命名規(guī)則。名規(guī)則。(4)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程。只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句的語(yǔ)法格式如下：創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句的語(yǔ)法格式如下：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 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程例例10-1 創(chuàng)建存儲(chǔ)過(guò)程，從表創(chuàng)建存儲(chǔ)過(guò)程，從表goods和表和表goods_classification的聯(lián)接中返回商品名、商的聯(lián)接中返回商品名、商品類(lèi)別、單價(jià)。品類(lèi)別、單價(jià)。10.2.1 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程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存儲(chǔ)過(guò)程創(chuàng)建后，存儲(chǔ)過(guò)程的名稱(chēng)存放在存儲(chǔ)過(guò)程創(chuàng)建后，存儲(chǔ)過(guò)程的名稱(chēng)存放在sysobject表中，表中，文本存放在文本存放在syscomments表中。表中。10.2.2 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程n執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)法格式：執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2

18、10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用例如，執(zhí)行例例如，執(zhí)行例10-1的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程goods_infon在在SQL查詢(xún)分析器中輸入命令：查詢(xún)分析器中輸入命令：EXEC goods_infon運(yùn)行的結(jié)果：運(yùn)行的結(jié)果：10.2.2 10.2.2 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程10.2 10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.3 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程n修改存儲(chǔ)過(guò)程可以通過(guò)企業(yè)管理器和修改存儲(chǔ)過(guò)程可以通過(guò)企業(yè)管理器和Transact SQL語(yǔ)語(yǔ)句實(shí)現(xiàn)。句實(shí)現(xiàn)。1. 使用企業(yè)管理器修改存儲(chǔ)過(guò)程使用企業(yè)管理器修改存儲(chǔ)過(guò)程(1)在企業(yè)管理器中展開(kāi)服務(wù)器組，再展開(kāi)服務(wù)器。

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

20、.3 10.2.3 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程2. 使用使用ALTER PROCEDURE語(yǔ)句修改存儲(chǔ)語(yǔ)句修改存儲(chǔ)過(guò)程過(guò)程nALTER PROCEDURE的語(yǔ)法規(guī)則是：的語(yǔ)法規(guī)則是：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 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程例例10-2 使用使用

21、ALTER PROCEDURE語(yǔ)句更改語(yǔ)句更改存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程。(1)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程employee_dep，以獲取總經(jīng)理辦的，以獲取總經(jīng)理辦的男員工。男員工。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執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程empl

22、oyee_dep，結(jié)果如圖，結(jié)果如圖 10.2.3 10.2.3 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程(2) 查看查看employee_dep存儲(chǔ)過(guò)程的文本信息存儲(chǔ)過(guò)程的文本信息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 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程(3)使用使用ALTER PROCEDURE語(yǔ)句對(duì)語(yǔ)句對(duì)employee_dep過(guò)程進(jìn)行修改，使其能夠顯示出過(guò)程進(jìn)行修改，使其能夠顯示出所有男

23、員工，并使所有男員工，并使employee_dep過(guò)程以加密方過(guò)程以加密方式存儲(chǔ)在表式存儲(chǔ)在表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 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程執(zhí)行修改后的存儲(chǔ)過(guò)程執(zhí)行修改后的存儲(chǔ)過(guò)程employ

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

25、創(chuàng)建與使用10.2.4 刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程 n存儲(chǔ)過(guò)程可以被快速刪除和重建，因?yàn)樗鼪](méi)有存存儲(chǔ)過(guò)程可以被快速刪除和重建，因?yàn)樗鼪](méi)有存儲(chǔ)數(shù)據(jù)。儲(chǔ)數(shù)據(jù)。1. 使用企業(yè)管理器刪除存儲(chǔ)過(guò)程使用企業(yè)管理器刪除存儲(chǔ)過(guò)程(1)在企業(yè)管理器中展開(kāi)服務(wù)器組，再展開(kāi)相應(yīng)的服在企業(yè)管理器中展開(kāi)服務(wù)器組，再展開(kāi)相應(yīng)的服務(wù)器。務(wù)器。(2)展開(kāi)展開(kāi)“數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)”文件夾，再展開(kāi)要?jiǎng)h除存儲(chǔ)過(guò)程文件夾，再展開(kāi)要?jiǎng)h除存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)。的數(shù)據(jù)庫(kù)。10.2.4 10.2.4 刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程(3)單擊單擊“存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程”項(xiàng)，在項(xiàng)，在右窗格中右擊要?jiǎng)h除的存右窗格中右擊要?jiǎng)h除的存儲(chǔ)過(guò)程，選擇的同時(shí)按下儲(chǔ)過(guò)程，選擇的同時(shí)

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

27、URE的語(yǔ)法如下：的語(yǔ)法如下：DROP PROCEDURE procedure_name ,.n n例如刪除例例如刪除例10-2創(chuàng)建的存儲(chǔ)過(guò)程創(chuàng)建的存儲(chǔ)過(guò)程employee_dep：DROP PROCEDURE employee_depGO10.2 10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值n存儲(chǔ)過(guò)程和調(diào)用者之間通過(guò)參數(shù)交換數(shù)據(jù)，可以存儲(chǔ)過(guò)程和調(diào)用者之間通過(guò)參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過(guò)存儲(chǔ)過(guò)程返回的狀態(tài)值對(duì)存儲(chǔ)過(guò)程進(jìn)調(diào)用者通過(guò)存儲(chǔ)過(guò)程返回的狀態(tài)值對(duì)存

28、儲(chǔ)過(guò)程進(jìn)行管理。行管理。1. 參數(shù)參數(shù)n存儲(chǔ)過(guò)程的參數(shù)在創(chuàng)建過(guò)程時(shí)聲明。存儲(chǔ)過(guò)程的參數(shù)在創(chuàng)建過(guò)程時(shí)聲明。nSQL Server支持兩類(lèi)參數(shù)：輸入?yún)?shù)和輸出參數(shù)。支持兩類(lèi)參數(shù)：輸入?yún)?shù)和輸出參數(shù)。10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值(1)輸入?yún)?shù)輸入?yún)?shù)n輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過(guò)程傳送數(shù)輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過(guò)程傳送數(shù)據(jù)值。要定義存儲(chǔ)過(guò)程的輸入?yún)?shù)，必須據(jù)值。要定義存儲(chǔ)過(guò)程的輸入?yún)?shù)，必須在在CREATE PROCEDURE語(yǔ)句中聲明一個(gè)語(yǔ)句中聲明一個(gè)或多個(gè)變量及類(lèi)型?；蚨鄠€(gè)變量及類(lèi)型。10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例

29、例10-3 創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程，從表創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程，從表employee、sell_order、goods、goods_classification的連接中返的連接中返回輸入的員工名、該員工銷(xiāo)售的商品名、商品類(lèi)別、回輸入的員工名、該員工銷(xiāo)售的商品名、商品類(lèi)別、銷(xiāo)售量等信息。銷(xiāo)售量等信息。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 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值n存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程sell_info以以employee_name變量作為輸變量作為輸入?yún)?shù)，執(zhí)行時(shí)，可以省略參數(shù)名，直接給參數(shù)入?yún)?shù)，執(zhí)行時(shí)，可以省略參數(shù)名，直接給參數(shù)值

31、。在值。在SQL查詢(xún)分析器中輸入命令：查詢(xún)分析器中輸入命令：EXEC sell_info 東方牧東方牧n運(yùn)行結(jié)果如圖。運(yùn)行結(jié)果如圖。n參數(shù)值可以包含通配符參數(shù)值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“錢(qián)錢(qián)”的員工的銷(xiāo)售情況可以使用以下命令：的員工的銷(xiāo)售情況可以使用以下命令：nEXEC sell_info 錢(qián)錢(qián)%10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值執(zhí)行時(shí)，參數(shù)可以由位置標(biāo)識(shí)，也可以由名執(zhí)行時(shí)，參數(shù)可以由位置標(biāo)識(shí)，也可以由名字標(biāo)識(shí)。字標(biāo)識(shí)。n例如，定義一個(gè)具有例如，定義一個(gè)具有3個(gè)參數(shù)的存儲(chǔ)過(guò)程：個(gè)參數(shù)的存儲(chǔ)過(guò)程：CREATE PROC mypro

32、c val1 int, val2 int, val3 intAS .n參數(shù)以位置傳遞：參數(shù)以位置傳遞：EXEC myproc 10,20,15n參數(shù)以名字傳遞，每個(gè)值由對(duì)應(yīng)的參數(shù)名引導(dǎo)：參數(shù)以名字傳遞，每個(gè)值由對(duì)應(yīng)的參數(shù)名引導(dǎo)：EXEC myproc val2=20,val1=10,val3=15n按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。是，按位置傳遞參數(shù)卻具有更快的速度。10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值(2)輸出參數(shù)輸出參數(shù)n輸出參數(shù)允許存儲(chǔ)過(guò)程將數(shù)據(jù)值或游標(biāo)變

33、輸出參數(shù)允許存儲(chǔ)過(guò)程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。量傳回調(diào)用程序。nOUTPUT關(guān)鍵字用以指出能返回到調(diào)用它關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過(guò)程中的參數(shù)。為了使用輸出的批處理或過(guò)程中的參數(shù)。為了使用輸出參數(shù)，在參數(shù)，在CREATE PROCEDURE和和EXECUTE語(yǔ)句中都必須使用語(yǔ)句中都必須使用OUTPUT關(guān)關(guān)鍵字。鍵字。10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例例10-4 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程price_goods，通過(guò)輸入，通過(guò)輸入?yún)?shù)在參數(shù)在goods表中查找商品，以輸出參數(shù)獲取表中查找商品，以輸出參數(shù)獲取商品單價(jià)。商品單價(jià)。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 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值n執(zhí)行執(zhí)行price_goods存儲(chǔ)過(guò)程的代碼如下：存儲(chǔ)過(guò)程的代碼如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT pricen運(yùn)行結(jié)果是商品名為運(yùn)行結(jié)果是商品名為Can

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

36、9之間的整數(shù)，表示沒(méi)有成功執(zhí)行。也可之間的整數(shù)，表示沒(méi)有成功執(zhí)行。也可以使用以使用RETURN語(yǔ)句，用大于語(yǔ)句，用大于0或小于或小于-99的整數(shù)的整數(shù)來(lái)定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)來(lái)定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。果。2. 返回存儲(chǔ)過(guò)程的狀態(tài)返回存儲(chǔ)過(guò)程的狀態(tài)10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例例10-5 創(chuàng)建存儲(chǔ)過(guò)程，輸入商品類(lèi)別，返回各種商創(chuàng)建存儲(chǔ)過(guò)程，輸入商品類(lèi)別，返回各種商品名稱(chēng)。在存儲(chǔ)過(guò)程中，用值品名稱(chēng)。在存儲(chǔ)過(guò)程中，用值15表示用戶(hù)沒(méi)有提供表示用戶(hù)沒(méi)有提供參數(shù)；值參數(shù)；值-l01表示沒(méi)有輸入商品類(lèi)別；值表示沒(méi)有輸入商品類(lèi)別；

37、值0表示過(guò)程表示過(guò)程運(yùn)行沒(méi)有出錯(cuò)。運(yùn)行沒(méi)有出錯(cuò)。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 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值n在執(zhí)行過(guò)程時(shí)，要正確接收返回的狀態(tài)值，在執(zhí)行過(guò)程時(shí)，要正確接收返回的狀態(tài)值，必須使用以下語(yǔ)句；必須使用以下語(yǔ)句；EXECUTE status_var=procedure_name(2)捕獲返回狀態(tài)值捕獲返回狀態(tài)值10.2.5 10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值DECLARE return_status intEXEC return_status=cl_goods 筆記本計(jì)算機(jī)筆記本計(jì)算機(jī)IF return_status=15 SE

39、LECT 語(yǔ)法錯(cuò)誤語(yǔ)法錯(cuò)誤ELSE IF return_status=-101 SELECT 沒(méi)有找到該商品類(lèi)別沒(méi)有找到該商品類(lèi)別n執(zhí)行時(shí)，將對(duì)不同的輸入值返回不同的狀態(tài)值及執(zhí)行時(shí)，將對(duì)不同的輸入值返回不同的狀態(tài)值及處理結(jié)果。處理結(jié)果。例例10-5的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程cl_goods執(zhí)行時(shí)使用以下語(yǔ)執(zhí)行時(shí)使用以下語(yǔ)句：句：n觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。n觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的，而觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過(guò)程可以通過(guò)過(guò)程名字直接調(diào)用。當(dāng)對(duì)某一存儲(chǔ)過(guò)程可以通過(guò)過(guò)程名字直接調(diào)用。當(dāng)對(duì)某一表進(jìn)行表進(jìn)行UPDATE、INSERT、

40、DELETE操作時(shí)，操作時(shí)，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。語(yǔ)句所定義的規(guī)則。n觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。不能保證的參照完整性和數(shù)據(jù)的一致性。 10.3 觸發(fā)器概述觸發(fā)器概述10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器10.4.2 刪除觸發(fā)器刪除觸發(fā)器10.4.3 修改觸發(fā)器修改觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用10.4 10.4 觸發(fā)器的創(chuàng)建與

41、使用觸發(fā)器的創(chuàng)建與使用1. 使用企業(yè)管理器創(chuàng)建觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器(1)啟動(dòng)企業(yè)管理器，登錄到指定的服務(wù)器上。啟動(dòng)企業(yè)管理器，登錄到指定的服務(wù)器上。(2)展開(kāi)數(shù)據(jù)庫(kù)，選擇要在其上創(chuàng)建觸發(fā)器的表所在展開(kāi)數(shù)據(jù)庫(kù)，選擇要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫(kù)，單擊該表（如的數(shù)據(jù)庫(kù)，單擊該表（如customer）。）。10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器(3)在選擇的數(shù)據(jù)表上在選擇的數(shù)據(jù)表上右擊鼠標(biāo)，在彈出右擊鼠標(biāo)，在彈出菜單上選擇菜單上選擇“所有所有任務(wù)任務(wù)”選項(xiàng)。單擊選項(xiàng)。單擊“管理觸發(fā)器管理觸發(fā)器”，彈出彈出“觸發(fā)器屬性觸發(fā)器屬性”對(duì)話(huà)框。對(duì)話(huà)框

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

43、ER語(yǔ)句創(chuàng)建觸發(fā)器語(yǔ)句創(chuàng)建觸發(fā)器nCREATE TRIGGER語(yǔ)句的語(yǔ)法格式如下：語(yǔ)句的語(yǔ)法格式如下：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 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器例例10-6 在在employee表上創(chuàng)建一個(gè)表上創(chuàng)建一個(gè)DELETE類(lèi)類(lèi)型的觸發(fā)器，該觸發(fā)器的名稱(chēng)為型的觸發(fā)器，該觸發(fā)器的名稱(chēng)為tr_employee。(1)創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)

44、器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工被刪除個(gè)員工被刪除SELECT msgRETURN10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器執(zhí)行觸發(fā)器tr_employeen觸發(fā)器不能通過(guò)名字來(lái)執(zhí)行，而是在相應(yīng)的觸發(fā)器不能通過(guò)名字來(lái)執(zhí)行，而是在相應(yīng)的SQL語(yǔ)句被執(zhí)行時(shí)自動(dòng)觸發(fā)的。例如執(zhí)行以下語(yǔ)句被執(zhí)行時(shí)自動(dòng)觸發(fā)的。例如執(zhí)行以下DELETE語(yǔ)句：語(yǔ)句：DELETE FROM employeeWH

45、ERE employee_name = 張三張三n該語(yǔ)句要?jiǎng)h除員工姓名為該語(yǔ)句要?jiǎng)h除員工姓名為“張三張三”記錄，由此激記錄，由此激活了表活了表employee 的的DELETE類(lèi)型的觸發(fā)器類(lèi)型的觸發(fā)器tr_employee，系統(tǒng)執(zhí)行，系統(tǒng)執(zhí)行tr_employee觸發(fā)器中觸發(fā)器中AS之后的語(yǔ)句，并顯示以下信息：之后的語(yǔ)句，并顯示以下信息：1個(gè)員工被刪除個(gè)員工被刪除10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器n在觸發(fā)器的執(zhí)行過(guò)程中，在觸發(fā)器的執(zhí)行過(guò)程中，SQL Server建立和管理兩個(gè)臨時(shí)建立和管理兩個(gè)臨時(shí)的虛擬表：的虛擬表：Deleted表和表和Inserted表。這兩個(gè)表包含了在激表。

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

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

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

49、表，其操作過(guò)程與創(chuàng)建觸發(fā)器相似，參見(jiàn)圖器相似，參見(jiàn)圖10-17。10.4.2 10.4.2 刪除觸發(fā)器刪除觸發(fā)器(3)從快捷菜單項(xiàng)中選從快捷菜單項(xiàng)中選擇擇“所有任務(wù)所有任務(wù)管管理觸發(fā)器理觸發(fā)器”選項(xiàng)，選項(xiàng)，打開(kāi)打開(kāi)“觸發(fā)器屬性觸發(fā)器屬性”對(duì)話(huà)框。對(duì)話(huà)框。(4)在在“觸發(fā)器屬性觸發(fā)器屬性”對(duì)話(huà)框的對(duì)話(huà)框的“名稱(chēng)名稱(chēng)”列表中選擇要?jiǎng)h除列表中選擇要?jiǎng)h除的觸發(fā)器名稱(chēng)，再的觸發(fā)器名稱(chēng)，再選擇選擇“刪除刪除”按鈕按鈕完成刪除操作。完成刪除操作。10.4.2 10.4.2 刪除觸發(fā)器刪除觸發(fā)器2. 使用使用DROP TRIGGER刪除指定觸發(fā)器刪除指定觸發(fā)器n刪除觸發(fā)器語(yǔ)句的語(yǔ)法格式如下：刪除觸發(fā)器語(yǔ)句的語(yǔ)

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

51、0.4.3 修改觸發(fā)器修改觸發(fā)器2. 使用企業(yè)管理器修改觸發(fā)器的正文使用企業(yè)管理器修改觸發(fā)器的正文操作步驟如下：操作步驟如下：(1)啟動(dòng)企業(yè)管理器，登錄到指定的服務(wù)器。啟動(dòng)企業(yè)管理器，登錄到指定的服務(wù)器。(2)選擇數(shù)據(jù)庫(kù)和表，其操作過(guò)程與創(chuàng)建觸發(fā)器相似，選擇數(shù)據(jù)庫(kù)和表，其操作過(guò)程與創(chuàng)建觸發(fā)器相似，參見(jiàn)圖參見(jiàn)圖10-17。(3)從快捷菜單項(xiàng)中選擇從快捷菜單項(xiàng)中選擇“所有任務(wù)所有任務(wù)管理觸發(fā)器管理觸發(fā)器”選項(xiàng)，打開(kāi)選項(xiàng)，打開(kāi)“觸發(fā)器屬性觸發(fā)器屬性”對(duì)話(huà)框，參見(jiàn)圖對(duì)話(huà)框，參見(jiàn)圖10-20。(4)在在“觸發(fā)器屬性觸發(fā)器屬性”對(duì)話(huà)框的對(duì)話(huà)框的“名稱(chēng)名稱(chēng)”列表中選擇列表中選擇要修改的觸發(fā)器名，在要修改的觸

52、發(fā)器名，在“文本文本”編輯框中可以修編輯框中可以修改選擇的觸發(fā)器正文。再使用改選擇的觸發(fā)器正文。再使用“檢查語(yǔ)法檢查語(yǔ)法”選項(xiàng)選項(xiàng)對(duì)語(yǔ)法進(jìn)行檢查。對(duì)語(yǔ)法進(jìn)行檢查。10.4.3 10.4.3 修改觸發(fā)器修改觸發(fā)器3. 使用使用ALTER TRIGGER語(yǔ)句修改觸發(fā)器語(yǔ)句修改觸發(fā)器n修改觸發(fā)器的語(yǔ)法如下：修改觸發(fā)器的語(yǔ)法如下：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 修改觸發(fā)器修改觸發(fā)器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工數(shù)據(jù)個(gè)員工數(shù)據(jù)被插入被插入SELECT msgRETURNn對(duì)對(duì)employee表執(zhí)行以下插入語(yǔ)句：表執(zhí)行以下插入語(yǔ)句：INSERT employee(employee_id,employee_name)VALUES (E016,王五王五)n激活激活I(lǐng)NSERT觸發(fā)器觸發(fā)器tr_employee，顯示信息：，顯示信息：1個(gè)員工數(shù)據(jù)被插入個(gè)員工數(shù)據(jù)被插入例如，將例例如

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

55、全部將數(shù)據(jù)所要執(zhí)行的操作完成，要不全部數(shù)據(jù)都不修要執(zhí)行的操作完成，要不全部數(shù)據(jù)都不修改。改。10.5.1 事務(wù)概述事務(wù)概述10.5.1 10.5.1 事務(wù)概述事務(wù)概述n例如，企業(yè)取消了倉(cāng)儲(chǔ)部，需要將例如，企業(yè)取消了倉(cāng)儲(chǔ)部，需要將“倉(cāng)儲(chǔ)部倉(cāng)儲(chǔ)部”從從department表中刪除，而表中刪除，而employee表中的部門(mén)編號(hào)與倉(cāng)儲(chǔ)表中的部門(mén)編號(hào)與倉(cāng)儲(chǔ)部相對(duì)應(yīng)的員工也應(yīng)刪除。部相對(duì)應(yīng)的員工也應(yīng)刪除。假設(shè)倉(cāng)儲(chǔ)部編號(hào)為假設(shè)倉(cāng)儲(chǔ)部編號(hào)為D004，第一條，第一條DELETE語(yǔ)句修改語(yǔ)句修改department表表為：為：DELETE FROM department WHERE department_id =

56、 D004第二條第二條DELETE語(yǔ)句修改語(yǔ)句修改employee表為：表為：DELETE FROM employee WHERE department_id = D004n因此，必須保證這兩條因此，必須保證這兩條DELETE語(yǔ)句同時(shí)執(zhí)行，或都不執(zhí)語(yǔ)句同時(shí)執(zhí)行，或都不執(zhí)行。這時(shí)可以使用數(shù)據(jù)庫(kù)中的事務(wù)行。這時(shí)可以使用數(shù)據(jù)庫(kù)中的事務(wù)(Transaction)技術(shù)來(lái)實(shí)技術(shù)來(lái)實(shí)現(xiàn)?，F(xiàn)。1事務(wù)的由來(lái)事務(wù)的由來(lái)2事務(wù)屬性事務(wù)屬性n由于事務(wù)作為一個(gè)邏輯工作單元，當(dāng)事務(wù)由于事務(wù)作為一個(gè)邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯(cuò)誤時(shí)，將取消事務(wù)所做的修改。執(zhí)行遇到錯(cuò)誤時(shí)，將取消事務(wù)所做的修改。一個(gè)邏輯單元必須具有一個(gè)邏輯單

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

58、遇到錯(cuò)誤，則自動(dòng)回滾該語(yǔ)句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。(2) 顯式事務(wù)模式：該模式允許用戶(hù)定義事務(wù)的啟動(dòng)和結(jié)顯式事務(wù)模式：該模式允許用戶(hù)定義事務(wù)的啟動(dòng)和結(jié)束。事務(wù)以束。事務(wù)以BEGIN TRANSACTION語(yǔ)句顯式開(kāi)始，以語(yǔ)句顯式開(kāi)始，以COMMIT或或ROLLBACK語(yǔ)句顯式結(jié)束。語(yǔ)句顯式結(jié)束。(3) 隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動(dòng)啟動(dòng)。隱性事務(wù)不需要使用務(wù)自動(dòng)啟動(dòng)。隱性事務(wù)不需要使用BEGIN TRANSACTION語(yǔ)句標(biāo)識(shí)事務(wù)的開(kāi)始，但需要以語(yǔ)句標(biāo)識(shí)事務(wù)的開(kāi)始，但需要以COMM

59、IT或或ROLLBACK語(yǔ)句來(lái)提交或回滾事務(wù)。語(yǔ)句來(lái)提交或回滾事務(wù)。10.5.1 10.5.1 事務(wù)概述事務(wù)概述10.5 10.5 事務(wù)處理事務(wù)處理1啟動(dòng)和結(jié)束事務(wù)啟動(dòng)和結(jié)束事務(wù)n啟動(dòng)事務(wù)語(yǔ)句的語(yǔ)法格式如下：?jiǎn)?dòng)事務(wù)語(yǔ)句的語(yǔ)法格式如下：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description n結(jié)束事務(wù)語(yǔ)句的語(yǔ)法格式如下：結(jié)束事務(wù)語(yǔ)句的語(yǔ)法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事務(wù)管理事務(wù)管理10.

60、5.2 10.5.2 事務(wù)管理事務(wù)管理例例10-8 建立一個(gè)顯式事務(wù)以顯示建立一個(gè)顯式事務(wù)以顯示Sales數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)的的employee表的數(shù)據(jù)。表的數(shù)據(jù)。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTIONn本例創(chuàng)建的事務(wù)以本例創(chuàng)建的事務(wù)以BEGIN TRANSACTION語(yǔ)句語(yǔ)句開(kāi)始，以開(kāi)始，以COMMIT TRANSACTION語(yǔ)句結(jié)束。語(yǔ)句結(jié)束。10.5.2 10.5.2 事務(wù)管理事務(wù)管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_d