SQL課件存儲過程和觸發(fā)器.ppt

第10章 存儲過程和觸發(fā)器,教學提示：開發(fā)中編寫的一些SQL語句會占用程序的很大篇幅，而且不便于在其他地方重用，且由于這些SQL語句通常還要跨越傳輸途徑從外部不僅會導致程序的運行效率低，還會產(chǎn)生安全隱患，而存儲過程則能克服以上的缺陷。 觸發(fā)器可以大大增強應(yīng)用程序的健壯性、數(shù)據(jù)庫可恢復(fù)性和數(shù)據(jù)庫的可管理性。 存儲過程和觸發(fā)器都是SQL Server的數(shù)據(jù)庫對象。存儲過程的存在獨立于表，它存放在服務(wù)器上，供客戶端調(diào)用。,教學目標：本章主要包含以下內(nèi)容。 存儲過程概述 設(shè)計存儲過程 實現(xiàn)和管理存儲過程 觸發(fā)器概述 設(shè)計觸發(fā)器 實現(xiàn)和管理觸發(fā)器 通過本章的學習，使讀者掌握存儲過程和觸發(fā)器的基礎(chǔ)知識，并基本學會編寫簡單的存儲過程和觸發(fā)器，為以后在實際應(yīng)用中不斷提高自己編寫存儲過程和觸發(fā)器的技能打下良好的基礎(chǔ)。,第10章 存儲過程和觸發(fā)器,10.1 存儲過程概述 10.2 設(shè)計存儲過程 10.3 實現(xiàn)和管理存儲過程 10.4 觸發(fā)器概述 10.5 觸發(fā)器設(shè)計規(guī)則 10.6 實現(xiàn)和管理觸發(fā)器,10.1 存儲過程概述,10.1.1 存儲過程的特點 能夠在單個存儲過程中執(zhí)行一系列的Transact-SQL語句，能夠以在一個存儲過程中調(diào)用其他的存儲過程。 存儲過程是保存在服務(wù)器端的已經(jīng)編譯的Transact-SQL語句。 存儲過程可以使用控制流語句和變量，大大增強了SQL的功能。 存儲過程在提交前會自動檢查語法。 存儲過程是管理員放在服務(wù)器端的Transact-SQL語句。,10.1.2 存儲過程的分類 1. 用戶存儲過程 2. 系統(tǒng)存儲過程 3. 擴展存儲過程,10.2 設(shè)計存儲過程,10.2.1 一些設(shè)計規(guī)則 用戶在設(shè)計數(shù)據(jù)庫(的存儲過程)時，應(yīng)遵守以下規(guī)則： 在SQL Server 2005中，存儲過程可以使用Transact-SQL中的任何語句，但是表10.1中的語句除外。 略,10.2.2 存儲過程的內(nèi)部命名規(guī)范化 存儲過程中的Transact-SQL語句使用的對象名稱必須符合規(guī)范的模式，如果未指明對象所屬的模式，存儲過程將使用當前默認的模式。 每個存儲過程必須有惟一的名稱，存儲過程中使用的變量類型必須符合Transact-SQL的要求，變量名必須以開頭，并且存儲過程的名稱和變量的命名要符合Transact-SQL標識符規(guī)則。,10.2.3 存儲過程的加密定義 如果用戶想定義一個不讓其他用戶看到具體操作的存儲過程，可以使用WITH ENCRYPTION參數(shù)，那么存儲過程將會以不可讀的狀態(tài)存在，并且存儲過程擁有者、創(chuàng)建者和數(shù)據(jù)庫管理員都不可見。,10.3 實現(xiàn)和管理存儲過程,10.3.1 創(chuàng)建存儲過程 由于必須使用Transact-SQL語句創(chuàng)建存儲過程，所以用戶必須首先掌握創(chuàng)建存儲過程的Transact-SQL語法結(jié)構(gòu)，然后再學習如何使用Management Studio創(chuàng)建存儲過程。,10.3.2 執(zhí)行存儲過程 執(zhí)行存儲過程有多種方式，比較常用的有以下幾種。 1. 通過Execute或Exec語句執(zhí)行 2. 通過設(shè)置，使存儲過程自動執(zhí)行 3. 作為批處理的第一行，直接輸入存儲過程名,10.3.3 修改存儲過程 1. 修改存儲過程的語法 注意：修改存儲過程的名稱會影響已關(guān)聯(lián)對象對此存儲過程的調(diào)用。 2. 使用Management Studio修改存儲過程,10.3.4 重新編譯存儲過程 1.sp_recompile系統(tǒng)存儲過程可以強制指定的存儲過程在下次調(diào)用時重新編譯。其調(diào)用的語法結(jié)構(gòu)為：sp_recompile objname = object，其中的object為存儲過程的名稱。 2.在創(chuàng)建存儲過程時使用WITH RECOMPILE參數(shù)項，SQL Server將不會把此存儲過程放在緩沖存儲器中，并且每次調(diào)用此存儲過程時都會重新編譯。 3.在調(diào)用存儲過程時，可以使用參數(shù)選項WITH RECOMPILE強制重新編譯。使用這個參數(shù)項要求存儲過程的參數(shù)必須是非典型的或者數(shù)據(jù)發(fā)生了很大的變化，否則一般不用它。,10.3.5 刪除存儲過程 如果一個存儲過程不再需要，用戶就可以刪除它，但是如果此存儲過程有關(guān)聯(lián)存儲過程調(diào)用，系統(tǒng)會返回錯誤信息。但是刪除存儲過程后，用戶重新定義的同名稱同參數(shù)的存儲過程可以被原來關(guān)聯(lián)的對象使用。 刪除存儲過程的Transact-SQL語法結(jié)構(gòu)為： DROP PROCEDURE schema_name. procedure ,.n ,10.4 觸發(fā)器概述,觸發(fā)器是一種特殊類型的存儲過程，這種存儲過程能夠在某個規(guī)定的事件發(fā)生時觸發(fā)執(zhí)行。觸發(fā)器通常可以強制執(zhí)行一定的業(yè)務(wù)規(guī)則，以保持數(shù)據(jù)完整性、檢查數(shù)據(jù)有效性、實現(xiàn)數(shù)據(jù)庫管理任務(wù)和一些附加的功能。 觸發(fā)器是數(shù)據(jù)庫中比較高級的應(yīng)用，靈活使用觸發(fā)器可以大大增強應(yīng)用程序的健壯性、數(shù)據(jù)庫的可恢復(fù)性和數(shù)據(jù)庫的可管理性。同時可以使開發(fā)人員和數(shù)據(jù)庫管理員實現(xiàn)一些復(fù)雜的功能，簡化開發(fā)步驟，降低開發(fā)成本，增加開發(fā)效率，提高數(shù)據(jù)庫的可靠性。,10.4.1 觸發(fā)器的特點 觸發(fā)器是一種特殊的存儲過程，除了存儲過程的特點外，它還另外有以下特點： 觸發(fā)器是自動執(zhí)行的，可以在一定條件下觸發(fā)。 觸發(fā)器可以同步數(shù)據(jù)庫的相關(guān)表，進行級聯(lián)更改。 觸發(fā)器可以實現(xiàn)更復(fù)雜的安全檢查。它可以實現(xiàn)比CHECK更復(fù)雜的業(yè)務(wù)規(guī)則，還可以引用其他表中的列。 觸發(fā)器可以實現(xiàn)數(shù)據(jù)庫的管理任務(wù)。如DDL觸發(fā)器，在DDL語句執(zhí)行后觸發(fā)，可以實現(xiàn)一些統(tǒng)一的數(shù)據(jù)庫管理策略。,10.4.2 觸發(fā)器的種類 1. DML觸發(fā)器 DML觸發(fā)器是在執(zhí)行數(shù)據(jù)操作語言事件時被調(diào)用的觸發(fā)器，其中數(shù)據(jù)操作語言事件包括：INSERT、UPDATE和DELETE語句。觸發(fā)器中可以包含復(fù)雜的Transact-SQL語句，觸發(fā)器整體被看作一個事務(wù)，可以回滾。 2. DDL觸發(fā)器 與DML觸發(fā)器類似，與DML不同的是，它相應(yīng)的觸發(fā)事件是由數(shù)據(jù)定義語言引起的事件，包括：CREATE、ALTER和DROP語句，DDL觸發(fā)器用于執(zhí)行數(shù)據(jù)庫管理任務(wù)，如調(diào)節(jié)和審計數(shù)據(jù)庫運轉(zhuǎn)。DDL觸發(fā)器只能在觸發(fā)事件發(fā)生后才會調(diào)用執(zhí)行，即它只能是AFTER類型的。,10.4.3 inserted表和deleted表 觸發(fā)器中兩個比較特殊的對象：inserted表和deleted表。在使用觸發(fā)器過程中，SQL Server使用到了兩張?zhí)厥獾呐R時表，分別是inserted表和deleted表。這兩張表都存在于高速緩存中(當然，如果內(nèi)存不夠用，也可能存儲在硬盤上)，實際上是事務(wù)日志的視圖，它們與創(chuàng)建了觸發(fā)器的表有著相同的結(jié)構(gòu)。,10.5 觸發(fā)器設(shè)計規(guī)則,10.5.1 確定觸發(fā)器的種類 首先用戶應(yīng)根據(jù)相應(yīng)的觸發(fā)事件確定到底是DML觸發(fā)器還是DDL觸發(fā)器，如果是DML觸發(fā)器，用戶在要根據(jù)數(shù)據(jù)操作類型確定是INSERT、UPDATE還是DELETE觸發(fā)器，然后再根據(jù)約束規(guī)則，是希望觸發(fā)事件發(fā)生后還是發(fā)生前來觸發(fā)執(zhí)行觸發(fā)器，從而確定是AFTER還是INSTEAD OF類型的觸發(fā)器。 對于DDL觸發(fā)器，它只有AFTER一種類型，只需根據(jù)觸發(fā)事件的類型確定觸發(fā)器，來做出相應(yīng)的響應(yīng)。,10.5.2 確定觸發(fā)器的響應(yīng)范圍 對于DML觸發(fā)器，其響應(yīng)范圍是在數(shù)據(jù)庫范圍中，我們無需討論。而對于DDL觸發(fā)器，就需要確定它的響應(yīng)范圍是數(shù)據(jù)庫級的還是服務(wù)器級的響應(yīng)。在此只列出了SQL Server 2005中服務(wù)器級的DDL觸發(fā)事件，如表10.2所示，其他的DDL事件都是數(shù)據(jù)庫級的。,10.5.3 一些設(shè)計規(guī)則 在設(shè)計觸發(fā)器時，用戶可以參考以下的設(shè)計規(guī)則： DML觸發(fā)器的實現(xiàn)者是表的默認擁有者，權(quán)限不能轉(zhuǎn)移給別的用戶。 DML觸發(fā)器必須是在當前數(shù)據(jù)庫上創(chuàng)建，盡管它可以引用別的數(shù)據(jù)庫。 不能對系統(tǒng)表和臨時表創(chuàng)建觸發(fā)器。 每個表可以有多個不同名稱的AFTER觸發(fā)器，但每種觸發(fā)事件只能有一個INSTEAD OF類型觸發(fā)器。 觸發(fā)器只能創(chuàng)建在表或者視圖的模式中。,10.6 實現(xiàn)和管理觸發(fā)器,10.6.1 創(chuàng)建觸發(fā)器 1. 創(chuàng)建DML觸發(fā)器的語法 2. 創(chuàng)建DDL觸發(fā)器的語法 3. 創(chuàng)建DML觸發(fā)器的步驟 4. 創(chuàng)建作用范圍為數(shù)據(jù)庫的DDL觸發(fā)器的步驟 5. 創(chuàng)建作用范圍為服務(wù)器的DDL觸發(fā)器,10.6.2 更改觸發(fā)器 當一個觸發(fā)器必須修改時，用戶可以先刪除此觸發(fā)器然后重建新的觸發(fā)器，也可以使用Transact-SQL語句對它進行修改。修改DML和DDL觸發(fā)器的語法結(jié)構(gòu)與創(chuàng)建它們的語法結(jié)構(gòu)類似，除了使用的開始關(guān)鍵詞變?yōu)锳LTER和在修改DML觸發(fā)器時不能使用WITH APPEND參數(shù)選項外，其他語法結(jié)構(gòu)都相同，在此我們不再給出詳細的語法列表。 在Microsoft SQL Server Management Studio中更改觸發(fā)器的具體步驟如下。略,10.6.3 刪除觸發(fā)器和停止觸發(fā)器 當一個觸發(fā)器不再使用時，用戶可以刪除它，也可以停止使用它，停止使用的觸發(fā)器沒有被刪除，仍然保存在數(shù)據(jù)庫中，當用戶想要使用的時候還可以激活它，新創(chuàng)建的觸發(fā)器默認為激活狀態(tài)。 1. 使用Management Studio刪除觸發(fā)器 2. 使用Tran