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

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

2、并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server提供支持。通過系統(tǒng)存儲過程，SQL Server中的許多管理性或信息性的活動(例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)都可以順利有效地完成。盡管系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對其進行調(diào)用(調(diào)用時，不必在存儲過程名前加上數(shù)據(jù)庫名)。當(dāng)創(chuàng)建一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會在新數(shù)據(jù)庫中被自動創(chuàng)建。10.1 存儲過程概述(2)用戶定義存儲過程是由用戶創(chuàng)建并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲過程。它處于用戶創(chuàng)建的數(shù)據(jù)庫中，存儲

3、過程名前沒有前綴sp_。10.1 存儲過程概述(3)臨時存儲過程臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加“#”或“#”前綴表示?！?”表示本地臨時存儲過程，“#”表示全局臨時存儲過程。使用臨時存儲過程必須創(chuàng)建本地連接，當(dāng)SQL Server關(guān)閉后，這些臨時存儲過程將自動被刪除。由于SQL Server支持重新使用執(zhí)行計劃，所以連接到SQL Server 2000的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲過程，而不使用臨時存儲過程。10.1 存儲過程概述(4)擴展存儲過程擴展存儲過程是SQL Server可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接

4、庫(DLL)。當(dāng)擴展存儲過程加載到SQL Server中，它的使用方法與系統(tǒng)存儲過程一樣。擴展存儲過程只能添加到master數(shù)據(jù)庫中，其前綴是xp_。10.1 存儲過程概述2. 存儲過程的功能特點SQL Server的存儲過程可實現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值。(2)包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其他過程。(3)為調(diào)用過程或批處理返回一個狀態(tài)值，以表示成功或失敗(及失敗原因)。10.1 存儲過程概述存儲過程具有以下優(yōu)點(1)模塊化編程。(2)快速執(zhí)行。 (3)減少網(wǎng)絡(luò)通信量。(4)提供安全機制。(5)保證操作一致性。10.2.1 創(chuàng)建存儲過

5、程10.2.2 執(zhí)行存儲過程10.2.3 修改存儲過程10.2.4 刪除存儲過程10.2.5 存儲過程參數(shù)與狀態(tài)值10.2 存儲過程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲過程1使用SQL Server管理平臺創(chuàng)建存儲過程 （1）打開SQL Server管理平臺，展開節(jié)點“對象資源管理器”“數(shù)據(jù)庫服務(wù)器”“可編程性”“存儲過程”，在窗口的右側(cè)顯示出當(dāng)前數(shù)據(jù)庫的所有存儲過程。單擊鼠標右鍵，在彈出的快捷菜單中選擇“新建存儲過程”命令 。10.2 存儲過程的創(chuàng)建與使用（2）在打開的SQL命令窗口中，系統(tǒng)給出了創(chuàng)建存儲過程命令的模板，如圖10-2所示。在模板中可以輸入創(chuàng)建存儲過程的Transact-SQL語

6、句后，單擊“執(zhí)行”按鈕即可創(chuàng)建存儲過程。 10.2.1 創(chuàng)建存儲過程（3）建立存儲過程的命令被成功執(zhí)行后，在“對象資源管理器”“數(shù)據(jù)庫服務(wù)器”“可編程性”“存儲過程”中可以看到新建立的存儲過程 10.2.1 創(chuàng)建存儲過程2. 使用CREATE PROCEDURE語句創(chuàng)建存儲過程使用CREATE PROCEDURE語句創(chuàng)建存儲過程應(yīng)該考慮以下幾個方面：(1)在一個批處理中，CREATE PROCEDURE語句不能與其他SQL語句合并在一起。(2)數(shù)據(jù)庫所有者具有默認的創(chuàng)建存儲過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。(3)存儲過程作為數(shù)據(jù)庫對象其命名必須符合標識符的命名規(guī)則。(4)只能在當(dāng)前數(shù)據(jù)

7、庫中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫的存儲過程。10.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程語句的語法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement ,.n 10.2.1 創(chuàng)建存儲過程例10-1 創(chuàng)建存儲過程，從表goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價。10

8、.2.1 創(chuàng)建存儲過程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification gcON g.classification_id = gc.classification_id存儲過程創(chuàng)建后，存儲過程的名稱存放在sysobject表中，文本存放在syscomments表中。10.2.2 執(zhí)行存儲過程執(zhí)行存儲過程的語法格式：EXECUTE return_status= procedure_name ;numb

9、er|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2 存儲過程的創(chuàng)建與使用例如，執(zhí)行例10-1的存儲過程goods_info在SQL查詢分析器中輸入命令：EXEC goods_info運行的結(jié)果：10.2.2 執(zhí)行存儲過程10.2 存儲過程的創(chuàng)建與使用10.2.3 修改存儲過程修改存儲過程可以通過SQL Server管理平臺和Transact-SQL語句實現(xiàn)。1使用SQL Server管理平臺修改存儲過程 10.2.3 修改存儲過程2. 使用ALTER PROCEDURE語句修

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

11、oyee_dep AS 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í)行存儲過程employee_dep，結(jié)果如圖 10.2.3 修改存儲過程(2) 查看employee_dep存儲過程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments

12、c ON o.id = c.idWHERE o.type = P AND = employee_depGO10.2.3 修改存儲過程(3)使用ALTER PROCEDURE語句對employee_dep過程進行修改，使其能夠顯示出所有男員工，并使employee_dep過程以加密方式存儲在表syscomments中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department

13、 d ON e.department_id=d.department_id WHERE sex=男 GO10.2.3 修改存儲過程執(zhí)行修改后的存儲過程employee_dep，結(jié)果如圖：10.2.3 修改存儲過程(4)從系統(tǒng)表sysobjects和syscomments提取修改后的存儲過程employee_dep的文本信息可以運行步驟（2）中的代碼，結(jié)果如圖 這是由于在ALTER PROCEDURE語句中使用WITH ENCRYPTION關(guān)鍵字對存儲過程employee_dep的文本進行了加密，其文本信息顯示為NULL。 10.2 存儲過程的創(chuàng)建與使用10.2.4 刪除存儲過程 存儲過程可以被

14、快速刪除和重建，因為它沒有存儲數(shù)據(jù)。1使用SQL Server管理平臺刪除存儲過程 （1）打開SQL Server管理平臺，展開節(jié)點“對象資源管理器”“數(shù)據(jù)庫服務(wù)器”“可編程性”“存儲過程”，選擇要刪除的存儲過程，單擊鼠標右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對象”對話框中單擊“確定”按鈕即可刪除存儲過程。 10.2.4 刪除存儲過程2. 使用DROP PROCEDURE刪除存儲過程DROP PROCEDURE的語法如下：DROP PROCEDURE procedure_name ,.n 例如刪除例10-2創(chuàng)建的存儲過程employee_dep：DROP PROCEDU

15、RE employee_depGO10.2 存儲過程的創(chuàng)建與使用10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲過程返回的狀態(tài)值對存儲過程進行管理。1. 參數(shù)存儲過程的參數(shù)在創(chuàng)建過程時聲明。SQL Server支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。10.2.5 存儲過程參數(shù)與狀態(tài)值(1)輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。要定義存儲過程的輸入?yún)?shù)，必須在CREATE PROCEDURE語句中聲明一個或多個變量及類型。10.2.5 存儲過程參數(shù)與狀態(tài)值例10-3 創(chuàng)建帶參數(shù)的存儲過程，從表employe

16、e、sell_order、goods、goods_classification的連接中返回輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。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

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

18、可以由位置標識，也可以由名字標識。例如，定義一個具有3個參數(shù)的存儲過程：CREATE PROC myproc val1 int, val2 int, val3 intAS .參數(shù)以位置傳遞：EXEC myproc 10,20,15參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：EXEC myproc val2=20,val1=10,val3=15按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。10.2.5 存儲過程參數(shù)與狀態(tài)值(2)輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標變量傳回調(diào)用程序。OUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程中的參數(shù)。為了使用

19、輸出參數(shù)，在CREATE PROCEDURE和EXECUTE語句中都必須使用OUTPUT關(guān)鍵字。10.2.5 存儲過程參數(shù)與狀態(tài)值例10-4 創(chuàng)建存儲過程price_goods，通過輸入?yún)?shù)在goods表中查找商品，以輸出參數(shù)獲取商品單價。CREATE PROC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name10.2.5 存儲過程參數(shù)與狀態(tài)值執(zhí)行price_goods存儲過程的代

20、碼如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT price運行結(jié)果是商品名為Canon LBP2900的商品單價：1380.0EXECUTE語句還需要關(guān)鍵字OUTPUT以允許參數(shù)值返回給變量。10.2.5 存儲過程參數(shù)與狀態(tài)值(1)用RETURN語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲過程沒有顯式設(shè)置返回代碼的值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或

21、小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。2. 返回存儲過程的狀態(tài)10.2.5 存儲過程參數(shù)與狀態(tài)值例10-5 創(chuàng)建存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值15表示用戶沒有提供參數(shù)；值-l01表示沒有輸入商品類別；值0表示過程運行沒有出錯。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) RET

22、URN -101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND gc.classification_name=cl_nameRETURN 010.2.5 存儲過程參數(shù)與狀態(tài)值在執(zhí)行過程時，要正確接收返回的狀態(tài)值，必須使用以下語句；EXECUTE status_var=procedure_name(2)捕獲返回狀態(tài)值10.2.5 存儲過程參數(shù)與狀態(tài)值DECLARE return_status intEXEC return_stat

23、us=cl_goods 筆記本計算機IF return_status=15 SELECT 語法錯誤ELSE IF return_status=-101 SELECT 沒有找到該商品類別執(zhí)行時，將對不同的輸入值返回不同的狀態(tài)值及處理結(jié)果。例10-5的存儲過程cl_goods執(zhí)行時使用以下語句：觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調(diào)用。當(dāng)對某一表進行UPDATE、INSERT、DELETE操作時，SQL Server就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是能

24、夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。 10.3 觸發(fā)器概述10.4.1 創(chuàng)建觸發(fā)器10.4.2 刪除觸發(fā)器10.4.3 修改觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用10.4 觸發(fā)器的創(chuàng)建與使用1使用SQL Server管理平臺創(chuàng)建觸發(fā)器 10.4.1 創(chuàng)建觸發(fā)器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 | AFTER | INSTEAD OF INSERT , U

25、PDATE , DELETE AS sql_statement . n 10.4.1 創(chuàng)建觸發(fā)器例10-6 在employee表上創(chuàng)建一個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)+個員工被刪除SELECT msgRETURN10.4.1 創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器tr_employee觸發(fā)器不能通過名字來執(zhí)行，而是在相應(yīng)的SQL語句被執(zhí)

26、行時自動觸發(fā)的。例如執(zhí)行以下DELETE語句：DELETE FROM employeeWHERE employee_name = 張三該語句要刪除員工姓名為“張三”記錄，由此激活了表employee 的DELETE類型的觸發(fā)器tr_employee，系統(tǒng)執(zhí)行tr_employee觸發(fā)器中AS之后的語句，并顯示以下信息：1個員工被刪除10.4.1 創(chuàng)建觸發(fā)器在觸發(fā)器的執(zhí)行過程中，SQL Server建立和管理兩個臨時的虛擬表：Deleted表和Inserted表。這兩個表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。在執(zhí)行INSERT或UPDATE語句之后所有被添加或被更新的記錄都會存儲在In

27、serted表中。在執(zhí)行DELETE或UPDATE語句時，從觸發(fā)程序表中被刪除的行會發(fā)送到Deleted表。對于更新操作，SQL Server先將要進行修改的記錄存儲到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。3. Deleted表和Inserted表10.4.1 創(chuàng)建觸發(fā)器例10-7 為表customer創(chuàng)建一個名為test_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時，激活該觸發(fā)器。創(chuàng)建test_tr觸發(fā)器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM i

28、nserted SELECT * FROM deletedcustomer表執(zhí)行以下插入操作：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,張三,1234567)10.4.1 創(chuàng)建觸發(fā)器INSERT操作激活觸發(fā)器test_tr，輸出如圖10-19所示的表格。10.4.2 修改觸發(fā)器通過SQL Server管理平臺、系統(tǒng)存儲過程或Transact_SQL語句，可以修改觸發(fā)器的名字和正文。 1. 使用sp_rename系統(tǒng)存儲過程修改觸發(fā)器的名字：sp_rename oldname, newname10.4

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

30、ALTER TRIGGER語句修改觸發(fā)器修改觸發(fā)器的語法如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 10.4.3 修改觸發(fā)器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個員工數(shù)據(jù)被插入SELECT msgRETURN對emplo

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

32、擊鼠標右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對象”對話框中單擊“確定”按鈕即可刪除觸發(fā)器。 10.4.2 刪除觸發(fā)器2. 使用DROP TRIGGER刪除指定觸發(fā)器刪除觸發(fā)器語句的語法格式如下：DROP TRIGGER trigger_name , .n 例如，刪除例10-6的觸發(fā)器tr_employee：DROP TRIGGER tr_employee10.5.1 事務(wù)概述10.5.2 事務(wù)管理10.5 事務(wù)處理10.5 事務(wù)處理事務(wù)(Transaction)是SQL Server中的一個邏輯工作單元，該單元將被作為一個整體進行處理。事務(wù)保證連續(xù)多個操作必須全部執(zhí)行成

33、功，否則必須立即回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要不全部將數(shù)據(jù)所要執(zhí)行的操作完成，要不全部數(shù)據(jù)都不修改。10.5.1 事務(wù)概述10.5.1 事務(wù)概述例如，企業(yè)取消了倉儲部，需要將“倉儲部”從department表中刪除，而employee表中的部門編號與倉儲部相對應(yīng)的員工也應(yīng)刪除。假設(shè)倉儲部編號為D004，第一條DELETE語句修改department表為：DELETE FROM department WHERE department_id = D004第二條DELETE語句修改employee表為：DELETE FROM employee WHERE department_id

34、= D004因此，必須保證這兩條DELETE語句同時執(zhí)行，或都不執(zhí)行。這時可以使用數(shù)據(jù)庫中的事務(wù)(Transaction)技術(shù)來實現(xiàn)。1事務(wù)的由來2事務(wù)屬性由于事務(wù)作為一個邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯誤時，將取消事務(wù)所做的修改。一個邏輯單元必須具有4個屬性：原子性(Atomicity)、一致性(Consistency)隔離性(Isolation)持久性(Durability)，這些屬性稱為ACID。10.5.1 事務(wù)概述3事務(wù)模式SQL Server以3種事務(wù)模式管理事務(wù)。(1) 自動提交事務(wù)模式：每條單獨的語句都是一個事務(wù)。在此模式下，每條Transact-SQL語句在成功執(zhí)行完成后，都被

35、自動提交，如果遇到錯誤，則自動回滾該語句。該模式為系統(tǒng)默認的事務(wù)管理模式。(2) 顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動和結(jié)束。事務(wù)以BEGIN TRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束。(3) 隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動啟動。隱性事務(wù)不需要使用BEGIN TRANSACTION語句標識事務(wù)的開始，但需要以COMMIT或ROLLBACK語句來提交或回滾事務(wù)。10.5.1 事務(wù)概述10.5 事務(wù)處理1啟動和結(jié)束事務(wù)啟動事務(wù)語句的語法格式如下：BEGIN TRANSACTION transaction_name | tran_n

36、ame_variable WITH MARK description 結(jié)束事務(wù)語句的語法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事務(wù)管理10.5.2 事務(wù)管理例10-8 建立一個顯式事務(wù)以顯示Sales數(shù)據(jù)庫的employee表的數(shù)據(jù)。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTION本例創(chuàng)建的事務(wù)以BEGIN TRANSACTION語句開始，以COMMIT TRANSACTION語句結(jié)束。10.5.2 事務(wù)管理DECLARE t

37、ransaction_name varchar(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 建立一個顯式命名事務(wù)以刪除department表的“倉儲部”記錄行。10.5.2 事務(wù)管理CREATE TABLE imp_tran( num ch

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

39、* FROM imp_tranCOMMIT TRANSACTION -提交第二個隱性事務(wù)GOSET IMPLICIT_TRANSACTIONS OFF -關(guān)閉隱性事務(wù)模式GO例10-10 隱性事務(wù)處理過程。10.5.2 事務(wù)管理當(dāng)事務(wù)事務(wù)回滾使用ROLLBACK TRANSACTION語句實現(xiàn)，其語法格式如下：ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 2事務(wù)回滾10.5.2 事務(wù)管理事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指定位置，則需要在事務(wù)中設(shè)

40、定保存點(SavePoint)。所謂保存點是指定其所在位置之前的事務(wù)語句，不能回滾的語句即此語句前面的操作被視為有效。其語法格式如下：SAVE TRANSACTION savepoint_name | savepoint_variable10.5.2 事務(wù)管理例10-11 使用ROLLBACK TRANSACTION語句標識事務(wù)結(jié)束。BEGIN TRANSACTION UPDATE goods SET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,o

41、rder_num,order_date) VALUES(S00005,G00006,5,getdate()ROLLBACK TRANSACTION10.5.2 事務(wù)管理BEGIN TRANSACTION my_transaction_delete DELETE FROM department WHERE department_id=D005 SAVE TRANSACTION after_delete -設(shè)置保存點 UPDATE employee SET department_id=D001 WHERE department_id=D005 IF (error=0 OR rowcount=0)

42、BEGIN ROLLBACK TRANSACTION after_delete - 如果出錯回滾到保存點after_delete COMMIT TRANSACTION my_transaction_delete END ELSE COMMIT TRANSACTION my_transaction_deleteGO例10-12 刪除倉儲部，再將倉儲部的職工劃分到總經(jīng)理辦。10.5.2 事務(wù)管理CREATE TRIGGER trig_uptab ON goodsFOR UPDATEASSAVE TRANSACTION tran_uptabINSERT INTO newgoods SELECT *

43、FROM insertedIF (error0)BEGIN ROLLBACK TRANSACTION tran_uptabEND例10-13 定義為表goods觸發(fā)器trig_uptab，如果goods表更新數(shù)據(jù)，則把新數(shù)據(jù)復(fù)制到表newgoods中，若出錯，則取消復(fù)制操作。10.5.2 事務(wù)管理和BEGINEND語句類似，BEGIN TRANSACTION和COMMIT TRANSACTION語句也可以進行嵌套，即事務(wù)可以嵌套執(zhí)行。3事務(wù)嵌套10.5.2 事務(wù)管理CREATE TABLE employee_tran( num char(2) NOT NULL, cname char(6) N

44、OT NULL)GO BEGIN TRANSACTION Tran1 -TRANCOUNT為1 INSERT INTO employee_tran VALUES (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 -TRANC

45、OUNT為2 PRINT TRANCOUNT COMMIT TRANSACTION Tran2 -TRANCOUNT為1 PRINT TRANCOUNTCOMMIT TRANSACTION Tran1 -TRANCOUNT為0PRINT TRANCOUNT例10-14 提交事務(wù)。運行結(jié)果：321010.6.1 鎖模式10.6.2 隔離級別10.6.3 查看和終止鎖10.6.4 死鎖及其防止10.6 SQL Server的鎖機制10.6 SQL Server的鎖機制鎖(Lock)作為一種安全機制，用于控制多個用戶的并發(fā)操作，以防止用戶讀取下在由其他用戶更改的數(shù)據(jù)或者多個用戶同時修改同一數(shù)據(jù)，從而

46、確保事務(wù)完整性和數(shù)據(jù)庫一致性。10.6.1 鎖模式10.6.1 鎖模式當(dāng)對一個數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問限制，稱對此數(shù)據(jù)源進行了“鎖定”。SQL Server有多種粒度鎖，允許一個事務(wù)鎖定不同類型的資源： 數(shù)據(jù)行(Row)：數(shù)據(jù)頁中的單行數(shù)據(jù)。 索引行(Key)：索引頁中的單行數(shù)據(jù)，即索引的鍵值。 頁(Page)：頁是SQL Server存取數(shù)據(jù)的基本單位，其大小為8KB。 擴展盤區(qū)(Extent)：一個盤區(qū)由8個連續(xù)的頁組成。 表(Table)。 數(shù)據(jù)庫(Database)。 允許一個事務(wù)鎖定的資源類型10.6.1 鎖模式確定并發(fā)事務(wù)訪問資源方式的鎖模式:(1) 共享鎖(Shar

47、ed Lock)。(2) 排它鎖(Exclusive Lock)。(3) 更新鎖(Update Lock)。從程序員的角度，鎖可以分為以下兩種類型： (1) 樂觀鎖(Optimistic Lock)。樂觀鎖假定在處理數(shù)據(jù)時，不需要在應(yīng)用程序的代碼中做任何事情就可以直接在記錄上加鎖，即完全依靠數(shù)據(jù)庫來管理鎖的工作。一般情況下，當(dāng)執(zhí)行事務(wù)處理時，SQL Server會自動對事務(wù)處理范圍內(nèi)更新到的表做鎖定。(2) 悲觀鎖(Pessimistic Lock)。悲觀鎖需要程序員直接管理數(shù)據(jù)或?qū)ο笊系募渔i處理，并負責(zé)獲取、共享和放棄正在使用的數(shù)據(jù)上的任何鎖。10.6.1 鎖模式10.6 SQL Serve

48、r的鎖機制隔離(Isolation)是計算機安全技術(shù)中的概念，其本質(zhì)上是一種封鎖機制。它是指自動數(shù)據(jù)處理系統(tǒng)中的用戶和資源的相關(guān)牽制關(guān)系，也就是用戶和進程彼此分開，且和操作系統(tǒng)的保護控制也分開來。事務(wù)準備接受不一致數(shù)據(jù)的級別稱為隔離級別(Isolation Level)。10.6.2 隔離級別10.6.2 隔離級別較低的隔離級別可以增加并發(fā)，但代價是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負面影響。應(yīng)用程序要求的隔離級別確定了SQL Server使用的鎖定行為。隔離級別是一個事務(wù)必須與其他事務(wù)進行隔離的程度。10.6.2 隔離級別(1) 提交讀(READ C

49、OMMITTED)。它是SQL Server的默認級別。在此隔離級別下，SELECT語句不會也不能返回尚未提交(Committed)的數(shù)據(jù)(即臟數(shù)據(jù))。(2) 未提交讀(READ UNCOMMITTED)。與提交讀隔離級別相反，它允許讀取臟數(shù)據(jù)，即已經(jīng)被其他用戶修改但尚未提交的數(shù)據(jù)。它是最低的事務(wù)隔離級別，僅可保證不讀取物理損壞的數(shù)據(jù)。(3) 可重復(fù)讀(REPEATABLE READ)。在此隔離級別下，用SELECT語句讀取的數(shù)據(jù)在整個語句執(zhí)行過程中不會被更改。此選項會影響系統(tǒng)的效能，非必要情況最好不用此隔離級別。(4) 可串行讀(SERIALIZABLE)。將共享鎖保持到事務(wù)完成，而不是不管事務(wù)是否完成都在不再需要所需的表或數(shù)據(jù)頁時就立即釋放共享鎖。它是最高的事務(wù)隔離級別，事務(wù)之間完全隔離。SQL Server支持以下4種隔離級別：10.6.2 隔離級別使用SET TRANSACTION ISOLATION LEVEL語句設(shè)置會話的隔離級別 其語法格式如下：SET TRANSACTION ISOLATI