存儲(chǔ)過(guò)程觸發(fā)器函數(shù)

第1頁(yè)第8章存儲(chǔ)過(guò)程、觸發(fā)器和函數(shù)本章概述本章要點(diǎn)本章內(nèi)容第2頁(yè)本章概述如何提高Transact-SQL語(yǔ)句的執(zhí)行效率呢？如何加強(qiáng)數(shù)據(jù)庫(kù)中數(shù)據(jù)完整性的機(jī)制呢？這些問(wèn)題的解決都依賴于數(shù)據(jù)庫(kù)的編程對(duì)象。典型的數(shù)據(jù)庫(kù)編程對(duì)象包括視圖、存儲(chǔ)過(guò)程、觸發(fā)器、函數(shù)等。存儲(chǔ)過(guò)程是一個(gè)可重用的代碼模塊，可以高效率地完成指定的操作。觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，可以實(shí)現(xiàn)自動(dòng)化的操作。用戶定義函數(shù)是由用戶根據(jù)應(yīng)用程序的需要而定義的可以完成特定操作的函數(shù)。本章將全面介紹存儲(chǔ)過(guò)程、觸發(fā)器、用戶定義函數(shù)等特點(diǎn)和使用方式。第3頁(yè)本章要點(diǎn)存儲(chǔ)過(guò)程的特點(diǎn)、類(lèi)型和作用使用CREATEPROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的執(zhí)行方式DML觸發(fā)器的特點(diǎn)和創(chuàng)建方式DML觸發(fā)器的工作原理使用CREATETRIGGER語(yǔ)句創(chuàng)建DML觸發(fā)器DDL觸發(fā)器的特點(diǎn)和創(chuàng)建方式用戶定義函數(shù)的類(lèi)型和特點(diǎn)使用CREATEFUNCTION語(yǔ)句創(chuàng)建用戶定義函數(shù)第4頁(yè)本章內(nèi)容8.1存儲(chǔ)過(guò)程8.2觸發(fā)器8.3用戶定義函數(shù)8.4本章小結(jié)8.1存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程可以提高應(yīng)用程序的設(shè)計(jì)效率和增強(qiáng)系統(tǒng)的安全性。本節(jié)將全面介紹存儲(chǔ)過(guò)程的特點(diǎn)、類(lèi)型、創(chuàng)建、執(zhí)行等內(nèi)容。第5頁(yè)存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程是一個(gè)可重用的代碼模塊，可以高效率地完成指定的操作。在MicrosoftSQLServer2008系統(tǒng)中，可以使用Transact-SQL語(yǔ)言編寫(xiě)存儲(chǔ)過(guò)程，也可以使用CLR方式編寫(xiě)存儲(chǔ)過(guò)程。使用CLR編寫(xiě)存儲(chǔ)過(guò)程是MicrosoftSQLServer2008系統(tǒng)與.NET框架緊密集成的一種表現(xiàn)形式。第6頁(yè)類(lèi)型在MicrosoftSQLServer2008系統(tǒng)中，提供了3種基本的存儲(chǔ)過(guò)程類(lèi)型用戶定義的存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程。第7頁(yè)使用CREATEPROCEDURE語(yǔ)句在MicrosoftSQLServer2008系統(tǒng)中，可以使用CREATEPROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程。需要強(qiáng)調(diào)的是，必須具有CREATEPROCEDURE權(quán)限才能創(chuàng)建存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程是架構(gòu)作用域中的對(duì)象，只能在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，應(yīng)該指定所有的輸入?yún)?shù)、執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句、返回至調(diào)用過(guò)程或批處理表明成功或失敗的狀態(tài)值、捕捉和處理潛在錯(cuò)誤的錯(cuò)誤處理語(yǔ)句。第8頁(yè)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程，需確定存儲(chǔ)過(guò)程的三個(gè)組成部分:1．所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)2．被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句3．返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗

在創(chuàng)建存儲(chǔ)過(guò)程之前應(yīng)該考慮到以下幾個(gè)方面：1．在一個(gè)批處理中CreateProcedure語(yǔ)句不能與其它SQL語(yǔ)句合并在一起。2．?dāng)?shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限它可把該權(quán)限傳遞給其它的用戶。3．存儲(chǔ)過(guò)程作為數(shù)據(jù)庫(kù)對(duì)象其命名必須符合命名規(guī)則。4．只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程

創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法規(guī)則CREATEPROCEDURE存儲(chǔ)過(guò)程名

@參數(shù)名類(lèi)型[=default][output][,…n][with{recompile|encryption|recompile,encryption}]ASsql_statementRecompile:指定每次執(zhí)行時(shí)都要重新編譯Encryption:指定對(duì)存儲(chǔ)過(guò)程的正文進(jìn)行加密，防止別的用戶看到編寫(xiě)的存儲(chǔ)過(guò)程腳本。定義無(wú)參數(shù)的存儲(chǔ)過(guò)程例1：在study數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)名稱為myproc1的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程的功能是從數(shù)據(jù)表studnet中查詢所有男同學(xué)的信息。GOCREATEPROCEDUREmyproc1ASSELECT*FROMsWHEREsex='男'GO定義具有參數(shù)的存儲(chǔ)過(guò)程。例2：創(chuàng)建一個(gè)名稱為InsertRecord的存儲(chǔ)過(guò)程，功能是向數(shù)據(jù)表student中插入一條記錄，新值由參數(shù)提供。USEstudyGOCREATEPROCEDUREInsertRecord@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)ASINSERTINTOsVALUES(@sno,@sn,@sex,@age,@dept)GO

定義具有參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程。例3：創(chuàng)建一個(gè)名稱為InsertRecordDefa的存儲(chǔ)過(guò)程，其功能是向student表中插入一條記錄，新值由參數(shù)提供，若未提供系別dept值時(shí)，由參數(shù)的默認(rèn)值代替。USEstudyGOCREATEPROCEDUREInsertRecordDefa@snochar(6),@snchar(20),@agenumeric(5),@sexchar(2),@deptchar(10)='無(wú)'ASINSERTINTOstudentVALUES(@sno,@sn,@sex,@age,@dept)定義能夠返回值的存儲(chǔ)過(guò)程。例4：創(chuàng)建一個(gè)名稱為Query_Study的存儲(chǔ)過(guò)程，其功能是從student表中根據(jù)學(xué)號(hào)查詢某一同學(xué)的姓名和系別。CREATEPROCEDUREQuery_Study@snochar(6),@snchar(20)OUTPUT,@deptchar(10)OUTPUTASSELECT@sn=sn,@dept=deptFROMstudentWHEREsno=@sno其語(yǔ)法規(guī)則為：DROPPROCEDURE{procedure}}[,…n]如：將存儲(chǔ)過(guò)程mynewproc從數(shù)據(jù)庫(kù)中刪除。

dropproceduremynewproc刪除存儲(chǔ)過(guò)程使用EXECUTE命令(可以簡(jiǎn)寫(xiě)為EXEC)1.執(zhí)行存儲(chǔ)過(guò)程myproc(無(wú)參調(diào)用)

EXECmyproc2.執(zhí)行存儲(chǔ)過(guò)程InsertRecord(帶參調(diào)用)EXECInsertRecord‘S1’,‘王大利’,‘男’,18,‘計(jì)算機(jī)系’或者：把值放入變量中，使用變量間接的提供參數(shù)值執(zhí)行存儲(chǔ)過(guò)程3.執(zhí)行存儲(chǔ)過(guò)程InsertRecordDefa

(含默認(rèn)值調(diào)用)

EXECInsertRecordDefa'S10','高平','女',184.執(zhí)行存儲(chǔ)過(guò)程Query_Study(含有輸出參數(shù))

DECLARE@snchar(20)DECLARE@deptchar(10)EXECQuery_Study'S10',@snOUTPUT,@deptOUTPUTPrint@sn--或者select@snprint@dept執(zhí)行存儲(chǔ)過(guò)程CREATEPROCEDUREdisRecordASdeclare@snochar(6),@snchar(20),@sexchar(2),@agesmallintdeclarespcursorforselects_no,s_name,s_sex,year(getdate())-year(s_birthday)fromstudentwheres_sex='女'openspfetchspinto@sno,@sn,@sex,@agewhile@@fetch_status=0beginprint@sno+@sn+@sex+convert(char,@age)fetchspinto@sno,@sn,@sexendclosespdeallocatesp存儲(chǔ)過(guò)程的執(zhí)行過(guò)程存儲(chǔ)過(guò)程創(chuàng)建之后，在第一次執(zhí)行時(shí)需要經(jīng)過(guò)語(yǔ)法分析階段、解析階段、編譯階段和執(zhí)行階段。第20頁(yè)查看存儲(chǔ)過(guò)程的信息在MicrosoftSQLServer2008系統(tǒng)中，可以使用系統(tǒng)存儲(chǔ)過(guò)程和目錄視圖查看有關(guān)存儲(chǔ)過(guò)程的信息。第21頁(yè)8.2觸發(fā)器MicrosoftSQLServer2008系統(tǒng)提供了兩種強(qiáng)制業(yè)務(wù)邏輯和數(shù)據(jù)完整性的機(jī)制，即約束技術(shù)和觸發(fā)器技術(shù)。第22頁(yè)觸發(fā)器的概念和類(lèi)型觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，它包括了大量的Transact-SQL語(yǔ)句。但是觸發(fā)器又與存儲(chǔ)過(guò)程不同，存儲(chǔ)過(guò)程可以由用戶直接調(diào)用執(zhí)行，而觸發(fā)器不能被直接調(diào)用執(zhí)行，它只能自動(dòng)執(zhí)行。

按照觸發(fā)事件的不同，可以把MicrosoftSQLServer2008系統(tǒng)提供的觸發(fā)器分成兩種類(lèi)型，即DML觸發(fā)器和DDL觸發(fā)器。第23頁(yè)DML觸發(fā)器當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操縱語(yǔ)言(DML)事件時(shí)將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT、UPDATE、DELETE語(yǔ)句。在DML觸發(fā)器中，可以執(zhí)行查詢其他表的操作，也可以包含更加復(fù)雜的Transact-SQL語(yǔ)句。在執(zhí)行觸發(fā)器操作過(guò)程中，如果檢測(cè)到錯(cuò)誤發(fā)生，則整個(gè)觸發(fā)事件語(yǔ)句和觸發(fā)器操作的事務(wù)自動(dòng)回滾。第24頁(yè)DML觸發(fā)器的類(lèi)型按照觸發(fā)器事件類(lèi)型的不同，可以把MSSQLServer2008系統(tǒng)提供的DML觸發(fā)器分成3種類(lèi)型，即INSERT類(lèi)型UPDATE類(lèi)型

DELETE類(lèi)型第25頁(yè)創(chuàng)建DML觸發(fā)器CREATETRIGGERtrigger_nameONtable_name|view_nameWITHENCRYPTION{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statementAFTER指定在對(duì)數(shù)據(jù)表的相關(guān)操作之后，觸發(fā)器被觸發(fā)。若指定FOR關(guān)鍵字，則默認(rèn)是AFTER設(shè)置。INSTEADOF指定執(zhí)行觸發(fā)器而不是執(zhí)行語(yǔ)句第26頁(yè)

例：創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向表student中插入一條記錄時(shí)，自動(dòng)顯示表中的記錄。CREATETRIGGERChange_DisplayONstudnetFORINSERT,UPDATE,DELETEASSELECT*FROMstudent該觸發(fā)器建立完畢后，當(dāng)執(zhí)行如下操作時(shí)將會(huì)顯示數(shù)據(jù)表s中的全部記錄。EXECInsertRecordDefa@sno='S11',@sn='張建峰',@age=17,@sex='男'第28頁(yè)使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程查看定義的存儲(chǔ)過(guò)程信息execsp_helptext‘存儲(chǔ)過(guò)程名’DML觸發(fā)器的工作原理

①向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)執(zhí)行。

②當(dāng)INSERT觸發(fā)器觸發(fā)時(shí)，新的記錄增加到觸發(fā)器表中和inserted表中。

③

inserted表是一個(gè)邏輯表，保存了所插入記錄的備份，允許用戶參考INSERT語(yǔ)句中數(shù)據(jù)。觸發(fā)器可以檢查inserted表，來(lái)確定該觸發(fā)器的操作是否應(yīng)該執(zhí)行和如何執(zhí)行。④在inserted表中的記錄，總是觸發(fā)器表中一行或多行記錄的冗余。第29頁(yè)DELETE觸發(fā)器當(dāng)觸發(fā)一個(gè)DELETE觸發(fā)器時(shí)，被刪除的記錄放在一個(gè)特殊的deleted表中。deleted表是一個(gè)邏輯表，用來(lái)保存已經(jīng)從表中刪除的記錄。該deleted表允許參考原來(lái)的DELETE語(yǔ)句刪除的已經(jīng)記錄在日志中的數(shù)據(jù)。第30頁(yè)UPDATE觸發(fā)器修改一條記錄就等于插入一條新記錄同時(shí)刪除一條舊記錄。UPDATE語(yǔ)句也可以看成是由刪除一條記錄的DELETE語(yǔ)句和增加一條記錄的INSERT語(yǔ)句組成。當(dāng)在某一個(gè)有UPDATE觸發(fā)器表的上面修改一條記錄時(shí)，表中原來(lái)的記錄移動(dòng)到deleted表中，修改過(guò)的記錄插入到了inserted表中。觸發(fā)器可以檢查deleted表和inserted表以及被修改的表，以便確定是否修改了多個(gè)行和應(yīng)該如何執(zhí)行觸發(fā)器的操作。第31頁(yè)DDL觸發(fā)器DDL觸發(fā)器觸發(fā)事件主要是CREATE、ALTER、DROP以及GRANT、DENY、REVOKE等語(yǔ)句，并且觸發(fā)的時(shí)間條件只有AFTER，沒(méi)有INSTEADOF。CREATETRIGGERCREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}WITHENCRYPTION{FOR|AFTER}{event_type}ASsql_statement定義一個(gè)DDL觸發(fā)器刪除表的操作失敗8.3用戶定義函數(shù)用戶定義函數(shù)可以使用Transact-SQL語(yǔ)言編寫(xiě)，也可以使用.NET編程語(yǔ)言來(lái)編寫(xiě)。每次使用用戶定義函數(shù)時(shí)均無(wú)需重新解析和重新優(yōu)化，從而大大縮短了執(zhí)行時(shí)間。減少網(wǎng)絡(luò)流量，基于某種無(wú)法用單一標(biāo)量表達(dá)式表示的復(fù)雜約束來(lái)過(guò)濾數(shù)據(jù)的操作，可以表示為函數(shù)。然后，該函數(shù)可以在WHERE子句中調(diào)用，以減少發(fā)送至客戶端的數(shù)字或行數(shù)。結(jié)構(gòu)所有的用戶