t網(wǎng)絡(luò)數(shù)據(jù)庫 課件 項目7 存儲過程和觸發(fā)器

項目七

存儲過程和觸發(fā)器任務(wù)1存儲過程任務(wù)2觸發(fā)器任務(wù)1存儲過程任務(wù)引入小林正在創(chuàng)建一個大型數(shù)據(jù)庫系統(tǒng)，其中創(chuàng)建完成某一特定功能的存儲過程。那么，怎么創(chuàng)建存儲過程？怎么使用存儲過程？存儲過程有哪些參數(shù)呢？知識準(zhǔn)備存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其他強(qiáng)大的編程功能。存儲過程可以使得數(shù)據(jù)庫的管理、顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作容易得多。存儲過程包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值?？梢猿鲇谌魏问褂肧QL語句的目的來使用存儲過程，它具有以下優(yōu)點：可以在單個存儲過程中執(zhí)行一系列SQL語句?？梢詮淖约旱拇鎯^程內(nèi)引用其他存儲過程，這可以簡化一系列復(fù)雜語句。存儲過程在創(chuàng)建時即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來比單個SQL語句快，且能減少網(wǎng)絡(luò)通信的負(fù)擔(dān)。一、創(chuàng)建存儲過程要使用存儲過程，首先要創(chuàng)建一個存儲過程。使用CREATEPROCEDURE語句創(chuàng)建存儲過程。其中，各參數(shù)含義如下：procedure_name：新存儲過程的名稱。number：是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句將同組的過程一起除去。@parameter：過程中的參數(shù)。data_type：參數(shù)的數(shù)據(jù)類型。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。Default：參數(shù)的默認(rèn)值。OUTPUT：表明參數(shù)是返回參數(shù)。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不會緩存該過程的計劃，該過程將在運行時重新編譯。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語句文本的條目。FORREPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。sql_statement：過程中要包含的任意數(shù)目和類型的SQL語句。二、執(zhí)行存儲過程執(zhí)行存儲過程使用EXECUTE語句其中，各參數(shù)含義如下：@return_status：是一個可選的整型變量，保存存儲過程的返回狀態(tài)。procedure_name：是調(diào)用的存儲過程的名稱。number：是可選的整數(shù)，用于將相同名稱的過程進(jìn)行組合，使得它們可以用一句DROPPROCEDURE語句除去。該參數(shù)不能用于擴(kuò)展存儲過程。@procedure_name_var：是局部定義變量名，代表存儲過程名稱。@parameter：是過程參數(shù)，在CREATEPROCEDURE語句中定義。value：是過程中參數(shù)的值。OUTPUT：指定存儲過程必須返回一個參數(shù)。DEFAULT：根據(jù)過程的定義，提供參數(shù)的默認(rèn)值。案例——創(chuàng)建存儲過程stud_grade，用于檢索所有學(xué)生的成績記錄案例——使用系統(tǒng)存儲過程查看EMIS數(shù)據(jù)庫和學(xué)生表的相關(guān)信息檢索所有學(xué)生的成績記錄

查看EMIS數(shù)據(jù)庫和學(xué)生表的相關(guān)信息三、存儲過程的參數(shù)在創(chuàng)建和使用存儲過程時，其參數(shù)是非常重要的。下面詳細(xì)討論存儲過程的參數(shù)傳遞和返回。1．使用參數(shù)在調(diào)用存儲過程時，有兩種傳遞參數(shù)的方法。2．使用默認(rèn)參數(shù)創(chuàng)建存儲過程時，可以為參數(shù)提供一個默認(rèn)值，默認(rèn)值必須為常量或者NULL。3．使用返回參數(shù)在創(chuàng)建存儲過程時，可以定義返回參數(shù)。案例——創(chuàng)建存儲過程average，返回兩個參數(shù)@st_name和＠st_avg，分別代表了姓名和平均分。查詢學(xué)號為“17043120”的學(xué)生姓名和平均分4．存儲過程的返回值存儲過程在執(zhí)行后都會返回一個整型值。5．存儲過程的刪除不再需要存儲過程時可將其刪除。通過DROPPROCEDURE語句來完成。任務(wù)2觸發(fā)器任務(wù)引入小林想要保證數(shù)據(jù)庫系統(tǒng)中數(shù)據(jù)完整性，還想在多個表中添加、更新或刪除行時，保留表之間所定義的關(guān)系，他咨詢師兄，師兄告訴他可以在程序中設(shè)置一個觸發(fā)器。那么，怎么創(chuàng)建觸發(fā)器？怎么使用觸發(fā)器呢？知識準(zhǔn)備觸發(fā)器在INSERT、UPDATE或DELETE語句對表或視圖進(jìn)行修改時會被自動執(zhí)行。觸發(fā)器可以查詢其他表，并可以包含復(fù)雜的SQL語句。一個表可以有多個觸發(fā)器。觸發(fā)器具有如下優(yōu)點：觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改。但是，通過級聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改。觸發(fā)器可以強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束。觸發(fā)器也可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。一個表中的多個同類觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個不同的對策，以響應(yīng)同一個修改語句。確保數(shù)據(jù)規(guī)范化。一、創(chuàng)建觸發(fā)器1．使用SSM創(chuàng)建觸發(fā)器（1）在資源管理器相應(yīng)的數(shù)據(jù)庫中的表節(jié)點下選擇“觸發(fā)器”，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“新建觸發(fā)器”選項，如圖所示。（2）打開對應(yīng)的窗口，在窗口中修改創(chuàng)建觸發(fā)器的模板代碼，如圖所示。2．使用T-SQL創(chuàng)建觸發(fā)器

快捷菜單

創(chuàng)建觸發(fā)器模板二、使用觸發(fā)器在SQLServer中，除了INSERT、UPDATE和DELETE3種觸發(fā)器外，還提供了INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE觸發(fā)器。1．INSERT觸發(fā)器當(dāng)向表中插入記錄時，INSERT觸發(fā)器被執(zhí)行。一般情況下，這種觸發(fā)器常用來檢查插入數(shù)據(jù)是否滿足要求。案例——創(chuàng)建一個觸發(fā)器，當(dāng)執(zhí)行INSERT操作時，該觸發(fā)器被觸發(fā)（1）創(chuàng)建觸發(fā)器。（2）執(zhí)行INSERT操作。2．UPDATE觸發(fā)器當(dāng)向表中更新記錄時，UPDATE觸發(fā)器被執(zhí)行。案例——創(chuàng)建一個觸發(fā)器，當(dāng)執(zhí)行UPDATE操作時，該觸發(fā)器被觸發(fā)（1）創(chuàng)建觸發(fā)器。（2）執(zhí)行UPDATE操作。3．DELETE觸發(fā)器創(chuàng)建觸發(fā)器測試觸發(fā)器trigger1的執(zhí)行創(chuàng)建觸發(fā)器測試觸發(fā)器trigger2的執(zhí)行三、刪除觸發(fā)器除了在對象資源管理器中刪除觸發(fā)器外，也可以使用DROPTRIGGER語句來刪除觸發(fā)