其他數(shù)據(jù)庫對象學習資料

1、第第10章章 其他數(shù)據(jù)庫對象其他數(shù)據(jù)庫對象學習目標本章重點本章內(nèi)容12/24/20211學習目標學習目標l從業(yè)務數(shù)據(jù)角度來看，同一種業(yè)務數(shù)據(jù)有可能分散在不同的表中，如從業(yè)務數(shù)據(jù)角度來看，同一種業(yè)務數(shù)據(jù)有可能分散在不同的表中，如何從一個數(shù)據(jù)庫對象中查看這些分散存儲的數(shù)據(jù)呢？何從一個數(shù)據(jù)庫對象中查看這些分散存儲的數(shù)據(jù)呢？l從安全角度來看，不同的操作人員或許只能看到表中不同部分的數(shù)據(jù)。從安全角度來看，不同的操作人員或許只能看到表中不同部分的數(shù)據(jù)。l從數(shù)據(jù)的應用角度來看，一個報表中的數(shù)據(jù)往往來自于多個不同的表從數(shù)據(jù)的應用角度來看，一個報表中的數(shù)據(jù)往往來自于多個不同的表中，如何提高報表的設計效率呢？視圖

2、是解決這些問題的一種有效手中，如何提高報表的設計效率呢？視圖是解決這些問題的一種有效手段。段。l存儲過程是一個可重用的代碼模塊，可以高效率地完成指定的操作。存儲過程是一個可重用的代碼模塊，可以高效率地完成指定的操作。l觸發(fā)器是一種特殊類型的存儲過程，可以實現(xiàn)自動化的操作。觸發(fā)器是一種特殊類型的存儲過程，可以實現(xiàn)自動化的操作。l用戶定義函數(shù)是由用戶根據(jù)自己應用程序的需要而定義的可以完成特用戶定義函數(shù)是由用戶根據(jù)自己應用程序的需要而定義的可以完成特定操作的函數(shù)。定操作的函數(shù)。l本章將全面研究視圖、存儲過程、觸發(fā)器、用戶定義函數(shù)等數(shù)據(jù)庫對本章將全面研究視圖、存儲過程、觸發(fā)器、用戶定義函數(shù)等數(shù)據(jù)庫對象

3、的特點和使用方式。象的特點和使用方式。12/24/20212本章重點本章重點l視圖特點和類型視圖特點和類型l管理視圖管理視圖l存儲過程特點和類型存儲過程特點和類型l管理存儲過程管理存儲過程l觸發(fā)器特點和類型觸發(fā)器特點和類型l管理觸發(fā)器管理觸發(fā)器l用戶定義函數(shù)用戶定義函數(shù)12/24/20213本章內(nèi)容本章內(nèi)容10.1 視圖視圖10.2 存儲過程存儲過程10.3 觸發(fā)器觸發(fā)器10.4 用戶定義函數(shù)用戶定義函數(shù)10.5 上機練習上機練習10.6 習題習題12/24/20214本章內(nèi)容本章內(nèi)容10.1 視圖視圖10.2 存儲過程存儲過程10.3 觸發(fā)器觸發(fā)器10.4 用戶定義函數(shù)用戶定義函數(shù)10.5

4、上機練習上機練習10.6 習題習題12/24/2021510.1 視圖視圖l本節(jié)全面研究與視圖有關的內(nèi)容。本節(jié)全面研究與視圖有關的內(nèi)容。l首先，分析視圖的作用和存在意義。首先，分析視圖的作用和存在意義。l其次，研究了視圖的類型和特點。其次，研究了視圖的類型和特點。l接下來，探討創(chuàng)建視圖技術。接下來，探討創(chuàng)建視圖技術。l然后，討論如何通過視圖修改表中數(shù)據(jù)。然后，討論如何通過視圖修改表中數(shù)據(jù)。l最后，瀏覽如何通過圖形化工具來創(chuàng)建和最后，瀏覽如何通過圖形化工具來創(chuàng)建和維護視圖。維護視圖。12/24/20216概述概述l數(shù)據(jù)是存儲在表中，對數(shù)據(jù)的操縱主要是通過表進行的。但是，僅僅數(shù)據(jù)是存儲在表中，對數(shù)

5、據(jù)的操縱主要是通過表進行的。但是，僅僅通過表操縱數(shù)據(jù)會帶來一系列的性能、安全、效率等問題。下面，對通過表操縱數(shù)據(jù)會帶來一系列的性能、安全、效率等問題。下面，對這些問題進行分析。這些問題進行分析。l從業(yè)務數(shù)據(jù)角度來看，由于數(shù)據(jù)庫設計時考慮到數(shù)據(jù)異常等問題，同從業(yè)務數(shù)據(jù)角度來看，由于數(shù)據(jù)庫設計時考慮到數(shù)據(jù)異常等問題，同一種業(yè)務數(shù)據(jù)有可能被分散在不同的表中，但是對這種業(yè)務數(shù)據(jù)的使一種業(yè)務數(shù)據(jù)有可能被分散在不同的表中，但是對這種業(yè)務數(shù)據(jù)的使用經(jīng)常是同時使用的。前面講過的連接、子查詢、聯(lián)合等技術就是解用經(jīng)常是同時使用的。前面講過的連接、子查詢、聯(lián)合等技術就是解決這種問題的一種手段。但是，對于多個表來說這

6、些操作都是比較復決這種問題的一種手段。但是，對于多個表來說這些操作都是比較復雜的，能不能只通過一個數(shù)據(jù)庫對象就可以同時看到這些分散存儲的雜的，能不能只通過一個數(shù)據(jù)庫對象就可以同時看到這些分散存儲的業(yè)務數(shù)據(jù)呢？如果能得話，將大大簡化查詢語句的復雜程度。業(yè)務數(shù)據(jù)呢？如果能得話，將大大簡化查詢語句的復雜程度。l從數(shù)據(jù)安全角度來看，由于工作性質(zhì)和需求不同，不同的操作人員只從數(shù)據(jù)安全角度來看，由于工作性質(zhì)和需求不同，不同的操作人員只是需要查看表中的部分數(shù)據(jù)，不能查看表中的所有數(shù)據(jù)。是需要查看表中的部分數(shù)據(jù)，不能查看表中的所有數(shù)據(jù)。 l從數(shù)據(jù)的應用角度來看，在設計報表時，需要明確地指定數(shù)據(jù)的來源從數(shù)據(jù)的應

7、用角度來看，在設計報表時，需要明確地指定數(shù)據(jù)的來源途徑和方式。能不能采取有效手段，提高報表的設計效率呢？途徑和方式。能不能采取有效手段，提高報表的設計效率呢？l解決上述問題的一種有效手段就是視圖。解決上述問題的一種有效手段就是視圖。 12/24/20217視圖的內(nèi)容視圖的內(nèi)容l基表的列的子集或行的子集，也就是說視圖可以是基表的其中一部分。基表的列的子集或行的子集，也就是說視圖可以是基表的其中一部分。l兩個或多個基表的聯(lián)合，也就是說視圖是對多個基表進行聯(lián)合運算檢兩個或多個基表的聯(lián)合，也就是說視圖是對多個基表進行聯(lián)合運算檢索的索的SELECT語句。語句。l兩個或多個基表的連接，也就是說視圖是通過對

8、若干個基表的連接生兩個或多個基表的連接，也就是說視圖是通過對若干個基表的連接生成的。成的。l基表的統(tǒng)計匯總，也就是說視圖不僅是基表的投影，還可以是經(jīng)過對基表的統(tǒng)計匯總，也就是說視圖不僅是基表的投影，還可以是經(jīng)過對基表的各種復雜運算的結果?；淼母鞣N復雜運算的結果。l另外一個視圖的子集，也就是說視圖既可以基于表，也可以基于另外另外一個視圖的子集，也就是說視圖既可以基于表，也可以基于另外一個視圖。一個視圖。l來自于函數(shù)或同義詞中的數(shù)據(jù)。來自于函數(shù)或同義詞中的數(shù)據(jù)。l視圖和基表的混合，在視圖的定義中，視圖和基表可以起到同樣的作視圖和基表的混合，在視圖的定義中，視圖和基表可以起到同樣的作用。用。12/

9、24/20218視圖類型視圖類型l在在Microsoft SQL Server 2005系統(tǒng)中，可以把系統(tǒng)中，可以把視圖分成視圖分成3種類型，即標準視圖、索引視圖和分種類型，即標準視圖、索引視圖和分區(qū)視圖。區(qū)視圖。l一般情況下的視圖都是標準視圖，它是一個虛擬一般情況下的視圖都是標準視圖，它是一個虛擬表并不占物理存儲空間。如果希望提高聚合多行表并不占物理存儲空間。如果希望提高聚合多行數(shù)據(jù)的視圖性能，那么可以創(chuàng)建索引視圖。數(shù)據(jù)的視圖性能，那么可以創(chuàng)建索引視圖。l索引視圖是被物理化的視圖，它包含有經(jīng)過計算索引視圖是被物理化的視圖，它包含有經(jīng)過計算的物理數(shù)據(jù)。的物理數(shù)據(jù)。l通過使用分區(qū)視圖，可以連接一

10、臺或多臺服務器通過使用分區(qū)視圖，可以連接一臺或多臺服務器中成員表中的分區(qū)數(shù)據(jù)，使得這些數(shù)據(jù)看起來就中成員表中的分區(qū)數(shù)據(jù)，使得這些數(shù)據(jù)看起來就像來自一個表中一樣。像來自一個表中一樣。12/24/20219創(chuàng)建視圖創(chuàng)建視圖l在在Microsoft SQL Server 2005系統(tǒng)中，主要使系統(tǒng)中，主要使用用CREATE VIEW語句創(chuàng)建視圖。只能在當前數(shù)語句創(chuàng)建視圖。只能在當前數(shù)據(jù)庫中創(chuàng)建視圖。當創(chuàng)建視圖時，據(jù)庫中創(chuàng)建視圖。當創(chuàng)建視圖時，Microsoft SQL Server首先驗證視圖定義中所引用的對象是首先驗證視圖定義中所引用的對象是否存在。否存在。l視圖的名稱應該符合命名規(guī)則。是否指定視

11、圖的視圖的名稱應該符合命名規(guī)則。是否指定視圖的架構則是可選的。視圖的外表和表的外表是一樣架構則是可選的。視圖的外表和表的外表是一樣的，因此為了區(qū)別表和視圖，建議采用一種命名的，因此為了區(qū)別表和視圖，建議采用一種命名機制，使人容易分辨出視圖和表，例如可以在視機制，使人容易分辨出視圖和表，例如可以在視圖名稱之前使用圖名稱之前使用vw_作為前綴。作為前綴。12/24/202110【例【例10-1】使用】使用CREATE VIEW語語句創(chuàng)建簡單的視圖句創(chuàng)建簡單的視圖 12/24/202111【例【例10-2】使用】使用sp_helptext系統(tǒng)存系統(tǒng)存儲過程查看視圖信息儲過程查看視圖信息 12/24/

12、202112【例【例10-3】使用】使用WITH ENCRYPTION子句加子句加密視圖定義文本信息密視圖定義文本信息 12/24/202113【例【例10-4】使用】使用DROP VIEW語句刪語句刪除視圖除視圖 12/24/202114通過視圖修改數(shù)據(jù)通過視圖修改數(shù)據(jù)l無論在什么時候修改視圖的數(shù)據(jù)，實際上無論在什么時候修改視圖的數(shù)據(jù)，實際上都是在修改視圖的基表中的數(shù)據(jù)。在滿足都是在修改視圖的基表中的數(shù)據(jù)。在滿足一定的限制條件下，可以通過視圖自由地一定的限制條件下，可以通過視圖自由地插入、刪除和更新基表中的數(shù)據(jù)。插入、刪除和更新基表中的數(shù)據(jù)。l在修改視圖時，要注意一些條件在修改視圖時，要注意

13、一些條件 12/24/202115【例【例10-5】通過視圖修改數(shù)據(jù)】通過視圖修改數(shù)據(jù) 12/24/202116【例【例10-5】12/24/202117使用使用SQL Server Management Studiol除了使用除了使用CREATE VIEW語句之外，也可語句之外，也可以使用以使用SQL Server Management Studio圖形化工具定義視圖。圖形化工具定義視圖。12/24/202118【例【例10-6】使用圖形化工具定義視】使用圖形化工具定義視圖圖 12/24/202119本章內(nèi)容本章內(nèi)容10.1 視圖視圖10.2 存儲過程存儲過程10.3 觸發(fā)器觸發(fā)器10.4

14、用戶定義函數(shù)用戶定義函數(shù)10.5 上機練習上機練習10.6 習題習題12/24/20212010.2 存儲過程存儲過程l存儲過程可以提高應用程序的設計效率和存儲過程可以提高應用程序的設計效率和增強系統(tǒng)的安全性。增強系統(tǒng)的安全性。l本節(jié)全面介紹存儲過程的特點、類型、創(chuàng)本節(jié)全面介紹存儲過程的特點、類型、創(chuàng)建及執(zhí)行等內(nèi)容。建及執(zhí)行等內(nèi)容。12/24/202121存儲過程的特點存儲過程的特點l存儲過程是一個可重用的代碼模塊，可以高效率存儲過程是一個可重用的代碼模塊，可以高效率地完成指定的操作。在地完成指定的操作。在Microsoft SQL Server 2005系統(tǒng)中，既可以使用系統(tǒng)中，既可以使用T

15、ransact-SQL語言編語言編寫存儲過程，也可以使用寫存儲過程，也可以使用CLR方式編寫存儲過程。方式編寫存儲過程。使用使用CLR編寫存儲過程是編寫存儲過程是Microsoft SQL Server 2005系統(tǒng)與系統(tǒng)與.NET框架緊密集成的一種表現(xiàn)形式。框架緊密集成的一種表現(xiàn)形式。l使用使用Transact-SQL語言編寫存儲過程而不使用存語言編寫存儲過程而不使用存儲在客戶端計算機上的儲在客戶端計算機上的Transact-SQL語言有許多語言有許多優(yōu)點優(yōu)點 12/24/202122存儲過程的類型存儲過程的類型l在在Microsoft SQL Server 2005系統(tǒng)中，提供了系統(tǒng)中，提

16、供了3種基本的存儲過程類型，即用戶定義的存儲過程、種基本的存儲過程類型，即用戶定義的存儲過程、擴展存儲過程和系統(tǒng)存儲過程。擴展存儲過程和系統(tǒng)存儲過程。l用戶定義的存儲過程是主要的存儲過程類型，是用戶定義的存儲過程是主要的存儲過程類型，是封裝了可重用代碼的模塊或例程。封裝了可重用代碼的模塊或例程。l用戶定義的存儲過程可以接受輸入?yún)?shù)、向客戶用戶定義的存儲過程可以接受輸入?yún)?shù)、向客戶端返回表格或標量結果和消息、調(diào)用數(shù)據(jù)定義語端返回表格或標量結果和消息、調(diào)用數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言語句，然后返回參數(shù)。言、數(shù)據(jù)操縱語言語句，然后返回參數(shù)。l在在Microsoft SQL Server 2005系統(tǒng)中

17、，用戶定系統(tǒng)中，用戶定義的存儲過程又可以分為義的存儲過程又可以分為Transact-SQL類型的存類型的存儲過程和儲過程和CLR類型的存儲過程。類型的存儲過程。 12/24/202123擴展存儲過程擴展存儲過程l擴展存儲過程是指使用某種變成語言如擴展存儲過程是指使用某種變成語言如C語語言創(chuàng)建的外部例程，是可以在言創(chuàng)建的外部例程，是可以在Microsoft SQL Server實例中動態(tài)加載和運行的實例中動態(tài)加載和運行的DLL。l但是，微軟公司宣布從但是，微軟公司宣布從Microsoft SQL Server 2005版本開始，將逐步刪除擴展存版本開始，將逐步刪除擴展存儲過程類型，這是因為使用儲

18、過程類型，這是因為使用CLR存儲過程存儲過程可以可靠和安全地替代擴展存儲過程的功可以可靠和安全地替代擴展存儲過程的功能。能。12/24/202124系統(tǒng)存儲過程系統(tǒng)存儲過程l系統(tǒng)存儲過程是指用來完成系統(tǒng)存儲過程是指用來完成Microsoft SQL Server 2005中許多管理活動的特殊存儲過程。中許多管理活動的特殊存儲過程。l從物理上來看，系統(tǒng)存儲過程存儲在從物理上來看，系統(tǒng)存儲過程存儲在Resource系系統(tǒng)數(shù)據(jù)庫中，并且?guī)в薪y(tǒng)數(shù)據(jù)庫中，并且?guī)в衧p_前綴。前綴。l從邏輯上來看，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)數(shù)從邏輯上來看，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫的據(jù)庫和用戶數(shù)據(jù)庫的s

19、ys架構中。架構中。l在在Microsoft SQL Server 2005系統(tǒng)中，主要的系統(tǒng)中，主要的系統(tǒng)存儲過程類型和功能如表系統(tǒng)存儲過程類型和功能如表10-1所示。所示。12/24/202125創(chuàng)建存儲過程的規(guī)則創(chuàng)建存儲過程的規(guī)則l在設計和創(chuàng)建存儲過程中應該滿足一定的在設計和創(chuàng)建存儲過程中應該滿足一定的約束和規(guī)則。只有滿足了這些約束和規(guī)則，約束和規(guī)則。只有滿足了這些約束和規(guī)則，才可以創(chuàng)建有效的存儲過程。才可以創(chuàng)建有效的存儲過程。l雖然說在雖然說在CREATE PROCEDURE語句中可語句中可以包括任意數(shù)量和類型的以包括任意數(shù)量和類型的Transact-SQL語語句，但是某些特殊的語句是

20、不能包含在存句，但是某些特殊的語句是不能包含在存儲過程定義中的。儲過程定義中的。 12/24/202126創(chuàng)建存儲過程創(chuàng)建存儲過程l在在Microsoft SQL Server 2005系統(tǒng)中，可以使系統(tǒng)中，可以使用用CREATE PROCEDURE語句創(chuàng)建存儲過程。語句創(chuàng)建存儲過程。需要強調(diào)的是，必須具有需要強調(diào)的是，必須具有CREATE PROCEDURE權限才能創(chuàng)建存儲過程，存儲過程權限才能創(chuàng)建存儲過程，存儲過程是架構作用域中的對象，只能在本地數(shù)據(jù)庫中創(chuàng)是架構作用域中的對象，只能在本地數(shù)據(jù)庫中創(chuàng)建存儲過程。建存儲過程。l在創(chuàng)建存儲過程時，應該指定所有的輸入?yún)?shù)、在創(chuàng)建存儲過程時，應該指定

21、所有的輸入?yún)?shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句、返回至調(diào)用過程或執(zhí)行數(shù)據(jù)庫操作的編程語句、返回至調(diào)用過程或批處理表明成功或失敗的狀態(tài)值、捕捉和處理潛批處理表明成功或失敗的狀態(tài)值、捕捉和處理潛在錯誤的錯誤處理語句。在錯誤的錯誤處理語句。 12/24/202127CREATE PROCEDURE語句語句lCREATE PROCEDURE procedure_namelparameter_name data_type, lWITH procedure_optionlASlsql_statement12/24/202128【例【例10-7】使用】使用CREATE PROCEDURE語句創(chuàng)建存儲過程語句創(chuàng)建存

22、儲過程 12/24/202129【例【例10-8】使用】使用OUTPUT關鍵字創(chuàng)關鍵字創(chuàng)建存儲過程建存儲過程 12/24/202130執(zhí)行存儲過程執(zhí)行存儲過程l在在Microsoft SQL Server 2005系統(tǒng)中，可系統(tǒng)中，可以使用以使用EXECUTE語句執(zhí)行存儲過程。語句執(zhí)行存儲過程。lEXECUTE語句也可以簡寫為語句也可以簡寫為EXEC。l如果將要執(zhí)行的存儲過程需要參數(shù)，那么如果將要執(zhí)行的存儲過程需要參數(shù)，那么應該在存儲過程名稱后面帶上參數(shù)值。應該在存儲過程名稱后面帶上參數(shù)值。12/24/202131【例【例10-9】使用】使用EXEC語句執(zhí)行存儲語句執(zhí)行存儲過程過程 12/24

23、/202132【例【例10-10】執(zhí)行帶有參數(shù)的存儲過】執(zhí)行帶有參數(shù)的存儲過程程 12/24/202133【例【例10-11】在執(zhí)行】在執(zhí)行EXEC語句時引語句時引用參數(shù)和使用用參數(shù)和使用OUTPUT關鍵字關鍵字 12/24/202134修改和刪除存儲過程修改和刪除存儲過程l在在Microsoft SQL Server 2005系統(tǒng)中，可以使用系統(tǒng)中，可以使用ALTER PROCEDURE語句修改已經(jīng)存在的存儲過程。修改存儲語句修改已經(jīng)存在的存儲過程。修改存儲過程不同于刪除和重建存儲過程，其目的是保持存儲過程過程不同于刪除和重建存儲過程，其目的是保持存儲過程的權限不發(fā)生變化。的權限不發(fā)生變化。

24、l例如，如果修改例如，如果修改HumanResources.GetEmployeeInfo存存儲過程，那么與該存儲過程對象相關的權限將不會發(fā)生任儲過程，那么與該存儲過程對象相關的權限將不會發(fā)生任何變化。但是，如果刪除何變化。但是，如果刪除HumanResources.GetEmployeeInfo存儲過程并且重新存儲過程并且重新創(chuàng)建同名的存儲過程，那么該存儲過程對象相關的權限都創(chuàng)建同名的存儲過程，那么該存儲過程對象相關的權限都需要重新定義。需要重新定義。l如果數(shù)據(jù)庫中某個存儲過程不再需要了，可以使用如果數(shù)據(jù)庫中某個存儲過程不再需要了，可以使用DROP PROCEDURE語句刪除該存儲過程。這種

25、刪除是永久性語句刪除該存儲過程。這種刪除是永久性的，不能恢復。的，不能恢復。12/24/202135存儲過程的執(zhí)行過程存儲過程的執(zhí)行過程l存儲過程創(chuàng)建之后，在第一次執(zhí)行時需要經(jīng)過語法分析階存儲過程創(chuàng)建之后，在第一次執(zhí)行時需要經(jīng)過語法分析階段、解析階段、編譯階段和執(zhí)行階段。段、解析階段、編譯階段和執(zhí)行階段。l語法分析階段是指創(chuàng)建存儲過程時，系統(tǒng)檢查其創(chuàng)建語句語法分析階段是指創(chuàng)建存儲過程時，系統(tǒng)檢查其創(chuàng)建語句的語法正確性的過程。在存儲過程的創(chuàng)建過程中如果碰到的語法正確性的過程。在存儲過程的創(chuàng)建過程中如果碰到語法錯誤，那么該存儲過程創(chuàng)建失敗。如果語法檢查通過語法錯誤，那么該存儲過程創(chuàng)建失敗。如果語法

26、檢查通過之后，系統(tǒng)將該存儲過程的名稱存儲在當前數(shù)據(jù)庫的之后，系統(tǒng)將該存儲過程的名稱存儲在當前數(shù)據(jù)庫的sys.sql_modules目錄視圖中。目錄視圖中。l解析階段是指某個存儲過程首次執(zhí)行時，查詢處理器從解析階段是指某個存儲過程首次執(zhí)行時，查詢處理器從sys.sql_modules目錄視圖中讀取該存儲過程的文本并且目錄視圖中讀取該存儲過程的文本并且檢查該過程引用的對象名稱是否存在的過程。檢查該過程引用的對象名稱是否存在的過程。 l編譯階段是指分析存儲過程和生成存儲過程執(zhí)行計劃的過編譯階段是指分析存儲過程和生成存儲過程執(zhí)行計劃的過程。程。 l執(zhí)行階段是指執(zhí)行駐留在過程高速緩沖存儲區(qū)中的存儲過執(zhí)行

27、階段是指執(zhí)行駐留在過程高速緩沖存儲區(qū)中的存儲過程的執(zhí)行計劃的過程。程的執(zhí)行計劃的過程。 12/24/202136查看存儲過程的信息查看存儲過程的信息l在在Microsoft SQL Server 2005系統(tǒng)中，可系統(tǒng)中，可以使用系統(tǒng)存儲過程和目錄視圖查看有關以使用系統(tǒng)存儲過程和目錄視圖查看有關存儲過程的信息。存儲過程的信息。l如果希望查看存儲過程的定義信息，那么如果希望查看存儲過程的定義信息，那么可以使用可以使用sys.sql_modules目錄視圖、目錄視圖、OBJECT_DEFINITION元數(shù)據(jù)函數(shù)、元數(shù)據(jù)函數(shù)、sp_helptext系統(tǒng)存儲過程等。系統(tǒng)存儲過程等。12/24/2021

28、37【例【例10-12】查看存儲過程信息】查看存儲過程信息 12/24/202138本章內(nèi)容本章內(nèi)容10.1 視圖視圖10.2 存儲過程存儲過程10.3 觸發(fā)器觸發(fā)器10.4 用戶定義函數(shù)用戶定義函數(shù)10.5 上機練習上機練習10.6 習題習題12/24/20213910.3 觸發(fā)器觸發(fā)器lMicrosoft SQL Server 2005系統(tǒng)提供了兩系統(tǒng)提供了兩種強制業(yè)務邏輯和數(shù)據(jù)完整性的機制，即種強制業(yè)務邏輯和數(shù)據(jù)完整性的機制，即約束技術和觸發(fā)器技術。約束技術和觸發(fā)器技術。l前面已經(jīng)講過了約束技術，本節(jié)講述觸發(fā)前面已經(jīng)講過了約束技術，本節(jié)講述觸發(fā)器技術。器技術。12/24/202140觸發(fā)

29、器類型觸發(fā)器類型l按照觸發(fā)事件的不同，可以把按照觸發(fā)事件的不同，可以把Microsoft SQL Server 2005系統(tǒng)提供系統(tǒng)提供的觸發(fā)器分成兩大類型，即的觸發(fā)器分成兩大類型，即DML觸發(fā)器和觸發(fā)器和DDL觸發(fā)器。需要補充的觸發(fā)器。需要補充的是，是，DDL觸發(fā)器是觸發(fā)器是Microsoft SQL Server 2005系統(tǒng)新增的功能，以系統(tǒng)新增的功能，以前的版本只有前的版本只有DML觸發(fā)器。觸發(fā)器。l當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言(data manipulation language，DML)事件時將調(diào)用事件時將調(diào)用DML觸發(fā)器。觸發(fā)器。DML事件包括在指定表或視

30、圖中修改數(shù)事件包括在指定表或視圖中修改數(shù)據(jù)的據(jù)的INSERT語句、語句、UPDATE語句或語句或DELETE語句。在語句。在DML觸發(fā)器中，觸發(fā)器中，可以執(zhí)行查詢其他表的操作，也可以包含更加復雜的可以執(zhí)行查詢其他表的操作，也可以包含更加復雜的Transact-SQL語句。語句。DML觸發(fā)器將觸發(fā)器本身和觸發(fā)事件的語句作為可以在觸發(fā)器觸發(fā)器將觸發(fā)器本身和觸發(fā)事件的語句作為可以在觸發(fā)器內(nèi)回滾的單個事務對待。也就是說，當在執(zhí)行觸發(fā)器操作過程中，如內(nèi)回滾的單個事務對待。也就是說，當在執(zhí)行觸發(fā)器操作過程中，如果檢測到錯誤發(fā)生，則整個觸發(fā)事件語句和觸發(fā)器操作的事務自動回果檢測到錯誤發(fā)生，則整個觸發(fā)事件語句

31、和觸發(fā)器操作的事務自動回滾。滾。lDDL觸發(fā)器與觸發(fā)器與DML觸發(fā)器相同的是，都需要觸發(fā)事件進行觸發(fā)。但觸發(fā)器相同的是，都需要觸發(fā)事件進行觸發(fā)。但是，是，DDL觸發(fā)器的觸發(fā)事件是數(shù)據(jù)定義語言觸發(fā)器的觸發(fā)事件是數(shù)據(jù)定義語言(data definition language，DDL)語句。語句。 12/24/202141DML觸發(fā)器的基本類型觸發(fā)器的基本類型l按照觸發(fā)器事件類型的不同，可以把按照觸發(fā)器事件類型的不同，可以把Microsoft SQL Server 2005系統(tǒng)系統(tǒng)DML觸發(fā)觸發(fā)器分成器分成3種類型，即種類型，即INSERT類型、類型、UPDATE類型和類型和DELETE類型。類型。

32、l這也是這也是DML觸發(fā)器的基本類型。觸發(fā)器的基本類型。12/24/202142創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器lDML觸發(fā)器是一種特殊類型的存儲過程，所以觸發(fā)器是一種特殊類型的存儲過程，所以DML觸發(fā)器的創(chuàng)建和存儲過程的創(chuàng)建方式有很多觸發(fā)器的創(chuàng)建和存儲過程的創(chuàng)建方式有很多相似的地方?？梢允褂孟嗨频牡胤?。可以使用CREATE TRIGGER語句語句創(chuàng)建創(chuàng)建DML觸發(fā)器。觸發(fā)器。l在在CREATE TRIGGER語句中，指定了定義觸發(fā)語句中，指定了定義觸發(fā)器的基表或視圖、觸發(fā)事件的類型和觸發(fā)的時間、器的基表或視圖、觸發(fā)事件的類型和觸發(fā)的時間、觸發(fā)器的所有指令等內(nèi)容。觸發(fā)器的所有指令等內(nèi)容。l使用使用C

33、REATE TRIGGER語句創(chuàng)建語句創(chuàng)建DML觸發(fā)器觸發(fā)器12/24/202143CREATE TRIGGER語句語句lCREATE TRIGGER trigger_name lON table_name_or_view_name lWITH ENCRYPTIONl FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE lAS sql_statement12/24/202144【例【例10-13】查看觸發(fā)器信息】查看觸發(fā)器信息 12/24/202145DML觸發(fā)器的工作原理觸發(fā)器的工作原理l前面介紹了前面介紹了DML觸發(fā)器的基本概念、類型、觸發(fā)器

34、的基本概念、類型、特點、創(chuàng)建、修改、刪除等內(nèi)容，現(xiàn)在我特點、創(chuàng)建、修改、刪除等內(nèi)容，現(xiàn)在我們來看看觸發(fā)器是如何工作的。通過了解們來看看觸發(fā)器是如何工作的。通過了解觸發(fā)器的工作原理，就可以更好地使用觸觸發(fā)器的工作原理，就可以更好地使用觸發(fā)器，寫出效率更高的觸發(fā)器。發(fā)器，寫出效率更高的觸發(fā)器。l下面主要介紹下面主要介紹INSERT、DELETE和和UPDATE類型觸發(fā)器的工作原理。類型觸發(fā)器的工作原理。12/24/202146INSERT觸發(fā)器的工作原理觸發(fā)器的工作原理l當向表中插入數(shù)據(jù)時，當向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)觸發(fā)器觸發(fā)執(zhí)行。當執(zhí)行。當INSERT觸發(fā)器觸發(fā)時，新的記錄觸發(fā)器

35、觸發(fā)時，新的記錄增加到觸發(fā)器表中和增加到觸發(fā)器表中和inserted表中。表中。l該該inserted表是一個邏輯表，保存了所插表是一個邏輯表，保存了所插入記錄的拷貝，允許用戶參考入記錄的拷貝，允許用戶參考INSERT語句語句中數(shù)據(jù)。觸發(fā)器可以檢查中數(shù)據(jù)。觸發(fā)器可以檢查inserted表，來表，來確定該觸發(fā)器的操作是否應該執(zhí)行和如何確定該觸發(fā)器的操作是否應該執(zhí)行和如何執(zhí)行。在執(zhí)行。在inserted表中的那些記錄，總是表中的那些記錄，總是觸發(fā)器表中一行或多行記錄的冗余。觸發(fā)器表中一行或多行記錄的冗余。12/24/202147DELETE觸發(fā)器的工作原理觸發(fā)器的工作原理l當觸發(fā)一個當觸發(fā)一個DE

36、LETE觸發(fā)器時，被刪除的記錄放在一個特觸發(fā)器時，被刪除的記錄放在一個特殊的殊的deleted表中。表中。deleted表是一個邏輯表，用來保存已表是一個邏輯表，用來保存已經(jīng)從表中刪除的記錄。該經(jīng)從表中刪除的記錄。該deleted表允許參考原來的表允許參考原來的DELETE語句刪除的已經(jīng)記錄在日志中的數(shù)據(jù)。當使用語句刪除的已經(jīng)記錄在日志中的數(shù)據(jù)。當使用DELETE語句時，應該考慮下列一些因素：語句時，應該考慮下列一些因素：l當記錄放在當記錄放在deleted表中的時候，該記錄就不會存在數(shù)據(jù)表中的時候，該記錄就不會存在數(shù)據(jù)庫的表中了。因此，在數(shù)據(jù)庫表和庫的表中了。因此，在數(shù)據(jù)庫表和deleted

37、表之間沒有共表之間沒有共同的記錄。同的記錄。l邏輯表邏輯表deleted總是存放在內(nèi)存中，以提供性能?？偸谴娣旁趦?nèi)存中，以提供性能。l在在DELETE觸發(fā)器中，不能包括觸發(fā)器中，不能包括TRUNCATE TABLE語句，語句，這是因為該語句是不記日志的操作。這是因為該語句是不記日志的操作。12/24/202148UPDATE觸發(fā)器的工作原理觸發(fā)器的工作原理l修改一條記錄就等于插入一條新記錄和刪除一條修改一條記錄就等于插入一條新記錄和刪除一條舊記錄。同樣，舊記錄。同樣，UPDATE語句也可以看成是由刪語句也可以看成是由刪除一條記錄的除一條記錄的DELETE語句和增加一條記錄的語句和增加一條記錄的

38、INSERT語句組成。當在某一個有語句組成。當在某一個有UPDATE觸發(fā)觸發(fā)器表的上面修改一條記錄時，表中原來的記錄移器表的上面修改一條記錄時，表中原來的記錄移動到動到deleted表中，修改過的記錄插入到了表中，修改過的記錄插入到了inserted表中。觸發(fā)器可以檢查表中。觸發(fā)器可以檢查deleted表和表和inserted表及被修改的表，以便確定是否修改了表及被修改的表，以便確定是否修改了多個行和應該如何執(zhí)行觸發(fā)器的操作。多個行和應該如何執(zhí)行觸發(fā)器的操作。12/24/202149一個具體的示例一個具體的示例l為了更加全面地掌握開發(fā)觸發(fā)器的步驟和為了更加全面地掌握開發(fā)觸發(fā)器的步驟和技術，本節(jié)

39、通過一個具體的示例，全面講技術，本節(jié)通過一個具體的示例，全面講述使用述使用Transact-SQL語言開發(fā)和創(chuàng)建觸發(fā)語言開發(fā)和創(chuàng)建觸發(fā)器的技術。器的技術。l一般地，開發(fā)觸發(fā)器的過程包括用戶需求一般地，開發(fā)觸發(fā)器的過程包括用戶需求分析、確定觸發(fā)器的邏輯結構、編寫觸發(fā)分析、確定觸發(fā)器的邏輯結構、編寫觸發(fā)器代碼和測試觸發(fā)器。器代碼和測試觸發(fā)器。12/24/202150【例【例10-14】設計和實現(xiàn)】設計和實現(xiàn)DML觸發(fā)器觸發(fā)器 12/24/202151創(chuàng)建創(chuàng)建t_accountData_insert觸發(fā)器觸發(fā)器 12/24/202152創(chuàng)建創(chuàng)建t_accountData_delete觸發(fā)觸發(fā) 12/

40、24/202153一組插入數(shù)據(jù)的操作一組插入數(shù)據(jù)的操作 12/24/202154審計到的插入數(shù)據(jù)的操作審計到的插入數(shù)據(jù)的操作 12/24/202155一組刪除數(shù)據(jù)的操作一組刪除數(shù)據(jù)的操作 12/24/202156審計到的刪除數(shù)據(jù)的操作審計到的刪除數(shù)據(jù)的操作 12/24/202157DDL觸發(fā)器觸發(fā)器lDDL觸發(fā)器與觸發(fā)器與DML觸發(fā)器有許多類似的地觸發(fā)器有許多類似的地方，如可以自動觸發(fā)完成規(guī)定的操作、都方，如可以自動觸發(fā)完成規(guī)定的操作、都可以使用可以使用CREATE TRIGGER語句創(chuàng)建等，語句創(chuàng)建等，但是也有一些不同的地方。但是也有一些不同的地方。l例如，例如，DDL觸發(fā)器的觸發(fā)事件主要是

41、觸發(fā)器的觸發(fā)事件主要是CREATE、ALTER、DROP以及以及GRANT、DENY及及REVOKE等語句，并且觸發(fā)的時等語句，并且觸發(fā)的時間條件只有間條件只有AFTER，沒有，沒有INSTEAD OF。12/24/202158【例【例10-15】使用】使用DDL觸發(fā)器觸發(fā)器 12/24/202159本章內(nèi)容本章內(nèi)容10.1 視圖視圖10.2 存儲過程存儲過程10.3 觸發(fā)器觸發(fā)器10.4 用戶定義函數(shù)用戶定義函數(shù)10.5 上機練習上機練習10.6 習題習題12/24/20216010.4 用戶定義函數(shù)用戶定義函數(shù)l在在Microsoft SQL Server 2005系統(tǒng)中，用系統(tǒng)中，用戶定

42、義函數(shù)是接受參數(shù)、執(zhí)行操作并且將戶定義函數(shù)是接受參數(shù)、執(zhí)行操作并且將運算結果以值的形式返回的例程。運算結果以值的形式返回的例程。l這種返回值既可以是單個標量值，也可以這種返回值既可以是單個標量值，也可以是一個結果集。是一個結果集。l在在Microsoft SQL Server 2005系統(tǒng)中，用系統(tǒng)中，用戶定義函數(shù)既可以使用戶定義函數(shù)既可以使用Transact-SQL語言語言編寫，也可以使用任何編寫，也可以使用任何.NET編程語言來編編程語言來編寫。寫。12/24/202161特點特點l在在Microsoft SQL Server 2005系統(tǒng)中，所有的用戶定義函數(shù)都具有系統(tǒng)中，所有的用戶定義

43、函數(shù)都具有相同的由兩部分組成的結構：標題和正文。相同的由兩部分組成的結構：標題和正文。l標題可以定義這些內(nèi)容：具有可選架構標題可以定義這些內(nèi)容：具有可選架構/所有者名稱的函數(shù)名稱；輸所有者名稱的函數(shù)名稱；輸入?yún)?shù)名稱和數(shù)據(jù)類型；可以用于輸入?yún)?shù)的選項；返回參數(shù)數(shù)據(jù)類入?yún)?shù)名稱和數(shù)據(jù)類型；可以用于輸入?yún)?shù)的選項；返回參數(shù)數(shù)據(jù)類型和可選名稱；可以用于返回參數(shù)的選項等。型和可選名稱；可以用于返回參數(shù)的選項等。l正文定義了函數(shù)將要執(zhí)行的操作，這些操作既可以是一個或多個正文定義了函數(shù)將要執(zhí)行的操作，這些操作既可以是一個或多個Transact-SQL語句，也可以是語句，也可以是.NET程序集的引用。程序集

44、的引用。l在在Microsoft SQL Server 2005系統(tǒng)中，用戶定義函數(shù)又可以分為兩系統(tǒng)中，用戶定義函數(shù)又可以分為兩大類，即用戶定義標量函數(shù)和用戶定義表值函數(shù)。用戶定義標量函數(shù)大類，即用戶定義標量函數(shù)和用戶定義表值函數(shù)。用戶定義標量函數(shù)返回在返回在RETURNS子句中定義的類型的單個數(shù)據(jù)值。對于多語句標量子句中定義的類型的單個數(shù)據(jù)值。對于多語句標量函數(shù)，定義在函數(shù)，定義在BEGIN END塊中的函數(shù)體包含一系列返回單個值的塊中的函數(shù)體包含一系列返回單個值的Transact-SQL語句。返回類型可以是除語句。返回類型可以是除text、ntext、image、cursor、timest

45、amp以外的任何數(shù)據(jù)類型。用戶定義表值函數(shù)返回以外的任何數(shù)據(jù)類型。用戶定義表值函數(shù)返回table數(shù)據(jù)類型。實際上，在表值函數(shù)中，表是單個數(shù)據(jù)類型。實際上，在表值函數(shù)中，表是單個SELECT語句的語句的結果集。結果集。12/24/202162創(chuàng)建時的考慮創(chuàng)建時的考慮l在在Microsoft SQL Server 2005系統(tǒng)中，可以分別使用系統(tǒng)中，可以分別使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION語句來實現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。語句來實現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。在創(chuàng)建用戶定義函數(shù)時，每個完全限定用戶函數(shù)的名稱在創(chuàng)建用戶定義函數(shù)時

46、，每個完全限定用戶函數(shù)的名稱(schema_name.function_name)必須唯一。必須唯一。l函數(shù)的函數(shù)的BEGIN END塊中的語句不能有任何副作用。函數(shù)塊中的語句不能有任何副作用。函數(shù)副作用是指對具有函數(shù)外作用域副作用是指對具有函數(shù)外作用域(例如修改數(shù)據(jù)庫表例如修改數(shù)據(jù)庫表)的資的資源狀態(tài)的任何永久性更改。函數(shù)中的語句唯一能做的更改源狀態(tài)的任何永久性更改。函數(shù)中的語句唯一能做的更改是對函數(shù)上的局部對象是對函數(shù)上的局部對象(如局部游標或局部變量如局部游標或局部變量)的更改。的更改。不能在函數(shù)中執(zhí)行的操作包括對數(shù)據(jù)庫表的修改、對不在不能在函數(shù)中執(zhí)行的操作包括對數(shù)據(jù)庫表的修改、對不在函數(shù)上的局部游標進行操作、發(fā)送電子郵件、嘗試修改目函數(shù)上的局部游標進行操作、發(fā)送電子郵件、嘗試修改目錄，以及生成返回至用戶的結果集。錄，以及生成返回至用戶的結果集。12/24/202163使用使用CREATE FUNCTION語句語句l在在Microsoft SQL S