項(xiàng)目七

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

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