第8章 事務(wù)管理 數(shù)據(jù)庫原理.ppt

1、事務(wù)管理,事務(wù)(Transaction)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么全做，要么全不做，是一個(gè)不可分割的工作單位 事務(wù)和程序是兩個(gè)概念 在關(guān)系數(shù)據(jù)庫中，一個(gè)事務(wù)可以是一條SQL語句，一組SQL語句或整個(gè)程序 一個(gè)應(yīng)用程序通常包含多個(gè)事務(wù),SQL編程基礎(chǔ) 事務(wù)管理 存儲(chǔ)過程 觸發(fā)器,SQL編程基礎(chǔ),1、函數(shù) 字符串函數(shù) 日期和時(shí)間函數(shù) 數(shù)學(xué)函數(shù) 轉(zhuǎn)換函數(shù) 系統(tǒng)函數(shù) 用戶自定義函數(shù),2、流程控制語句,（1）ifelse語句 if | else | 可以多層嵌套,（2）beginend 語句 begin |n end （3）whilecontinue.break語句 while bre

2、ak|continue,goto label 程序中有一行語句以label:開頭 例 利用goto語句求1加到5的總和。 分析： （1）需要兩個(gè)變量存儲(chǔ)變量值和和值，用select語句賦初值分別為1和0 （2）goto執(zhí)行完一次以后轉(zhuǎn)到label_1處循環(huán)執(zhí)行 （3）經(jīng)過5次循環(huán)，求出結(jié)果,3、goto語句,declare count int,sum int /*聲明變量*/ select count=1,sum=0 /*變量賦初值*/ label_1: /*循環(huán)標(biāo)識(shí)位置*/ select sum=sum+count /*循環(huán)部分 select count=count+1 */ if coun

3、t=5 /*條件判斷語句*/ goto label_1 select sum,count /*查詢輸出結(jié)果*/,4、return語句,無條件終止查詢或存儲(chǔ)過程，位于return語句后的程序?qū)⒉粫?huì)被執(zhí)行。 return ,5、打印輸出語句,print | 例 利用print語句判斷變量值 declare sum int /*聲明變量*/ select sum=5 /*變量賦初始值*/ if sum=5 /*ifelse語句*/ print sum值不大于5 /*輸出結(jié)果語句*/ else print sum大于5,6、注釋語句,（1）整塊注釋 /*/ （2）從行的后部分注釋 -,事務(wù)管理,事務(wù)管

4、理語句： begin transaction /*事務(wù)開始*/ commit transaction /*提交事務(wù)*/ 事務(wù)只使用于數(shù)據(jù)修改語句 在提交事務(wù)之前可以取消事務(wù)并撤消對(duì)數(shù)據(jù)的修改，提交之后不能取消； rollback transaction savepoint_name savepoint_name：標(biāo)記事務(wù)回滾的保存點(diǎn),例 建立一個(gè)事務(wù)，將course表中cno字段為1的記錄的cname值更改為“大學(xué)英語” begin transaction update course /*更改表中數(shù)據(jù)*/ set cname=大學(xué)英語 where cno=1 commit transactio

5、n 事務(wù)執(zhí)行后，可以使用查詢語句查詢修改后的表 select * from course where cno=1,例 建立一個(gè)事務(wù)，用來更改course表中cno為1以及cno為2的記錄的cname字段的值，分別更改為“大學(xué)英語”和“線性代數(shù)”，并回滾到事務(wù)保存點(diǎn)，并查看事務(wù)的執(zhí)行結(jié)果。 （1）建立事務(wù) begin transaction save transaction A update course set cname=大學(xué)英語 where cno=1 save transaction B update course set cname=線性代數(shù) where cno=2 rollback

6、transaction B commit transaction,（2）查看事務(wù)執(zhí)行 select * from course where cno=1 select * from course where cno=2,存儲(chǔ)過程,SQL Server提供了一種方法，它可以將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，以實(shí)現(xiàn)某個(gè)任務(wù)，這種方法就是存儲(chǔ)過程。 在SQL Server中存儲(chǔ)過程分為兩類：即系統(tǒng)存儲(chǔ)過程和用戶自定義的存儲(chǔ)過程。 存儲(chǔ)過程和視圖的區(qū)別： 視圖是封裝好的select語句，存儲(chǔ)過程是sql語句。,創(chuàng)建存儲(chǔ)過程,在SQL Server中，可以使用三種方法創(chuàng)建存

7、儲(chǔ)過程 ： 使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程。 利用SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過程。 使用CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過程。,創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分：,所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。 被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲(chǔ)過程的語句。 返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。,1. 使用CREATE PROCEDURE命令創(chuàng)建存儲(chǔ)過程,創(chuàng)建存儲(chǔ)過程前，應(yīng)該考慮下列幾個(gè)事項(xiàng)： 不能將 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個(gè)批處理中。 創(chuàng)建存儲(chǔ)過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，該所有者可將此

8、權(quán)限授予其他用戶。 存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象，其名稱必須遵守標(biāo)識(shí)符規(guī)則。 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。 一個(gè)存儲(chǔ)過程的最大尺寸為128M。,使用CREATE PROCEDURE創(chuàng)建存儲(chǔ)過程的語法形式如下：,create procedure ;版本號(hào) =output with recompile|encryption| recompile,encryption for replication as ,用CREATE PROCEDURE創(chuàng)建存儲(chǔ)過程的語法參數(shù)的意義如下：,過程名：用于指定要?jiǎng)?chuàng)建的存儲(chǔ)過程的名稱。 版本號(hào)：該參數(shù)是可選的整數(shù)，它用來對(duì)同名的存儲(chǔ)過程分組，以便用一條 DROP PROC

9、EDURE 語句即可將同組的過程一起除去。 參數(shù)名：過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個(gè)或多個(gè)參數(shù)。 參數(shù)類型：用于指定參數(shù)的數(shù)據(jù)類型。 Default：用于指定參數(shù)的默認(rèn)值。 OUTPUT：表明該參數(shù)是一個(gè)返回參數(shù)。,RECOMPILE：重編譯，表明 SQL Server 不會(huì)保存該存儲(chǔ)過程的執(zhí)行計(jì)劃 。即要求每次執(zhí)行都要對(duì)過程重編譯和優(yōu)化，并創(chuàng)建新的查詢計(jì)劃。 ENCRYPTION ：表示 SQL Server 加密了 syscomments 表（系統(tǒng)表），該表的text字段是包含 CREATE PROCEDURE 語句的存儲(chǔ)過程文本。 FOR REPLI

10、CATION：用于指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程。 AS：用于指定該存儲(chǔ)過程要執(zhí)行的操作。 sql語句：是存儲(chǔ)過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。,運(yùn)行存儲(chǔ)過程,execute 參數(shù)名=output with recompile,例:創(chuàng)建存儲(chǔ)過程,1. 建立不帶參數(shù)的存儲(chǔ)過程 建立一個(gè)名為“全部學(xué)生”的存儲(chǔ)過程，用來查詢student表的所有記錄 create procedure 全部學(xué)生 as select * from student 執(zhí)行不帶參數(shù)的execute 語句 Use StuCouDb Go Execute 全部學(xué)生,2.建立帶參數(shù)的存

11、儲(chǔ)過程 (1) 建立一個(gè)名為”按系查詢”的存儲(chǔ)過程,查詢某個(gè)系的全體學(xué)生信息. create procedure 系別查詢 stu_dept varchar(10) as select * from student where dept=stu_dept 執(zhí)行帶參數(shù)的execute語句 execute 系別查詢 計(jì)算機(jī)系,sno sname ssex sage dept 98001 王芳 女21計(jì)算機(jī)系 98002 李兵 男20計(jì)算機(jī)系,(2) 建立一個(gè)名為”成績(jī)查詢”存儲(chǔ)過程,根據(jù)姓名和課程號(hào)查詢成績(jī). Create procedure 成績(jī)查詢 sname char(20),cno cha

12、r(4) As Select sname,cno,mark From student join grade on student.sno=grade.sno Where sname=sname and cno=cno 帶參數(shù)的execute語句 execute 成績(jī)查詢 sname,cno,3.帶輸出參數(shù)的存儲(chǔ)過程 create procedure mathadd m1 int,m2 int, result int output as select result=m1+m2 調(diào)用帶參數(shù)的存儲(chǔ)過程 Declare answer int Execute mathadd 8,10,answer ou

13、tput Select answer,1. 使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程,在企業(yè)管理器中，選擇工具菜單中的向?qū)нx項(xiàng)，選擇“創(chuàng)建存儲(chǔ)過程向?qū)А?,則出現(xiàn)歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框。根據(jù)圖提示可完成創(chuàng)建存儲(chǔ)過程。,新建SQL Server組,歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框,選擇數(shù)據(jù)庫對(duì)話框,選擇數(shù)據(jù)庫對(duì)象對(duì)話框,完成創(chuàng)建存儲(chǔ)過程向?qū)?duì)話框,編輯存儲(chǔ)過程屬性對(duì)話框,編輯存儲(chǔ)過程SQL對(duì)話框,2. 使用SQL Server 企業(yè)管理器創(chuàng)建存儲(chǔ)過程,（）在SQL Server企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，在彈出的快捷菜單中選擇“新建”選項(xiàng)，再選擇下一級(jí)

14、菜單中的“存儲(chǔ)過程”選項(xiàng)，如圖5-77所示；或者用右鍵單擊存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過程”選項(xiàng)，如圖5-78所示。均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程對(duì)話框。 （）在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的T_SQL語句，單擊“檢查語法”，則可以檢查語法是否正確；單擊“確定”按鈕，即可保存該存儲(chǔ)過程。如果要設(shè)置權(quán)限，單擊“權(quán)限”按鈕。,選擇新建存儲(chǔ)過程對(duì)話框（1）,選擇新建存儲(chǔ)過程對(duì)話框（2）,新建存儲(chǔ)過程對(duì)話框,設(shè)置權(quán)限對(duì)話框,查看和修改存儲(chǔ)過程,查看存儲(chǔ)過程 存儲(chǔ)過程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂檬褂闷髽I(yè)管理器或

15、系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程。,（1）使用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過程,在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，單擊存儲(chǔ)過程文件夾，此時(shí)在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲(chǔ)過程。用右鍵單擊要查看的存儲(chǔ)過程，從彈出的快捷菜單中選擇屬性選項(xiàng)，此時(shí)便可以看到存儲(chǔ)過程的源代碼。,（2）使用系統(tǒng)存儲(chǔ)過程來查看用戶創(chuàng)建的存儲(chǔ)過程,可供使用的系統(tǒng)存儲(chǔ)過程如下： (1) sp_help：用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型 sp_help objname= name 參數(shù)name為要查看的存儲(chǔ)過程的名稱。 (2) sp_helptext：用于顯示存儲(chǔ)過程的源代碼 sp

16、_helptext objname= name 參數(shù)name為要查看的存儲(chǔ)過程的名稱。,(3) sp_depends：用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫 對(duì)象 sp_depends objname=object 參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過程的名稱。,(4) sp_stored_procedures 用于返回當(dāng)前數(shù)據(jù)庫中的存儲(chǔ)過程列表,2. 修改存儲(chǔ)過程,使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行 CREATE PROCEDURE 語句創(chuàng)建的過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。其語法形式如下： ALTERPROCEDURE; =output WITH

17、RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTION FOR REPLICATION AS ,例 修改存儲(chǔ)過程”按系查詢”,用來統(tǒng)計(jì)某系的總?cè)藬?shù). alter procedure 系別查詢 stu_dept varchar(10) as select count(*) from student where dept=stu_dept 執(zhí)行語句查看修改結(jié)果,重命名和刪除存儲(chǔ)過程,1. 重命名存儲(chǔ)過程 修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存儲(chǔ)過程sp_rename，其語法形式如下： sp_rename 原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱 另外，通過企業(yè)管理器也可以修改存儲(chǔ)過程

18、的名稱。,2. 刪除存儲(chǔ)過程,刪除存儲(chǔ)過程可以使用DROP命令，DROP命令可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下： drop procedure 當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存儲(chǔ)過程。,觸發(fā)器,創(chuàng)建觸發(fā)器 查看、修改和刪除觸發(fā)器 觸發(fā)器的應(yīng)用,觸發(fā)器,觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它不同于前面介紹過的存儲(chǔ)過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過程可以通過存儲(chǔ)過程名稱而被直接調(diào)用。觸發(fā)器是一個(gè)功能強(qiáng)大的工具，它使每個(gè)站點(diǎn)可以在有數(shù)據(jù)修改時(shí)自動(dòng)強(qiáng)制執(zhí)行其業(yè)務(wù)規(guī)則。觸發(fā)器可以用于 SQL Server 約束、默認(rèn)值和規(guī)則的完整性檢查。,

19、觸發(fā)器主要優(yōu)點(diǎn)如下：,觸發(fā)器是自動(dòng)的：當(dāng)對(duì)表中的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序采取的操作）之后立即被激活。 觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改。 觸發(fā)器可以強(qiáng)制限制，這些限制比用 CHECK 約束所定義的更復(fù)雜。,創(chuàng)建觸發(fā)器,創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個(gè)問題： 創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。 觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則。 觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。 雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表。,創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定： 名稱； 在其上定義觸發(fā)器的表； 觸

20、發(fā)器將何時(shí)激發(fā)； 激活觸發(fā)器的數(shù)據(jù)修改語句。,1. 使用企業(yè)管理器創(chuàng)建觸發(fā)器,在企業(yè)管理器中，展開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，然后展開要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫，用右鍵單擊該表，從彈出的快捷菜單中選擇所有任務(wù)子菜單下的管理觸發(fā)器選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器屬性對(duì)話框。在該對(duì)話框中，在名稱文本框中選擇新建，然后在文本框中輸入觸發(fā)器文本。單擊“檢查語法”按鈕，則可以檢查語法是否正確。單擊“應(yīng)用”按鈕，則在名稱下拉列表中會(huì)有新創(chuàng)建的觸發(fā)器名稱。最后，單擊“確定”按鈕，即可關(guān)閉該對(duì)話框，成功創(chuàng)建觸發(fā)器。,2. 使用CREATE TRIGGER命令創(chuàng)建觸發(fā)器,其語法形式如下： CREATE TRIGGER

21、ON表名|視圖名 WITH ENCRYPTION FOR DELETE,INSERT,UPDATEWITH APPENDNOT FOR REPLICATIONAS 觸發(fā)器名 觸發(fā)器作為一種數(shù)據(jù)庫對(duì)象，在syscomment 表中存儲(chǔ)有完整的文本定義信息。,例子：,創(chuàng)建了一個(gè)觸發(fā)器，在 titles 表上創(chuàng)建一個(gè)插入、更新類型的觸發(fā)器riminder，其程序清單如下： USE pubs IF EXISTS(SELECT name FROM sysobjects WHERE name=reminder AND type=TR) DROP TRIGGER reminder GO CREATE TRI

22、GGER reminder ON titles FOR INSERT,UPDATE AS sql語句 GO,例 利用觸發(fā)器來保證學(xué)生選課系統(tǒng)StuCouDb數(shù)據(jù)庫中SC表sno字段的參照完整性. 分析: 進(jìn)行插入操作時(shí),觸發(fā)器要保證inserted表中的字段sno包含在student表中 create trigger grade_insert on grade for insert as if (select count(*) from student,inserted where student.sno=inserted.sno)=0 rollback transaction,例 利用出發(fā)器

23、來保證不能更新StuCouDb數(shù)據(jù)庫中student表的sname字段. create trigger stu_update on student for update if update(sname) begin raiserror(you can not modify this colomn,16,1) rollback transaction end,關(guān)于inserted表和deleted表,Inserted表和deleted表是觸發(fā)器專用的臨時(shí)虛擬表.由系統(tǒng)負(fù)責(zé)維護(hù),不允許用戶對(duì)這兩個(gè)表進(jìn)行直接的修改. Insertd表:存放insert或update語句執(zhí)行過程中,插入到觸發(fā)表中的新

24、數(shù)據(jù)行的副本.因此inserted 表中的行是和觸發(fā)表中的新數(shù)據(jù)行相同. Deleted表:存放delete 或update語句執(zhí)行過程中,從觸發(fā)表中刪除的舊數(shù)據(jù)行的副本.因此,deleted表和觸發(fā)表不會(huì)有相同的行. 觸發(fā)操作完成后,與觸發(fā)器相關(guān)的表被自動(dòng)刪除掉.,查看、修改和刪除觸發(fā)器,1. 查看觸發(fā)器 （）使用企業(yè)管理器查看觸發(fā)器信息 （）使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器,sp_help、sp_helptext和sp_depends具體用途和語法形式如下。,sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時(shí)間。 sp_help 觸發(fā)器名稱 sp_helptext：用于查看觸發(fā)器的正文信息 sp_helptext 觸發(fā)器名稱 sp_depends：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。 sp_depends 觸發(fā)器名稱 sp_depends 表名,2. 修改觸發(fā)器,（）使用企業(yè)管理器修改觸發(fā)器正文 在企業(yè)管理器中，展開指定的服務(wù)器和數(shù)據(jù)庫，選擇指定的數(shù)據(jù)庫和表，用右鍵單擊要修改的表，從彈出的快捷菜單中選擇所有任務(wù)子菜單下的管理觸發(fā)器選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器屬性對(duì)話框。在名稱選項(xiàng)框中選擇要修改