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

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

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

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

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

dropproceduremynewproc刪除存儲過程使用EXECUTE命令(可以簡寫為EXEC)1.執(zhí)行存儲過程myproc(無參調(diào)用)

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

(含默認值調(diào)用)

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

DECLARE@snchar(20)DECLARE@deptchar(10)EXECQuery_Study'S10',@snOUTPUT,@deptOUTPUTPrint@sn--或者select@snprint@dept執(zhí)行存儲過程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存儲過程的執(zhí)行過程存儲過程創(chuàng)建之后，在第一次執(zhí)行時需要經(jīng)過語法分析階段、解析階段、編譯階段和執(zhí)行階段。第20頁查看存儲過程的信息在MicrosoftSQLServer2008系統(tǒng)中，可以使用系統(tǒng)存儲過程和目錄視圖查看有關(guān)存儲過程的信息。第21頁8.2觸發(fā)器MicrosoftSQLServer2008系統(tǒng)提供了兩種強制業(yè)務(wù)邏輯和數(shù)據(jù)完整性的機制，即約束技術(shù)和觸發(fā)器技術(shù)。第22頁觸發(fā)器的概念和類型觸發(fā)器是一種特殊類型的存儲過程，它包括了大量的Transact-SQL語句。但是觸發(fā)器又與存儲過程不同，存儲過程可以由用戶直接調(diào)用執(zhí)行，而觸發(fā)器不能被直接調(diào)用執(zhí)行，它只能自動執(zhí)行。

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

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

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

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

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

③

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