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

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

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

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

使用存儲過程而不使用存儲在客戶端計算機(jī)本地的T-SQL程序的優(yōu)點(diǎn)包括：允許標(biāo)準(zhǔn)組件式編程，增強(qiáng)重用性和共享性能夠?qū)崿F(xiàn)較快的執(zhí)行速度能夠減少網(wǎng)絡(luò)流量可被作為一種安全機(jī)制來充分利用9.1.1存儲過程的基本知識在SQLServer2005中存儲過程分為5類：系統(tǒng)：系統(tǒng)提供的存儲過程，sp_*，例如：sp_rename擴(kuò)展：SQLServer環(huán)境之外的動態(tài)鏈接庫DLL，xp_遠(yuǎn)程：遠(yuǎn)程服務(wù)器上的存儲過程用戶：創(chuàng)建在用戶數(shù)據(jù)庫中的存儲過程臨時：屬于用戶存儲過程，#開頭（局部：一個用戶會話）,##（全局：所有用戶會話）9.1.2創(chuàng)建用戶存儲過程格式：CREATEPROC過程名@形參名類型@變參名類型OUTPUTASSQL語句9.1.2創(chuàng)建用戶存儲過程例：創(chuàng)建一個多表查詢的存儲過程。USELibraryGOCREATEPROCEDUREborrowed_book1ASSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMreaderrINNERJOINborrowbONr.RID=b.RIDINNERJOINbookkONb.BID=k.BIDWHERERname='程鵬'執(zhí)行存儲過程：borrowed_book1或EXECborrowed_book19.1.3存儲過程的參數(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'程鵬'--實參表變量傳值：DECLARE@temp1char(20)SET@temp1='楊樹華'EXECborrowed_book2@temp1--實參表9.1.3存儲過程的參數(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í)行存儲過程：EXECborrowed_book39.1.3存儲過程的參數(shù)例：利用輸出參數(shù)計算階乘。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)用存儲過程：DECLARE@oufloatEXECfactorial5,@ouOUT--實參表PRINT@ou9.2觸發(fā)器9.2.1觸發(fā)器的基本知識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ā)器的基本知識觸發(fā)器是特殊的存儲過程，基于一個表創(chuàng)建，主要作用就是實現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生變化（update,insert,delete）后，自動運(yùn)行以保證數(shù)據(jù)的完整性和正確性。通俗的說：通過一個動作（update,insert,delete）調(diào)用一個存儲過程（觸發(fā)器）。9.2.1觸發(fā)器的基本知識在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時將啟用。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的T-SQL語句。系統(tǒng)將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務(wù)對待，如果檢測到錯誤（例如，磁盤空間不足），則整個事務(wù)即自動回滾。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生變化（update,insert,delete）后，自動運(yùn)行以保證數(shù)據(jù)的完整性和正確性。通俗的說：通過一個動作（update,insert,delete）調(diào)用一個存儲過程（觸發(fā)器）。9.2.1觸發(fā)器的基本知識SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL）事件時將調(diào)用這些觸發(fā)器。但與DML觸發(fā)器不同的是，它們不會為響應(yīng)針對表或視圖的UPDATE、INSERT或DELETE語句而激發(fā)，相反，它們會為響應(yīng)多種數(shù)據(jù)定義語言（DDL）語句而激發(fā)。這些語句主要是以CREATE、ALTER和DROP開頭的語句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。9.2.2創(chuàng)建DML觸發(fā)器語法格式：CREATETRIGGER觸發(fā)器ON表名FOR[update,insert,delete]ASSQL語句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中添加借閱信息記錄時，得到該書的應(yīng)還日期。說明：在表borrow中增加一個應(yīng)還日期SReturnDate。USELibraryIFEXISTS(SELECTnameFROMsysobjectsWHEREname='T_return_date'ANDtype='TR')DROPTRIGGERT_return_dateCREATETRIGGERT_return_date--創(chuàng)建觸發(fā)器ONBorrow--基于表borrowAfterINSERT--插入操作AS--查詢插入記錄INSERTED中讀者的類型DECLARE@typeint,@dzbhchar(10),@tsbhchar(15)SET@dzbh=(SELECTRIDFROMinserted)SET@tsbh=(SELECTBIDFROMinserted)SELECT@type=TypeIDFROMreaderWHERERID=(SELECTRIDFROMinserted)--副本/*把Borrow表中的應(yīng)還日期改為當(dāng)前日期加上各類讀者的借閱期限*/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ù)庫Library中，當(dāng)讀者還書時，實際上要修改表brorrow中相應(yīng)記錄還期列的值，請計算出是否過期。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ù)返回兩個日期之差，單位為DAYFROMborrowWHERERID=@dzbhandBID=@tsbhIF@days>0PRINT'沒有過期！'ELSEPRINT'過期'+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ā)器例：對Library庫中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)+'本書沒還。

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

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