第9章存儲(chǔ)過(guò)程和觸發(fā)器

第9章存儲(chǔ)過(guò)程和觸發(fā)器本章導(dǎo)讀：存儲(chǔ)過(guò)程和觸發(fā)器都不是標(biāo)準(zhǔn)的SQL語(yǔ)句，不同的數(shù)據(jù)庫(kù)管理系統(tǒng)在具體實(shí)現(xiàn)時(shí)語(yǔ)法會(huì)有所不同。存儲(chǔ)過(guò)程是一組Transact-SQL語(yǔ)句和可選控制語(yǔ)句的預(yù)編譯集合，它是按名存儲(chǔ)并運(yùn)行于服務(wù)器上，獨(dú)立于表的數(shù)據(jù)庫(kù)對(duì)象。而觸發(fā)器是一種在關(guān)系表上定義，伴隨用戶修改相關(guān)數(shù)據(jù)而自動(dòng)執(zhí)行的特殊存儲(chǔ)過(guò)程，主要用于強(qiáng)化復(fù)雜的規(guī)則和要求。知識(shí)要點(diǎn)：存儲(chǔ)過(guò)程觸發(fā)器9.1存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的一組預(yù)編譯好的T-SQL代碼。存儲(chǔ)過(guò)程可以通過(guò)輸入?yún)?shù)接收調(diào)用程序的實(shí)參輸入，也可以通過(guò)輸出形參將運(yùn)行結(jié)果返回給調(diào)用程序，還可以通過(guò)狀態(tài)參數(shù)判斷存儲(chǔ)過(guò)程的執(zhí)行成功與否。用戶自定義的存儲(chǔ)過(guò)程名存儲(chǔ)于系統(tǒng)表sysobjects中，而存儲(chǔ)過(guò)程中定義的文本內(nèi)容存儲(chǔ)于系統(tǒng)表syscomments中。9.1.1存儲(chǔ)過(guò)程概述9.1.2存儲(chǔ)過(guò)程的創(chuàng)建9.1.1存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程是一個(gè)獨(dú)立于表之外的數(shù)據(jù)庫(kù)對(duì)象，可以作為一個(gè)單元被用戶的應(yīng)用程序調(diào)用。執(zhí)行存儲(chǔ)過(guò)程時(shí)，只需要將參數(shù)傳遞到數(shù)據(jù)庫(kù)中，而不需要將整條SQL語(yǔ)句都提交給數(shù)據(jù)庫(kù)，從而減少了網(wǎng)絡(luò)傳送流量，另外，因?yàn)槭∪チ藞?zhí)行SQL語(yǔ)句時(shí)對(duì)SQL進(jìn)行編譯的時(shí)間，也提高了程序的運(yùn)行速度。SQLServer支持5種類(lèi)型的存儲(chǔ)過(guò)程：1．系統(tǒng)存儲(chǔ)過(guò)程SQLServer提供了大量的系統(tǒng)存儲(chǔ)過(guò)程，很多管理活動(dòng)都是通過(guò)系統(tǒng)存儲(chǔ)過(guò)程實(shí)現(xiàn)的。系統(tǒng)存儲(chǔ)過(guò)程名以sp_為前綴，存儲(chǔ)在master數(shù)據(jù)庫(kù)中，用戶可以在任何數(shù)據(jù)庫(kù)中執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程。另外，用戶可以在master數(shù)據(jù)庫(kù)中定義sp_為前綴的自定義系統(tǒng)存儲(chǔ)過(guò)程。2．用戶存儲(chǔ)過(guò)程是指用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程。為了與系統(tǒng)存儲(chǔ)過(guò)程相區(qū)別，一般不要將用戶存儲(chǔ)過(guò)程名定義為以sp_為前綴的名稱(chēng)。如果用戶自定義的存儲(chǔ)過(guò)程和系統(tǒng)存儲(chǔ)過(guò)程同名，那么用戶存儲(chǔ)過(guò)程永遠(yuǎn)不執(zhí)行。9.1.1存儲(chǔ)過(guò)程概述3．臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程分為局部臨時(shí)存儲(chǔ)過(guò)程和全局臨時(shí)存儲(chǔ)過(guò)程。局部臨時(shí)存儲(chǔ)過(guò)程名稱(chēng)以#為前綴，存放在tempdb數(shù)據(jù)庫(kù)中，只由創(chuàng)建并連接的用戶使用，當(dāng)該用戶斷開(kāi)連接時(shí)將自動(dòng)刪除局部臨時(shí)存儲(chǔ)過(guò)程。全局臨時(shí)存儲(chǔ)過(guò)程名稱(chēng)以##為前綴，存放在tempdb數(shù)據(jù)庫(kù)中，允許所有連接的用戶使用，在所有用戶斷開(kāi)連接時(shí)自動(dòng)被刪除。4．遠(yuǎn)程存儲(chǔ)過(guò)程位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程。5．?dāng)U展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程：利用外部語(yǔ)言（如C語(yǔ)言編寫(xiě)的存儲(chǔ)過(guò)程，以彌補(bǔ)SQLServer的不足之處，擴(kuò)展新的功能，擴(kuò)展存儲(chǔ)過(guò)程名以xp_為前綴。9.1.2存儲(chǔ)過(guò)程的創(chuàng)建SQLServer提供了兩種創(chuàng)建存儲(chǔ)過(guò)程的方法：使用企業(yè)管理器和使用T-SQL語(yǔ)句的CreateProcedure命令。1．使用Transact-SQL語(yǔ)句在SQLServer中，使用T-SQL語(yǔ)句的CreateProcedure命令創(chuàng)建存儲(chǔ)過(guò)程，其格式如下：createproc[edure]<存儲(chǔ)過(guò)程名>[;分組編號(hào)][{@形式參數(shù)數(shù)據(jù)類(lèi)型}[=默認(rèn)值][output][varying]][,...n][with{recompile|encryption|recompile,encryption}][forreplication]assql語(yǔ)句[...n]9.1.2存儲(chǔ)過(guò)程的創(chuàng)建功能：在當(dāng)前數(shù)據(jù)中創(chuàng)建指定名稱(chēng)的存儲(chǔ)過(guò)程。說(shuō)明：（1）存儲(chǔ)過(guò)程名：存儲(chǔ)過(guò)程名須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者是唯一的；（2）分組編號(hào)：整數(shù)，指明同名存儲(chǔ)過(guò)程的分組編號(hào)，以便于一條dropprocedure語(yǔ)句刪除一組同名存儲(chǔ)過(guò)程；（3）@形式參數(shù)：指明形式參數(shù)（形參）名稱(chēng)，形參有輸入?yún)?shù)和輸出參數(shù)之分；（4）數(shù)據(jù)類(lèi)型：指明形參的數(shù)據(jù)類(lèi)型，包括text，ntext和image等數(shù)據(jù)類(lèi)型，當(dāng)形參是輸入?yún)?shù)時(shí)，不能使用cusor（游標(biāo)）數(shù)據(jù)類(lèi)型；（5）默認(rèn)值：指明輸入?yún)?shù)的默認(rèn)值，可以是常量、NULL或字符匹配運(yùn)算符like關(guān)鍵字，當(dāng)輸入?yún)?shù)定義了默認(rèn)值后，調(diào)用語(yǔ)句可以省略實(shí)參，否則必須提供實(shí)參；（6）output：指明形參是輸出參數(shù)，且允許有返回值，否則為輸入?yún)?shù)；9.1.2存儲(chǔ)過(guò)程的創(chuàng)建（7）varying：指明返回值是可變的，當(dāng)形參數(shù)據(jù)類(lèi)型為cursor時(shí)需指定varying選項(xiàng)；（8）recompile|encryption：recompile表示每次重新編譯存儲(chǔ)過(guò)程，而encryption表示加密存儲(chǔ)過(guò)程文本；（9）forreplication：表示創(chuàng)建的存儲(chǔ)過(guò)程只能在復(fù)制過(guò)程中執(zhí)行，而不能在訂閱服務(wù)器上執(zhí)行。forreplication和withencryption不能聯(lián)合使用；（10）as：表示指定要執(zhí)行的操作；（11）sql語(yǔ)句：過(guò)程中包含的任意類(lèi)型和數(shù)目的SQL語(yǔ)句，但有一些限制，如不可以使用創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象的語(yǔ)句。。9.1.2存儲(chǔ)過(guò)程的創(chuàng)建【例9-1】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用來(lái)求任意一個(gè)數(shù)的階乘。usejxglifexists(selectnamefromsysobjectswherename='fact'andtype='p')dropprocfactgocreateprocedurefact@nint,@fintoutputasif@n<0print'你輸入了的'+cast(@nasvarchar(20))+'，請(qǐng)輸入非負(fù)數(shù)'elsebegindeclare@iintset@i=1set@f=1while@i<=@nbegin

set@f=@f*@iset@i=@i+1

endprintcast(@nasvarchar(20))+'的階乘是：'+cast(@fasvarchar(20))end9.1.2存儲(chǔ)過(guò)程的創(chuàng)建2．使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程【例9-2】使用企業(yè)管理器創(chuàng)建一個(gè)打印9乘9乘法表的存儲(chǔ)過(guò)程。操作步驟如下：（1）在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)jxgl節(jié)點(diǎn)中的“存儲(chǔ)過(guò)程”節(jié)點(diǎn)，右擊彈出快捷菜單，如圖9-1所示，單擊“新建存儲(chǔ)過(guò)程”命令后，彈出“存儲(chǔ)過(guò)程屬性”對(duì)話框，如圖9-2所示。9.1.2存儲(chǔ)過(guò)程的創(chuàng)建

圖9-1“企業(yè)管理器”對(duì)話框1 圖9-2“存儲(chǔ)過(guò)程屬性”對(duì)話框9.1.2存儲(chǔ)過(guò)程的創(chuàng)建（2）在文本框中輸入代碼后，如果單擊“檢查語(yǔ)法”按鈕，可以對(duì)輸入的代碼進(jìn)行語(yǔ)法檢查，如圖9-3所示。連續(xù)單擊前后兩個(gè)“確定”按鈕，自動(dòng)保存并返回“企業(yè)管理器”對(duì)話框2，從中可以發(fā)現(xiàn)存儲(chǔ)過(guò)程“multi”，如圖9-4所示。

注意：在企業(yè)管理器中可以對(duì)已創(chuàng)建的存儲(chǔ)過(guò)程，執(zhí)行刪除、重命名操作，也可通過(guò)屬性執(zhí)行查看、修改存儲(chǔ)過(guò)程等操作。9.1.2存儲(chǔ)過(guò)程的創(chuàng)建createproceduremultiasdeclare@iint,@jint,@outvarchar(80)set@i=1while@i<=9beginset@out=cast(@iaschar(1))+')'set@j=1while@j<=@ibeginset@out=@out+cast(@iaschar(1))+'*'+cast(@jaschar(1))+'='+cast(@i*@jaschar(2))+space(2)

set@j=@j+1endprint@out

set@i=@i+1end9.1.3存儲(chǔ)過(guò)程的執(zhí)行在查詢(xún)分析器中可直接使用存儲(chǔ)過(guò)程的名字或使用execute語(yǔ)句執(zhí)行存儲(chǔ)過(guò)程。執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)法格式如下：

[[exec[ute]]{[@返回狀態(tài)值=]{存儲(chǔ)過(guò)程名[;分組編號(hào)]|@存儲(chǔ)過(guò)程名變量}}[[@形式參數(shù)名稱(chēng)=]{實(shí)際參數(shù)值|@實(shí)際參數(shù)變量[output]|[default]}][,...n][withrecompile]說(shuō)明：9.1.3存儲(chǔ)過(guò)程的執(zhí)行（1）execute：執(zhí)行存儲(chǔ)過(guò)程的命令，該命令若是批處理中的第一條，則可以省略。（2）返回狀態(tài)值：是一個(gè)可選的整型局部變量，保存存儲(chǔ)過(guò)程的返回狀態(tài)。這個(gè)變量在用于execute語(yǔ)句時(shí)，必須已在批處理、存儲(chǔ)過(guò)程或函數(shù)中聲明過(guò)；（3）存儲(chǔ)過(guò)程名：要調(diào)用的存儲(chǔ)過(guò)程的名稱(chēng)；（4）@存儲(chǔ)過(guò)程名變量：局部變量名，代表存儲(chǔ)過(guò)程的名稱(chēng)；（5）@形式參數(shù)名稱(chēng)：是指存儲(chǔ)過(guò)程中已定義的形式參數(shù)名稱(chēng)，使用時(shí)注意以下幾點(diǎn)：在使用格式“@形式參數(shù)名稱(chēng)=實(shí)際參數(shù)值|@實(shí)際參數(shù)變量”時(shí)，形式參數(shù)名稱(chēng)及其實(shí)際參數(shù)值不一定按照createprocedure語(yǔ)句中定義的順序出現(xiàn)。在使用格式“實(shí)際參數(shù)值|@實(shí)際參數(shù)變量”時(shí)，即省略“@形式參數(shù)名稱(chēng)”，要求必須按順序傳遞給createprocedure語(yǔ)句中定義的輸入?yún)?shù)；9.1.3存儲(chǔ)過(guò)程的執(zhí)行（6）實(shí)際參數(shù)值：存儲(chǔ)過(guò)程調(diào)用時(shí)傳遞給輸入?yún)?shù)的值；（7）@實(shí)際參數(shù)變量：存儲(chǔ)過(guò)程調(diào)用時(shí)傳遞給輸入?yún)?shù)的實(shí)際參數(shù)變量，其用法同（5），或者用來(lái)保存并返回輸出參數(shù)的變量，此時(shí)需要配合output選項(xiàng)；（8）output：表示@實(shí)際參數(shù)變量是用來(lái)保存并返回輸出參數(shù)的變量；（9）default：表示調(diào)用存儲(chǔ)過(guò)程時(shí)，使用存儲(chǔ)過(guò)程定義時(shí)指定的默認(rèn)值作為輸入?yún)?shù)，實(shí)際調(diào)用可以省略。當(dāng)調(diào)用存儲(chǔ)過(guò)程時(shí)，需要的參數(shù)值沒(méi)有事先定義默認(rèn)值而指定了default關(guān)鍵字，或缺少輸入?yún)?shù)值，都會(huì)出錯(cuò)；（10）withrecompile：表示執(zhí)行存儲(chǔ)過(guò)程時(shí)強(qiáng)制重新編譯，該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過(guò)程。建議盡量少使用該選項(xiàng)，因?yàn)樗妮^多系統(tǒng)資源；【例9-3】執(zhí)行存儲(chǔ)過(guò)程fact。declare@fasfloatexecutefact-3,@foutput --你輸入了的-3，請(qǐng)輸入非負(fù)數(shù)print''executefact3,@foutput --3的階乘是：69.1.4存儲(chǔ)過(guò)程的查看使用系統(tǒng)存儲(chǔ)過(guò)程sp_help，sp_helptext來(lái)查看存儲(chǔ)過(guò)程信息，它們格式與功能如下：格式1：sp_help<存儲(chǔ)過(guò)程名>說(shuō)明：查看存儲(chǔ)過(guò)程的概要信息。格式2：sp_helptext<存儲(chǔ)過(guò)程名>說(shuō)明：查看存儲(chǔ)過(guò)程的定義文本信息?！纠?-4】查看存儲(chǔ)過(guò)程fact的過(guò)程名的概要信息和定義文本信息。usejxglsp_helpfactgosp_helptextfact9.1.5存儲(chǔ)過(guò)程的修改SQLServer提供了兩種修改存儲(chǔ)過(guò)程的方法：使用企業(yè)管理器和使用T-SQL語(yǔ)句的alterprocedure命令。修改存儲(chǔ)過(guò)程的Transact-SQL語(yǔ)句格式如下：alterproc[edure]存儲(chǔ)過(guò)程名[;編號(hào)][{@參數(shù)名數(shù)據(jù)類(lèi)型}[varying][=默認(rèn)值][output]][,...n]with{recompile|encryption|recompile,encryption}]assql語(yǔ)句[...n]說(shuō)明：各參數(shù)含義與createprocedure語(yǔ)句相同。9.1.5存儲(chǔ)過(guò)程的修改【例9-5】修改存儲(chǔ)過(guò)程fact為判斷一個(gè)數(shù)是否是水仙花數(shù)。usejxglgoalterprocfact@nintasif@n<100or@n>999print'你輸入了的'+cast(@nasvarchar(20))+'，請(qǐng)輸入3位正數(shù)'elsebegindeclare@iint,@jint,@kint

set@i=@n/100set@j=(@n-@I*100)/10

set@k=@n%10if@n=@i*@i*@i+@j*@j*@j+@k*@k*@kprintcast(@naschar(3))+'是水仙花數(shù)'elseprintcast(@naschar(3))+'不是水仙花數(shù)'endgo9.1.6存儲(chǔ)過(guò)程的改名SQLServer提供了兩種重命名存儲(chǔ)過(guò)程的方法：使用企業(yè)管理器和使用系統(tǒng)存儲(chǔ)過(guò)程的sp_rename命令。使用sp_rename重命名存儲(chǔ)過(guò)程的語(yǔ)法格式如下：格式：sp_rename[@objname=]'對(duì)象名',[@newname=]'新對(duì)象名'說(shuō)明：（1）[@objname=]'對(duì)象名'：指定存儲(chǔ)過(guò)程的當(dāng)前名稱(chēng)。（2）[@newname=]'新對(duì)象名'：指定存儲(chǔ)過(guò)程的新名稱(chēng)。9.1.6存儲(chǔ)過(guò)程的改名【例9-6】將存儲(chǔ)過(guò)程名fact修改為marquee。sp_renamefact,marqueeexecutemarquee153運(yùn)行結(jié)果如圖9-5所示。圖9-5例9-6運(yùn)行結(jié)果9.1.7存儲(chǔ)過(guò)程的刪除SQLServer提供了兩種刪除存儲(chǔ)過(guò)程的方法：使用企業(yè)管理器和使用T-SQL語(yǔ)句的dropprocedure命令。使用T-SQL語(yǔ)句刪除存儲(chǔ)過(guò)程的語(yǔ)法格式如下：格式：dropprocedure存儲(chǔ)過(guò)程名[;分組編號(hào)]說(shuō)明：刪除指定名稱(chēng)的存儲(chǔ)過(guò)程，如果同時(shí)指定存儲(chǔ)過(guò)程的分組編號(hào)，那么只刪除指定編號(hào)的存儲(chǔ)過(guò)程，否則一組同名的存儲(chǔ)過(guò)程一道刪除。9.1.8存儲(chǔ)過(guò)程的應(yīng)用存儲(chǔ)過(guò)程不僅可以封裝處理數(shù)據(jù)的語(yǔ)句，還可以通過(guò)參數(shù)實(shí)現(xiàn)數(shù)據(jù)傳入和傳出，但存儲(chǔ)過(guò)程的返回值不能作為表達(dá)式一部分，而必須通過(guò)execute語(yǔ)句才能得到存儲(chǔ)過(guò)程的返回值。1．使用存儲(chǔ)過(guò)程封裝處理數(shù)據(jù)的語(yǔ)句【9-7】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢(xún)班級(jí)號(hào)為090102學(xué)生信息的功能。usejxglgocreateprocpro_學(xué)生asselect學(xué)號(hào),姓名,性別,year(getdate())-year(出生日期)as年齡,籍貫

from學(xué)生

whereleft(學(xué)號(hào),6)='090102'go9.1.8存儲(chǔ)過(guò)程的應(yīng)用2．使用帶輸入?yún)?shù)的存儲(chǔ)過(guò)程【9-8】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢(xún)指定學(xué)生姓名成績(jī)信息的功能。usejxglgocreateprocpro_學(xué)生_選修@xmchar(6)asselect學(xué)生.學(xué)號(hào),姓名,課程號(hào),成績(jī)

from學(xué)生,選修

where學(xué)生.學(xué)號(hào)=選修.學(xué)號(hào)and姓名=@xmgo9.1.8存儲(chǔ)過(guò)程的應(yīng)用3．使用輸入?yún)?shù)帶默認(rèn)值的存儲(chǔ)過(guò)程【9-9】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢(xún)指定課程號(hào)的課程及其選修信息。usejxglgo--創(chuàng)建存儲(chǔ)過(guò)程代碼如下：createprocpro_課程_選修@kchchar(2)='01'asselect課程.課程號(hào),課程名稱(chēng),學(xué)號(hào),成績(jī)from課程,選修

where課程.課程號(hào)=選修.課程號(hào)and課程.課程號(hào)=@kch--調(diào)用存儲(chǔ)過(guò)程代碼如下：goexecpro_課程_選修goexecpro_課程_選修defaultgoexecpro_課程_選修'02'go9.1.8存儲(chǔ)過(guò)程的應(yīng)用4．使用帶輸出參數(shù)的存儲(chǔ)過(guò)程【9-10】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢(xún)教師的平均工資。usejxglgo--以下是創(chuàng)建存儲(chǔ)過(guò)程代碼：createprocpro_avg_教師@avgscorefloatoutputasselect@avgscore=avg(基本工資)from教師go--以下是執(zhí)行存儲(chǔ)過(guò)程代碼：declare@avgscorefloatexecpro_avg_教師@avgscoreoutputprint'教師的平均工資：'+cast(@avgscoreasvarchar(6))運(yùn)行結(jié)果如下：警告:聚合或其它SET操作消除了空值。教師的平均工資：22269.1.8存儲(chǔ)過(guò)程的應(yīng)用5．使用帶輸入輸出參數(shù)的存儲(chǔ)過(guò)程【9-11】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)指定姓名時(shí)，查詢(xún)?cè)撋羞x修課程的平均成績(jī)。usejxglgocreateprocpro_avg_成績(jī)@xmchar(6),@avgscorefloatoutputasselect@avgscore=avg(成績(jī))from學(xué)生,選修

where學(xué)生.學(xué)號(hào)=選修.學(xué)號(hào)and姓名=@xmgo--以下是執(zhí)行存儲(chǔ)過(guò)程代碼：declare@xmchar(6),@avgscorefloatset@xm='儲(chǔ)兆雯'execpro_avg_成績(jī)'儲(chǔ)兆雯',@avgscoreoutputprint@avgscore9.1.8存儲(chǔ)過(guò)程的應(yīng)用6．使用輸出參數(shù)是游標(biāo)類(lèi)型的存儲(chǔ)過(guò)程【9-12】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)逐行顯示表“學(xué)生”中的數(shù)據(jù)。（1）創(chuàng)建存儲(chǔ)過(guò)程代碼：usejxglgoifexists(selectnamefromsysobjectswherename='cursor_選修'andtype='p')dropproccursor_選修gocreateproccursor_選修@xhchar(8)='08010101',@js_cursorcursorvaryingoutputasset@js_cursor=cursorforward_onlystaticforselect*from選修where學(xué)號(hào)=@xhopen@js_cursorgo9.1.8存儲(chǔ)過(guò)程的應(yīng)用（2）調(diào)用存儲(chǔ)過(guò)程代碼declare@xhchar(8),@mycursorset@xh='08010101'execcursor_選修@xh,@myoutputwhile(@@fetch_status=0)beginfetchnextfrom@my --提取數(shù)據(jù)

endclose@my --關(guān)閉游標(biāo)deallocate@my --刪除游標(biāo)9.1.8存儲(chǔ)過(guò)程的應(yīng)用（3）運(yùn)行結(jié)果如圖9-5所示。圖9-6例9-12運(yùn)行結(jié)果圖9-6例9-12運(yùn)行結(jié)果9.1.8存儲(chǔ)過(guò)程的應(yīng)用7．使用帶返回狀態(tài)值的參數(shù)，返回值只能是整數(shù)【9-13】創(chuàng)建存儲(chǔ)過(guò)程AvgScore，根據(jù)給定的班級(jí)名稱(chēng)計(jì)算該班級(jí)的平均成績(jī)，并將結(jié)果使用輸出參數(shù)返回。如果指定的班級(jí)名稱(chēng)存在，則返回1，否則返回0。createprocedureavgscore@classvarchar(20),@scorefloatoutputasdeclare@classidintset@classid=0--根據(jù)參數(shù)中指定的班級(jí)名稱(chēng)class,獲取班級(jí)編號(hào)select@classid=班級(jí)號(hào)from班級(jí)

where班級(jí)名稱(chēng)=@classif@classid=0return0elsebeginselect@score=avg(成績(jī))from選修whereleft(學(xué)號(hào),6)=@classidreturn1endgo--調(diào)用存儲(chǔ)過(guò)程declare@scorefloatdeclare@resultintexec@result=avgscore'08會(huì)計(jì)（1）班',@scoreoutput--檢查返回值if@result=1print'平均成績(jī):'+cast(@scoreasvarchar(20))elseprint'沒(méi)有對(duì)應(yīng)的記錄'9.1.8存儲(chǔ)過(guò)程的應(yīng)用8．創(chuàng)建用戶自定義的系統(tǒng)存儲(chǔ)過(guò)程【例9-14】創(chuàng)建一個(gè)自定義存儲(chǔ)過(guò)程，顯示指定表名的索引，如果沒(méi)有指定表名，返回學(xué)生表的索引信息。usemastergoifexists(selectnamefromsysobjectswherename='sp_showtableindex'andtype='p')dropprocsp_showtableindexgocreateprocsp_showtableindex@tablenamevarchar(30)='學(xué)生'as表名,as索引名,indidas索引標(biāo)識(shí)號(hào)fromsysindexesinxjoinsysobjectstabontab.id=like@tablenamegousejxglgoexecsp_showtableindex9.1.8存儲(chǔ)過(guò)程的應(yīng)用9．創(chuàng)建帶編號(hào)的存儲(chǔ)過(guò)程【例9-15】創(chuàng)建一組存儲(chǔ)過(guò)程score，顯示選修表中各班級(jí)的最高分和最低分。ifexists(selectnamefromsysobjectswherename='proc_score'andtype='p')dropprocscoregocreateprocedureproc_score;1asselectleft(學(xué)號(hào),6)as班級(jí)號(hào),max(成績(jī))as最高分from選修groupbyleft(學(xué)號(hào),6)gocreateprocedureproc_score;2asselectleft(學(xué)號(hào),6)as班級(jí)號(hào),min(成績(jī))as最低分from選修groupbyleft(學(xué)號(hào),6)goexecproc_score;1execproc_score;29.2觸發(fā)器觸發(fā)器是由用戶定義的一類(lèi)特殊存儲(chǔ)過(guò)程，常常用于對(duì)表實(shí)施復(fù)雜的完整性約束。它不能被顯示地調(diào)用，而是在往表中插入記錄﹑更新記錄或者刪除記錄時(shí)被自動(dòng)地激活。這一點(diǎn)也是觸發(fā)器與存儲(chǔ)過(guò)程不同的地方，而存儲(chǔ)過(guò)程是由命令調(diào)用執(zhí)行的。9.2.1觸發(fā)器概述9.2.2觸發(fā)器的創(chuàng)建9.2.1觸發(fā)器概述觸發(fā)器是一個(gè)功能強(qiáng)大的工具，用于保證表中數(shù)據(jù)的變化遵循數(shù)據(jù)庫(kù)設(shè)計(jì)者確定的完整性約束和規(guī)則。如當(dāng)向表插入、更新、刪除數(shù)據(jù)后，對(duì)其關(guān)聯(lián)表的數(shù)據(jù)同時(shí)進(jìn)行調(diào)整，以實(shí)時(shí)同步反映數(shù)據(jù)的變化。1．觸發(fā)器功能使用觸發(fā)器有助于保持?jǐn)?shù)據(jù)庫(kù)的數(shù)據(jù)完整性，在觸發(fā)器中可以完成以下幾個(gè)功能：（1）不允許刪除或更新特定的數(shù)據(jù)記錄；（2）不允許插入不符合邏輯關(guān)系的記錄；（3）刪除參照表的一條記錄的同時(shí)級(jí)聯(lián)刪除被參照表的相關(guān)記錄；（4）更新參照表的一條記錄的同時(shí)級(jí)聯(lián)更新被參照表的相關(guān)記錄。9.2.1觸發(fā)器概述2．觸發(fā)器類(lèi)型根據(jù)激活觸發(fā)器執(zhí)行的時(shí)機(jī)不同，常將觸發(fā)器分為兩類(lèi)：insteadof和after觸發(fā)器。（1）insteadof觸發(fā)器：在觸發(fā)事件（Insert、Delete和Update）之前激活觸發(fā)器，其功能是不執(zhí)行觸發(fā)事件語(yǔ)句，而是執(zhí)行insteadof觸發(fā)器本身。insteadof觸發(fā)器可以在表和視圖上定義，一個(gè)表或視圖上的每一個(gè)Insert、Delete和Update操作只能有一個(gè)insteadof觸發(fā)器。（2）after觸發(fā)器：在觸發(fā)事件（Insert、Delete和Update）成功執(zhí)行之后激活觸發(fā)器，其功能是除非觸發(fā)體中有回滾語(yǔ)句，否則即使觸發(fā)事件違反約束規(guī)則，也不能阻止觸發(fā)事件語(yǔ)句執(zhí)行。after觸發(fā)器只能在表上定義，一個(gè)表或視圖上的每一個(gè)Insert、Delete和Update操作可以有多個(gè)after觸發(fā)器。9.2.1觸發(fā)器概述3．inserted表和deleted表使用觸發(fā)器時(shí)，SQLServer會(huì)為每個(gè)觸發(fā)器建立兩個(gè)特殊的臨時(shí)表：inserted表和deleted表。這兩表存儲(chǔ)在內(nèi)存中，與被該觸發(fā)器應(yīng)用的表結(jié)構(gòu)完全相同，而且由系統(tǒng)維護(hù)和管理，用戶只能讀取數(shù)據(jù)而不能修改數(shù)據(jù)。每個(gè)觸發(fā)器只能訪問(wèn)自己的臨時(shí)表，觸發(fā)器執(zhí)行完畢，兩表也自動(dòng)釋放。（1）inserted表：用于存儲(chǔ)insert和update語(yǔ)句所影響的行副本。當(dāng)執(zhí)行insert和update操作時(shí)，新的數(shù)據(jù)行同時(shí)被添加基本表和inserted表中。（2）deleted表：用于存儲(chǔ)delete和update語(yǔ)句所影響的行副本。當(dāng)執(zhí)行delete和update操作時(shí)，指定的原數(shù)據(jù)行被用戶從基本表中刪除，然后被轉(zhuǎn)移到deleted表中。一般來(lái)說(shuō)，在基本表和deleted表中不會(huì)存在相同的數(shù)據(jù)行。注意：update操作可以看成兩個(gè)步驟：首先，將基本表中要更新的原數(shù)據(jù)行移到deleted表中，然后從inserted表中復(fù)制更新后的新數(shù)據(jù)行到基本表中。9.2.2觸發(fā)器的創(chuàng)建SQLServer提供了兩種創(chuàng)建觸發(fā)器的方法：使用企業(yè)管理器和使用T-SQL語(yǔ)句的CreateTrigger命令。1．創(chuàng)建觸發(fā)器的注意事項(xiàng)（1）createtrigger語(yǔ)句必須是批處理中的第一條語(yǔ)句；（2）創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)移給其它用戶；（3）觸發(fā)器為數(shù)據(jù)庫(kù)對(duì)象，其名稱(chēng)必須遵循標(biāo)識(shí)符的命名規(guī)則；（4）只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器，但可以引用其它數(shù)據(jù)庫(kù)中的對(duì)象；（5）不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，可以引用臨時(shí)表，但不能引用系統(tǒng)表；（6）如果已經(jīng)給一個(gè)表的外鍵定義了級(jí)聯(lián)刪除或級(jí)聯(lián)更新，則不能在該表上定義insteadofdelete或insteadofupdate觸發(fā)器；（7）雖然truncatetable語(yǔ)句類(lèi)似于沒(méi)有where子句的delete語(yǔ)句，但它并不會(huì)引發(fā)delete觸發(fā)器；（8）writetext語(yǔ)句（更新text、ntext或image類(lèi)型的列）不會(huì)引發(fā)insert或update觸發(fā)器；（9）觸發(fā)器不能返回任何結(jié)果，應(yīng)避免使用select語(yǔ)句給變量賦值，除非設(shè)置setnocount；（10）每個(gè)表或視圖只有一個(gè)insteadof觸發(fā)器，但可以有多個(gè)after觸發(fā)器。9.2.2觸發(fā)器的創(chuàng)建2．使用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器在SQLServer中，使用T-SQL語(yǔ)句的CreateTrigger命令創(chuàng)建觸發(fā)器，其格式如下：createtrigger<觸發(fā)器名>on{表名|視圖名}[withencryption]{{for|after|insteadof}{[delete][,][insert][,][update]}as[ifupdate(列)[{and|or}update(列)][...n]]|[ifcolumns_updated(){bitwise_operator}update_bitmask{comparison_operator}column_bitmask[...n]sql語(yǔ)句[...n]}功能：在指定的表上創(chuàng)建一個(gè)指定名稱(chēng)的觸發(fā)器。9.2.2觸發(fā)器的創(chuàng)建說(shuō)明：（1）表名|視圖名：是指在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱(chēng)為觸發(fā)器表或觸發(fā)器視圖?？梢赃x擇是否指定表或視圖的所有者名稱(chēng)。（2）withencryption：在syscomments表中加密createtrigger語(yǔ)句定義的文本內(nèi)容。（3）after：只有在引起觸發(fā)器的T-SQL語(yǔ)句成功執(zhí)行后才激活觸發(fā)器，且在所有級(jí)聯(lián)操作和約束檢查成功完成后。如果僅指定for關(guān)鍵字，則默認(rèn)為after觸發(fā)器。（4）insteadof：執(zhí)行觸發(fā)器本身而不執(zhí)行引起觸發(fā)器激活的T-SQL語(yǔ)句（替代引起觸發(fā)器執(zhí)行的T-SQL語(yǔ)句）。（5）{[delete][,][insert][,][update]}：定義觸發(fā)器激活的觸發(fā)事件，各選項(xiàng)自由組合，中間用逗號(hào)隔開(kāi)。（6）as：引入觸發(fā)器要執(zhí)行的操作。（7）ifupdate(列)：判斷指定的列（計(jì)算列除外）是否進(jìn)行了insert或update操作（delete操作除外），可以指定多列，列名不需要指定表別名。對(duì)于insert操作和update操作，返回true值時(shí)表示這些列插入或更新了數(shù)據(jù)。（8）ifcolumns_update()：用于判斷指定列是否進(jìn)行了insert或update操作，返回值為二進(jìn)制位。若指定列進(jìn)行了插入或更新操作，則返回值為二進(jìn)制位1，否則為二進(jìn)制位0；（9）bitwise_operator：二進(jìn)制運(yùn)算符；（10）update_bitmask：二進(jìn)制位串；（11）SQL語(yǔ)句：當(dāng)嘗試delete、insert或update操作時(shí)要執(zhí)行的transact-sql語(yǔ)句。9.2.2觸發(fā)器的創(chuàng)建【9-16】在學(xué)生表上定義一個(gè)觸發(fā)器，當(dāng)學(xué)生表上數(shù)據(jù)變化時(shí)，顯示表中所有內(nèi)容。（1）打開(kāi)查詢(xún)分析器，輸入創(chuàng)建觸發(fā)器的代碼如下：usejxglgocreatetrigger學(xué)生_changon學(xué)生

afterinsert,update,deleteasselect*from學(xué)生9.2.2觸發(fā)器的創(chuàng)建（2）單擊運(yùn)行按鈕后，從右側(cè)的對(duì)象瀏覽器的“觸發(fā)器”節(jié)點(diǎn)中可以發(fā)現(xiàn)“學(xué)生_chang”觸發(fā)器，運(yùn)行結(jié)果如圖9-7所示。圖9-7例9-15運(yùn)行結(jié)果29.2.2觸發(fā)器的創(chuàng)建（3）輸入針對(duì)學(xué)生表的插入、刪除、更新操作命令，代碼如下所示：insertinto學(xué)生(學(xué)號(hào),姓名,性別)values('09060101','王昭君','女')（4）單擊運(yùn)行按鈕后，運(yùn)行結(jié)果如圖9-8所示。圖9-8例9-15運(yùn)行結(jié)果29.2.2觸發(fā)器的創(chuàng)建3．使用企業(yè)管理器創(chuàng)建觸發(fā)器【9-17】在“學(xué)生”表上創(chuàng)建一個(gè)after類(lèi)型的插入觸發(fā)器“ins_stu”，當(dāng)插入記錄時(shí)，給予提示禁止插入記錄的信息。操作步驟如下：（1）展開(kāi)企業(yè)管理器控制臺(tái)目錄，直至出現(xiàn)數(shù)據(jù)庫(kù)（jxgl）的“表”節(jié)點(diǎn)，在右側(cè)窗口中選擇表“學(xué)生”，右擊之彈出快捷菜單，選擇“管理觸發(fā)器”選項(xiàng)，如圖9-9所示。（2）單擊釋放后，彈出“觸發(fā)器屬性”界面，如圖9-10所示。

圖9-9“企業(yè)管理器”對(duì)話框 圖9-10“觸發(fā)器屬性”界面（3）在“觸發(fā)器屬性”界面的“文本”窗口中，輸入觸發(fā)器輸入代碼，如圖9-12所示。如果單擊“檢查語(yǔ)法”按鈕，可以進(jìn)一步進(jìn)行“語(yǔ)法檢查”。（4）連續(xù)兩次單擊“確定”按鈕，返回“企業(yè)管理器”，重復(fù)步驟（1），再次彈出“觸發(fā)器屬性”界面，單擊“名稱(chēng)”下拉列表框，發(fā)現(xiàn)剛才創(chuàng)建的觸發(fā)器名稱(chēng)，如圖9-13所示。

圖9-11“企業(yè)管理器”對(duì)話框 圖9-12“觸發(fā)器屬性”界面代碼如下：createtriggerins_stuon學(xué)生forinsertasraiserror('禁止插入記錄',10,1)驗(yàn)證觸發(fā)器的語(yǔ)句如下：insertinto學(xué)生(學(xué)號(hào),姓名,性別,出生日期)values('09050102','劉飛翔','男','1991-6-7')注意：用戶打開(kāi)學(xué)生表時(shí)，可以發(fā)現(xiàn)上述記錄已經(jīng)插入表中，這是由于觸發(fā)器的for選項(xiàng)沒(méi)有指定after關(guān)鍵字，默認(rèn)值是after，即指定的insert操作成功時(shí)，執(zhí)行觸發(fā)觸發(fā)器?！?-18】將“學(xué)生”表的插入觸發(fā)器“ins_stu”修改為insteadof類(lèi)型，當(dāng)插入記錄時(shí)，給予提示禁止插入記錄的信息。usejxglgoaltertriggerins_stuon學(xué)生insteadofinsertasraiserror('禁止插入記錄',10,1)驗(yàn)證觸發(fā)器的語(yǔ)句如下：insertinto學(xué)生(學(xué)號(hào),姓名,性別,出生日期)values('09050103','劉飛娜','女','1992-7-17')9.2.3觸發(fā)器的修改SQLServer提供了兩種修改觸發(fā)器的方法：使用企業(yè)管理和使用T-SQL語(yǔ)句。使用T-SQL語(yǔ)句修改觸發(fā)器語(yǔ)法格式如下：altertrigger<觸發(fā)器名>on{表名|視圖名}[withencryption]{{for|after|insteadof}{[delete][,][insert][,][update]}as[ifupdate(列)[{and|or}update(列)][...n]]|[ifcolumns_updated(){bitwise_operator}update_bitmask){comparison_operator}column_bitmask[...n]sql語(yǔ)句[...n]}說(shuō)明：各子句的含義同創(chuàng)建觸發(fā)器中的子句一樣。9.2.3觸發(fā)器的修改【9-19】修改觸發(fā)器“學(xué)生_change”，使之滿足以下要求：實(shí)現(xiàn)向選修表插入某門(mén)課程的成績(jī)時(shí)，檢查課程表中是否存在該門(mén)課程，如沒(méi)有就顯示提示信息并禁止插入該記錄。usejxglgoaltertrigger學(xué)生_changon選修afterinsertasif(selectcount(*)from課程,insertedwhere課程.課程號(hào)=inserted.課程號(hào))=0beginraiserror('沒(méi)有此課程',16,1)rollbacktransactionendreturn驗(yàn)證觸發(fā)器的作用，可以向選修表中加入以下記錄：Insertinto選修(學(xué)號(hào),課程號(hào),成績(jī))values('08010101','15',60)運(yùn)行結(jié)果如下：服務(wù)器:消息50000，級(jí)別16，狀態(tài)1，過(guò)程學(xué)生_chang，行7沒(méi)有此課程9.2.4觸發(fā)器的禁用和啟動(dòng)在操作表和使用觸發(fā)器的時(shí)候，有時(shí)可能需要臨時(shí)禁用某個(gè)觸發(fā)器，使用完畢后可能需要繼續(xù)啟用觸發(fā)器，這就需要禁用或啟動(dòng)觸發(fā)器的命令。格式：altertable<表名>{enable|disable}<all|觸發(fā)器名>說(shuō)明：（1）enable是啟動(dòng)觸發(fā)器，而disable是禁用觸發(fā)器；（2）all代表所有觸發(fā)器，而觸發(fā)器名則表示指定觸發(fā)器。9.2.5觸發(fā)器的刪除當(dāng)不需要某個(gè)觸發(fā)器的時(shí)，可以刪除它。觸發(fā)器被刪除后，觸發(fā)器所在的表中數(shù)據(jù)不會(huì)因此而改變。另外，當(dāng)某個(gè)表被刪除時(shí)，定義與該表相關(guān)的所有觸發(fā)器也會(huì)自動(dòng)刪除。格式：droptrigger<觸發(fā)器名>說(shuō)明：刪除指定名稱(chēng)的觸發(fā)器9.2.6觸發(fā)器的管理觸發(fā)器是特殊的存儲(chǔ)過(guò)程，所有適合存儲(chǔ)過(guò)程的管理方式都適用于觸發(fā)器。（1）sp_helptrigger<表名>：查看指定表中定義的當(dāng)前數(shù)據(jù)庫(kù)的觸發(fā)器類(lèi)型；（2）sp_help<觸發(fā)器名>：查看觸發(fā)器概要信息，如名稱(chēng)，屬性，類(lèi)型和創(chuàng)建時(shí)間；（3）sp_helptext<觸發(fā)器名>：查看觸發(fā)器詳細(xì)定義文本信息；（4）sp_depends<表名>|<觸發(fā)器名>：查看指定表涉及到的觸發(fā)器或觸發(fā)器涉及到的表。（5）sp_rename<觸發(fā)器舊名><觸發(fā)器新名>9.2.7觸發(fā)器的應(yīng)用觸發(fā)器可以很好地維護(hù)數(shù)據(jù)，當(dāng)向表中添加數(shù)據(jù)或更改記錄后，對(duì)其關(guān)聯(lián)表的數(shù)據(jù)進(jìn)行調(diào)整，以實(shí)現(xiàn)反映數(shù)據(jù)的變化。如果觸發(fā)器執(zhí)行的一些動(dòng)作可以通過(guò)約束來(lái)實(shí)現(xiàn)，則首先考慮約束，因?yàn)橛|發(fā)器比約束占用更多的系統(tǒng)資源，但對(duì)于強(qiáng)制各種規(guī)范化和強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)則，必須使用觸發(fā)器。9.2.7觸發(fā)器的應(yīng)用1．級(jí)聯(lián)更新【9-20】在學(xué)生表上創(chuàng)建一個(gè)update觸發(fā)器，當(dāng)更新學(xué)生學(xué)號(hào)時(shí)，同時(shí)更新選修表中的學(xué)生學(xué)號(hào)。createtriggerup_學(xué)生on學(xué)生forupdateasdeclare@oldidchar(8),@newidchar(8)select@oldid=deleted.學(xué)號(hào),@newid=inserted.學(xué)號(hào)

fromdeleted,insertedwheredeleted.姓名=inserted.姓名update選修set學(xué)號(hào)=@newidwhere學(xué)號(hào)=@oldid9.2.7觸發(fā)器的應(yīng)用2．級(jí)聯(lián)刪除【9-21】在學(xué)生表上創(chuàng)建一個(gè)delete觸發(fā)器，當(dāng)刪除學(xué)生記錄時(shí)，同時(shí)刪除選修表中對(duì)應(yīng)的學(xué)生記錄。createtriggerdel_學(xué)生on學(xué)生afterdeleteasdeletefrom選修where學(xué)號(hào)in(select學(xué)號(hào)fromdeleted)9.2.7觸發(fā)器的應(yīng)用3．禁止插入（級(jí)聯(lián)限制）【9-22】在學(xué)生表上創(chuàng)建一個(gè)觸發(fā)器，當(dāng)向選修表中插入學(xué)號(hào)時(shí)，同時(shí)檢查學(xué)生表中是否存在該學(xué)號(hào)，若不存在，不允許插入該記錄。createtriggerins_選修on選修afterinsertasif(selectcount(*)from學(xué)生,insertedwhere學(xué)生.學(xué)號(hào)=inserted.學(xué)號(hào))=0beginprint