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

1、事務(wù)管理,事務(wù)(Transaction)是用戶定義的一個數(shù)據(jù)庫操作序列，這些操作要么全做，要么全不做，是一個不可分割的工作單位 事務(wù)和程序是兩個概念 在關(guān)系數(shù)據(jù)庫中，一個事務(wù)可以是一條SQL語句，一組SQL語句或整個程序 一個應(yīng)用程序通常包含多個事務(wù),SQL編程基礎(chǔ) 事務(wù)管理 存儲過程 觸發(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）需要兩個變量存儲變量值和和值，用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)識位置*/ select sum=sum+count /*循環(huán)部分 select count=count+1 */ if coun

3、t=5 /*條件判斷語句*/ goto label_1 select sum,count /*查詢輸出結(jié)果*/,4、return語句,無條件終止查詢或存儲過程，位于return語句后的程序?qū)⒉粫粓?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ù)并撤消對數(shù)據(jù)的修改，提交之后不能取消； rollback transaction savepoint_name savepoint_name：標(biāo)記事務(wù)回滾的保存點(diǎn),例 建立一個事務(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,例 建立一個事務(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,存儲過程,SQL Server提供了一種方法，它可以將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，以實(shí)現(xiàn)某個任務(wù)，這種方法就是存儲過程。 在SQL Server中存儲過程分為兩類：即系統(tǒng)存儲過程和用戶自定義的存儲過程。 存儲過程和視圖的區(qū)別： 視圖是封裝好的select語句，存儲過程是sql語句。,創(chuàng)建存儲過程,在SQL Server中，可以使用三種方法創(chuàng)建存

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

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

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

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

11、儲過程 (1) 建立一個名為”按系查詢”的存儲過程,查詢某個系的全體學(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) 建立一個名為”成績查詢”存儲過程,根據(jù)姓名和課程號查詢成績. Create procedure 成績查詢 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 成績查詢 sname,cno,3.帶輸出參數(shù)的存儲過程 create procedure mathadd m1 int,m2 int, result int output as select result=m1+m2 調(diào)用帶參數(shù)的存儲過程 Declare answer int Execute mathadd 8,10,answer ou

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

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

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

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

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

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

19、觸發(fā)器主要優(yōu)點(diǎn)如下：,觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序采取的操作）之后立即被激活。 觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改。 觸發(fā)器可以強(qiáng)制限制，這些限制比用 CHECK 約束所定義的更復(fù)雜。,創(chuàng)建觸發(fā)器,創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個問題： 創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。 觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。 觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。 雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表。,創(chuàng)建一個觸發(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)，則會出現(xiàn)觸發(fā)器屬性對話框。在該對話框中，在名稱文本框中選擇新建，然后在文本框中輸入觸發(fā)器文本。單擊“檢查語法”按鈕，則可以檢查語法是否正確。單擊“應(yīng)用”按鈕，則在名稱下拉列表中會有新創(chuàng)建的觸發(fā)器名稱。最后，單擊“確定”按鈕，即可關(guān)閉該對話框，成功創(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ù)庫對象，在syscomment 表中存儲有完整的文本定義信息。,例子：,創(chuàng)建了一個觸發(fā)器，在 titles 表上創(chuàng)建一個插入、更新類型的觸發(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ù),不允許用戶對這兩個表進(jìn)行直接的修改. Insertd表:存放insert或update語句執(zhí)行過程中,插入到觸發(fā)表中的新

24、數(shù)據(jù)行的副本.因此inserted 表中的行是和觸發(fā)表中的新數(shù)據(jù)行相同. Deleted表:存放delete 或update語句執(zhí)行過程中,從觸發(fā)表中刪除的舊數(shù)據(jù)行的副本.因此,deleted表和觸發(fā)表不會有相同的行. 觸發(fā)操作完成后,與觸發(fā)器相關(guān)的表被自動刪除掉.,查看、修改和刪除觸發(fā)器,1. 查看觸發(fā)器 （）使用企業(yè)管理器查看觸發(fā)器信息 （）使用系統(tǒng)存儲過程查看觸發(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)，則會出現(xiàn)觸發(fā)器屬性對話框。在名稱選項(xiàng)框中選擇要修改