數(shù)據(jù)庫(kù)原理及應(yīng)用存儲(chǔ)過(guò)程和觸發(fā)器

數(shù)據(jù)庫(kù)原理及應(yīng)用AnIntroductiontoDatabaseSystem數(shù)據(jù)庫(kù)系統(tǒng)概論AnIntroductiontoDatabaseSystem第9章存儲(chǔ)過(guò)程和觸發(fā)器

教學(xué)目標(biāo)：

掌握存儲(chǔ)過(guò)程和觸發(fā)器的基本概念，學(xué)會(huì)編寫(xiě)簡(jiǎn)單的存儲(chǔ)過(guò)程和觸發(fā)器，對(duì)存儲(chǔ)過(guò)程和觸發(fā)器的實(shí)際應(yīng)用有較好的理解。第9章存儲(chǔ)過(guò)程和觸發(fā)器9.1存儲(chǔ)過(guò)程9.2觸發(fā)器9.1存儲(chǔ)過(guò)程9.1.1存儲(chǔ)過(guò)程的基本知識(shí)9.1.2創(chuàng)建用戶存儲(chǔ)過(guò)程9.1.3存儲(chǔ)過(guò)程的參數(shù)9.1.1存儲(chǔ)過(guò)程的基本知識(shí)存儲(chǔ)過(guò)程（StoredProcedure）是一組編譯好存儲(chǔ)在服務(wù)器上的完成特定功能T-SQL代碼，是某數(shù)據(jù)庫(kù)的對(duì)象?？蛻舳藨?yīng)用程序可以通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行存儲(chǔ)過(guò)程。Sp（x,y）客戶：sp（1，2）9.1.1存儲(chǔ)過(guò)程的基本知識(shí)

使用存儲(chǔ)過(guò)程而不使用存儲(chǔ)在客戶端計(jì)算機(jī)本地的T-SQL程序的優(yōu)點(diǎn)包括：允許標(biāo)準(zhǔn)組件式編程，增強(qiáng)重用性和共享性能夠?qū)崿F(xiàn)較快的執(zhí)行速度能夠減少網(wǎng)絡(luò)流量可被作為一種安全機(jī)制來(lái)充分利用9.1.1存儲(chǔ)過(guò)程的基本知識(shí)在SQLServer2005中存儲(chǔ)過(guò)程分為5類(lèi)：系統(tǒng)：系統(tǒng)提供的存儲(chǔ)過(guò)程，sp_*，例如：sp_rename擴(kuò)展：SQLServer環(huán)境之外的動(dòng)態(tài)鏈接庫(kù)DLL，xp_遠(yuǎn)程：遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程用戶：創(chuàng)建在用戶數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程臨時(shí)：屬于用戶存儲(chǔ)過(guò)程，#開(kāi)頭（局部：一個(gè)用戶會(huì)話）,##（全局：所有用戶會(huì)話）9.1.2創(chuàng)建用戶存儲(chǔ)過(guò)程格式：CREATEPROC過(guò)程名@形參名類(lèi)型@變參名類(lèi)型OUTPUTASSQL語(yǔ)句9.1.2創(chuàng)建用戶存儲(chǔ)過(guò)程例：創(chuàng)建一個(gè)多表查詢(xún)的存儲(chǔ)過(guò)程。USELibraryGOCREATEPROCEDUREborrowed_book1ASSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMreaderrINNERJOINborrowbONr.RID=b.RIDINNERJOINbookkONb.BID=k.BIDWHERERname='程鵬'執(zhí)行存儲(chǔ)過(guò)程：borrowed_book1或EXECborrowed_book19.1.3存儲(chǔ)過(guò)程的參數(shù)例：輸入?yún)?shù)為某人的名字。USELibraryGOCREATEPROCEDUREborrowed_book2@namevarchar(10)--形式參數(shù)AsSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMreaderrINNERJOINborrowbONr.RID=b.RIDINNERJOINbookkONb.BID=k.BIDWHERERname=@nameGO直接傳值：EXECborrowed_book2'程鵬'--實(shí)參表變量傳值：DECLARE@temp1char(20)SET@temp1='楊樹(shù)華'EXECborrowed_book2@temp1--實(shí)參表9.1.3存儲(chǔ)過(guò)程的參數(shù)例：使用默認(rèn)參數(shù)USELibraryGOCREATEPROCEDUREborrowed_book3@namevarchar(10)=NULL--默認(rèn)參數(shù)ASIF@nameISNULLSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMreaderrINNERJOINborrowbONr.RID=b.RIDINNERJOINbookkONb.BID=k.BIDELSESELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMreaderrINNERJOINborrowbONr.RID=b.RIDINNERJOINbookkONb.BID=k.BIDWHERERname=@nameGO執(zhí)行存儲(chǔ)過(guò)程：EXECborrowed_book39.1.3存儲(chǔ)過(guò)程的參數(shù)例：利用輸出參數(shù)計(jì)算階乘。USELibraryIFEXISTS(SELECTnameFROMsysobjectsWHEREname='factorial'ANDtype='P')DROPPROCEDUREfactorialGOCREATEPROCEDUREfactorial@infloat,--輸入形式參數(shù)

@outfloatOUTPUT--輸出形式參數(shù)ASDECLARE@iintDECLARE@sfloatSET@i=1SET@s=1WHILE@i<=@inBEGINSET@s=@s*@iSET@i=@i+1ENDSET@out=@s--給輸出參數(shù)賦值調(diào)用存儲(chǔ)過(guò)程：DECLARE@oufloatEXECfactorial5,@ouOUT--實(shí)參表PRINT@ou9.2觸發(fā)器9.2.1觸發(fā)器的基本知識(shí)9.2.2創(chuàng)建DML觸發(fā)器9.2.3創(chuàng)建DDL觸發(fā)器9.2.4修改觸發(fā)器9.2.5刪除觸發(fā)器9.2.6查看觸發(fā)器9.2.1觸發(fā)器的基本知識(shí)觸發(fā)器是特殊的存儲(chǔ)過(guò)程，基于一個(gè)表創(chuàng)建，主要作用就是實(shí)現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生變化（update,insert,delete）后，自動(dòng)運(yùn)行以保證數(shù)據(jù)的完整性和正確性。通俗的說(shuō)：通過(guò)一個(gè)動(dòng)作（update,insert,delete）調(diào)用一個(gè)存儲(chǔ)過(guò)程（觸發(fā)器）。9.2.1觸發(fā)器的基本知識(shí)在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言（DML）事件時(shí)將啟用。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語(yǔ)句、UPDATE語(yǔ)句或DELETE語(yǔ)句。DML觸發(fā)器可以查詢(xún)其他表，還可以包含復(fù)雜的T-SQL語(yǔ)句。系統(tǒng)將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待，如果檢測(cè)到錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生變化（update,insert,delete）后，自動(dòng)運(yùn)行以保證數(shù)據(jù)的完整性和正確性。通俗的說(shuō)：通過(guò)一個(gè)動(dòng)作（update,insert,delete）調(diào)用一個(gè)存儲(chǔ)過(guò)程（觸發(fā)器）。9.2.1觸發(fā)器的基本知識(shí)SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言（DDL）事件時(shí)將調(diào)用這些觸發(fā)器。但與DML觸發(fā)器不同的是，它們不會(huì)為響應(yīng)針對(duì)表或視圖的UPDATE、INSERT或DELETE語(yǔ)句而激發(fā)，相反，它們會(huì)為響應(yīng)多種數(shù)據(jù)定義語(yǔ)言（DDL）語(yǔ)句而激發(fā)。這些語(yǔ)句主要是以CREATE、ALTER和DROP開(kāi)頭的語(yǔ)句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。9.2.2創(chuàng)建DML觸發(fā)器語(yǔ)法格式：CREATETRIGGER觸發(fā)器ON表名FOR[update,insert,delete]ASSQL語(yǔ)句9.2.2創(chuàng)建DML觸發(fā)器例：創(chuàng)建基于表reader，DELETE操作的觸發(fā)器。USELibraryGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reader_d'ANDtype='TR')DROPTRIGGERreader_d--如果已經(jīng)存在觸發(fā)器reader_d則刪除GOCREATETRIGGERreader_d--創(chuàng)建觸發(fā)器ONreader--基于表FORDELETE--刪除事件ASPRINT'數(shù)據(jù)被刪除!'--執(zhí)行顯示輸出GO應(yīng)用：USELibraryGODELETEreaderwhereRname='aaa'9.2.2創(chuàng)建DML觸發(fā)器例7：在表borrow中添加借閱信息記錄時(shí)，得到該書(shū)的應(yīng)還日期。說(shuō)明：在表borrow中增加一個(gè)應(yīng)還日期SReturnDate。USELibraryIFEXISTS(SELECTnameFROMsysobjectsWHEREname='T_return_date'ANDtype='TR')DROPTRIGGERT_return_dateCREATETRIGGERT_return_date--創(chuàng)建觸發(fā)器ONBorrow--基于表borrowAfterINSERT--插入操作AS--查詢(xún)插入記錄INSERTED中讀者的類(lèi)型DECLARE@typeint,@dzbhchar(10),@tsbhchar(15)SET@dzbh=(SELECTRIDFROMinserted)SET@tsbh=(SELECTBIDFROMinserted)SELECT@type=TypeIDFROMreaderWHERERID=(SELECTRIDFROMinserted)--副本/*把Borrow表中的應(yīng)還日期改為當(dāng)前日期加上各類(lèi)讀者的借閱期限*/UPDATEBorrowSETSReturnDate=getdate()+CASEWHEN@type=1THEN90WHEN@type=2THEN60WHEN@type=3THEN30ENDWHERERID=@dzbhandBID=@tsbh

應(yīng)用：USELibraryINSERTINTOborrow(RID,BID)values('2000186010','TP85-08')9.2.2創(chuàng)建DML觸發(fā)器例：在數(shù)據(jù)庫(kù)Library中，當(dāng)讀者還書(shū)時(shí)，實(shí)際上要修改表brorrow中相應(yīng)記錄還期列的值，請(qǐng)計(jì)算出是否過(guò)期。USELibraryIFEXISTS(SELECTnameFROMsysobjectsWHEREname='T_fine_js'ANDtype='TR')DROPTRIGGERT_fine_jsGOCREATETRIGGERT_fine_jsONborrowAfterUPDATEASDECLARE@daysint,@dzbhchar(10),@tsbhchar(15)SET@dzbh=(selectRIDfrominserted)SET@tsbh=(selectBIDfrominserted)SELECT@days=DATEDIFF(day,ReturnDate,SReturnDate)--DATEDIFF函數(shù)返回兩個(gè)日期之差，單位為DAYFROMborrowWHERERID=@dzbhandBID=@tsbhIF@days>0PRINT'沒(méi)有過(guò)期！'ELSEPRINT'過(guò)期'+convert(char(6),@days)+'天'GO應(yīng)用：USELibraryUPDATEborrowSETReturnDate='2007-12-12'WHERERID='2000186010‘a(chǎn)ndBID='TP85-08'GO9.2.2創(chuàng)建DML觸發(fā)器例：對(duì)Library庫(kù)中Reader表的DELETE操作定義觸發(fā)器。USELibraryGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reader_d'ANDtype='TR')DROPTRIGGERreader_dGOCREATETRIGGERreader_dONReaderFORDELETEASDECLARE@data_yjintSELECT@data_yj=LendnumFROMdeletedIF@data_yj>0BEGINPRINT'該讀者不能刪除！還有'+convert(char(2),@data_yj)+'本書(shū)沒(méi)還。

ROLLBACKENDELSEPRINT'該讀者已被刪除！'GO應(yīng)用：USELibraryGODELETEReaderWHERERID='2005216119'

9.2.3創(chuàng)建DDL觸發(fā)器語(yǔ)法形式：CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}[WITH<ddl_trigger_option>[,...n]]{FOR|AFTER}{event_type|event_group}[,...n]AS{sql_statement[;][...n]|EXTERNALNAME<