第10章-存儲(chǔ)過程與觸發(fā)器課件

1、第10章 存儲(chǔ)過程與觸發(fā)器第10章 存儲(chǔ)過程與觸發(fā)器本章內(nèi)容10.1 存儲(chǔ)過程概述10.2 存儲(chǔ)過程的創(chuàng)建與使用10.3 觸發(fā)器概述10.4 觸發(fā)器的創(chuàng)建與使用10.5 事務(wù)處理10.6 SQL Server的鎖機(jī)制本章內(nèi)容10.1 存儲(chǔ)過程概述10.1 存儲(chǔ)過程概述 存儲(chǔ)過程是SQL Server服務(wù)器上一組預(yù)編譯的Transact-SQL語句，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。10.1 存儲(chǔ)過程概述 存儲(chǔ)過程是SQL Server服務(wù)10.1 存儲(chǔ)過程概述SQL Server存儲(chǔ)過程的類型包括：系統(tǒng)存儲(chǔ)過程用戶定義存儲(chǔ)過程臨時(shí)存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程。1.

2、 存儲(chǔ)過程的類型10.1 存儲(chǔ)過程概述SQL Server存儲(chǔ)過程的類型包括10.1 存儲(chǔ)過程概述(1)系統(tǒng)存儲(chǔ)過程 是指由系統(tǒng)提供的存儲(chǔ)過程，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server提供支持。通過系統(tǒng)存儲(chǔ)過程，SQL Server中的許多管理性或信息性的活動(dòng)(例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息)都可以順利有效地完成。盡管系統(tǒng)存儲(chǔ)過程被放在master數(shù)據(jù)庫(kù)中，仍可以在其他數(shù)據(jù)庫(kù)中對(duì)其進(jìn)行調(diào)用(調(diào)用時(shí)，不必在存儲(chǔ)過程名前加上數(shù)據(jù)庫(kù)名)。當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過

3、程會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)建。10.1 存儲(chǔ)過程概述(1)系統(tǒng)存儲(chǔ)過程 是指由系統(tǒng)提供的存10.1 存儲(chǔ)過程概述(2)用戶定義存儲(chǔ)過程是由用戶創(chuàng)建并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲(chǔ)過程。它處于用戶創(chuàng)建的數(shù)據(jù)庫(kù)中，存儲(chǔ)過程名前沒有前綴sp_。10.1 存儲(chǔ)過程概述(2)用戶定義存儲(chǔ)過程是由用戶創(chuàng)建并能10.1 存儲(chǔ)過程概述(3)臨時(shí)存儲(chǔ)過程臨時(shí)存儲(chǔ)過程與臨時(shí)表類似，分為局部臨時(shí)存儲(chǔ)過程和全局臨時(shí)存儲(chǔ)過程，且可以分別向該過程名稱前面添加“#”或“#”前綴表示?！?”表示本地臨時(shí)存儲(chǔ)過程，“#”表示全局臨時(shí)存儲(chǔ)過程。使用臨時(shí)存儲(chǔ)過程必須創(chuàng)建本地連接，當(dāng)SQL Server關(guān)閉后，這

4、些臨時(shí)存儲(chǔ)過程將自動(dòng)被刪除。由于SQL Server支持重新使用執(zhí)行計(jì)劃，所以連接到SQL Server 2000的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲(chǔ)過程，而不使用臨時(shí)存儲(chǔ)過程。10.1 存儲(chǔ)過程概述(3)臨時(shí)存儲(chǔ)過程臨時(shí)存儲(chǔ)過程與臨時(shí)表10.1 存儲(chǔ)過程概述(4)擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程是SQL Server可以動(dòng)態(tài)裝載和執(zhí)行的動(dòng)態(tài)鏈接庫(kù)(DLL)。當(dāng)擴(kuò)展存儲(chǔ)過程加載到SQL Server中，它的使用方法與系統(tǒng)存儲(chǔ)過程一樣。擴(kuò)展存儲(chǔ)過程只能添加到master數(shù)據(jù)庫(kù)中，其前綴是xp_。10.1 存儲(chǔ)過程概述(4)擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程是SQL10.1 存儲(chǔ)過程概述2. 存儲(chǔ)過程

5、的功能特點(diǎn)SQL Server的存儲(chǔ)過程可實(shí)現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個(gè)值。(2)包含執(zhí)行數(shù)據(jù)庫(kù)操作的編程語句，包括調(diào)用其他過程。(3)為調(diào)用過程或批處理返回一個(gè)狀態(tài)值，以表示成功或失敗(及失敗原因)。10.1 存儲(chǔ)過程概述2. 存儲(chǔ)過程的功能特點(diǎn)SQL Ser10.1 存儲(chǔ)過程概述存儲(chǔ)過程具有以下優(yōu)點(diǎn)(1)模塊化編程。(2)快速執(zhí)行。 (3)減少網(wǎng)絡(luò)通信量。(4)提供安全機(jī)制。(5)保證操作一致性。10.1 存儲(chǔ)過程概述存儲(chǔ)過程具有以下優(yōu)點(diǎn)(1)模塊化編程。10.2.1 創(chuàng)建存儲(chǔ)過程10.2.2 執(zhí)行存儲(chǔ)過程10.2.3 修改存儲(chǔ)過程10.2.4

6、 刪除存儲(chǔ)過程10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲(chǔ)過程10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲(chǔ)過程1使用SQL Server管理平臺(tái)創(chuàng)建存儲(chǔ)過程 （1）打開SQL Server管理平臺(tái)，展開節(jié)點(diǎn)“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過程”，在窗口的右側(cè)顯示出當(dāng)前數(shù)據(jù)庫(kù)的所有存儲(chǔ)過程。單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“新建存儲(chǔ)過程”命令 。10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲(chǔ)過程1使用SQL Server管理（2）在打開的SQL命令窗口中，系統(tǒng)給出了創(chuàng)建存儲(chǔ)過程命令的模板，如圖10-2所示。在模板中可

7、以輸入創(chuàng)建存儲(chǔ)過程的Transact-SQL語句后，單擊“執(zhí)行”按鈕即可創(chuàng)建存儲(chǔ)過程。 10.2.1 創(chuàng)建存儲(chǔ)過程（2）在打開的SQL命令窗口中，系統(tǒng)給出了創(chuàng)建存儲(chǔ)過程命令的（3）建立存儲(chǔ)過程的命令被成功執(zhí)行后，在“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過程”中可以看到新建立的存儲(chǔ)過程 10.2.1 創(chuàng)建存儲(chǔ)過程（3）建立存儲(chǔ)過程的命令被成功執(zhí)行后，在“對(duì)象資源管理器”2. 使用CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程使用CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程應(yīng)該考慮以下幾個(gè)方面：(1)在一個(gè)批處理中，CREATE PROCEDURE語句不能與其他SQL語句合并在

8、一起。(2)數(shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。(3)存儲(chǔ)過程作為數(shù)據(jù)庫(kù)對(duì)象其命名必須符合標(biāo)識(shí)符的命名規(guī)則。(4)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過程。10.2.1 創(chuàng)建存儲(chǔ)過程2. 使用CREATE PROCEDURE語句創(chuàng)建存儲(chǔ)過程使創(chuàng)建存儲(chǔ)過程語句的語法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION

9、FOR REPLICATION AS sql_statement ,.n 10.2.1 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程語句的語法格式如下：CREATE PROCED例10-1 創(chuàng)建存儲(chǔ)過程，從表goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價(jià)。10.2.1 創(chuàng)建存儲(chǔ)過程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification gcON g.classification_i

10、d = gc.classification_id存儲(chǔ)過程創(chuàng)建后，存儲(chǔ)過程的名稱存放在sysobject表中，文本存放在syscomments表中。例10-1 創(chuàng)建存儲(chǔ)過程，從表goods和表goods_c10.2.2 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程的語法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.2 執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程的語法格式：10.2例如，執(zhí)行

11、例10-1的存儲(chǔ)過程goods_info在SQL查詢分析器中輸入命令：EXEC goods_info運(yùn)行的結(jié)果：10.2.2 執(zhí)行存儲(chǔ)過程例如，執(zhí)行例10-1的存儲(chǔ)過程goods_info在SQL查10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.3 修改存儲(chǔ)過程修改存儲(chǔ)過程可以通過SQL Server管理平臺(tái)和Transact-SQL語句實(shí)現(xiàn)。1使用SQL Server管理平臺(tái)修改存儲(chǔ)過程 10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.3 修改存儲(chǔ)過程修10.2.3 修改存儲(chǔ)過程2. 使用ALTER PROCEDURE語句修改存儲(chǔ)過程ALTER PROCEDURE的語法規(guī)則是：ALTER PROCEDURE

12、 procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 10.2.3 修改存儲(chǔ)過程2. 使用ALTER PROCED10.2.3 修改存儲(chǔ)過程例10-2 使用ALTER PROCEDURE語句更改存儲(chǔ)過程。(1)創(chuàng)建存儲(chǔ)過程employee_dep，以獲取總經(jīng)理辦的男員工。CREATE PROCEDURE employee_dep AS

13、SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 AND e.department_id=D001GO執(zhí)行存儲(chǔ)過程employee_dep，結(jié)果如圖 10.2.3 修改存儲(chǔ)過程例10-2 使用ALTER PR10.2.3 修改存儲(chǔ)過程(2) 查看employee_dep存儲(chǔ)過程的文本信息SELECT o.id, c.textFROM sysobjects o INNER

14、JOIN syscomments c ON o.id = c.idWHERE o.type = P AND = employee_depGO10.2.3 修改存儲(chǔ)過程(2) 查看employee_de10.2.3 修改存儲(chǔ)過程(3)使用ALTER PROCEDURE語句對(duì)employee_dep過程進(jìn)行修改，使其能夠顯示出所有男員工，并使employee_dep過程以加密方式存儲(chǔ)在表syscomments中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name, sex, address, depar

15、tment_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 GO10.2.3 修改存儲(chǔ)過程(3)使用ALTER PROCED10.2.3 修改存儲(chǔ)過程執(zhí)行修改后的存儲(chǔ)過程employee_dep，結(jié)果如圖：10.2.3 修改存儲(chǔ)過程執(zhí)行修改后的存儲(chǔ)過程employe10.2.3 修改存儲(chǔ)過程(4)從系統(tǒng)表sysobjects和syscomments提取修改后的存儲(chǔ)過程employee_dep的文本信息可以運(yùn)行步驟（2）中的代碼，結(jié)果如圖 這是由于在ALTER P

16、ROCEDURE語句中使用WITH ENCRYPTION關(guān)鍵字對(duì)存儲(chǔ)過程employee_dep的文本進(jìn)行了加密，其文本信息顯示為NULL。 10.2.3 修改存儲(chǔ)過程(4)從系統(tǒng)表sysobjects10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.4 刪除存儲(chǔ)過程 存儲(chǔ)過程可以被快速刪除和重建，因?yàn)樗鼪]有存儲(chǔ)數(shù)據(jù)。1使用SQL Server管理平臺(tái)刪除存儲(chǔ)過程 （1）打開SQL Server管理平臺(tái)，展開節(jié)點(diǎn)“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過程”，選擇要?jiǎng)h除的存儲(chǔ)過程，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對(duì)象”對(duì)話框中單擊“確定”按鈕即可刪除存儲(chǔ)

17、過程。 10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.4 刪除存儲(chǔ)過程 10.2.4 刪除存儲(chǔ)過程2. 使用DROP PROCEDURE刪除存儲(chǔ)過程DROP PROCEDURE的語法如下：DROP PROCEDURE procedure_name ,.n 例如刪除例10-2創(chuàng)建的存儲(chǔ)過程employee_dep：DROP PROCEDURE employee_depGO10.2.4 刪除存儲(chǔ)過程2. 使用DROP PROCEDU10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值存儲(chǔ)過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲(chǔ)過程返回的狀

18、態(tài)值對(duì)存儲(chǔ)過程進(jìn)行管理。1. 參數(shù)存儲(chǔ)過程的參數(shù)在創(chuàng)建過程時(shí)聲明。SQL Server支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。10.2 存儲(chǔ)過程的創(chuàng)建與使用10.2.5 存儲(chǔ)過程參數(shù)與10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(1)輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過程傳送數(shù)據(jù)值。要定義存儲(chǔ)過程的輸入?yún)?shù)，必須在CREATE PROCEDURE語句中聲明一個(gè)或多個(gè)變量及類型。10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(1)輸入?yún)?shù)輸入?yún)?shù)允許10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-3 創(chuàng)建帶參數(shù)的存儲(chǔ)過程，從表employee、sell_order、goods、goods_classification的連接中返回

19、輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name, goods_name,classification_name, order_numFROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification

20、_id=g.classification_idWHERE employee_name LIKE employee_name10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-3 創(chuàng)建帶參數(shù)的10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值存儲(chǔ)過程sell_info以employee_name變量作為輸入?yún)?shù)，執(zhí)行時(shí)，可以省略參數(shù)名，直接給參數(shù)值。在SQL查詢分析器中輸入命令：EXEC sell_info 東方牧運(yùn)行結(jié)果如圖。參數(shù)值可以包含通配符“%”，例如，查找所有姓“錢”的員工的銷售情況可以使用以下命令：EXEC sell_info 錢%10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值存儲(chǔ)過程sell_info10.2.5 存儲(chǔ)

21、過程參數(shù)與狀態(tài)值執(zhí)行時(shí)，參數(shù)可以由位置標(biāo)識(shí)，也可以由名字標(biāo)識(shí)。例如，定義一個(gè)具有3個(gè)參數(shù)的存儲(chǔ)過程：CREATE PROC myproc val1 int, val2 int, val3 intAS .參數(shù)以位置傳遞：EXEC myproc 10,20,15參數(shù)以名字傳遞，每個(gè)值由對(duì)應(yīng)的參數(shù)名引導(dǎo)：EXEC myproc val2=20,val1=10,val3=15按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值執(zhí)行時(shí)，參數(shù)可以由位置標(biāo)識(shí)10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(2)輸出參數(shù)輸出參數(shù)允許存儲(chǔ)過程將數(shù)據(jù)值或游標(biāo)變

22、量傳回調(diào)用程序。OUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程中的參數(shù)。為了使用輸出參數(shù)，在CREATE PROCEDURE和EXECUTE語句中都必須使用OUTPUT關(guān)鍵字。10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(2)輸出參數(shù)輸出參數(shù)允許10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-4 創(chuàng)建存儲(chǔ)過程price_goods，通過輸入?yún)?shù)在goods表中查找商品，以輸出參數(shù)獲取商品單價(jià)。CREATE PROC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_pric

23、eFROM goodsWHERE goods_name=goods_name10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-4 創(chuàng)建存儲(chǔ)過程10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值執(zhí)行price_goods存儲(chǔ)過程的代碼如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT price運(yùn)行結(jié)果是商品名為Canon LBP2900的商品單價(jià)：1380.0EXECUTE語句還需要關(guān)鍵字OUTPUT以允許參數(shù)值返回給變量。10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值執(zhí)行price_goods10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(1)用RE

24、TURN語句定義返回值存儲(chǔ)過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲(chǔ)過程沒有顯式設(shè)置返回代碼的值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。2. 返回存儲(chǔ)過程的狀態(tài)10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值(1)用RETURN語句定10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-5 創(chuàng)建存儲(chǔ)過程，輸入商品類別，返回各種商品名稱。在存儲(chǔ)過程中，用值15表示用戶沒有提供參數(shù)；值-l01表示沒有輸入商品類別；值0表示過程運(yùn)行沒有

25、出錯(cuò)。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=cl_name) RETURN -101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND gc.classification_nam

26、e=cl_nameRETURN 010.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值例10-5 創(chuàng)建存儲(chǔ)過程10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值在執(zhí)行過程時(shí)，要正確接收返回的狀態(tài)值，必須使用以下語句；EXECUTE status_var=procedure_name(2)捕獲返回狀態(tài)值10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值在執(zhí)行過程時(shí)，要正確接收返10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值DECLARE return_status intEXEC return_status=cl_goods 筆記本計(jì)算機(jī)IF return_status=15 SELECT 語法錯(cuò)誤ELSE IF return_status=-101 SE

27、LECT 沒有找到該商品類別執(zhí)行時(shí)，將對(duì)不同的輸入值返回不同的狀態(tài)值及處理結(jié)果。例10-5的存儲(chǔ)過程cl_goods執(zhí)行時(shí)使用以下語句：10.2.5 存儲(chǔ)過程參數(shù)與狀態(tài)值DECLARE retu觸發(fā)器是一種特殊類型的存儲(chǔ)過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過程可以通過過程名字直接調(diào)用。當(dāng)對(duì)某一表進(jìn)行UPDATE、INSERT、DELETE操作時(shí)，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。 10.3 觸發(fā)器概述觸發(fā)器是一種特殊類型

28、的存儲(chǔ)過程。10.3 觸發(fā)器概述10.4.1 創(chuàng)建觸發(fā)器10.4.2 刪除觸發(fā)器10.4.3 修改觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用10.4.1 創(chuàng)建觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用10.4 觸發(fā)器的創(chuàng)建與使用1使用SQL Server管理平臺(tái)創(chuàng)建觸發(fā)器 10.4.1 創(chuàng)建觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用1使用SQL Server管理10.4.1 創(chuàng)建觸發(fā)器2. 使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器CREATE TRIGGER語句的語法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR

29、 | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement . n 10.4.1 創(chuàng)建觸發(fā)器2. 使用CREATE TRIGGE10.4.1 創(chuàng)建觸發(fā)器例10-6 在employee表上創(chuàng)建一個(gè)DELETE類型的觸發(fā)器，該觸發(fā)器的名稱為tr_employee。(1)創(chuàng)建觸發(fā)器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工被刪除SELECT msgR

30、ETURN10.4.1 創(chuàng)建觸發(fā)器例10-6 在employee表上10.4.1 創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器tr_employee觸發(fā)器不能通過名字來執(zhí)行，而是在相應(yīng)的SQL語句被執(zhí)行時(shí)自動(dòng)觸發(fā)的。例如執(zhí)行以下DELETE語句：DELETE FROM employeeWHERE employee_name = 張三該語句要?jiǎng)h除員工姓名為“張三”記錄，由此激活了表employee 的DELETE類型的觸發(fā)器tr_employee，系統(tǒng)執(zhí)行tr_employee觸發(fā)器中AS之后的語句，并顯示以下信息：1個(gè)員工被刪除10.4.1 創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器tr_employe10.4.1 創(chuàng)建觸發(fā)器

31、在觸發(fā)器的執(zhí)行過程中，SQL Server建立和管理兩個(gè)臨時(shí)的虛擬表：Deleted表和Inserted表。這兩個(gè)表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。在執(zhí)行INSERT或UPDATE語句之后所有被添加或被更新的記錄都會(huì)存儲(chǔ)在Inserted表中。在執(zhí)行DELETE或UPDATE語句時(shí)，從觸發(fā)程序表中被刪除的行會(huì)發(fā)送到Deleted表。對(duì)于更新操作，SQL Server先將要進(jìn)行修改的記錄存儲(chǔ)到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。3. Deleted表和Inserted表10.4.1 創(chuàng)建觸發(fā)器在觸發(fā)器的執(zhí)行過程中，SQL Ser10.4

32、.1 創(chuàng)建觸發(fā)器例10-7 為表customer創(chuàng)建一個(gè)名為test_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時(shí)，激活該觸發(fā)器。創(chuàng)建test_tr觸發(fā)器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM inserted SELECT * FROM deletedcustomer表執(zhí)行以下插入操作：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,張三,1234567)10.4.1 創(chuàng)建觸發(fā)器例10-7 為表cu

33、stomer創(chuàng)10.4.1 創(chuàng)建觸發(fā)器INSERT操作激活觸發(fā)器test_tr，輸出如圖10-19所示的表格。10.4.1 創(chuàng)建觸發(fā)器INSERT操作激活觸發(fā)器test_10.4.2 修改觸發(fā)器通過SQL Server管理平臺(tái)、系統(tǒng)存儲(chǔ)過程或Transact_SQL語句，可以修改觸發(fā)器的名字和正文。 1. 使用sp_rename系統(tǒng)存儲(chǔ)過程修改觸發(fā)器的名字：sp_rename oldname, newname10.4 觸發(fā)器的創(chuàng)建與使用10.4.2 修改觸發(fā)器通過SQL Server管理平臺(tái)、10.4.3 修改觸發(fā)器2使用SQL Server管理平臺(tái)修改觸發(fā)器的正文 修改觸發(fā)器的操作步驟如下：

34、（1）打開SQL Server管理平臺(tái)，展開節(jié)點(diǎn)“對(duì)象資源管理器”“Sales”數(shù)據(jù)庫(kù)|“表”“customer”表|“觸發(fā)器”，選擇要?jiǎng)h除的觸發(fā)器（如例10-7創(chuàng)建的test_tr觸發(fā)器），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“修改”命令。（2）此時(shí)在右邊的編輯器窗口中出現(xiàn)觸發(fā)器的源代碼（將CREATE TRIGGER改為了ALTER TRIGGER），可以直接進(jìn)行修改。修改完后單擊工具欄中的“執(zhí)行”按鈕執(zhí)行該觸發(fā)器代碼，從而達(dá)到目的。 10.4.3 修改觸發(fā)器2使用SQL Server管理平臺(tái)10.4.3 修改觸發(fā)器3. 使用ALTER TRIGGER語句修改觸發(fā)器修改觸發(fā)器的語法如下：A

35、LTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 10.4.3 修改觸發(fā)器3. 使用ALTER TRIGGER10.4.3 修改觸發(fā)器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工數(shù)據(jù)被插入SELECT msgRETURN對(duì)employe

36、e表執(zhí)行以下插入語句：INSERT employee(employee_id,employee_name)VALUES (E016,王五)激活I(lǐng)NSERT觸發(fā)器tr_employee，顯示信息：1個(gè)員工數(shù)據(jù)被插入例如，將例10-6的觸發(fā)器tr_employee修改為INSERT操作后進(jìn)行。10.4.3 修改觸發(fā)器ALTER TRIGGER tr_e10.4 觸發(fā)器的創(chuàng)建與使用10.4.3 刪除觸發(fā)器1使用SQL Server管理平臺(tái)刪除觸發(fā)器 操作步驟如下：（1）打開SQL Server管理平臺(tái)，展開節(jié)點(diǎn)“對(duì)象資源管理器”“Sales”數(shù)據(jù)庫(kù)|“表”“customer”表|“觸發(fā)器”，選擇要?jiǎng)h

37、除的觸發(fā)器（如例10-7創(chuàng)建的test_tr觸發(fā)器），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對(duì)象”對(duì)話框中單擊“確定”按鈕即可刪除觸發(fā)器。 10.4 觸發(fā)器的創(chuàng)建與使用10.4.3 刪除觸發(fā)器1使10.4.2 刪除觸發(fā)器2. 使用DROP TRIGGER刪除指定觸發(fā)器刪除觸發(fā)器語句的語法格式如下：DROP TRIGGER trigger_name , .n 例如，刪除例10-6的觸發(fā)器tr_employee：DROP TRIGGER tr_employee10.4.2 刪除觸發(fā)器2. 使用DROP TRIGGER10.5.1 事務(wù)概述10.5.2 事務(wù)管理10.

38、5 事務(wù)處理10.5.1 事務(wù)概述10.5 事務(wù)處理10.5 事務(wù)處理事務(wù)(Transaction)是SQL Server中的一個(gè)邏輯工作單元，該單元將被作為一個(gè)整體進(jìn)行處理。事務(wù)保證連續(xù)多個(gè)操作必須全部執(zhí)行成功，否則必須立即回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要不全部將數(shù)據(jù)所要執(zhí)行的操作完成，要不全部數(shù)據(jù)都不修改。10.5.1 事務(wù)概述10.5 事務(wù)處理事務(wù)(Transaction)是SQL S10.5.1 事務(wù)概述例如，企業(yè)取消了倉(cāng)儲(chǔ)部，需要將“倉(cāng)儲(chǔ)部”從department表中刪除，而employee表中的部門編號(hào)與倉(cāng)儲(chǔ)部相對(duì)應(yīng)的員工也應(yīng)刪除。假設(shè)倉(cāng)儲(chǔ)部編號(hào)為D004，第一條DE

39、LETE語句修改department表為：DELETE FROM department WHERE department_id = D004第二條DELETE語句修改employee表為：DELETE FROM employee WHERE department_id = D004因此，必須保證這兩條DELETE語句同時(shí)執(zhí)行，或都不執(zhí)行。這時(shí)可以使用數(shù)據(jù)庫(kù)中的事務(wù)(Transaction)技術(shù)來實(shí)現(xiàn)。1事務(wù)的由來10.5.1 事務(wù)概述例如，企業(yè)取消了倉(cāng)儲(chǔ)部，需要將“倉(cāng)儲(chǔ)部2事務(wù)屬性由于事務(wù)作為一個(gè)邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯(cuò)誤時(shí)，將取消事務(wù)所做的修改。一個(gè)邏輯單元必須具有4個(gè)屬性：原子性(A

40、tomicity)、一致性(Consistency)隔離性(Isolation)持久性(Durability)，這些屬性稱為ACID。10.5.1 事務(wù)概述2事務(wù)屬性由于事務(wù)作為一個(gè)邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯(cuò)誤3事務(wù)模式SQL Server以3種事務(wù)模式管理事務(wù)。(1) 自動(dòng)提交事務(wù)模式：每條單獨(dú)的語句都是一個(gè)事務(wù)。在此模式下，每條Transact-SQL語句在成功執(zhí)行完成后，都被自動(dòng)提交，如果遇到錯(cuò)誤，則自動(dòng)回滾該語句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。(2) 顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動(dòng)和結(jié)束。事務(wù)以BEGIN TRANSACTION語句顯式開始，以COMMIT或ROLLB

41、ACK語句顯式結(jié)束。(3) 隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動(dòng)啟動(dòng)。隱性事務(wù)不需要使用BEGIN TRANSACTION語句標(biāo)識(shí)事務(wù)的開始，但需要以COMMIT或ROLLBACK語句來提交或回滾事務(wù)。10.5.1 事務(wù)概述3事務(wù)模式SQL Server以3種事務(wù)模式管理事務(wù)。1010.5 事務(wù)處理1啟動(dòng)和結(jié)束事務(wù)啟動(dòng)事務(wù)語句的語法格式如下：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description 結(jié)束事務(wù)語句的語法格式如下：COMMIT TRANSACTION transacti

42、on_name | tran_name_variable 10.5.2 事務(wù)管理10.5 事務(wù)處理1啟動(dòng)和結(jié)束事務(wù)10.5.2 事務(wù)管理10.5.2 事務(wù)管理例10-8 建立一個(gè)顯式事務(wù)以顯示Sales數(shù)據(jù)庫(kù)的employee表的數(shù)據(jù)。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTION本例創(chuàng)建的事務(wù)以BEGIN TRANSACTION語句開始，以COMMIT TRANSACTION語句結(jié)束。10.5.2 事務(wù)管理例10-8 建立一個(gè)顯式事務(wù)以顯示Sa10.5.2 事務(wù)管理DECLARE transaction_name var

43、char(32)SELECT transaction_name=tran_deleteBEGIN TRANSACTION transaction_name DELETE FROM department WHERE department_id=D004 DELETE FROM employee WHERE department_id=D004COMMIT TRANSACTION tran_delete例10-9 建立一個(gè)顯式命名事務(wù)以刪除department表的“倉(cāng)儲(chǔ)部”記錄行。10.5.2 事務(wù)管理DECLARE transactio10.5.2 事務(wù)管理CREATE TABLE imp_tr

44、an( num char(2) NOT NULL, cname char(6) NOT NULL)GO SET IMPLICIT_TRANSACTIONS ON -啟動(dòng)隱性事務(wù)模式GO- 第一個(gè)事務(wù)由INSERT語句啟動(dòng)INSERT INTO imp_tran VALUES (01, Zhang)INSERT INTO imp_tran VALUES (02, Wang)COMMIT TRANSACTION -提交第一個(gè)隱性事務(wù)GO- 第二個(gè)隱式事務(wù)由SELECT語句啟動(dòng)SELECT COUNT(*) FROM imp_tranINSERT INTO imp_tran VALUES (03,

45、Li)SELECT * FROM imp_tranCOMMIT TRANSACTION -提交第二個(gè)隱性事務(wù)GOSET IMPLICIT_TRANSACTIONS OFF -關(guān)閉隱性事務(wù)模式GO例10-10 隱性事務(wù)處理過程。10.5.2 事務(wù)管理CREATE TABLE imp_tr10.5.2 事務(wù)管理當(dāng)事務(wù)事務(wù)回滾使用ROLLBACK TRANSACTION語句實(shí)現(xiàn)，其語法格式如下：ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 2事務(wù)回滾10

46、.5.2 事務(wù)管理當(dāng)事務(wù)事務(wù)回滾使用ROLLBACK T10.5.2 事務(wù)管理事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指定位置，則需要在事務(wù)中設(shè)定保存點(diǎn)(SavePoint)。所謂保存點(diǎn)是指定其所在位置之前的事務(wù)語句，不能回滾的語句即此語句前面的操作被視為有效。其語法格式如下：SAVE TRANSACTION savepoint_name | savepoint_variable10.5.2 事務(wù)管理事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指10.5.2 事務(wù)管理例10-11 使用ROLLBACK TRANSACTION語句標(biāo)識(shí)事務(wù)結(jié)束。BEGIN TRANSACTION UPDATE goods S

47、ET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,order_num,order_date) VALUES(S00005,G00006,5,getdate()ROLLBACK TRANSACTION10.5.2 事務(wù)管理例10-11 使用ROLLBACK T10.5.2 事務(wù)管理BEGIN TRANSACTION my_transaction_delete DELETE FROM department WHERE department_id=D00

48、5 SAVE TRANSACTION after_delete -設(shè)置保存點(diǎn) UPDATE employee SET department_id=D001 WHERE department_id=D005 IF (error=0 OR rowcount=0) BEGIN ROLLBACK TRANSACTION after_delete - 如果出錯(cuò)回滾到保存點(diǎn)after_delete COMMIT TRANSACTION my_transaction_delete END ELSE COMMIT TRANSACTION my_transaction_deleteGO例10-12 刪除倉(cāng)儲(chǔ)部，

49、再將倉(cāng)儲(chǔ)部的職工劃分到總經(jīng)理辦。10.5.2 事務(wù)管理BEGIN TRANSACTION m10.5.2 事務(wù)管理CREATE TRIGGER trig_uptab ON goodsFOR UPDATEASSAVE TRANSACTION tran_uptabINSERT INTO newgoods SELECT * FROM insertedIF (error0)BEGIN ROLLBACK TRANSACTION tran_uptabEND例10-13 定義為表goods觸發(fā)器trig_uptab，如果goods表更新數(shù)據(jù)，則把新數(shù)據(jù)復(fù)制到表newgoods中，若出錯(cuò)，則取消復(fù)制操作。10

50、.5.2 事務(wù)管理CREATE TRIGGER trig10.5.2 事務(wù)管理和BEGINEND語句類似，BEGIN TRANSACTION和COMMIT TRANSACTION語句也可以進(jìn)行嵌套，即事務(wù)可以嵌套執(zhí)行。3事務(wù)嵌套10.5.2 事務(wù)管理和BEGINEND語句類似，BEGI10.5.2 事務(wù)管理CREATE TABLE employee_tran( num char(2) NOT NULL, cname char(6) NOT NULL)GO BEGIN TRANSACTION Tran1 -TRANCOUNT為1 INSERT INTO employee_tran VALUES

51、(01, Zhang) BEGIN TRANSACTION Tran2 -TRANCOUNT為2 INSERT INTO employee_tran VALUES (02, Wang) BEGIN TRANSACTION Tran3 -TRANCOUNT為3 PRINT TRANCOUNT INSERT INTO employee_tran VALUES (03, Li) COMMIT TRANSACTION Tran3 -TRANCOUNT為2 PRINT TRANCOUNT COMMIT TRANSACTION Tran2 -TRANCOUNT為1 PRINT TRANCOUNTCOMMI

52、T TRANSACTION Tran1 -TRANCOUNT為0PRINT TRANCOUNT例10-14 提交事務(wù)。運(yùn)行結(jié)果：321010.5.2 事務(wù)管理CREATE TABLE employ10.6.1 鎖模式10.6.2 隔離級(jí)別10.6.3 查看和終止鎖10.6.4 死鎖及其防止10.6 SQL Server的鎖機(jī)制10.6.1 鎖模式10.6 SQL Server的鎖機(jī)10.6 SQL Server的鎖機(jī)制鎖(Lock)作為一種安全機(jī)制，用于控制多個(gè)用戶的并發(fā)操作，以防止用戶讀取下在由其他用戶更改的數(shù)據(jù)或者多個(gè)用戶同時(shí)修改同一數(shù)據(jù)，從而確保事務(wù)完整性和數(shù)據(jù)庫(kù)一致性。10.6.1 鎖

53、模式10.6 SQL Server的鎖機(jī)制鎖(Lock)作為一種10.6.1 鎖模式當(dāng)對(duì)一個(gè)數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問限制，稱對(duì)此數(shù)據(jù)源進(jìn)行了“鎖定”。SQL Server有多種粒度鎖，允許一個(gè)事務(wù)鎖定不同類型的資源： 數(shù)據(jù)行(Row)：數(shù)據(jù)頁(yè)中的單行數(shù)據(jù)。 索引行(Key)：索引頁(yè)中的單行數(shù)據(jù)，即索引的鍵值。 頁(yè)(Page)：頁(yè)是SQL Server存取數(shù)據(jù)的基本單位，其大小為8KB。 擴(kuò)展盤區(qū)(Extent)：一個(gè)盤區(qū)由8個(gè)連續(xù)的頁(yè)組成。 表(Table)。 數(shù)據(jù)庫(kù)(Database)。 允許一個(gè)事務(wù)鎖定的資源類型10.6.1 鎖模式當(dāng)對(duì)一個(gè)數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定10

54、.6.1 鎖模式確定并發(fā)事務(wù)訪問資源方式的鎖模式:(1) 共享鎖(Shared Lock)。(2) 排它鎖(Exclusive Lock)。(3) 更新鎖(Update Lock)。10.6.1 鎖模式確定并發(fā)事務(wù)訪問資源方式的鎖模式:(1)從程序員的角度，鎖可以分為以下兩種類型： (1) 樂觀鎖(Optimistic Lock)。樂觀鎖假定在處理數(shù)據(jù)時(shí)，不需要在應(yīng)用程序的代碼中做任何事情就可以直接在記錄上加鎖，即完全依靠數(shù)據(jù)庫(kù)來管理鎖的工作。一般情況下，當(dāng)執(zhí)行事務(wù)處理時(shí)，SQL Server會(huì)自動(dòng)對(duì)事務(wù)處理范圍內(nèi)更新到的表做鎖定。(2) 悲觀鎖(Pessimistic Lock)。悲觀鎖需要

55、程序員直接管理數(shù)據(jù)或?qū)ο笊系募渔i處理，并負(fù)責(zé)獲取、共享和放棄正在使用的數(shù)據(jù)上的任何鎖。10.6.1 鎖模式從程序員的角度，鎖可以分為以下兩種類型： (1) 樂觀鎖(O10.6 SQL Server的鎖機(jī)制隔離(Isolation)是計(jì)算機(jī)安全技術(shù)中的概念，其本質(zhì)上是一種封鎖機(jī)制。它是指自動(dòng)數(shù)據(jù)處理系統(tǒng)中的用戶和資源的相關(guān)牽制關(guān)系，也就是用戶和進(jìn)程彼此分開，且和操作系統(tǒng)的保護(hù)控制也分開來。事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級(jí)別稱為隔離級(jí)別(Isolation Level)。10.6.2 隔離級(jí)別10.6 SQL Server的鎖機(jī)制隔離(Isolatio10.6.2 隔離級(jí)別較低的隔離級(jí)別可以增加并發(fā)，但

56、代價(jià)是降低數(shù)據(jù)的正確性。相反，較高的隔離級(jí)別可以確保數(shù)據(jù)的正確性，但可能對(duì)并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級(jí)別確定了SQL Server使用的鎖定行為。隔離級(jí)別是一個(gè)事務(wù)必須與其他事務(wù)進(jìn)行隔離的程度。10.6.2 隔離級(jí)別較低的隔離級(jí)別可以增加并發(fā)，但代價(jià)是降10.6.2 隔離級(jí)別(1) 提交讀(READ COMMITTED)。它是SQL Server的默認(rèn)級(jí)別。在此隔離級(jí)別下，SELECT語句不會(huì)也不能返回尚未提交(Committed)的數(shù)據(jù)(即臟數(shù)據(jù))。(2) 未提交讀(READ UNCOMMITTED)。與提交讀隔離級(jí)別相反，它允許讀取臟數(shù)據(jù)，即已經(jīng)被其他用戶修改但尚未提交的數(shù)據(jù)。它

57、是最低的事務(wù)隔離級(jí)別，僅可保證不讀取物理?yè)p壞的數(shù)據(jù)。(3) 可重復(fù)讀(REPEATABLE READ)。在此隔離級(jí)別下，用SELECT語句讀取的數(shù)據(jù)在整個(gè)語句執(zhí)行過程中不會(huì)被更改。此選項(xiàng)會(huì)影響系統(tǒng)的效能，非必要情況最好不用此隔離級(jí)別。(4) 可串行讀(SERIALIZABLE)。將共享鎖保持到事務(wù)完成，而不是不管事務(wù)是否完成都在不再需要所需的表或數(shù)據(jù)頁(yè)時(shí)就立即釋放共享鎖。它是最高的事務(wù)隔離級(jí)別，事務(wù)之間完全隔離。SQL Server支持以下4種隔離級(jí)別：10.6.2 隔離級(jí)別(1) 提交讀(READ COMMIT10.6.2 隔離級(jí)別使用SET TRANSACTION ISOLATION LEVEL語句設(shè)置會(huì)話的隔離級(jí)別 其語法格式如下：SET TRANSACTION ISOLATION LE