視圖、存儲(chǔ)過程和觸發(fā)器

1、SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第1頁第第10章章 視圖、存儲(chǔ)過程和觸發(fā)器視圖、存儲(chǔ)過程和觸發(fā)器 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社教學(xué)目標(biāo)教學(xué)重點(diǎn)教學(xué)過程SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第2頁教學(xué)目標(biāo)教學(xué)目標(biāo)l視圖、存儲(chǔ)過程和觸發(fā)器都是數(shù)據(jù)庫中的視圖、存儲(chǔ)過程和觸發(fā)器都是數(shù)據(jù)庫中的重要對象。本章講述視圖、存儲(chǔ)過程和觸重要對象。本章講述視圖、存儲(chǔ)過程和觸發(fā)器的管理操作。發(fā)器的管理操作。

2、 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第3頁教學(xué)重點(diǎn)教學(xué)重點(diǎn)l了解視圖的基本概念和特點(diǎn)了解視圖的基本概念和特點(diǎn)l掌握創(chuàng)建和修改視圖技術(shù)掌握創(chuàng)建和修改視圖技術(shù)l掌握使用視圖更新數(shù)據(jù)技術(shù)掌握使用視圖更新數(shù)據(jù)技術(shù)l了解存儲(chǔ)過程的類型和作用了解存儲(chǔ)過程的類型和作用l掌握創(chuàng)建存儲(chǔ)過程技術(shù)掌握創(chuàng)建存儲(chǔ)過程技術(shù)l掌握執(zhí)行存儲(chǔ)過程技術(shù)掌握執(zhí)行存儲(chǔ)過程技術(shù)l理解觸發(fā)器的特點(diǎn)和作用理解觸發(fā)器的特點(diǎn)和作用l掌握創(chuàng)建觸發(fā)器技術(shù)掌握創(chuàng)建觸發(fā)器技術(shù)SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6

3、月17日第4頁教學(xué)過程教學(xué)過程l視圖視圖l存儲(chǔ)過程存儲(chǔ)過程l觸發(fā)器觸發(fā)器 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第5頁10.1 視圖視圖 l視圖的特點(diǎn)視圖的特點(diǎn)l創(chuàng)建、修改和刪除視圖創(chuàng)建、修改和刪除視圖 l通過視圖更新數(shù)據(jù)通過視圖更新數(shù)據(jù) l查看和隱藏視圖定義信息查看和隱藏視圖定義信息 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第6頁視圖的特點(diǎn)視圖的特點(diǎn)l視圖只是一種邏輯對象，是一種虛擬表，視圖只是一種邏輯對象，是一種虛擬表，它并不是物理對象，因?yàn)橐晥D不占

4、物理存它并不是物理對象，因?yàn)橐晥D不占物理存儲(chǔ)空間。在視圖中被查詢的表稱為視圖的儲(chǔ)空間。在視圖中被查詢的表稱為視圖的基表。大多數(shù)的基表。大多數(shù)的SELECT語句都可以用在語句都可以用在創(chuàng)建視圖中。創(chuàng)建視圖中。l使用視圖有許多優(yōu)點(diǎn)，例如集中用戶使用使用視圖有許多優(yōu)點(diǎn)，例如集中用戶使用的數(shù)據(jù)、掩碼數(shù)據(jù)的復(fù)雜性、簡化權(quán)限管的數(shù)據(jù)、掩碼數(shù)據(jù)的復(fù)雜性、簡化權(quán)限管理以及為向其他應(yīng)用程序輸出而重新組織理以及為向其他應(yīng)用程序輸出而重新組織數(shù)據(jù)等等。數(shù)據(jù)等等。 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第7頁創(chuàng)建、修改和刪除視圖創(chuàng)建、修改和刪除視

5、圖l當(dāng)創(chuàng)建視圖時(shí)，當(dāng)創(chuàng)建視圖時(shí)，Microsoft SQL Server首先驗(yàn)證首先驗(yàn)證在視圖定義中所參考的對象是否存在。視圖的名在視圖定義中所參考的對象是否存在。視圖的名稱必須符合命名規(guī)則。指定視圖所有者的姓名是稱必須符合命名規(guī)則。指定視圖所有者的姓名是可選的。因?yàn)橐晥D的外表和表的外表是一樣的，可選的。因?yàn)橐晥D的外表和表的外表是一樣的，因此，為了區(qū)別表和視圖，應(yīng)該使用一種命名機(jī)因此，為了區(qū)別表和視圖，應(yīng)該使用一種命名機(jī)制，使人容易分辨出視圖和表。創(chuàng)建視圖的語法制，使人容易分辨出視圖和表。創(chuàng)建視圖的語法形式如下：形式如下： CREATE VIEW view_name ( column, n)

6、WITH ENCRYPTION AS select_statement WITH CHECK OPTIONSQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第8頁通過視圖更新數(shù)據(jù)通過視圖更新數(shù)據(jù)l如果滿足一些限制條件，那么可以通過視圖自由地插入、如果滿足一些限制條件，那么可以通過視圖自由地插入、刪除和更新數(shù)據(jù)。一般地，視圖必須定義在一個(gè)表上并且刪除和更新數(shù)據(jù)。一般地，視圖必須定義在一個(gè)表上并且不包括合計(jì)函數(shù)或在不包括合計(jì)函數(shù)或在SELECT語句中不包括語句中不包括GROUP BY子句。在修改視圖時(shí)，要注意下列一些條件：子句。在修改視圖

7、時(shí)，要注意下列一些條件： 不能同時(shí)影響兩個(gè)或兩個(gè)以上的基表?？梢孕薷挠蓛蓚€(gè)或兩個(gè)以不能同時(shí)影響兩個(gè)或兩個(gè)以上的基表。可以修改由兩個(gè)或兩個(gè)以上的基表得到的視圖，但是每一次修改的數(shù)據(jù)只能影響一個(gè)基表；上的基表得到的視圖，但是每一次修改的數(shù)據(jù)只能影響一個(gè)基表； 某些列不能修改。不能修改那些通過計(jì)算得到結(jié)果的列，例如包某些列不能修改。不能修改那些通過計(jì)算得到結(jié)果的列，例如包含有計(jì)算值的列、有內(nèi)置函數(shù)的列或有合計(jì)函數(shù)的列；含有計(jì)算值的列、有內(nèi)置函數(shù)的列或有合計(jì)函數(shù)的列； 如果影響到表中那些沒有缺省值的列，那么可能引起錯(cuò)誤。例如，如果影響到表中那些沒有缺省值的列，那么可能引起錯(cuò)誤。例如，如果使用如果使用I

8、NSERT語句向視圖中插入數(shù)據(jù)，且該視圖的基表有一語句向視圖中插入數(shù)據(jù)，且該視圖的基表有一個(gè)沒有缺省值的列或有一個(gè)不允許空的列，且該列沒有出現(xiàn)在視個(gè)沒有缺省值的列或有一個(gè)不允許空的列，且該列沒有出現(xiàn)在視圖的定義中，那么就會(huì)產(chǎn)生一個(gè)錯(cuò)誤消息；圖的定義中，那么就會(huì)產(chǎn)生一個(gè)錯(cuò)誤消息； 如果在視圖定義中指定了如果在視圖定義中指定了WITH CHECK OPTION選項(xiàng)，那么系統(tǒng)選項(xiàng)，那么系統(tǒng)驗(yàn)證所修改的數(shù)據(jù)。驗(yàn)證所修改的數(shù)據(jù)。 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第9頁查看和隱藏視圖定義信息查看和隱藏視圖定義信息l為了顯示創(chuàng)建視

9、圖的文本信息，使用為了顯示創(chuàng)建視圖的文本信息，使用SQL Server Enterprise Manager查詢查詢Information_schema.views視圖或執(zhí)行視圖或執(zhí)行sp_helptext系統(tǒng)存儲(chǔ)過程，并且使用視圖名稱作系統(tǒng)存儲(chǔ)過程，并且使用視圖名稱作為該系統(tǒng)存儲(chǔ)過程的參數(shù)，其語法形式為為該系統(tǒng)存儲(chǔ)過程的參數(shù)，其語法形式為sp_helptext object_name。 l在實(shí)際應(yīng)用中，經(jīng)常需要禁止用戶看到這些定義在實(shí)際應(yīng)用中，經(jīng)常需要禁止用戶看到這些定義文本。如果不需要用戶看到這些定義，那么可以文本。如果不需要用戶看到這些定義，那么可以把把syscomments系統(tǒng)表中的有

10、關(guān)內(nèi)容進(jìn)行加密。系統(tǒng)表中的有關(guān)內(nèi)容進(jìn)行加密。通過在視圖定義語句中增加通過在視圖定義語句中增加WITH ENCRYPTION選項(xiàng)，可以加密包含選項(xiàng)，可以加密包含CREATE VIEW語句文本的語句文本的syscomments系統(tǒng)表。系統(tǒng)表。 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第10頁10.2 存儲(chǔ)過程存儲(chǔ)過程 l存儲(chǔ)過程的特點(diǎn)存儲(chǔ)過程的特點(diǎn) l存儲(chǔ)過程的類型存儲(chǔ)過程的類型 l創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 l執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社20

11、22年6月17日第11頁存儲(chǔ)過程的特點(diǎn)存儲(chǔ)過程的特點(diǎn)l存儲(chǔ)過程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯存儲(chǔ)過程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯的的Transact-SQL語句。存儲(chǔ)過程是一種封語句。存儲(chǔ)過程是一種封裝重復(fù)任務(wù)操作的一種方法，支持用戶提裝重復(fù)任務(wù)操作的一種方法，支持用戶提供的變量，具有強(qiáng)大的編程功能。供的變量，具有強(qiáng)大的編程功能。l一般地，在一般地，在Microsoft SQL Server系統(tǒng)中系統(tǒng)中使用存儲(chǔ)過程可以帶來許多好處使用存儲(chǔ)過程可以帶來許多好處 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第12頁存儲(chǔ)過程的好處存儲(chǔ)過

12、程的好處l與其他應(yīng)用程序共享應(yīng)用程序的邏輯，因此確保一致的數(shù)與其他應(yīng)用程序共享應(yīng)用程序的邏輯，因此確保一致的數(shù)據(jù)訪問和操縱。據(jù)訪問和操縱。l提供一種安全機(jī)制。如果用戶被授予執(zhí)行存儲(chǔ)過程的權(quán)限，提供一種安全機(jī)制。如果用戶被授予執(zhí)行存儲(chǔ)過程的權(quán)限，那么即使該用戶沒有訪問在該存儲(chǔ)過程中所參考的表或視那么即使該用戶沒有訪問在該存儲(chǔ)過程中所參考的表或視圖的權(quán)限，該用戶也可以完全執(zhí)行該存儲(chǔ)過程，而不會(huì)受圖的權(quán)限，該用戶也可以完全執(zhí)行該存儲(chǔ)過程，而不會(huì)受到影響。到影響。l由于存儲(chǔ)過程在第一次執(zhí)行之后，其執(zhí)行規(guī)劃就駐存在過由于存儲(chǔ)過程在第一次執(zhí)行之后，其執(zhí)行規(guī)劃就駐存在過程高速緩沖存儲(chǔ)器中。在以后的操作中，只

13、需要從過程高程高速緩沖存儲(chǔ)器中。在以后的操作中，只需要從過程高速緩沖存儲(chǔ)器中調(diào)用編譯好的存儲(chǔ)過程的二進(jìn)制形式來執(zhí)速緩沖存儲(chǔ)器中調(diào)用編譯好的存儲(chǔ)過程的二進(jìn)制形式來執(zhí)行，因此可以加速存儲(chǔ)過程的執(zhí)行，提高系統(tǒng)的性能。行，因此可以加速存儲(chǔ)過程的執(zhí)行，提高系統(tǒng)的性能。l減少網(wǎng)絡(luò)交通。減少網(wǎng)絡(luò)交通。 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第13頁存儲(chǔ)過程的類型存儲(chǔ)過程的類型l在在Microsoft SQL Server 2000關(guān)系型數(shù)據(jù)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)中，支持庫管理系統(tǒng)中，支持5種類型的存儲(chǔ)過程：種類型的存儲(chǔ)過程：系統(tǒng)存儲(chǔ)過程、

14、本地存儲(chǔ)過程、臨時(shí)存儲(chǔ)系統(tǒng)存儲(chǔ)過程、本地存儲(chǔ)過程、臨時(shí)存儲(chǔ)過程、遠(yuǎn)程存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程。不過程、遠(yuǎn)程存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程。不同類型的存儲(chǔ)過程具有不同的作用。同類型的存儲(chǔ)過程具有不同的作用。SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第14頁創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程lCREATE PROCEDURE語句語法形式如下：語句語法形式如下： CREATE PROCEDURE procedure_name parameter data_type WITH RECOMPILE | ENCRYPTION | RECOMPILE , EN

15、CRYPTION AS sql_statementSQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第15頁執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程l只有具有存儲(chǔ)過程的執(zhí)行權(quán)限只有具有存儲(chǔ)過程的執(zhí)行權(quán)限EXECUTE，才可以執(zhí)行存儲(chǔ)過程。執(zhí)行存儲(chǔ)過程有兩才可以執(zhí)行存儲(chǔ)過程。執(zhí)行存儲(chǔ)過程有兩種方法。一種方法是直接執(zhí)行存儲(chǔ)過程，種方法。一種方法是直接執(zhí)行存儲(chǔ)過程，另外一種方法是在另外一種方法是在INSERT語句中執(zhí)行存儲(chǔ)語句中執(zhí)行存儲(chǔ)過程。過程。l直接執(zhí)行存儲(chǔ)過程就是調(diào)用直接執(zhí)行存儲(chǔ)過程就是調(diào)用EXECUTE語句語句來執(zhí)行存儲(chǔ)過程。在執(zhí)行存儲(chǔ)過程的時(shí)候，

16、來執(zhí)行存儲(chǔ)過程。在執(zhí)行存儲(chǔ)過程的時(shí)候，需要提供存儲(chǔ)過程的名稱和存儲(chǔ)過程所需需要提供存儲(chǔ)過程的名稱和存儲(chǔ)過程所需要的參數(shù)。要的參數(shù)。 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第16頁10.3 觸發(fā)器觸發(fā)器 l觸發(fā)器的概念觸發(fā)器的概念 l創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 l觸發(fā)器的工作原理觸發(fā)器的工作原理 l一個(gè)觸發(fā)器實(shí)例一個(gè)觸發(fā)器實(shí)例 SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第17頁觸發(fā)器的概念觸發(fā)器的概念l當(dāng)有操作影響到觸發(fā)器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器就自動(dòng)發(fā)生。當(dāng)有操作影

17、響到觸發(fā)器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器就自動(dòng)發(fā)生。因此，觸發(fā)器是在特定表上進(jìn)行定義的，該表也稱為觸發(fā)因此，觸發(fā)器是在特定表上進(jìn)行定義的，該表也稱為觸發(fā)器表。當(dāng)有操作針對觸發(fā)器表時(shí)，例如在表中插入、刪除、器表。當(dāng)有操作針對觸發(fā)器表時(shí)，例如在表中插入、刪除、更新數(shù)據(jù)時(shí)，如果該表有相應(yīng)操作類型的觸發(fā)器，那么觸更新數(shù)據(jù)時(shí)，如果該表有相應(yīng)操作類型的觸發(fā)器，那么觸發(fā)器就自動(dòng)觸發(fā)執(zhí)行。因?yàn)橛|發(fā)器在操作有效之后才能執(zhí)發(fā)器就自動(dòng)觸發(fā)執(zhí)行。因?yàn)橛|發(fā)器在操作有效之后才能執(zhí)行，所以在修改中他們代表行，所以在修改中他們代表“最后的動(dòng)作最后的動(dòng)作”。假如導(dǎo)致觸。假如導(dǎo)致觸發(fā)器的一個(gè)請求失敗，發(fā)器的一個(gè)請求失敗，SQL Serve

18、r系統(tǒng)將拒絕更新數(shù)據(jù)，系統(tǒng)將拒絕更新數(shù)據(jù)，并且對那些應(yīng)用程序返回一個(gè)錯(cuò)誤消息。并且對那些應(yīng)用程序返回一個(gè)錯(cuò)誤消息。l觸發(fā)器既然是一種特殊類型的存儲(chǔ)過程，那么它與存儲(chǔ)過觸發(fā)器既然是一種特殊類型的存儲(chǔ)過程，那么它與存儲(chǔ)過程也有一些區(qū)別。例如，存儲(chǔ)過程可以由用戶直接調(diào)用執(zhí)程也有一些區(qū)別。例如，存儲(chǔ)過程可以由用戶直接調(diào)用執(zhí)行，但是觸發(fā)器不能被直接調(diào)用執(zhí)行。行，但是觸發(fā)器不能被直接調(diào)用執(zhí)行。lMicrosoft SQL Server 2000系統(tǒng)提供了系統(tǒng)提供了3種類型的觸發(fā)種類型的觸發(fā)器，即器，即INSERT類型、類型、UPDATE類型和類型和DELETE類型。類型。SQL Server實(shí)用簡明教程實(shí)

19、用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第18頁創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器l可以使用可以使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器。語句創(chuàng)建觸發(fā)器。在在CREATE TRIGGER語句中，指定了定義觸發(fā)語句中，指定了定義觸發(fā)器的基表器的基表 、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指令。令。CREATE TRIGGER語句的基本語法形式如語句的基本語法形式如下：下： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF

20、DELETE , INSERT , UPDATE AS sql_statementSQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第19頁觸發(fā)器的工作原理（觸發(fā)器的工作原理（1）l當(dāng)向表中插入數(shù)據(jù)時(shí)，當(dāng)向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)觸發(fā)器觸發(fā)執(zhí)行。當(dāng)執(zhí)行。當(dāng)INSERT觸發(fā)器觸發(fā)時(shí)，新的記錄觸發(fā)器觸發(fā)時(shí)，新的記錄增加到觸發(fā)器表中和增加到觸發(fā)器表中和inserted表中。該表中。該inserted表是一個(gè)邏輯表，保存了所插入表是一個(gè)邏輯表，保存了所插入記錄的拷貝，允許用戶參考記錄的拷貝，允許用戶參考INSERT語句中語句中數(shù)

21、據(jù)。觸發(fā)器可以檢查數(shù)據(jù)。觸發(fā)器可以檢查inserted表，來確表，來確定該觸發(fā)器的操作是否應(yīng)該執(zhí)行和如何執(zhí)定該觸發(fā)器的操作是否應(yīng)該執(zhí)行和如何執(zhí)行。在行。在inserted表中的那些記錄，總是觸表中的那些記錄，總是觸發(fā)器表中一行或多行記錄的冗余。發(fā)器表中一行或多行記錄的冗余。SQL Server實(shí)用簡明教程實(shí)用簡明教程(第二版第二版) 清華大學(xué)出版社清華大學(xué)出版社2022年6月17日第20頁觸發(fā)器的工作原理（觸發(fā)器的工作原理（2）l當(dāng)觸發(fā)一個(gè)當(dāng)觸發(fā)一個(gè)DELETE觸發(fā)器時(shí)，被刪除的觸發(fā)器時(shí)，被刪除的記錄放在一個(gè)特殊的記錄放在一個(gè)特殊的deleted表中。表中。deleted表是一個(gè)邏輯表，用來保存