存儲過程與觸發(fā)器課件

1、第8章 存儲過程與觸發(fā)器 將一些T-SQL語句打包成一個數(shù)據(jù)庫對象(存儲過程或觸發(fā)器)并存儲在SQL Server服務器上，等到需要時，就調(diào)用或觸發(fā)這些T-SQL語句包。本章教與學的建議：教師使用4課時邊講解示例，邊安排練習，學生在教師的指導下使用4課時在課內(nèi)練習和檢查。本章目標是通過學習，能夠實施存儲過程和觸發(fā)器應用管理。第8章 存儲過程與觸發(fā)器7/31/20222本章學習任務 存儲過程概述 存儲過程定義與特點、類型 創(chuàng)建和執(zhí)行存儲過程 創(chuàng)建和執(zhí)行帶參數(shù)或不帶參數(shù)的存儲過程、創(chuàng)建和使用擴展存儲過程 修改和刪除存儲過程 查看、修改、刪除存儲過程 創(chuàng)建和管理觸發(fā)器 觸發(fā)器概述、創(chuàng)建觸發(fā)器、管理觸

2、發(fā)器第8章 存儲過程與觸發(fā)器7/31/20223存儲過程的概述 當開發(fā)一個應用程序時，為了易于修改和擴充方便，我們經(jīng)常會將負責不同功能的語句集中起來而且按照用途分別獨立放置，以便能夠反復調(diào)用，而這些獨立放置且擁有不同功能的語句，即是“過程”(Procedure)。SQL Server的存儲過程是一組完成特定功能的T-SQL語句集，經(jīng)編譯后以特定的名稱存儲在數(shù)據(jù)庫中，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行存儲過程。7/31/20224存儲過程特點與類型 存儲過程特點允許模塊化的程序設計更快的執(zhí)行速度有效降低網(wǎng)絡流量較好的安全機制存儲過程類型系統(tǒng)存儲過程本地存儲過程臨

3、時存儲過程遠程存儲過裎擴展存儲過程7/31/20225不帶參數(shù)的存儲過程使用對象資源管理器創(chuàng)建存儲過程使用SQL命令創(chuàng)建存儲過程CREATE PROCEDURE procedure_nameAS sql_statementsprocedure_name為所創(chuàng)建的存儲過程的名字；sql_statements為在存儲過程中需要執(zhí)行的數(shù)據(jù)庫操作。7/31/20226不帶參數(shù)的存儲過程7/31/20227不帶參數(shù)的存儲過程7/31/20228帶參數(shù)的存儲過程 存儲過程通過參數(shù)來與調(diào)用它的程序通信。在程序調(diào)用存儲過程時，可以通過輸入?yún)?shù)將數(shù)據(jù)傳給存儲過程，存儲過程可以通過輸出參數(shù)和返回值將數(shù)據(jù)返回給調(diào)用

4、它的程序。 創(chuàng)建帶參數(shù)的存儲過程的語法格式CREATE PROCEDURE procedure_nameparameter data_type =DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION| RECOMPILE, ENCRYPTINAS sql_ statements 7/31/20229帶參數(shù)的存儲過程 使用參數(shù)名傳送參數(shù)值 EXECUTE procedure_name parameter_name = value ,nprocedure_name為存儲過程名；parameter_name為輸入?yún)?shù)名；value為傳遞給輸入?yún)?shù)的值。 如果存儲過程中

5、有輸入?yún)?shù)，在執(zhí)行存儲過程時沒有給出參數(shù)，則系統(tǒng)會顯示錯誤提示。7/31/202210帶參數(shù)的存儲過程 7/31/202211帶參數(shù)的存儲過程 7/31/202212帶參數(shù)的存儲過程 按位置傳送參數(shù)值 EXECUTE proc_name value1,value2,proc_name為存儲過程名；value1,value2,為傳遞給各輸入?yún)?shù)的值。 具有默認值的存儲過程例如，針對一個表，創(chuàng)建一個存儲過程，執(zhí)行存儲過程時將向數(shù)據(jù)表插入一條記錄，新記錄的值由參數(shù)提供，如果未提供某個字段的值時，由參數(shù)的默認值代替。7/31/202213帶參數(shù)的存儲過程 7/31/202214帶參數(shù)的存儲過程 7/3

6、1/202215帶參數(shù)的存儲過程 創(chuàng)建具有返回值的存儲過程parameter_name datatype=default OUTPUT ,nparameter_name存儲過程的輸出參數(shù)名，必須以符號為前綴。存儲過程通過該參數(shù)返回結果。datatype指明輸出參數(shù)的數(shù)據(jù)類型，它既可以是系統(tǒng)提供的數(shù)據(jù)類型，也可以是用戶自定義的數(shù)據(jù)類型，但必須是除text和image以外的數(shù)據(jù)類型。default指定輸出參數(shù)的默認值。OUTPUT指明參數(shù)為輸出參數(shù)。7/31/202216帶參數(shù)的存儲過程 7/31/202217帶參數(shù)的存儲過程 執(zhí)行具有返回值的存儲過程EXECUTE return_status=p

7、rocedure_name | procudure_name_varparameter_name=value|variableOUTPUT ,nWITH RECOMPILEprocedure_name:需執(zhí)行的存儲過程的名字；parameter_name=value | variable為輸入?yún)?shù)傳遞值；parameter_name=variable OUTPUT為傳遞給輸出參數(shù)的變量，variable用來存放返回參數(shù)的值。OUTPUT指明這是一個輸出傳遞參數(shù)，與響應的存儲過程中的輸出參數(shù)相匹配。7/31/202218帶參數(shù)的存儲過程 7/31/202219帶參數(shù)的存儲過程 存儲過程的重編譯處

8、理 存儲過程的處理在創(chuàng)建存儲過程時，SQL Server需要對存儲過程中的語句進行語法檢查。如果存儲過程定義中存在語法錯誤，將返回錯誤，并且將不能創(chuàng)建該存儲過程。如果語法正確，則存儲過程的文本將存儲在syscomments系統(tǒng)表中。 在建立存儲過程時設定重編譯選項CREATE PROCEDURE WITH RECOMPILE通過在創(chuàng)建時設定重編譯選項，在每次執(zhí)行時對存儲過程進行重編譯處理。7/31/202220帶參數(shù)的存儲過程 存儲過程的重編譯處理 在執(zhí)行存儲過程時設定重編譯選項EXECUTE procedure_nameparameter_name=variable OUTPUT WITH

9、RECOMPILE通過在執(zhí)行存儲過程的EXECUTE語句中設定WITH RECOMPILE選項，可以在執(zhí)行存儲過程時重新編譯該存儲過程。通過系統(tǒng)存儲過程設定重編譯選項EXEC sp_recompile OBJECTsp_recompile為用于重編譯存儲過程的系統(tǒng)存儲過程；OBJECT為當前數(shù)據(jù)庫中的存儲過程、觸發(fā)器、表或視圖的名稱。7/31/202221帶參數(shù)的存儲過程 7/31/202222擴展存儲過程的創(chuàng)建和使用 擴展存儲過程是使用編程語言創(chuàng)建的外部程序，它與通常的存儲過程不同。存儲過程是一系列預編譯的T-SQL語句，而擴展存儲過程是對動態(tài)鏈接庫函數(shù)的調(diào)用。使用T-SQL語句注冊擴展存儲

10、過程sp_addextendedproc functname= procedure, dllname= dll 使用對象資源管理器注冊擴展存儲過程使用擴展存儲過程擴展存儲過程注冊到SQL Server中之后，這個擴展存儲過程就可以像普通存儲過程一樣被使用。7/31/202223修改和刪除存儲過程存儲過程被創(chuàng)建以后，它的名字存儲在系統(tǒng)表sysobjects中；它的源代碼存放在系統(tǒng)表syscomments中。我們既可以通過對象資源管理器查看存儲過程的源代碼，也可以通過SQL Server提供的系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程信息。使用系統(tǒng)存儲過程查看存儲過程源代碼的語句是：sp_helptex

11、t procedure_name 如果在創(chuàng)建存儲過程時使用了WITH ENCRYPTION選項，那么就無法查看到存儲過程的源代碼。7/31/202224存儲過程的查看、修改、刪除 7/31/202225存儲過程的查看、修改、刪除 使用 ALTER PROCEDURE命令ALTER PROCEDURE procedure_nameparameter data_type=DEFAULTOUTPUT ,nWITHRECOMPILE | ENCRYPTION | RECOMPILE,ENCRYTIONAS Sql_statement,nprocedure_name 欲修改存儲過程名稱parameter

12、 輸入和輸出參數(shù)data_type 參數(shù)的數(shù)據(jù)類型default 指定的默認值WITH RECOMPILE 重編譯選項WITH ENCRYPTION加密選項7/31/202226存儲過程的查看、修改、刪除 7/31/202227存儲過程的查看、修改、刪除 重新命名存儲過程sp_rename old_procedure_name, new_procedure_name 使用DROP PROCEDURE語句DROP PROCEDURE procedure_name,nDROP命令可將一個或多個存儲過程或者存儲過程組從當前數(shù)據(jù)庫中刪除。7/31/202228創(chuàng)建和管理觸發(fā)器 觸發(fā)器是一種特殊的存儲過

13、程，它在特定語言事件發(fā)生時自動執(zhí)行，通常用于實現(xiàn)強制業(yè)務規(guī)則和數(shù)據(jù)完整性。觸發(fā)器的主要作用是實現(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據(jù)一致性。除此之外，觸發(fā)器還有以下作用。 觸發(fā)器可以對數(shù)據(jù)庫進行級聯(lián)修改。 實現(xiàn)比CHECK約束更為復雜的限制。 比較數(shù)據(jù)修改前后的差別。 強制表的修改要合乎業(yè)務規(guī)則。7/31/202229觸發(fā)器概述觸發(fā)器也是一種存儲過程，一種在基表被修改時自動執(zhí)行的內(nèi)嵌過程，主要通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名字而被直接調(diào)用。當對某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQL Server就會自動執(zhí)行觸發(fā)器所定義的SQL

14、語句。從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是其能夠實現(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據(jù)的一致性。7/31/202230創(chuàng)建觸發(fā)器 使用對象資源管理器創(chuàng)建觸發(fā)器使用命令創(chuàng)建觸發(fā)器CREATE TRIGGER trigge_nameON table | viewFOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)andor UPDATE(column_name) sql_statesments7/31/202231創(chuàng)建觸發(fā)器 7/3

15、1/202232創(chuàng)建觸發(fā)器 7/31/202233管理觸發(fā)器 使用系統(tǒng)存儲過程查看觸發(fā)器信息系統(tǒng)存儲過程sp_help、sp_helptext和sp_depends分別提供有關觸發(fā)器的不同信息。使用系統(tǒng)表查看觸發(fā)器信息用戶還可以通過查詢系統(tǒng)表sysobjects得到觸發(fā)器的相關信息。使用對象資源管理器查看觸發(fā)器的相關信息使用sp_rename命令修改觸發(fā)器的名字sp_rename oldname, newname其中，oldname為觸發(fā)器原來的名稱，newname為觸發(fā)器的新名稱。通過對象資源管理器修改觸發(fā)器定義7/31/202234管理觸發(fā)器 使用ALERT TRIGGER命令修改觸發(fā)器A

16、LTER TRIGGER trigge_name ON table|viewFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEWITH ENCRYPTIONASIF UPDATE(column_name)and|or UPDATE(column name)sql_statesments7/31/202235管理觸發(fā)器 使用DROP TRIGGER刪除觸發(fā)器DROP TRIGGER trigger_name使用對象資源管理器刪除觸發(fā)器，右擊將要刪除觸發(fā)器，在彈出菜單中選擇“刪除”，接著再單擊“刪除對象”對話框中的“確定”即可。刪除數(shù)據(jù)表將自動刪除與數(shù)據(jù)表相關的觸發(fā)器

17、。當不再需要某個觸發(fā)器時，可以將其刪除。刪除了觸發(fā)器后，它所基于的表和數(shù)據(jù)不會受到影響。7/31/202236管理觸發(fā)器 禁止和啟用觸發(fā)器ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL | trigger_name,n在有些情況下，用戶希望暫停觸發(fā)器的作用，但并不刪除它，這時就可以通過DISABLE TRIGGER語句使觸發(fā)器無效；DISABLE TRIGGER指定禁用trigger_name要使DML觸發(fā)器重新有效，可使用ENABLE TRIGGER語句ENABLE TRIGGER指定啟用trigger_name7/31/202237 實驗目的 理解存儲過程和觸發(fā)器的概念。 學會使用對象資源管理器和SQL編輯器創(chuàng)建存儲過程和觸發(fā)器。 學會存儲過程和觸發(fā)器的管理方法。 實驗準備 在Windows Server 2000以上版本的微機上安裝MS SQL Server 2005 ； 創(chuàng)建數(shù)據(jù)庫和所需要的數(shù)據(jù)表 ； 向各個數(shù)據(jù)表輸入實驗數(shù)據(jù) 。 實驗內(nèi)容和步驟