存儲(chǔ)過(guò)程與觸發(fā)器綜合概述課件

第8章存儲(chǔ)過(guò)程與觸發(fā)器第8章存儲(chǔ)過(guò)程與觸發(fā)器1本章學(xué)習(xí)目標(biāo)l理解存儲(chǔ)過(guò)程和觸發(fā)器的作用l

熟練創(chuàng)建和管理存儲(chǔ)過(guò)程及觸發(fā)器l

靈活運(yùn)用存儲(chǔ)過(guò)程和觸發(fā)器提高系統(tǒng)開發(fā)效率本章學(xué)習(xí)目標(biāo)2

8.1存儲(chǔ)過(guò)程

8.1.1存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)

1．存儲(chǔ)過(guò)程簡(jiǎn)介

存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的由SQL語(yǔ)句和控制流語(yǔ)句組成的一個(gè)預(yù)編譯集合。存儲(chǔ)過(guò)程劃分為系統(tǒng)存儲(chǔ)過(guò)程以及用戶存儲(chǔ)過(guò)程。一個(gè)存儲(chǔ)過(guò)程可以作為一個(gè)獨(dú)立的單元進(jìn)行處理。在SQLServer中可以通過(guò)存儲(chǔ)過(guò)程來(lái)完成很多管理任務(wù)，利用一些常用的系統(tǒng)存儲(chǔ)過(guò)程可以很方便的查看、操作數(shù)據(jù)庫(kù)對(duì)象。系統(tǒng)存儲(chǔ)過(guò)程的命名通常以”sp_”作為前綴，并且存儲(chǔ)于Master數(shù)據(jù)庫(kù)中，如果用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫(kù)中調(diào)用這些存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程提供了一種封裝某一個(gè)需要重復(fù)執(zhí)行任務(wù)的方法。一旦定義了一個(gè)存儲(chǔ)過(guò)程之后，在應(yīng)用程序中就可以對(duì)其進(jìn)行調(diào)用。在存儲(chǔ)過(guò)程中，不僅可以包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢，而且也可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集。8.1存儲(chǔ)過(guò)程

8.1.1存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)

1．存3所以通過(guò)設(shè)計(jì)自定義用戶存儲(chǔ)過(guò)程，用戶可以使其實(shí)現(xiàn)強(qiáng)大的編程功能。

使用存儲(chǔ)過(guò)程可以比單獨(dú)的SQL語(yǔ)句完成更為復(fù)雜的功能，并且系統(tǒng)會(huì)對(duì)存儲(chǔ)過(guò)程中的SQL語(yǔ)句進(jìn)行了預(yù)編譯處理，使得執(zhí)行速度有了大幅度的提升。存儲(chǔ)過(guò)程被第一次調(diào)用后，會(huì)保存在高速緩沖區(qū)中，這樣再次執(zhí)行同一個(gè)存儲(chǔ)過(guò)程時(shí)，會(huì)提高了重復(fù)調(diào)用的效率。在實(shí)際應(yīng)用中，可以將復(fù)雜的商業(yè)規(guī)則封裝在存儲(chǔ)過(guò)程中，從而提高程序語(yǔ)句的利用率。

2．存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程一旦執(zhí)行一次后，其執(zhí)行的計(jì)劃就會(huì)駐留在計(jì)算機(jī)的高速緩沖存儲(chǔ)器中。其后對(duì)同一個(gè)存儲(chǔ)過(guò)程的調(diào)用就可以直接利用編譯后在高速緩存中的二進(jìn)制形式來(lái)完成操作?？梢栽趩蝹€(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列SQL語(yǔ)句，因而可以用于設(shè)計(jì)、封裝企業(yè)的功能模塊。應(yīng)用程序使用相同的存儲(chǔ)過(guò)程進(jìn)行操作，確保了在數(shù)據(jù)訪問(wèn)、操縱的一致性。所以通過(guò)設(shè)計(jì)自定義用戶存儲(chǔ)過(guò)程，用戶可以使其實(shí)4可以在當(dāng)前的存儲(chǔ)過(guò)程內(nèi)部引用其它存儲(chǔ)過(guò)程，這樣可以將復(fù)雜語(yǔ)句進(jìn)行簡(jiǎn)化。提供了一種數(shù)據(jù)庫(kù)訪問(wèn)的途徑，只需要用戶擁有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，那么通過(guò)使用存儲(chǔ)過(guò)程就可以完成對(duì)數(shù)據(jù)庫(kù)的各種操作，如添加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等，而不需要考慮用戶是否擁有存儲(chǔ)過(guò)程所處理的數(shù)據(jù)對(duì)象的訪問(wèn)權(quán)限。3．使用存儲(chǔ)過(guò)程的注意事項(xiàng)在命名自定義存儲(chǔ)過(guò)程時(shí)避免與系統(tǒng)存儲(chǔ)過(guò)程名相同。存儲(chǔ)過(guò)程最多能夠支持32層的嵌套。命名存儲(chǔ)過(guò)程中的標(biāo)識(shí)符時(shí)，長(zhǎng)度不能超過(guò)128個(gè)字符。存儲(chǔ)過(guò)程中參數(shù)的個(gè)數(shù)不能超過(guò)2100。3．使用存儲(chǔ)過(guò)程的注意事項(xiàng)58.1.2創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程

在SQLServer中，既可以通過(guò)企業(yè)管理器，也可以通過(guò)使用CREATEPROCEDRUE語(yǔ)句的方式來(lái)創(chuàng)建存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程

步驟：

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)并在該節(jié)點(diǎn)上點(diǎn)擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲(chǔ)過(guò)程】級(jí)聯(lián)菜單，將彈出如圖8-1所示的對(duì)話框。圖8-1新建存儲(chǔ)過(guò)程對(duì)話框8.1.2創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程

在SQLServer中，6③在【存儲(chǔ)過(guò)程屬性】對(duì)話框中的【文本】編輯框中，對(duì)[OWNER]以及[PROCEDURENAME]部分進(jìn)行修改，分別修改為該存儲(chǔ)過(guò)程的所有者和存儲(chǔ)過(guò)程名稱，并且輸入該存儲(chǔ)過(guò)程所包含的SQL語(yǔ)句。如圖8-2所示為創(chuàng)建了一個(gè)用于查詢學(xué)生信息的存儲(chǔ)過(guò)程。圖8-2用于查詢學(xué)生信息的存儲(chǔ)過(guò)程③在【存儲(chǔ)過(guò)程屬性】對(duì)話框中的【文本】編輯框中，對(duì)[OWNE7④如果需要驗(yàn)證存儲(chǔ)過(guò)程中的SQL語(yǔ)句的正確性，可以單擊【檢查語(yǔ)法】按鈕，如果沒(méi)有語(yǔ)法錯(cuò)誤則會(huì)彈出如圖8-3所示的“語(yǔ)法檢查成功”的對(duì)話框。圖8-3“語(yǔ)法檢查成功”的提示對(duì)話框⑤當(dāng)存儲(chǔ)過(guò)程中沒(méi)有語(yǔ)法錯(cuò)誤后，可以單擊【確定】按鈕來(lái)保存自定義的存儲(chǔ)過(guò)程。④如果需要驗(yàn)證存儲(chǔ)過(guò)程中的SQL語(yǔ)句的正確性，可以單擊【檢查82．使用CREATEPROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程

該語(yǔ)句可以創(chuàng)建永久使用的存儲(chǔ)過(guò)程，也可以創(chuàng)建局部臨時(shí)過(guò)程、全局臨時(shí)過(guò)程。

語(yǔ)法格式為：

CREATEPROCEDURE存儲(chǔ)過(guò)程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語(yǔ)句[...n]]其中：存儲(chǔ)過(guò)程名稱必須符合標(biāo)識(shí)符命名規(guī)則，而且對(duì)于數(shù)據(jù)庫(kù)以及所有者命名必須唯一。一個(gè)完整的命名不能超過(guò)128個(gè)字符。2．使用CREATEPROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程9【例8-1】使用CREATEPROCEDURE語(yǔ)句創(chuàng)建一個(gè)名稱為proc_bjrs的存儲(chǔ)過(guò)程，用于檢索現(xiàn)有班級(jí)及人數(shù)。

CREATEPROCEDUREproc_bjrsAS

SELECTDISTINCT(班級(jí)表.班級(jí)編號(hào)),班級(jí)表.班級(jí)名稱，人數(shù)=COUNT(學(xué)生基本信息表.學(xué)號(hào))

FROM學(xué)生基本信息表,班級(jí)表

WHERE班級(jí)表.班級(jí)編號(hào)=學(xué)生基本信息表.班級(jí)編號(hào)

GROUPBY班級(jí)表.班級(jí)編號(hào),班級(jí)表.班級(jí)名稱

GO

在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲(chǔ)過(guò)程EXECUTEproc_bjrs，運(yùn)行結(jié)果如圖8-4所示?！纠?-1】使用CREATEPROCEDURE語(yǔ)句創(chuàng)建一個(gè)10圖8-4檢索現(xiàn)有班級(jí)以及人數(shù)圖8-4檢索現(xiàn)有班級(jí)以及人數(shù)11【例8-2】設(shè)計(jì)一個(gè)帶有參數(shù)的存儲(chǔ)過(guò)程，該參數(shù)用于傳遞班級(jí)編號(hào)，根據(jù)該參數(shù)在學(xué)生基本信息表中檢索出某一個(gè)班級(jí)中所有學(xué)生的信息。

CREATEPROCEDUREproc_bjcx@bjbhVARCHAR(8)

AS

SELECT*FROM學(xué)生基本信息表WHERE班級(jí)編號(hào)=@bjbh

GO

其中參數(shù)@bjbh用于接收班級(jí)編號(hào)，在實(shí)際調(diào)用中根據(jù)該參數(shù)將會(huì)返回一個(gè)結(jié)果集。例如，EXECUTEproc_bjcx'20051003'，將會(huì)顯示班級(jí)編號(hào)為20051003的學(xué)生信息。運(yùn)行結(jié)果如圖8-5所示【例8-2】設(shè)計(jì)一個(gè)帶有參數(shù)的存儲(chǔ)過(guò)程，該參數(shù)用于傳遞班級(jí)編12圖8-5用于傳遞班級(jí)編號(hào)的存儲(chǔ)過(guò)程圖8-5用于傳遞班級(jí)編號(hào)的存儲(chǔ)過(guò)程138.1.3修改與刪除存儲(chǔ)過(guò)程

在SQLServer中，可以通過(guò)企業(yè)管理器或SQL語(yǔ)句兩種方式修改或刪除存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器修改存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過(guò)程的列表。右擊需要修改的存儲(chǔ)過(guò)程，在彈出菜單中選擇【屬性】，彈出存儲(chǔ)過(guò)程屬性對(duì)話框，如圖8-6所示。圖8-6【存儲(chǔ)過(guò)程屬性】對(duì)話框8.1.3修改與刪除存儲(chǔ)過(guò)程

在SQLServer中，14③在【存儲(chǔ)過(guò)程屬性】對(duì)話框的【文本】編輯框中修改包含的SQL語(yǔ)句。需要注意的是，存儲(chǔ)過(guò)程的名稱不能修改。

④在檢查了語(yǔ)法的正確性之后，可以單擊【確定】按鈕保存修改并關(guān)閉對(duì)話框。

2．使用企業(yè)管理器重命名存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過(guò)程的列表。右擊需要重命名的存儲(chǔ)過(guò)程，并選擇彈出菜單中的【重命名】命令，如圖8-7所示。③在【存儲(chǔ)過(guò)程屬性】對(duì)話框的【文本】編輯框中修改包含的SQL15圖8-7彈出菜單中的【重命名】命令③重新輸入存儲(chǔ)過(guò)程的名稱后，按下回車鍵完成修改。圖8-7彈出菜單中的【重命名】命令③重新輸入存儲(chǔ)過(guò)程的名16

3．使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程

ALTERPROCEDURE語(yǔ)句可以對(duì)數(shù)據(jù)庫(kù)中已有的存儲(chǔ)過(guò)程進(jìn)行修該，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。該語(yǔ)句的語(yǔ)法格式為：

ALTERPROCEDURE存儲(chǔ)過(guò)程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語(yǔ)句[...n]]

其中，存儲(chǔ)過(guò)程名稱為數(shù)據(jù)庫(kù)中已經(jīng)存在的過(guò)程名，語(yǔ)法中的常用選項(xiàng)與CREATEPROCEDURE語(yǔ)句一致。

3．使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程

A17【例8-3】修改已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程proc_bjcx及其功能，將其修改為可以根據(jù)系部編號(hào)來(lái)查詢某一個(gè)系部中所有學(xué)生的信息。修改后將存儲(chǔ)過(guò)程名稱重命名為proc_xbcx。

在查詢分析器中運(yùn)行如下命令，即可完成重命名：

ALTERPROCEDUREproc_bjcx@xbbhVARCHAR(2)

AS

SELECT*FROM學(xué)生基本信息表

WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROM班級(jí)表WHERE系部編號(hào)=@xbbh)

GO

SP_RENAME'proc_bjcx','proc_xbcx'

GO【例8-3】修改已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程proc_bjcx及其功能18使用SP_RENAME可以重新命名一個(gè)現(xiàn)有的存儲(chǔ)過(guò)程。SP_RENAME的語(yǔ)法格式為：

SP_RENAME原名稱,新名稱。

完成修改后查詢系部編號(hào)為’01’的學(xué)生信息。

EXECproc_xbcx'01'

4．使用企業(yè)管理器刪除存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中顯示出存儲(chǔ)過(guò)程的列表。右擊需要?jiǎng)h除的存儲(chǔ)過(guò)程，并在彈出菜單中選擇【刪除】命令，將彈出【除去對(duì)象】對(duì)話框，如圖8-8所示。

③在【除去對(duì)象】對(duì)話框中單擊【全部除去】按鈕將會(huì)完成對(duì)存儲(chǔ)過(guò)程的刪除操作。使用SP_RENAME可以重新命名一個(gè)現(xiàn)有的存儲(chǔ)過(guò)程。SP_19圖8-8【除去對(duì)象】對(duì)話框圖8-8【除去對(duì)象】對(duì)話框205．使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程

使用DROPPROCEDURE語(yǔ)句可以從當(dāng)前的數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)存儲(chǔ)過(guò)程。DROPPROCEDURE語(yǔ)句的語(yǔ)法格式為：

DROPPROCEDURE{存儲(chǔ)過(guò)程名稱

}[,...n]

【例8-4】同時(shí)刪除當(dāng)前數(shù)據(jù)庫(kù)中的兩個(gè)存儲(chǔ)過(guò)程，proc_a和proc_b。

在查詢分析器中運(yùn)行如下命令，即可完成刪除操作：

DROPPROCEDUREproc_a,proc_b

GO5．使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程

使用D218.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡(jiǎn)介

觸發(fā)器屬于一種特殊的存儲(chǔ)過(guò)程，可以在其中包含復(fù)雜的SQL語(yǔ)句。觸發(fā)器與存儲(chǔ)過(guò)程的區(qū)別在于觸發(fā)器能夠自動(dòng)執(zhí)行并且不含有參數(shù)。通?？梢栽谟|發(fā)器內(nèi)編寫一段自動(dòng)執(zhí)行的程序，用于保證數(shù)據(jù)操作的完整性，從而擴(kuò)展了對(duì)默認(rèn)值、約束和規(guī)則的完整性檢查。對(duì)表進(jìn)行包括添加數(shù)據(jù)、刪除數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時(shí)，觸發(fā)器就會(huì)自動(dòng)執(zhí)行。

觸發(fā)器可以劃分為三種類別：INSERT觸發(fā)器、DELETE觸發(fā)器、UPDATE觸發(fā)器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、刪除、修改行為時(shí)自動(dòng)執(zhí)行。8.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡(jiǎn)222．觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器有助于在添加、更新或刪除表中的記錄時(shí)保留表之間已定義的關(guān)系。觸發(fā)器可以自動(dòng)調(diào)用，當(dāng)發(fā)生了對(duì)數(shù)據(jù)所作的任何修改時(shí)，與之相關(guān)的觸發(fā)器就會(huì)立刻被激活?？梢酝瓿蓴?shù)據(jù)庫(kù)中相關(guān)表之間的級(jí)聯(lián)修改。級(jí)聯(lián)修改是指為了保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對(duì)一張表進(jìn)行修改的同時(shí)，其他表中需要進(jìn)行的修改能夠自動(dòng)實(shí)現(xiàn)。觸發(fā)器可以對(duì)需要存儲(chǔ)的數(shù)據(jù)加以限制，并且能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的功能。在CHECK約束中不允許引用其他表中的列，而觸發(fā)器可以引用。3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。

后觸發(fā)：指能夠引發(fā)觸發(fā)器的修改操作在完成之后才執(zhí)行觸發(fā)器的行為方式。創(chuàng)建該類觸發(fā)器，需要使用AFTER關(guān)鍵字或者FOR關(guān)鍵字。

2．觸發(fā)器的優(yōu)點(diǎn)3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式23替代觸發(fā)：指定執(zhí)行一個(gè)觸發(fā)器，而不是執(zhí)行SQL語(yǔ)句，這種替代觸發(fā)語(yǔ)句的方式稱為替代觸發(fā)方式。8.2.3創(chuàng)建觸發(fā)器

可以使用企業(yè)管理器以及CREATETRIGGER語(yǔ)句兩種方式來(lái)創(chuàng)建觸發(fā)器。

1．使用企業(yè)管理器創(chuàng)建觸發(fā)器

①打開企業(yè)管理器，在控制臺(tái)根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表中選擇其中的一張表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將打開圖8-9所示的【觸發(fā)器屬性】對(duì)話框。替代觸發(fā)：指定執(zhí)行一個(gè)觸發(fā)器，而不是執(zhí)行SQL語(yǔ)句，這種替代24圖8-9【觸發(fā)器屬性】對(duì)話框圖8-9【觸發(fā)器屬性】對(duì)話框25④在【觸發(fā)器屬性】對(duì)話框的文本框中，將[TRIGGERNAME]修改為新建觸發(fā)器的名稱，并輸入觸發(fā)器所包含的SQL語(yǔ)句。

⑤通過(guò)點(diǎn)擊【檢查語(yǔ)法】按鈕來(lái)判斷觸發(fā)器的語(yǔ)法是否正確，最后，點(diǎn)擊【確定】按鈕保存新建的觸發(fā)器。

2．使用CREATETRIGGER語(yǔ)句創(chuàng)建觸發(fā)器

CREATETRIGGER語(yǔ)句的部分語(yǔ)法格式：

CREATETRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

FOR|AFTER|INSTEADOF[DELETE][,][INSERT][,][UPDATE]

AS

SQL語(yǔ)句[

...n]④在【觸發(fā)器屬性】對(duì)話框的文本框中，將[TRIGGERNA26

其中：觸發(fā)器名稱必須符合標(biāo)識(shí)符命名規(guī)則，并且在同一個(gè)數(shù)據(jù)庫(kù)中觸發(fā)器的名稱不允許重復(fù)。觸發(fā)器名稱中可以省略所有者名稱。表名或視圖名稱是指建立觸發(fā)器所依賴的對(duì)象。也可以稱為觸發(fā)器表或觸發(fā)器視圖。WITHENCRYPTION該選項(xiàng)的作用為對(duì)觸發(fā)器中所包含的SQL語(yǔ)句進(jìn)行加密。FOR或AFTER代表后觸發(fā)方式，即當(dāng)滿足所有的引用級(jí)聯(lián)操作和約束檢查后并且完成了SQL語(yǔ)句中指定的所有操作后，指定的觸發(fā)器才會(huì)被執(zhí)行。在視圖上不允許采用后觸發(fā)方式。INSTEADOF表示替代觸發(fā)方式，每一個(gè)INSERT、UPDATE、DELETE語(yǔ)句只能定義一個(gè)INSTEADOF觸發(fā)器。其中：27DELETE、INSERT、UPDATE三個(gè)選項(xiàng)中應(yīng)該至少選擇其一，用于表示在表或視圖上執(zhí)行了哪一類的操作時(shí)會(huì)將觸發(fā)器激活。SQL語(yǔ)句為觸發(fā)器中包含的條件以及需要執(zhí)行的操作。在執(zhí)行觸發(fā)器時(shí)，系統(tǒng)會(huì)自動(dòng)創(chuàng)建兩張臨時(shí)表INSERTED、DELETED，這兩張表的結(jié)構(gòu)與觸發(fā)器所依賴的表類似，用于保存在用戶操作過(guò)程中被插入或被刪除的數(shù)據(jù)。每一個(gè)觸發(fā)器在執(zhí)行過(guò)程中都會(huì)產(chǎn)生與之相關(guān)的上述兩張臨時(shí)表，并且在執(zhí)行結(jié)束后，兩個(gè)臨時(shí)表會(huì)自動(dòng)被系統(tǒng)刪除。對(duì)臨時(shí)表可以進(jìn)行查詢操作，如SELECT*FROMDELETED，但是不能對(duì)臨時(shí)表進(jìn)行修改DELETE、INSERT、UPDATE三個(gè)選項(xiàng)中應(yīng)該至28【例8-5】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在班級(jí)表中刪除某一個(gè)班級(jí)時(shí)，在學(xué)生基本信息表中該班級(jí)所包含的學(xué)生信息也全部被刪除。

提示：在此例中，由于涉及到了班級(jí)表的刪除操作，因而需要設(shè)計(jì)一個(gè)DELETE類型的觸發(fā)器。

在查詢分析器中運(yùn)行如下命令：

USEXSCJ

GO

CREATETRIGGERdel_bjON班級(jí)表

AFTERDELETE

AS

DELETEFROM學(xué)生基本信息表WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROMDELETED)

GO

運(yùn)行結(jié)果如圖8-10所示?！纠?-5】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用29圖8-10一個(gè)DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器能夠保證在學(xué)生基本信息表中添加新的紀(jì)錄時(shí)，新學(xué)生的班級(jí)編號(hào)必須已經(jīng)存在于班級(jí)表中。圖8-10一個(gè)DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)30提示：設(shè)計(jì)該觸發(fā)器有助于實(shí)現(xiàn)學(xué)生信息的完整性。在此例中由于涉及到了學(xué)生基本信息表中的添加操作，因而需要設(shè)計(jì)一個(gè)INSERT類型的觸發(fā)器。

在查詢分析器中運(yùn)行如下命令：USEXSCJ

GO

CREATETRIGGERinsert_xsON學(xué)生基本信息表

AFTERINSERT

AS

IFEXISTS

(

SELECT*FROMINSERTED

WHERE班級(jí)編號(hào)IN(SELECT班級(jí)編號(hào)FROM班級(jí)表)

)

PRINT'添加成功！'

ELSE

BEGIN

PRINT'班級(jí)編號(hào)與現(xiàn)有的班級(jí)不符！'

ROLLBACKTRANSACTION

END提示：設(shè)計(jì)該觸發(fā)器有助于實(shí)現(xiàn)學(xué)生信息的完整性。在此例中由于涉31運(yùn)行結(jié)果如圖8-11所示。圖8-11一個(gè)INSERT類型的觸發(fā)器

運(yùn)行結(jié)果如圖8-11所示。圖8-11一個(gè)INSE32創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄進(jìn)行測(cè)試，例如：

INSERTINTO學(xué)生基本信息表(學(xué)號(hào),姓名,性別,族別,班級(jí)編號(hào))VALUES('000108','王松濤','男','漢','20081001')

由于“班級(jí)表”中不存在編號(hào)為20081001的班級(jí)，因而添加操作將會(huì)被取消。ROLLBACKTRANSACTION用于回滾已經(jīng)完成的操作。運(yùn)行結(jié)果如圖8-12所示。圖8-12添加操作被取消創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄338.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器

①在控制臺(tái)根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表中選擇某一張需要對(duì)其所建立的觸發(fā)器進(jìn)行修該的表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將打開【觸發(fā)器】屬性對(duì)話框，如圖8-13所示。

④在【觸發(fā)器屬性】對(duì)話框中，在【名稱】下拉列表框選擇一個(gè)已有的觸發(fā)器，并在【文本】編輯框中對(duì)其所包含的SQL語(yǔ)句進(jìn)行修改。8.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器34圖8-13【觸發(fā)器】屬性對(duì)話框圖8-13【觸發(fā)器】屬性對(duì)話框35⑤在語(yǔ)法檢查無(wú)誤的情況下，點(diǎn)擊【確定】按鈕保存修改。

⑥如果需要?jiǎng)h除當(dāng)前觸發(fā)器，可以點(diǎn)擊【刪除】按鈕。

2．使用ALTERTRIGGER語(yǔ)句修改觸發(fā)器

ALTERTRIGGER語(yǔ)句與CREATETRIGGER語(yǔ)句的語(yǔ)法格式及其參數(shù)類似，部分語(yǔ)法格式為：

ALTERTRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

FOR|AFTER|INSTEADOF[DELETE][,][INSERT][,][UPDATE]

AS

SQL語(yǔ)句[

...n]⑤在語(yǔ)法檢查無(wú)誤的情況下，點(diǎn)擊【確定】按鈕保存修改。

⑥如果36【例8-7】修改del_bj觸發(fā)器，使得在班級(jí)表中刪除某一個(gè)班級(jí)時(shí)，不僅在學(xué)生基本信息表中該班級(jí)所包含的學(xué)生信息將被刪除，而且在成績(jī)表中與學(xué)生相關(guān)的數(shù)據(jù)也將被刪除。

在查詢分析器中運(yùn)行如下命令：

ALTERTRIGGERdel_bjON班級(jí)表

AFTERDELETE

AS

DELETEFROM成績(jī)表WHERE學(xué)號(hào)

IN(SELECT學(xué)號(hào)FROM學(xué)生基本信息表WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROMDELETED))

DELETEFROM學(xué)生基本信息表WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROMDELETED)

運(yùn)行結(jié)果如圖8-14所示。【例8-7】修改del_bj觸發(fā)器，使得在班級(jí)表中刪除某一個(gè)37圖8-14修改del_bj觸發(fā)器3．使用DROPTRIGGER語(yǔ)句刪除觸發(fā)器

移除觸發(fā)器時(shí)，與觸發(fā)器有關(guān)的信息將從sysobjects和syscomments系統(tǒng)表中刪除?？梢酝ㄟ^(guò)刪除觸發(fā)器或刪除表兩種方式移除觸發(fā)器。刪除表時(shí)，將除去所有與表相關(guān)聯(lián)的觸發(fā)器。圖8-14修改del_bj觸發(fā)器3．使用DROPTR38DROPTRIGGER語(yǔ)句的語(yǔ)法格式為：

DROPTRIGGER觸發(fā)器名稱[,...n]

觸發(fā)器名稱為數(shù)據(jù)庫(kù)中現(xiàn)有的觸發(fā)器?？梢灾付ㄒ粋€(gè)或多個(gè)觸發(fā)器名稱來(lái)刪除一個(gè)或多個(gè)觸發(fā)器?！纠?-8】刪除觸發(fā)器del_bj以及insert_xs。

在查詢分析器中運(yùn)行如下命令：

USEXSCJ

GO

DROPTRIGGERdel_bj,insert_xs

運(yùn)行結(jié)果如圖8-15所示。DROPTRIGGER語(yǔ)句的語(yǔ)法格式為：

DROPTRI39圖8-15刪除觸發(fā)器del_bj以及insert_xs圖8-15刪除觸發(fā)器del_bj以及insert_xs40本章小結(jié)

本章學(xué)習(xí)了存儲(chǔ)過(guò)程與觸發(fā)器的相關(guān)知識(shí)，存儲(chǔ)過(guò)程中可以將多條SQL語(yǔ)句集中在一起完成復(fù)雜的功能，從而用戶可以在不具有對(duì)數(shù)據(jù)對(duì)象訪問(wèn)的權(quán)限下調(diào)用這些存儲(chǔ)過(guò)程完成的特定的操作。觸發(fā)器可以幫助用戶完成數(shù)據(jù)的驗(yàn)證功能，從而能夠保證數(shù)據(jù)的一致性、完整性。本章小結(jié)

本章學(xué)習(xí)了存儲(chǔ)過(guò)程與觸發(fā)器的相關(guān)知識(shí)，存儲(chǔ)過(guò)程中可41練習(xí)與上機(jī)

一．選擇題

1．下面關(guān)于存儲(chǔ)過(guò)程的描述中哪些是正確的（）

A．自定義存儲(chǔ)過(guò)程與系統(tǒng)存儲(chǔ)過(guò)程名稱可以相同

B．存儲(chǔ)過(guò)程最多能夠支持64層的嵌套

C．命名存儲(chǔ)過(guò)程中的標(biāo)識(shí)符時(shí)，長(zhǎng)度不能超過(guò)256個(gè)字符

D．存儲(chǔ)過(guò)程中參數(shù)的個(gè)數(shù)不能超過(guò)2100

2．用于創(chuàng)建存儲(chǔ)過(guò)程的SQL語(yǔ)句為（）

A．CREATEDATABASEB．CREATETRIGGER

C．CREATEPROCEDURED．CREATETABLE

3．用于修改存儲(chǔ)過(guò)程的SQL語(yǔ)句為（）

A．ALTERTABLEB．ALTERDATABASE

C．ALTERTRIGGERD．ALTERPROCEDURE

練習(xí)與上機(jī)

一．選擇題

1．下面關(guān)于存儲(chǔ)過(guò)程的描述中哪些是正42

4．下列對(duì)觸發(fā)器的描述中哪一個(gè)是錯(cuò)誤的（）

A．觸發(fā)器屬于一種特殊的存儲(chǔ)過(guò)程

B．觸發(fā)器與存儲(chǔ)過(guò)程的區(qū)別在于觸發(fā)器能夠自動(dòng)執(zhí)行并且不含有參數(shù)

C．觸發(fā)器有助于在添加、更新或刪除表中的記錄時(shí)保留表之間已定義的關(guān)系

D．既可以對(duì)INSERTED、DELETED臨時(shí)表進(jìn)行查詢，也可以進(jìn)行修改

4．下列對(duì)觸發(fā)器的描述中哪一個(gè)是錯(cuò)誤的（）

A．觸發(fā)器43

二．填空題

1．一個(gè)存儲(chǔ)過(guò)程的名稱不能超過(guò)_______個(gè)字符。

2．使用_______語(yǔ)句可以對(duì)存儲(chǔ)過(guò)程進(jìn)行重命名。

3．觸發(fā)器有_______、_______觸發(fā)方式。

4．用_________語(yǔ)句可以刪除觸發(fā)器。

5．觸發(fā)器可以劃分為三種類別____________、____________、____________。

三．簡(jiǎn)答題

1．簡(jiǎn)述什么是存儲(chǔ)過(guò)程？

2．簡(jiǎn)要說(shuō)明存儲(chǔ)過(guò)程的語(yǔ)法格式？

3．簡(jiǎn)述什么是觸發(fā)器？

4．簡(jiǎn)要說(shuō)明觸發(fā)器的語(yǔ)法格式？

二．填空題

1．一個(gè)存儲(chǔ)過(guò)程的名稱不能超過(guò)_______個(gè)44項(xiàng)目實(shí)訓(xùn)

1．創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)根據(jù)系部的編號(hào)查詢出系部中男生、女生的人數(shù)。

2．創(chuàng)建一個(gè)INSERT觸發(fā)器，該觸發(fā)器能夠在向成績(jī)表中添加數(shù)據(jù)時(shí)，自動(dòng)判斷學(xué)號(hào)、課程編號(hào)、成績(jī)是否合法，如果非法則對(duì)插入操作進(jìn)行回滾。

3.修改存儲(chǔ)過(guò)程的名字。

4．建立一個(gè)存儲(chǔ)過(guò)程，來(lái)修改student數(shù)據(jù)庫(kù)的c、sc兩張表，當(dāng)用戶修改c表中的cno后，修改cno表中對(duì)應(yīng)的數(shù)據(jù)。項(xiàng)目實(shí)訓(xùn)

1．創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程能夠?qū)崿F(xiàn)根據(jù)系部的45第8章存儲(chǔ)過(guò)程與觸發(fā)器第8章存儲(chǔ)過(guò)程與觸發(fā)器46本章學(xué)習(xí)目標(biāo)l理解存儲(chǔ)過(guò)程和觸發(fā)器的作用l

熟練創(chuàng)建和管理存儲(chǔ)過(guò)程及觸發(fā)器l

靈活運(yùn)用存儲(chǔ)過(guò)程和觸發(fā)器提高系統(tǒng)開發(fā)效率本章學(xué)習(xí)目標(biāo)47

8.1存儲(chǔ)過(guò)程

8.1.1存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)

1．存儲(chǔ)過(guò)程簡(jiǎn)介

存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的由SQL語(yǔ)句和控制流語(yǔ)句組成的一個(gè)預(yù)編譯集合。存儲(chǔ)過(guò)程劃分為系統(tǒng)存儲(chǔ)過(guò)程以及用戶存儲(chǔ)過(guò)程。一個(gè)存儲(chǔ)過(guò)程可以作為一個(gè)獨(dú)立的單元進(jìn)行處理。在SQLServer中可以通過(guò)存儲(chǔ)過(guò)程來(lái)完成很多管理任務(wù)，利用一些常用的系統(tǒng)存儲(chǔ)過(guò)程可以很方便的查看、操作數(shù)據(jù)庫(kù)對(duì)象。系統(tǒng)存儲(chǔ)過(guò)程的命名通常以”sp_”作為前綴，并且存儲(chǔ)于Master數(shù)據(jù)庫(kù)中，如果用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫(kù)中調(diào)用這些存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程提供了一種封裝某一個(gè)需要重復(fù)執(zhí)行任務(wù)的方法。一旦定義了一個(gè)存儲(chǔ)過(guò)程之后，在應(yīng)用程序中就可以對(duì)其進(jìn)行調(diào)用。在存儲(chǔ)過(guò)程中，不僅可以包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢，而且也可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集。8.1存儲(chǔ)過(guò)程

8.1.1存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)

1．存48所以通過(guò)設(shè)計(jì)自定義用戶存儲(chǔ)過(guò)程，用戶可以使其實(shí)現(xiàn)強(qiáng)大的編程功能。

使用存儲(chǔ)過(guò)程可以比單獨(dú)的SQL語(yǔ)句完成更為復(fù)雜的功能，并且系統(tǒng)會(huì)對(duì)存儲(chǔ)過(guò)程中的SQL語(yǔ)句進(jìn)行了預(yù)編譯處理，使得執(zhí)行速度有了大幅度的提升。存儲(chǔ)過(guò)程被第一次調(diào)用后，會(huì)保存在高速緩沖區(qū)中，這樣再次執(zhí)行同一個(gè)存儲(chǔ)過(guò)程時(shí)，會(huì)提高了重復(fù)調(diào)用的效率。在實(shí)際應(yīng)用中，可以將復(fù)雜的商業(yè)規(guī)則封裝在存儲(chǔ)過(guò)程中，從而提高程序語(yǔ)句的利用率。

2．存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程一旦執(zhí)行一次后，其執(zhí)行的計(jì)劃就會(huì)駐留在計(jì)算機(jī)的高速緩沖存儲(chǔ)器中。其后對(duì)同一個(gè)存儲(chǔ)過(guò)程的調(diào)用就可以直接利用編譯后在高速緩存中的二進(jìn)制形式來(lái)完成操作?？梢栽趩蝹€(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列SQL語(yǔ)句，因而可以用于設(shè)計(jì)、封裝企業(yè)的功能模塊。應(yīng)用程序使用相同的存儲(chǔ)過(guò)程進(jìn)行操作，確保了在數(shù)據(jù)訪問(wèn)、操縱的一致性。所以通過(guò)設(shè)計(jì)自定義用戶存儲(chǔ)過(guò)程，用戶可以使其實(shí)49可以在當(dāng)前的存儲(chǔ)過(guò)程內(nèi)部引用其它存儲(chǔ)過(guò)程，這樣可以將復(fù)雜語(yǔ)句進(jìn)行簡(jiǎn)化。提供了一種數(shù)據(jù)庫(kù)訪問(wèn)的途徑，只需要用戶擁有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，那么通過(guò)使用存儲(chǔ)過(guò)程就可以完成對(duì)數(shù)據(jù)庫(kù)的各種操作，如添加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等，而不需要考慮用戶是否擁有存儲(chǔ)過(guò)程所處理的數(shù)據(jù)對(duì)象的訪問(wèn)權(quán)限。3．使用存儲(chǔ)過(guò)程的注意事項(xiàng)在命名自定義存儲(chǔ)過(guò)程時(shí)避免與系統(tǒng)存儲(chǔ)過(guò)程名相同。存儲(chǔ)過(guò)程最多能夠支持32層的嵌套。命名存儲(chǔ)過(guò)程中的標(biāo)識(shí)符時(shí)，長(zhǎng)度不能超過(guò)128個(gè)字符。存儲(chǔ)過(guò)程中參數(shù)的個(gè)數(shù)不能超過(guò)2100。3．使用存儲(chǔ)過(guò)程的注意事項(xiàng)508.1.2創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程

在SQLServer中，既可以通過(guò)企業(yè)管理器，也可以通過(guò)使用CREATEPROCEDRUE語(yǔ)句的方式來(lái)創(chuàng)建存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程

步驟：

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)并在該節(jié)點(diǎn)上點(diǎn)擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲(chǔ)過(guò)程】級(jí)聯(lián)菜單，將彈出如圖8-1所示的對(duì)話框。圖8-1新建存儲(chǔ)過(guò)程對(duì)話框8.1.2創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程

在SQLServer中，51③在【存儲(chǔ)過(guò)程屬性】對(duì)話框中的【文本】編輯框中，對(duì)[OWNER]以及[PROCEDURENAME]部分進(jìn)行修改，分別修改為該存儲(chǔ)過(guò)程的所有者和存儲(chǔ)過(guò)程名稱，并且輸入該存儲(chǔ)過(guò)程所包含的SQL語(yǔ)句。如圖8-2所示為創(chuàng)建了一個(gè)用于查詢學(xué)生信息的存儲(chǔ)過(guò)程。圖8-2用于查詢學(xué)生信息的存儲(chǔ)過(guò)程③在【存儲(chǔ)過(guò)程屬性】對(duì)話框中的【文本】編輯框中，對(duì)[OWNE52④如果需要驗(yàn)證存儲(chǔ)過(guò)程中的SQL語(yǔ)句的正確性，可以單擊【檢查語(yǔ)法】按鈕，如果沒(méi)有語(yǔ)法錯(cuò)誤則會(huì)彈出如圖8-3所示的“語(yǔ)法檢查成功”的對(duì)話框。圖8-3“語(yǔ)法檢查成功”的提示對(duì)話框⑤當(dāng)存儲(chǔ)過(guò)程中沒(méi)有語(yǔ)法錯(cuò)誤后，可以單擊【確定】按鈕來(lái)保存自定義的存儲(chǔ)過(guò)程。④如果需要驗(yàn)證存儲(chǔ)過(guò)程中的SQL語(yǔ)句的正確性，可以單擊【檢查532．使用CREATEPROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程

該語(yǔ)句可以創(chuàng)建永久使用的存儲(chǔ)過(guò)程，也可以創(chuàng)建局部臨時(shí)過(guò)程、全局臨時(shí)過(guò)程。

語(yǔ)法格式為：

CREATEPROCEDURE存儲(chǔ)過(guò)程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語(yǔ)句[...n]]其中：存儲(chǔ)過(guò)程名稱必須符合標(biāo)識(shí)符命名規(guī)則，而且對(duì)于數(shù)據(jù)庫(kù)以及所有者命名必須唯一。一個(gè)完整的命名不能超過(guò)128個(gè)字符。2．使用CREATEPROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程54【例8-1】使用CREATEPROCEDURE語(yǔ)句創(chuàng)建一個(gè)名稱為proc_bjrs的存儲(chǔ)過(guò)程，用于檢索現(xiàn)有班級(jí)及人數(shù)。

CREATEPROCEDUREproc_bjrsAS

SELECTDISTINCT(班級(jí)表.班級(jí)編號(hào)),班級(jí)表.班級(jí)名稱，人數(shù)=COUNT(學(xué)生基本信息表.學(xué)號(hào))

FROM學(xué)生基本信息表,班級(jí)表

WHERE班級(jí)表.班級(jí)編號(hào)=學(xué)生基本信息表.班級(jí)編號(hào)

GROUPBY班級(jí)表.班級(jí)編號(hào),班級(jí)表.班級(jí)名稱

GO

在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲(chǔ)過(guò)程EXECUTEproc_bjrs，運(yùn)行結(jié)果如圖8-4所示。【例8-1】使用CREATEPROCEDURE語(yǔ)句創(chuàng)建一個(gè)55圖8-4檢索現(xiàn)有班級(jí)以及人數(shù)圖8-4檢索現(xiàn)有班級(jí)以及人數(shù)56【例8-2】設(shè)計(jì)一個(gè)帶有參數(shù)的存儲(chǔ)過(guò)程，該參數(shù)用于傳遞班級(jí)編號(hào)，根據(jù)該參數(shù)在學(xué)生基本信息表中檢索出某一個(gè)班級(jí)中所有學(xué)生的信息。

CREATEPROCEDUREproc_bjcx@bjbhVARCHAR(8)

AS

SELECT*FROM學(xué)生基本信息表WHERE班級(jí)編號(hào)=@bjbh

GO

其中參數(shù)@bjbh用于接收班級(jí)編號(hào)，在實(shí)際調(diào)用中根據(jù)該參數(shù)將會(huì)返回一個(gè)結(jié)果集。例如，EXECUTEproc_bjcx'20051003'，將會(huì)顯示班級(jí)編號(hào)為20051003的學(xué)生信息。運(yùn)行結(jié)果如圖8-5所示【例8-2】設(shè)計(jì)一個(gè)帶有參數(shù)的存儲(chǔ)過(guò)程，該參數(shù)用于傳遞班級(jí)編57圖8-5用于傳遞班級(jí)編號(hào)的存儲(chǔ)過(guò)程圖8-5用于傳遞班級(jí)編號(hào)的存儲(chǔ)過(guò)程588.1.3修改與刪除存儲(chǔ)過(guò)程

在SQLServer中，可以通過(guò)企業(yè)管理器或SQL語(yǔ)句兩種方式修改或刪除存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器修改存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過(guò)程的列表。右擊需要修改的存儲(chǔ)過(guò)程，在彈出菜單中選擇【屬性】，彈出存儲(chǔ)過(guò)程屬性對(duì)話框，如圖8-6所示。圖8-6【存儲(chǔ)過(guò)程屬性】對(duì)話框8.1.3修改與刪除存儲(chǔ)過(guò)程

在SQLServer中，59③在【存儲(chǔ)過(guò)程屬性】對(duì)話框的【文本】編輯框中修改包含的SQL語(yǔ)句。需要注意的是，存儲(chǔ)過(guò)程的名稱不能修改。

④在檢查了語(yǔ)法的正確性之后，可以單擊【確定】按鈕保存修改并關(guān)閉對(duì)話框。

2．使用企業(yè)管理器重命名存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過(guò)程的列表。右擊需要重命名的存儲(chǔ)過(guò)程，并選擇彈出菜單中的【重命名】命令，如圖8-7所示。③在【存儲(chǔ)過(guò)程屬性】對(duì)話框的【文本】編輯框中修改包含的SQL60圖8-7彈出菜單中的【重命名】命令③重新輸入存儲(chǔ)過(guò)程的名稱后，按下回車鍵完成修改。圖8-7彈出菜單中的【重命名】命令③重新輸入存儲(chǔ)過(guò)程的名61

3．使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程

ALTERPROCEDURE語(yǔ)句可以對(duì)數(shù)據(jù)庫(kù)中已有的存儲(chǔ)過(guò)程進(jìn)行修該，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。該語(yǔ)句的語(yǔ)法格式為：

ALTERPROCEDURE存儲(chǔ)過(guò)程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語(yǔ)句[...n]]

其中，存儲(chǔ)過(guò)程名稱為數(shù)據(jù)庫(kù)中已經(jīng)存在的過(guò)程名，語(yǔ)法中的常用選項(xiàng)與CREATEPROCEDURE語(yǔ)句一致。

3．使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程

A62【例8-3】修改已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程proc_bjcx及其功能，將其修改為可以根據(jù)系部編號(hào)來(lái)查詢某一個(gè)系部中所有學(xué)生的信息。修改后將存儲(chǔ)過(guò)程名稱重命名為proc_xbcx。

在查詢分析器中運(yùn)行如下命令，即可完成重命名：

ALTERPROCEDUREproc_bjcx@xbbhVARCHAR(2)

AS

SELECT*FROM學(xué)生基本信息表

WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROM班級(jí)表WHERE系部編號(hào)=@xbbh)

GO

SP_RENAME'proc_bjcx','proc_xbcx'

GO【例8-3】修改已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程proc_bjcx及其功能63使用SP_RENAME可以重新命名一個(gè)現(xiàn)有的存儲(chǔ)過(guò)程。SP_RENAME的語(yǔ)法格式為：

SP_RENAME原名稱,新名稱。

完成修改后查詢系部編號(hào)為’01’的學(xué)生信息。

EXECproc_xbcx'01'

4．使用企業(yè)管理器刪除存儲(chǔ)過(guò)程

①在企業(yè)管理器中，展開服務(wù)器組、服務(wù)器以及數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，展開該數(shù)據(jù)庫(kù)后點(diǎn)擊【存儲(chǔ)過(guò)程】，在右側(cè)的窗口中顯示出存儲(chǔ)過(guò)程的列表。右擊需要?jiǎng)h除的存儲(chǔ)過(guò)程，并在彈出菜單中選擇【刪除】命令，將彈出【除去對(duì)象】對(duì)話框，如圖8-8所示。

③在【除去對(duì)象】對(duì)話框中單擊【全部除去】按鈕將會(huì)完成對(duì)存儲(chǔ)過(guò)程的刪除操作。使用SP_RENAME可以重新命名一個(gè)現(xiàn)有的存儲(chǔ)過(guò)程。SP_64圖8-8【除去對(duì)象】對(duì)話框圖8-8【除去對(duì)象】對(duì)話框655．使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程

使用DROPPROCEDURE語(yǔ)句可以從當(dāng)前的數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)存儲(chǔ)過(guò)程。DROPPROCEDURE語(yǔ)句的語(yǔ)法格式為：

DROPPROCEDURE{存儲(chǔ)過(guò)程名稱

}[,...n]

【例8-4】同時(shí)刪除當(dāng)前數(shù)據(jù)庫(kù)中的兩個(gè)存儲(chǔ)過(guò)程，proc_a和proc_b。

在查詢分析器中運(yùn)行如下命令，即可完成刪除操作：

DROPPROCEDUREproc_a,proc_b

GO5．使用DROPPROCEDURE語(yǔ)句刪除存儲(chǔ)過(guò)程

使用D668.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡(jiǎn)介

觸發(fā)器屬于一種特殊的存儲(chǔ)過(guò)程，可以在其中包含復(fù)雜的SQL語(yǔ)句。觸發(fā)器與存儲(chǔ)過(guò)程的區(qū)別在于觸發(fā)器能夠自動(dòng)執(zhí)行并且不含有參數(shù)。通?？梢栽谟|發(fā)器內(nèi)編寫一段自動(dòng)執(zhí)行的程序，用于保證數(shù)據(jù)操作的完整性，從而擴(kuò)展了對(duì)默認(rèn)值、約束和規(guī)則的完整性檢查。對(duì)表進(jìn)行包括添加數(shù)據(jù)、刪除數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時(shí)，觸發(fā)器就會(huì)自動(dòng)執(zhí)行。

觸發(fā)器可以劃分為三種類別：INSERT觸發(fā)器、DELETE觸發(fā)器、UPDATE觸發(fā)器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、刪除、修改行為時(shí)自動(dòng)執(zhí)行。8.2觸發(fā)器

8.2.1觸發(fā)器及其作用

1．觸發(fā)器簡(jiǎn)672．觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器有助于在添加、更新或刪除表中的記錄時(shí)保留表之間已定義的關(guān)系。觸發(fā)器可以自動(dòng)調(diào)用，當(dāng)發(fā)生了對(duì)數(shù)據(jù)所作的任何修改時(shí)，與之相關(guān)的觸發(fā)器就會(huì)立刻被激活?？梢酝瓿蓴?shù)據(jù)庫(kù)中相關(guān)表之間的級(jí)聯(lián)修改。級(jí)聯(lián)修改是指為了保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對(duì)一張表進(jìn)行修改的同時(shí)，其他表中需要進(jìn)行的修改能夠自動(dòng)實(shí)現(xiàn)。觸發(fā)器可以對(duì)需要存儲(chǔ)的數(shù)據(jù)加以限制，并且能夠?qū)崿F(xiàn)比CHECK約束更為復(fù)雜的功能。在CHECK約束中不允許引用其他表中的列，而觸發(fā)器可以引用。3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。

后觸發(fā)：指能夠引發(fā)觸發(fā)器的修改操作在完成之后才執(zhí)行觸發(fā)器的行為方式。創(chuàng)建該類觸發(fā)器，需要使用AFTER關(guān)鍵字或者FOR關(guān)鍵字。

2．觸發(fā)器的優(yōu)點(diǎn)3．觸發(fā)方式

觸發(fā)器的觸發(fā)方式68替代觸發(fā)：指定執(zhí)行一個(gè)觸發(fā)器，而不是執(zhí)行SQL語(yǔ)句，這種替代觸發(fā)語(yǔ)句的方式稱為替代觸發(fā)方式。8.2.3創(chuàng)建觸發(fā)器

可以使用企業(yè)管理器以及CREATETRIGGER語(yǔ)句兩種方式來(lái)創(chuàng)建觸發(fā)器。

1．使用企業(yè)管理器創(chuàng)建觸發(fā)器

①打開企業(yè)管理器，在控制臺(tái)根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表中選擇其中的一張表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將打開圖8-9所示的【觸發(fā)器屬性】對(duì)話框。替代觸發(fā)：指定執(zhí)行一個(gè)觸發(fā)器，而不是執(zhí)行SQL語(yǔ)句，這種替代69圖8-9【觸發(fā)器屬性】對(duì)話框圖8-9【觸發(fā)器屬性】對(duì)話框70④在【觸發(fā)器屬性】對(duì)話框的文本框中，將[TRIGGERNAME]修改為新建觸發(fā)器的名稱，并輸入觸發(fā)器所包含的SQL語(yǔ)句。

⑤通過(guò)點(diǎn)擊【檢查語(yǔ)法】按鈕來(lái)判斷觸發(fā)器的語(yǔ)法是否正確，最后，點(diǎn)擊【確定】按鈕保存新建的觸發(fā)器。

2．使用CREATETRIGGER語(yǔ)句創(chuàng)建觸發(fā)器

CREATETRIGGER語(yǔ)句的部分語(yǔ)法格式：

CREATETRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

FOR|AFTER|INSTEADOF[DELETE][,][INSERT][,][UPDATE]

AS

SQL語(yǔ)句[

...n]④在【觸發(fā)器屬性】對(duì)話框的文本框中，將[TRIGGERNA71

其中：觸發(fā)器名稱必須符合標(biāo)識(shí)符命名規(guī)則，并且在同一個(gè)數(shù)據(jù)庫(kù)中觸發(fā)器的名稱不允許重復(fù)。觸發(fā)器名稱中可以省略所有者名稱。表名或視圖名稱是指建立觸發(fā)器所依賴的對(duì)象。也可以稱為觸發(fā)器表或觸發(fā)器視圖。WITHENCRYPTION該選項(xiàng)的作用為對(duì)觸發(fā)器中所包含的SQL語(yǔ)句進(jìn)行加密。FOR或AFTER代表后觸發(fā)方式，即當(dāng)滿足所有的引用級(jí)聯(lián)操作和約束檢查后并且完成了SQL語(yǔ)句中指定的所有操作后，指定的觸發(fā)器才會(huì)被執(zhí)行。在視圖上不允許采用后觸發(fā)方式。INSTEADOF表示替代觸發(fā)方式，每一個(gè)INSERT、UPDATE、DELETE語(yǔ)句只能定義一個(gè)INSTEADOF觸發(fā)器。其中：72DELETE、INSERT、UPDATE三個(gè)選項(xiàng)中應(yīng)該至少選擇其一，用于表示在表或視圖上執(zhí)行了哪一類的操作時(shí)會(huì)將觸發(fā)器激活。SQL語(yǔ)句為觸發(fā)器中包含的條件以及需要執(zhí)行的操作。在執(zhí)行觸發(fā)器時(shí)，系統(tǒng)會(huì)自動(dòng)創(chuàng)建兩張臨時(shí)表INSERTED、DELETED，這兩張表的結(jié)構(gòu)與觸發(fā)器所依賴的表類似，用于保存在用戶操作過(guò)程中被插入或被刪除的數(shù)據(jù)。每一個(gè)觸發(fā)器在執(zhí)行過(guò)程中都會(huì)產(chǎn)生與之相關(guān)的上述兩張臨時(shí)表，并且在執(zhí)行結(jié)束后，兩個(gè)臨時(shí)表會(huì)自動(dòng)被系統(tǒng)刪除。對(duì)臨時(shí)表可以進(jìn)行查詢操作，如SELECT*FROMDELETED，但是不能對(duì)臨時(shí)表進(jìn)行修改DELETE、INSERT、UPDATE三個(gè)選項(xiàng)中應(yīng)該至73【例8-5】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在班級(jí)表中刪除某一個(gè)班級(jí)時(shí)，在學(xué)生基本信息表中該班級(jí)所包含的學(xué)生信息也全部被刪除。

提示：在此例中，由于涉及到了班級(jí)表的刪除操作，因而需要設(shè)計(jì)一個(gè)DELETE類型的觸發(fā)器。

在查詢分析器中運(yùn)行如下命令：

USEXSCJ

GO

CREATETRIGGERdel_bjON班級(jí)表

AFTERDELETE

AS

DELETEFROM學(xué)生基本信息表WHERE班級(jí)編號(hào)

IN(SELECT班級(jí)編號(hào)FROMDELETED)

GO

運(yùn)行結(jié)果如圖8-10所示?！纠?-5】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用74圖8-10一個(gè)DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)庫(kù)XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器能夠保證在學(xué)生基本信息表中添加新的紀(jì)錄時(shí)，新學(xué)生的班級(jí)編號(hào)必須已經(jīng)存在于班級(jí)表中。圖8-10一個(gè)DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)75提示：設(shè)計(jì)該觸發(fā)器有助于實(shí)現(xiàn)學(xué)生信息的完整性。在此例中由于涉及到了學(xué)生基本信息表中的添加操作，因而需要設(shè)計(jì)一個(gè)INSERT類型的觸發(fā)器。

在查詢分析器中運(yùn)行如下命令：USEXSCJ

GO

CREATETRIGGERinsert_xsON學(xué)生基本信息表

AFTERINSERT

AS

IFEXISTS

(

SELECT*FROMINSERTED

WHERE班級(jí)編號(hào)IN(SELECT班級(jí)編號(hào)FROM班級(jí)表)

)

PRINT'添加成功！'

ELSE

BEGIN

PRINT'班級(jí)編號(hào)與現(xiàn)有的班級(jí)不符！'

ROLLBACKTRANSACTION

END提示：設(shè)計(jì)該觸發(fā)器有助于實(shí)現(xiàn)學(xué)生信息的完整性。在此例中由于涉76運(yùn)行結(jié)果如圖8-11所示。圖8-11一個(gè)INSERT類型的觸發(fā)器

運(yùn)行結(jié)果如圖8-11所示。圖8-11一個(gè)INSE77創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄進(jìn)行測(cè)試，例如：

INSERTINTO學(xué)生基本信息表(學(xué)號(hào),姓名,性別,族別,班級(jí)編號(hào))VALUES('000108','王松濤','男','漢','20081001')

由于“班級(jí)表”中不存在編號(hào)為20081001的班級(jí)，因而添加操作將會(huì)被取消。ROLLBACKTRANSACTION用于回滾已經(jīng)完成的操作。運(yùn)行結(jié)果如圖8-12所示。圖8-12添加操作被取消創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄788.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器

①在控制臺(tái)根目錄下依次展開服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表中選擇某一張需要對(duì)其所建立的觸發(fā)器進(jìn)行修該的表。

③右擊所選中的表，在彈出的下拉菜單中選擇【所有任務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將打開【觸發(fā)器】屬性對(duì)話框，如圖8-13所示。

④在【觸發(fā)器屬性】對(duì)話框中，在【名稱】下拉列表框選擇一個(gè)已有的觸發(fā)器，并在【文本】編輯框中對(duì)其所包含的SQL語(yǔ)句進(jìn)行修改。8.2.4修改與刪除觸發(fā)器

1．使用企業(yè)管理器修改觸發(fā)器79圖8-13【觸發(fā)器】屬性對(duì)話框圖8-13【觸發(fā)器】屬性對(duì)話框80⑤在語(yǔ)法檢查無(wú)誤的情況下，點(diǎn)擊【確定】按鈕保存修改。

⑥如果需要?jiǎng)h除當(dāng)前觸發(fā)器，可以點(diǎn)擊【刪除】按鈕。

2．使用ALTERTRIGGER語(yǔ)句修改觸發(fā)器

ALTERTRIGGER語(yǔ)句與CREATETRIGGER語(yǔ)句的語(yǔ)法格式及其參數(shù)類似，部分語(yǔ)法格式為：

ALTERTRIGGER觸發(fā)器名稱

ON{表名|視圖名

}

[WITHENCRYPTION]

FOR|AFTER|INSTEADOF[DELETE][,][INSE