存儲過程與觸發(fā)器課件

1、第7章 存儲過程與觸發(fā)器 科學(xué)出版社高校計算機(jī),電氣與信息管理與商務(wù)類規(guī)劃教材2012上海市精品課程上海教育高地建設(shè)項目主編 賈鐵軍 甘泉副主編 沈?qū)W東 常艷 連志剛 胡靜編著 李向臣 曾剛 數(shù)據(jù)庫原理應(yīng)用與實踐目 錄教學(xué)目標(biāo) 了解存儲過程的特點(diǎn)、類型和作用 理解和掌握存儲過程的執(zhí)行方式 了解DML觸發(fā)器的工作原理 理解和掌握DDL觸發(fā)器的特點(diǎn)和創(chuàng)建方式重點(diǎn)7.1存儲過程概述 7.1.1 存儲過程的概念 存儲過程（Stored Procedure）是數(shù)據(jù)庫系統(tǒng)中，一組完成特定功能的SQL語句集。經(jīng)編譯后存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程名及給出參數(shù)（若此存儲過程帶有參數(shù)）進(jìn)行調(diào)用執(zhí)行。 SQ

2、L Server提供了一種方法，可將一些固定的操作集中由SQL Server數(shù)據(jù)庫服務(wù)器完成,以實現(xiàn)某個任務(wù),這種方法就是存儲過程。 要點(diǎn)復(fù)習(xí)可調(diào)用(執(zhí)行)的過程文件7.1存儲過程概述 7.1.1 存儲過程的概念 存儲過程是一組相關(guān)的T-SQL語句集合，提供了一種封裝任務(wù)的方法，并具有強(qiáng)大的編程功能。 存儲過程包括4個方面：（1）包含在數(shù)據(jù)庫中執(zhí)行操作的語句，包括調(diào)用(執(zhí)行)其他存儲過程。（2）接受輸入?yún)?shù)。（3）狀態(tài)值返回指示成功或失敗。（4）以輸出參數(shù)的形式，將多個值返回到發(fā)起調(diào)用的存儲過程或客戶端應(yīng)用程序。 7.1存儲過程概述 2 存儲過程的類型在SQL Server系統(tǒng)中，提供3種基本

3、存儲過程類型:（1）用戶定義的存儲過程（2）擴(kuò)展存儲過程（3）系統(tǒng)存儲過程 除此之外，還有臨時存儲過程、遠(yuǎn)程存儲過程等，各自起著不同的作用。 7.2.1 創(chuàng)建存儲過程可以使用三種創(chuàng)建存儲過程方法：（1）利用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。（2）使用SQL Server企業(yè)管理器創(chuàng)建存儲過程。（3）使用T-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲過程(重點(diǎn)介紹)。7.2 存儲過程的實現(xiàn)*7.2.2 創(chuàng)建參數(shù)化存儲過程1.使用輸入?yún)?shù)的準(zhǔn)則 若要定義接受參數(shù)的存儲過程，應(yīng)在CREATE PROCEDURE語句中聲明參數(shù)。使用輸入?yún)?shù)時，應(yīng)考慮以下準(zhǔn)則：（1）根據(jù)情況為參數(shù)提供默認(rèn)值

4、。如果定義了默認(rèn)值，則用戶無需為該參數(shù)指定值即可執(zhí)行存儲過程。（2）在存儲過程的開頭驗證所有傳入的參數(shù)值，以盡早查出缺少的值和無效值。7.2 存儲過程的實現(xiàn)2. 使用輸入?yún)?shù)的示例【案例7-4】修改存儲過程LongLeadProducts,添加參數(shù)MinimumLength,并指定其為int數(shù)據(jù)類型和其默認(rèn)值為1。ALTER PROC Production.LongLeadProducts MinimumLength int = 1 - default valueAS IF (MinimumLength = 1 ORDER BY DaysToManufacture DESC, Name GO7

5、.2 存儲過程的實現(xiàn) 7.2.5 更名或刪除存儲過程1. 更名存儲過程 修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下： sp_rename原存儲過程名，新存儲過程名2. 刪除存儲過程 若要從當(dāng)前數(shù)據(jù)庫中刪除用戶定義的存儲過程，應(yīng)使用DROP PROCEDURE語句。其語法格式為： DROP PROCEDURE 存儲過程名7.2 存儲過程的實現(xiàn)7.3 觸發(fā)器應(yīng)用 1. 觸發(fā)器的概念及類型 按照觸發(fā)事件的不同，可以把SQL Server系統(tǒng)提供的觸發(fā)器分成兩大類型，即DML觸發(fā)器和DDL觸發(fā)器。 在SQL Server中，可以創(chuàng)建CLR觸發(fā)器，既可以是DML觸發(fā)器，也可

6、以是DDL觸發(fā)器。 由于SQLServer 與.NET Framework 公共語言運(yùn)行庫 (CLR) 相集成，可用任何 .NET Framework 語言創(chuàng)建 CLR 觸發(fā)器。 7.3 觸發(fā)器應(yīng)用 1. 觸發(fā)器的概念及類型 當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言(data manipulation language，DML)事件時將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。 按照觸發(fā)器事件類型的不同，可以把SQL Server 系統(tǒng)提供的DML觸發(fā)器分成3種類型，即INSERT類型、UPDATE類型和DELETE類型。這也是DML觸發(fā)

7、器的基本類型。 7.3 觸發(fā)器應(yīng)用 2. 觸發(fā)器的用途 觸發(fā)器是一種特殊的存儲過程，在INSERT，UPDATE或DELETE語句修改指定表中的數(shù)據(jù)時執(zhí)行。觸發(fā)器可查詢其他表并且可包含復(fù)雜的T-SQL語句。人們通常創(chuàng)建觸發(fā)器以往不同表中的邏輯相關(guān)數(shù)據(jù)之間實施引用完整性或一致性。 7.3 觸發(fā)器應(yīng)用 7.3.2 創(chuàng)建觸發(fā)器可使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器，其語法如下： CREATE TRIGGER trigger_name ON table | view FOR |AFTER| INSTEAD OF| INSTERT|UPDATE|DELETE WITH APPEND AS sq

8、l_statement7.3 觸發(fā)器應(yīng)用 7.3.2 創(chuàng)建觸發(fā)器 觸發(fā)器的類型，有兩類觸發(fā)器DML和DDL，其中，DML觸發(fā)器也有兩類： （1） AFTER觸發(fā)器。該觸發(fā)器在執(zhí)INSERT, UPDATE, DELETE語句的之后執(zhí)行。 （2）INSTEAD OF觸發(fā)器，該觸發(fā)器替代常規(guī)觸發(fā)操作執(zhí)行,還可以在基于一個或多個基表的視圖上定義。 7.3 觸發(fā)器應(yīng)用 7.3.3 INSERT觸發(fā)器的工作方式 當(dāng)執(zhí)行INSERT語句將要數(shù)據(jù)插入表或視圖時，如果該表或視圖配置了INSERT觸發(fā)器，就會激發(fā)該INSERT觸發(fā)器來執(zhí)行特定的操作。 當(dāng)INSERT觸發(fā)器觸發(fā)時，新行將插入觸發(fā)器和inserte

9、d表。inserted表是一個邏輯表，保留已插入行的副本。inserted表包含由INSERT語句引起的已記入日志的插入活動。 7.3 觸發(fā)器應(yīng)用 7.3.4 DELETE觸發(fā)器的工作方式 DELETE觸發(fā)器是一種特殊的存儲過程，它在每次DELETE語句從配置了該觸發(fā)器的表或者視圖中刪除數(shù)據(jù)時執(zhí)行。 當(dāng)DELETE觸發(fā)器激發(fā)時，被刪除的行將放置在特殊的deleted表中。Deleted表是一個邏輯表，它保留已刪除行的副本。 7.3 觸發(fā)器應(yīng)用 7.3.5 UPDATE觸發(fā)器的工作方式 UPDATE觸發(fā)器是在每次UPDATE語句配置了UPDATE觸發(fā)器的表或視圖中的數(shù)據(jù)進(jìn)行更改時執(zhí)行的觸發(fā)器。UPDATE觸發(fā)器的工作過程可視為兩步：（1）數(shù)據(jù)前映像的DELETE步驟。（2）捕獲數(shù)據(jù)后映像是INSERT語句。 當(dāng)UPDATE語句在已定義了觸發(fā)器的表上執(zhí)行時，原始行（前映像）移入deleted表，而更新行（后映像）插入inserted表中。7.3 觸發(fā)器應(yīng)用 現(xiàn)在可以來總結(jié)一下，觸發(fā)器可用來維持?jǐn)?shù)據(jù)完整性，它防止了對數(shù)據(jù)的不正確、未授權(quán)的、和不一致的改變。當(dāng)觸發(fā)器激發(fā)對INSERT，DELETE，或UPDATE語句的響應(yīng)時， 兩個特殊的表被創(chuàng)