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

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

創建存儲過程創建存儲過程的語法規則CREATEPROCEDURE存儲過程名

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

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

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

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

(含默認值調用)

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

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

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

DELETE類型第25頁創建DML觸發器CREATETRIGGERtrigger_nameONtable_name|view_nameWITHENCRYPTION{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statementAFTER指定在對數據表的相關操作之后，觸發器被觸發。若指定FOR關鍵字，則默認是AFTER設置。INSTEADOF指定執行觸發器而不是執行語句第26頁

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

①向表中插入數據時，INSERT觸發器觸發執行。

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

③

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