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

第10章其他數(shù)據(jù)庫對象學習目標本章重點本章內(nèi)容6/7/20231學習目標從業(yè)務(wù)數(shù)據(jù)角度來看，同一種業(yè)務(wù)數(shù)據(jù)有可能分散在不同的表中，如何從一個數(shù)據(jù)庫對象中查看這些分散存儲的數(shù)據(jù)呢？從安全角度來看，不同的操作人員或許只能看到表中不同部分的數(shù)據(jù)。從數(shù)據(jù)的應(yīng)用角度來看，一個報表中的數(shù)據(jù)往往來自于多個不同的表中，如何提高報表的設(shè)計效率呢？視圖是解決這些問題的一種有效手段。存儲過程是一個可重用的代碼模塊，可以高效率地完成指定的操作。觸發(fā)器是一種特殊類型的存儲過程，可以實現(xiàn)自動化的操作。用戶定義函數(shù)是由用戶根據(jù)自己應(yīng)用程序的需要而定義的可以完成特定操作的函數(shù)。本章將全面研究視圖、存儲過程、觸發(fā)器、用戶定義函數(shù)等數(shù)據(jù)庫對象的特點和使用方式。6/7/20232本章重點視圖特點和類型管理視圖存儲過程特點和類型管理存儲過程觸發(fā)器特點和類型管理觸發(fā)器用戶定義函數(shù)6/7/20233本章內(nèi)容10.1視圖10.2存儲過程10.3觸發(fā)器10.4用戶定義函數(shù)10.5上機練習10.6習題6/7/20234本章內(nèi)容10.1視圖10.2存儲過程10.3觸發(fā)器10.4用戶定義函數(shù)10.5上機練習10.6習題6/7/2023510.1視圖本節(jié)全面研究與視圖有關(guān)的內(nèi)容。首先，分析視圖的作用和存在意義。其次，研究了視圖的類型和特點。接下來，探討創(chuàng)建視圖技術(shù)。然后，討論如何通過視圖修改表中數(shù)據(jù)。最后，瀏覽如何通過圖形化工具來創(chuàng)建和維護視圖。6/7/20236概述數(shù)據(jù)是存儲在表中，對數(shù)據(jù)的操縱主要是通過表進行的。但是，僅僅通過表操縱數(shù)據(jù)會帶來一系列的性能、安全、效率等問題。下面，對這些問題進行分析。從業(yè)務(wù)數(shù)據(jù)角度來看，由于數(shù)據(jù)庫設(shè)計時考慮到數(shù)據(jù)異常等問題，同一種業(yè)務(wù)數(shù)據(jù)有可能被分散在不同的表中，但是對這種業(yè)務(wù)數(shù)據(jù)的使用經(jīng)常是同時使用的。前面講過的連接、子查詢、聯(lián)合等技術(shù)就是解決這種問題的一種手段。但是，對于多個表來說這些操作都是比較復(fù)雜的，能不能只通過一個數(shù)據(jù)庫對象就可以同時看到這些分散存儲的業(yè)務(wù)數(shù)據(jù)呢？如果能得話，將大大簡化查詢語句的復(fù)雜程度。從數(shù)據(jù)安全角度來看，由于工作性質(zhì)和需求不同，不同的操作人員只是需要查看表中的部分數(shù)據(jù)，不能查看表中的所有數(shù)據(jù)。從數(shù)據(jù)的應(yīng)用角度來看，在設(shè)計報表時，需要明確地指定數(shù)據(jù)的來源途徑和方式。能不能采取有效手段，提高報表的設(shè)計效率呢？解決上述問題的一種有效手段就是視圖。6/7/20237視圖的內(nèi)容基表的列的子集或行的子集，也就是說視圖可以是基表的其中一部分。兩個或多個基表的聯(lián)合，也就是說視圖是對多個基表進行聯(lián)合運算檢索的SELECT語句。兩個或多個基表的連接，也就是說視圖是通過對若干個基表的連接生成的?；淼慕y(tǒng)計匯總，也就是說視圖不僅是基表的投影，還可以是經(jīng)過對基表的各種復(fù)雜運算的結(jié)果。另外一個視圖的子集，也就是說視圖既可以基于表，也可以基于另外一個視圖。來自于函數(shù)或同義詞中的數(shù)據(jù)。視圖和基表的混合，在視圖的定義中，視圖和基表可以起到同樣的作用。6/7/20238視圖類型在MicrosoftSQLServer2005系統(tǒng)中，可以把視圖分成3種類型，即標準視圖、索引視圖和分區(qū)視圖。一般情況下的視圖都是標準視圖，它是一個虛擬表并不占物理存儲空間。如果希望提高聚合多行數(shù)據(jù)的視圖性能，那么可以創(chuàng)建索引視圖。索引視圖是被物理化的視圖，它包含有經(jīng)過計算的物理數(shù)據(jù)。通過使用分區(qū)視圖，可以連接一臺或多臺服務(wù)器中成員表中的分區(qū)數(shù)據(jù)，使得這些數(shù)據(jù)看起來就像來自一個表中一樣。6/7/20239創(chuàng)建視圖在MicrosoftSQLServer2005系統(tǒng)中，主要使用CREATEVIEW語句創(chuàng)建視圖。只能在當前數(shù)據(jù)庫中創(chuàng)建視圖。當創(chuàng)建視圖時，MicrosoftSQLServer首先驗證視圖定義中所引用的對象是否存在。視圖的名稱應(yīng)該符合命名規(guī)則。是否指定視圖的架構(gòu)則是可選的。視圖的外表和表的外表是一樣的，因此為了區(qū)別表和視圖，建議采用一種命名機制，使人容易分辨出視圖和表，例如可以在視圖名稱之前使用vw_作為前綴。6/7/202310【例10-1】使用CREATEVIEW語句創(chuàng)建簡單的視圖6/7/202311【例10-2】使用sp_helptext系統(tǒng)存儲過程查看視圖信息6/7/202312【例10-3】使用WITHENCRYPTION子句加密視圖定義文本信息

6/7/202313【例10-4】使用DROPVIEW語句刪除視圖6/7/202314通過視圖修改數(shù)據(jù)無論在什么時候修改視圖的數(shù)據(jù)，實際上都是在修改視圖的基表中的數(shù)據(jù)。在滿足一定的限制條件下，可以通過視圖自由地插入、刪除和更新基表中的數(shù)據(jù)。在修改視圖時，要注意一些條件6/7/202315【例10-5】通過視圖修改數(shù)據(jù)6/7/202316【例10-5】6/7/202317使用SQLServerManagementStudio除了使用CREATEVIEW語句之外，也可以使用SQLServerManagementStudio圖形化工具定義視圖。6/7/202318【例10-6】使用圖形化工具定義視圖6/7/202319本章內(nèi)容10.1視圖10.2存儲過程10.3觸發(fā)器10.4用戶定義函數(shù)10.5上機練習10.6習題6/7/20232010.2存儲過程存儲過程可以提高應(yīng)用程序的設(shè)計效率和增強系統(tǒng)的安全性。本節(jié)全面介紹存儲過程的特點、類型、創(chuàng)建及執(zhí)行等內(nèi)容。6/7/202321存儲過程的特點存儲過程是一個可重用的代碼模塊，可以高效率地完成指定的操作。在MicrosoftSQLServer2005系統(tǒng)中，既可以使用Transact-SQL語言編寫存儲過程，也可以使用CLR方式編寫存儲過程。使用CLR編寫存儲過程是MicrosoftSQLServer2005系統(tǒng)與.NET框架緊密集成的一種表現(xiàn)形式。使用Transact-SQL語言編寫存儲過程而不使用存儲在客戶端計算機上的Transact-SQL語言有許多優(yōu)點6/7/202322存儲過程的類型在MicrosoftSQLServer2005系統(tǒng)中，提供了3種基本的存儲過程類型，即用戶定義的存儲過程、擴展存儲過程和系統(tǒng)存儲過程。用戶定義的存儲過程是主要的存儲過程類型，是封裝了可重用代碼的模塊或例程。用戶定義的存儲過程可以接受輸入?yún)?shù)、向客戶端返回表格或標量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言語句，然后返回參數(shù)。在MicrosoftSQLServer2005系統(tǒng)中，用戶定義的存儲過程又可以分為Transact-SQL類型的存儲過程和CLR類型的存儲過程。6/7/202323擴展存儲過程擴展存儲過程是指使用某種變成語言如C語言創(chuàng)建的外部例程，是可以在MicrosoftSQLServer實例中動態(tài)加載和運行的DLL。但是，微軟公司宣布從MicrosoftSQLServer2005版本開始，將逐步刪除擴展存儲過程類型，這是因為使用CLR存儲過程可以可靠和安全地替代擴展存儲過程的功能。6/7/202324系統(tǒng)存儲過程系統(tǒng)存儲過程是指用來完成MicrosoftSQLServer2005中許多管理活動的特殊存儲過程。從物理上來看，系統(tǒng)存儲過程存儲在Resource系統(tǒng)數(shù)據(jù)庫中，并且?guī)в衧p_前綴。從邏輯上來看，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫的sys架構(gòu)中。在MicrosoftSQLServer2005系統(tǒng)中，主要的系統(tǒng)存儲過程類型和功能如表10-1所示。6/7/202325創(chuàng)建存儲過程的規(guī)則在設(shè)計和創(chuàng)建存儲過程中應(yīng)該滿足一定的約束和規(guī)則。只有滿足了這些約束和規(guī)則，才可以創(chuàng)建有效的存儲過程。雖然說在CREATEPROCEDURE語句中可以包括任意數(shù)量和類型的Transact-SQL語句，但是某些特殊的語句是不能包含在存儲過程定義中的。6/7/202326創(chuàng)建存儲過程在MicrosoftSQLServer2005系統(tǒng)中，可以使用CREATEPROCEDURE語句創(chuàng)建存儲過程。需要強調(diào)的是，必須具有CREATEPROCEDURE權(quán)限才能創(chuàng)建存儲過程，存儲過程是架構(gòu)作用域中的對象，只能在本地數(shù)據(jù)庫中創(chuàng)建存儲過程。在創(chuàng)建存儲過程時，應(yīng)該指定所有的輸入?yún)?shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句、返回至調(diào)用過程或批處理表明成功或失敗的狀態(tài)值、捕捉和處理潛在錯誤的錯誤處理語句。6/7/202327CREATEPROCEDURE語句CREATEPROCEDUREprocedure_nameparameter_namedata_type,…WITHprocedure_optionASsql_statement6/7/202328【例10-7】使用CREATEPROCEDURE語句創(chuàng)建存儲過程6/7/202329【例10-8】使用OUTPUT關(guān)鍵字創(chuàng)建存儲過程6/7/202330執(zhí)行存儲過程在MicrosoftSQLServer2005系統(tǒng)中，可以使用EXECUTE語句執(zhí)行存儲過程。EXECUTE語句也可以簡寫為EXEC。如果將要執(zhí)行的存儲過程需要參數(shù)，那么應(yīng)該在存儲過程名稱后面帶上參數(shù)值。6/7/202331【例10-9】使用EXEC語句執(zhí)行存儲過程6/7/202332【例10-10】執(zhí)行帶有參數(shù)的存儲過程6/7/202333【例10-11】在執(zhí)行EXEC語句時引用參數(shù)和使用OUTPUT關(guān)鍵字6/7/202334修改和刪除存儲過程在MicrosoftSQLServer2005系統(tǒng)中，可以使用ALTERPROCEDURE語句修改已經(jīng)存在的存儲過程。修改存儲過程不同于刪除和重建存儲過程，其目的是保持存儲過程的權(quán)限不發(fā)生變化。例如，如果修改HumanResources.GetEmployeeInfo存儲過程，那么與該存儲過程對象相關(guān)的權(quán)限將不會發(fā)生任何變化。但是，如果刪除HumanResources.GetEmployeeInfo存儲過程并且重新創(chuàng)建同名的存儲過程，那么該存儲過程對象相關(guān)的權(quán)限都需要重新定義。如果數(shù)據(jù)庫中某個存儲過程不再需要了，可以使用DROPPROCEDURE語句刪除該存儲過程。這種刪除是永久性的，不能恢復(fù)。6/7/202335存儲過程的執(zhí)行過程存儲過程創(chuàng)建之后，在第一次執(zhí)行時需要經(jīng)過語法分析階段、解析階段、編譯階段和執(zhí)行階段。語法分析階段是指創(chuàng)建存儲過程時，系統(tǒng)檢查其創(chuàng)建語句的語法正確性的過程。在存儲過程的創(chuàng)建過程中如果碰到語法錯誤，那么該存儲過程創(chuàng)建失敗。如果語法檢查通過之后，系統(tǒng)將該存儲過程的名稱存儲在當前數(shù)據(jù)庫的sys.sql_modules目錄視圖中。解析階段是指某個存儲過程首次執(zhí)行時，查詢處理器從sys.sql_modules目錄視圖中讀取該存儲過程的文本并且檢查該過程引用的對象名稱是否存在的過程。編譯階段是指分析存儲過程和生成存儲過程執(zhí)行計劃的過程。執(zhí)行階段是指執(zhí)行駐留在過程高速緩沖存儲區(qū)中的存儲過程的執(zhí)行計劃的過程。6/7/202336查看存儲過程的信息在MicrosoftSQLServer2005系統(tǒng)中，可以使用系統(tǒng)存儲過程和目錄視圖查看有關(guān)存儲過程的信息。如果希望查看存儲過程的定義信息，那么可以使用sys.sql_modules目錄視圖、OBJECT_DEFINITION元數(shù)據(jù)函數(shù)、sp_helptext系統(tǒng)存儲過程等。6/7/202337【例10-12】查看存儲過程信息6/7/202338本章內(nèi)容10.1視圖10.2存儲過程10.3觸發(fā)器10.4用戶定義函數(shù)10.5上機練習10.6習題6/7/20233910.3觸發(fā)器MicrosoftSQLServer2005系統(tǒng)提供了兩種強制業(yè)務(wù)邏輯和數(shù)據(jù)完整性的機制，即約束技術(shù)和觸發(fā)器技術(shù)。前面已經(jīng)講過了約束技術(shù)，本節(jié)講述觸發(fā)器技術(shù)。6/7/202340觸發(fā)器類型按照觸發(fā)事件的不同，可以把MicrosoftSQLServer2005系統(tǒng)提供的觸發(fā)器分成兩大類型，即DML觸發(fā)器和DDL觸發(fā)器。需要補充的是，DDL觸發(fā)器是MicrosoftSQLServer2005系統(tǒng)新增的功能，以前的版本只有DML觸發(fā)器。當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言(datamanipulationlanguage，DML)事件時將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。在DML觸發(fā)器中，可以執(zhí)行查詢其他表的操作，也可以包含更加復(fù)雜的Transact-SQL語句。DML觸發(fā)器將觸發(fā)器本身和觸發(fā)事件的語句作為可以在觸發(fā)器內(nèi)回滾的單個事務(wù)對待。也就是說，當在執(zhí)行觸發(fā)器操作過程中，如果檢測到錯誤發(fā)生，則整個觸發(fā)事件語句和觸發(fā)器操作的事務(wù)自動回滾。DDL觸發(fā)器與DML觸發(fā)器相同的是，都需要觸發(fā)事件進行觸發(fā)。但是，DDL觸發(fā)器的觸發(fā)事件是數(shù)據(jù)定義語言(datadefinitionlanguage，DDL)語句。6/7/202341DML觸發(fā)器的基本類型按照觸發(fā)器事件類型的不同，可以把MicrosoftSQLServer2005系統(tǒng)DML觸發(fā)器分成3種類型，即INSERT類型、UPDATE類型和DELETE類型。這也是DML觸發(fā)器的基本類型。6/7/202342創(chuàng)建DML觸發(fā)器DML觸發(fā)器是一種特殊類型的存儲過程，所以DML觸發(fā)器的創(chuàng)建和存儲過程的創(chuàng)建方式有很多相似的地方?？梢允褂肅REATETRIGGER語句創(chuàng)建DML觸發(fā)器。在CREATETRIGGER語句中，指定了定義觸發(fā)器的基表或視圖、觸發(fā)事件的類型和觸發(fā)的時間、觸發(fā)器的所有指令等內(nèi)容。使用CREATETRIGGER語句創(chuàng)建DML觸發(fā)器6/7/202343CREATETRIGGER語句CREATETRIGGERtrigger_nameONtable_name_or_view_nameWITHENCRYPTION{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement6/7/202344【例10-13】查看觸發(fā)器信息6/7/202345DML觸發(fā)器的工作原理前面介紹了DML觸發(fā)器的基本概念、類型、特點、創(chuàng)建、修改、刪除等內(nèi)容，現(xiàn)在我們來看看觸發(fā)器是如何工作的。通過了解觸發(fā)器的工作原理，就可以更好地使用觸發(fā)器，寫出效率更高的觸發(fā)器。下面主要介紹INSERT、DELETE和UPDATE類型觸發(fā)器的工作原理。6/7/202346INSERT觸發(fā)器的工作原理當向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)執(zhí)行。當INSERT觸發(fā)器觸發(fā)時，新的記錄增加到觸發(fā)器表中和inserted表中。該inserted表是一個邏輯表，保存了所插入記錄的拷貝，允許用戶參考INSERT語句中數(shù)據(jù)。觸發(fā)器可以檢查inserted表，來確定該觸發(fā)器的操作是否應(yīng)該執(zhí)行和如何執(zhí)行。在inserted表中的那些記錄，總是觸發(fā)器表中一行或多行記錄的冗余。6/7/202347DELETE觸發(fā)器的工作原理當觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄放在一個特殊的deleted表中。deleted表是一個邏輯表，用來保存已經(jīng)從表中刪除的記錄。該deleted表允許參考原來的DELETE語句刪除的已經(jīng)記錄在日志中的數(shù)據(jù)。當使用DELETE語句時，應(yīng)該考慮下列一些因素：當記錄放在deleted表中的時候，該記錄就不會存在數(shù)據(jù)庫的表中了。因此，在數(shù)據(jù)庫表和deleted表之間沒有共同的記錄。邏輯表deleted總是存放在內(nèi)存中，以提供性能。在DELETE觸發(fā)器中，不能包括TRUNCATETABLE語句，這是因為該語句是不記日志的操作。6/7/202348UPDATE觸發(fā)器的工作原理修改一條記錄就等于插入一條新記錄和刪除一條舊記錄。同樣，UPDATE語句也可以看成是由刪除一條記錄的DELETE語句和增加一條記錄的INSERT語句組成。當在某一個有UPDATE觸發(fā)器表的上面修改一條記錄時，表中原來的記錄移動到deleted表中，修改過的記錄插入到了inserted表中。觸發(fā)器可以檢查deleted表和inserted表及被修改的表，以便確定是否修改了多個行和應(yīng)該如何執(zhí)行觸發(fā)器的操作。6/7/202349一個具體的示例為了更加全面地掌握開發(fā)觸發(fā)器的步驟和技術(shù)，本節(jié)通過一個具體的示例，全面講述使用Transact-SQL語言開發(fā)和創(chuàng)建觸發(fā)器的技術(shù)。一般地，開發(fā)觸發(fā)器的過程包括用戶需求分析、確定觸發(fā)器的邏輯結(jié)構(gòu)、編寫觸發(fā)器代碼和測試觸發(fā)器。6/7/202350【例10-14】設(shè)計和實現(xiàn)DML觸發(fā)器6/7/202351創(chuàng)建t_accountData_insert觸發(fā)器6/7/202352創(chuàng)建t_accountData_delete觸發(fā)6/7/202353一組插入數(shù)據(jù)的操作6/7/202354審計到的插入數(shù)據(jù)的操作6/7/202355一組刪除數(shù)據(jù)的操作6/7/202356審計到的刪除數(shù)據(jù)的操作6/7/202357DDL觸發(fā)器DDL觸發(fā)器與DML觸發(fā)器有許多類似的地方，如可以自動觸發(fā)完成規(guī)定的操作、都可以使用CREATETRIGGER語句創(chuàng)建等，但是也有一些不同的地方。例如，DDL觸發(fā)器的觸發(fā)事件主要是CREATE、ALTER、DROP以及GRANT、DENY及REVOKE等語句，并且觸發(fā)的時間條件只有AFTER，沒有INSTEADOF。6/7/202358【例10-15】使用DDL觸發(fā)器6/7/202359本章內(nèi)容10.1視圖10.2存儲過程10.3觸發(fā)器10.4用戶定義函數(shù)10.5上機練習10.6習題6/7/20236010.4用戶定義函數(shù)在MicrosoftSQLServer2005系統(tǒng)中，用戶定義函數(shù)是接受參數(shù)、執(zhí)行操作并且將運算結(jié)果以值的形式返回的例程。這種返回值既可以是單個標量值，也可以是一個結(jié)果集。在MicrosoftSQLServer2005系統(tǒng)中，用戶定義函數(shù)既可以使用Transact-SQL語言編寫，也可以使用任何.NET編程語言來編寫。6/7/202361特點在MicrosoftSQLServer2005系統(tǒng)中，所有的用戶定義函數(shù)都具有相同的由兩部分組成的結(jié)構(gòu)：標題和正文。標題可以定義這些內(nèi)容：具有可選架構(gòu)/所有者名稱的函數(shù)名稱；輸入?yún)?shù)名稱和數(shù)據(jù)類型；可以用于輸入?yún)?shù)的選項；返回參數(shù)數(shù)據(jù)類型和可選名稱；可以用于返回參數(shù)的選項等。正文定義了函數(shù)將要執(zhí)行的操作，這些操作既可以是一個或多個Transact-SQL語句，也可以是.NET程序集的引用。在MicrosoftSQLServer2005系統(tǒng)中，用戶定義函數(shù)又可以分為兩大類，即用戶定義標量函數(shù)和用戶定義表值函數(shù)。用戶定義標量函數(shù)返回在RETURNS子句中定義的類型的單個數(shù)據(jù)值。對于多語句標量函數(shù)，定義在BEGINEND塊中的函數(shù)體包含一系列返回單個值的Transact-SQL語句。返回類型可以是除text、ntext、image、cursor、timestamp以外的任何數(shù)據(jù)類型。用戶定義表值函數(shù)返回table數(shù)據(jù)類型。實際上，在表值函數(shù)中，表是單個SELECT語句的結(jié)果集。6/7/202362創(chuàng)建時的考慮在MicrosoftSQLServer2005系統(tǒng)中，可以分別使用CREATEFUNCTION、ALTERFUNCTION、DROPFUNCTION語句來實現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。在創(chuàng)建用戶定義函數(shù)時，每個完全限定用戶函數(shù)的名稱(schema_name.function_name)必須唯一。函數(shù)的BEGINEND塊中的語句不能有任何副作用。函數(shù)副作用是指對具有函數(shù)外作用域(例如修改數(shù)據(jù)庫表)的資源狀態(tài)的任何永久性更改。函數(shù)中的語句唯一能做的更改是對函數(shù)上的局部對象(如局部游標或局部變量)的更改。不能在函數(shù)中執(zhí)行的操作包括對數(shù)據(jù)庫表的修改、對不在函數(shù)上的局部游標進行操作、發(fā)送電子郵件、嘗試修改目錄，以及生成返回至用戶的結(jié)果集。6/7/202363使用CREATEFUNCTION語句在MicrosoftSQLServer2005系統(tǒng)中，使用CREATEFUNCTION語句可以創(chuàng)建標量函數(shù)、內(nèi)聯(lián)表值函數(shù)、多語句表值函數(shù)。需要說明的是，如果RETURNS子句指定了一種標量數(shù)據(jù)類型，則該函數(shù)為標量值。如果RETURNS子句指定了TABLE，則該函數(shù)為表值函