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

第8章存儲(chǔ)過(guò)程和觸發(fā)器存儲(chǔ)過(guò)程概述8.1存儲(chǔ)過(guò)程的創(chuàng)建8.2執(zhí)行存儲(chǔ)過(guò)程8.3存儲(chǔ)過(guò)程的參數(shù)8.4存儲(chǔ)過(guò)程的返回值8.5存儲(chǔ)過(guò)程的查看、修改和刪除8.6觸發(fā)器的概念8.7

觸發(fā)器的創(chuàng)建8.8觸發(fā)器的使用8.9

觸發(fā)器的修改及刪除8.108.1存儲(chǔ)過(guò)程概述8.1.1存儲(chǔ)過(guò)程的基本概念存儲(chǔ)過(guò)程是獨(dú)立存在于表之外的數(shù)據(jù)庫(kù)對(duì)象，由被編譯在一起的一組Transact-SQL語(yǔ)句組成。它可以被客戶(hù)調(diào)用，也可以被另一個(gè)存儲(chǔ)過(guò)程或觸發(fā)器調(diào)用，它的參數(shù)可以被傳遞，它的出錯(cuò)代碼也可以被檢驗(yàn)。

在SQLServer中，使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)如下：①存儲(chǔ)過(guò)程在服務(wù)器端運(yùn)行，執(zhí)行速度快。②存儲(chǔ)過(guò)程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲(chǔ)器，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，提高了系統(tǒng)性能。③確保數(shù)據(jù)庫(kù)的安全。使用存儲(chǔ)過(guò)程可以完成所有的數(shù)據(jù)庫(kù)操作，并可通過(guò)編程方式控制上述操作對(duì)數(shù)據(jù)庫(kù)信息訪(fǎng)問(wèn)的權(quán)限。④自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過(guò)程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，大大方便了用戶(hù)的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。8.1.2存儲(chǔ)過(guò)程的類(lèi)型SQLServer支持五種類(lèi)型的存儲(chǔ)過(guò)程：系統(tǒng)存儲(chǔ)過(guò)程、本地存儲(chǔ)過(guò)程、臨時(shí)存儲(chǔ)過(guò)程、遠(yuǎn)程存儲(chǔ)過(guò)程和擴(kuò)展存儲(chǔ)過(guò)程。在不同情況下需要執(zhí)行不同的存儲(chǔ)過(guò)程。1．系統(tǒng)存儲(chǔ)過(guò)程

系統(tǒng)存儲(chǔ)過(guò)程是由系統(tǒng)提供的存儲(chǔ)過(guò)程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲(chǔ)過(guò)程定義在系統(tǒng)數(shù)據(jù)庫(kù)master中，其前綴是sp_。2．本地存儲(chǔ)過(guò)程

本地存儲(chǔ)過(guò)程是指在用戶(hù)數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程完成特定數(shù)據(jù)庫(kù)操作任務(wù)，其名稱(chēng)不能以sp_為前綴。3．臨時(shí)存儲(chǔ)過(guò)程

臨時(shí)存儲(chǔ)過(guò)程屬于本地存儲(chǔ)過(guò)程。如果本地存儲(chǔ)過(guò)程的名稱(chēng)前面有一個(gè)“#”，該存儲(chǔ)過(guò)程就稱(chēng)為局部臨時(shí)存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程只能在一個(gè)用戶(hù)會(huì)話(huà)中使用；如果本地存儲(chǔ)過(guò)程的名稱(chēng)前有兩個(gè)“##”，該過(guò)程就是全局臨時(shí)存儲(chǔ)過(guò)程，這種存儲(chǔ)過(guò)程可以在所有用戶(hù)會(huì)話(huà)中使用。4．遠(yuǎn)程存儲(chǔ)過(guò)程

遠(yuǎn)程存儲(chǔ)過(guò)程指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲(chǔ)過(guò)程。5．?dāng)U展存儲(chǔ)過(guò)程

在SQLServer環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)稱(chēng)為擴(kuò)展存儲(chǔ)過(guò)程，其前綴是sp_。使用時(shí)需要先加載到SQLServer系統(tǒng)中，并且按照使用存儲(chǔ)過(guò)程的方法執(zhí)行。8.2存儲(chǔ)過(guò)程的創(chuàng)建

默認(rèn)情況下，用戶(hù)創(chuàng)建的存儲(chǔ)過(guò)程歸數(shù)據(jù)庫(kù)所有者擁有，數(shù)據(jù)庫(kù)的所有者可以把許可授權(quán)給其他用戶(hù)。

存儲(chǔ)過(guò)程由CREATEPROCEDURE語(yǔ)句創(chuàng)建，存儲(chǔ)過(guò)程的定義包括：過(guò)程名和參數(shù)的說(shuō)明以及過(guò)程體，即包含執(zhí)行存儲(chǔ)過(guò)程操作的Transact-SQL語(yǔ)句。要使用存儲(chǔ)過(guò)程，首先要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)過(guò)程。8.2.1使用CREATEPROCEDURE語(yǔ)句創(chuàng)建1．語(yǔ)法格式CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]2．參數(shù)說(shuō)明procedure_name：新存儲(chǔ)過(guò)程的名稱(chēng)。過(guò)程名必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須惟一。如果要?jiǎng)?chuàng)建局部臨時(shí)過(guò)程，可以在procedure_name前面加一個(gè)編號(hào)符#，要?jiǎng)?chuàng)建全局臨時(shí)過(guò)程，可以在procedure_name前面加兩個(gè)編號(hào)符##。完整的名稱(chēng)包括（#或##）不能超過(guò)128個(gè)字符。指定過(guò)程所有者的名稱(chēng)是可選的。number：是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條[DROPPROCEDURE]語(yǔ)句即可將同組的過(guò)程一起除去。@parameter：過(guò)程中的參數(shù)。在[CREATEPROCEDURE]語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶(hù)必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過(guò)程最多可以有2100個(gè)參數(shù)。使用@符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱(chēng)。參數(shù)名稱(chēng)必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身；相同的參數(shù)名稱(chēng)可以用在其他過(guò)程中。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫(kù)對(duì)象的名稱(chēng)。data_type：參數(shù)的數(shù)據(jù)類(lèi)型。所有數(shù)據(jù)類(lèi)型（包括text、ntext和image）均可以用作存儲(chǔ)過(guò)程的參數(shù)。不過(guò)，cursor數(shù)據(jù)類(lèi)型只能用于[OUTPUT]語(yǔ)句的參數(shù)。如果指定的數(shù)據(jù)類(lèi)型為cursor，也必須同時(shí)指定[VARYING]和[OUTPUT]關(guān)鍵字。說(shuō)明：對(duì)于可以是cursor數(shù)據(jù)類(lèi)型的輸出參數(shù)，沒(méi)有最大數(shù)目的限制。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。default：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。默認(rèn)值必須是常量或[NULL]。如果過(guò)程將對(duì)該參數(shù)使用[LIKE]關(guān)鍵字，那么默認(rèn)值中可以包含通配符（％、_、[]和^）。OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給[EXEC[UTE]。使用[OUTPUT]參數(shù)可將信息返回給調(diào)用過(guò)程。text、ntext和image類(lèi)型數(shù)據(jù)可用作[OUTPUT]參數(shù)。使用[OUTPUT]關(guān)鍵字的輸出參數(shù)可以走游標(biāo)占位符。n：表示最多可以指定2100個(gè)參數(shù)的占位符。RECOMPILE|ENCRYPTION|（RECOMPILE，ENCRYPTION）：RECOMPILE表明SQLServer不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。在使用非典型值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，應(yīng)該使用[RECOMPILE]選項(xiàng)。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語(yǔ)句文本的條目。使用ENCRYPTION可防止將過(guò)程作為SQLServer復(fù)制的一部分發(fā)布。說(shuō)明：在升級(jí)過(guò)程中，SQLServer利用存儲(chǔ)在syscomments中的加密注釋來(lái)重新創(chuàng)建加密過(guò)程。FORREPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。使用[FORREPLICATION]選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選，且只能在復(fù)制過(guò)程中執(zhí)行。該選項(xiàng)不能和[WITHRECOMPILE]選項(xiàng)一起使用。AS：指定過(guò)程要執(zhí)行的操作。sql_statement：過(guò)程中要包含的任意數(shù)目和類(lèi)型的Transact-SQL語(yǔ)句，但有一些限制。ASsql_statement[…n]：其中的[n]是表示此過(guò)程可以包含多條Transact-SQL語(yǔ)句的占位符。3．注意事項(xiàng)①用戶(hù)定義的存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建（臨時(shí)過(guò)程除外，臨時(shí)過(guò)程總是在tempdb中創(chuàng)建）。②成功執(zhí)行CREATEPROCEDURE語(yǔ)句后，過(guò)程名稱(chēng)存儲(chǔ)在sysobjects系統(tǒng)表中，而CREATEPROCEDURE語(yǔ)句的文本存儲(chǔ)在syscomments中。③自動(dòng)執(zhí)行存儲(chǔ)過(guò)程。SQLServer啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過(guò)程。這些存儲(chǔ)過(guò)程必須由系統(tǒng)管理員在master數(shù)據(jù)庫(kù)中創(chuàng)建，并在sysadmin固定服務(wù)器角色下作為后臺(tái)過(guò)程執(zhí)行。這些過(guò)程不能有任何輸入?yún)?shù)。④sql_statement的限制。除了SETSHOWPLANTEXT和SETSHOWPLANALL外，其他SET語(yǔ)句均可在存儲(chǔ)過(guò)程內(nèi)使用。

必須使用對(duì)象所有者名對(duì)數(shù)據(jù)庫(kù)對(duì)象進(jìn)行限定的語(yǔ)句有：CREATETABLE、ALTERTABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS及DBCC語(yǔ)句。⑤權(quán)限。CREATEPROCEDURE的權(quán)限默認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色成員。sysadmin固定服務(wù)器角色成員和dlowner固定數(shù)據(jù)庫(kù)角色成員可以將CREATEPROCEDURE權(quán)限轉(zhuǎn)讓給其他用戶(hù)。USEStudent--檢查是否已存在同名的存儲(chǔ)過(guò)程，若有，則刪除。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_info_pro'ANDtype='P')DROPPROCEDUREstu_info_proGO--創(chuàng)建存儲(chǔ)過(guò)程CREATEPROCEDUREstu_info_proASSELECTstudent_Name,student_Sex,addressFROMStudent_InfoGOEXECstu_info_pro8.2.2使用企業(yè)管理器創(chuàng)建（1）打開(kāi)企業(yè)管理器，展開(kāi)服務(wù)器組，并展開(kāi)相應(yīng)的服務(wù)器。（2）打開(kāi)“數(shù)據(jù)庫(kù)”文件夾，并打開(kāi)要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)。（3）選擇“存儲(chǔ)過(guò)程”選項(xiàng)，右擊鼠標(biāo)，執(zhí)行“新建存儲(chǔ)過(guò)程”命令，打開(kāi)創(chuàng)建存儲(chǔ)過(guò)程對(duì)話(huà)框如圖8-2所示。圖8-2創(chuàng)建存儲(chǔ)過(guò)程對(duì)話(huà)框（4）在“文本”列表框中顯示了CREATEPROCEDURE語(yǔ)句的框架，可以修改要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名稱(chēng)，然后加入存儲(chǔ)過(guò)程所包含的SQL語(yǔ)句。（5）單擊“檢查語(yǔ)法”按鈕，可以檢查創(chuàng)建存儲(chǔ)過(guò)程的SQL語(yǔ)句的語(yǔ)法是否正確。（6）如果要將其設(shè)置為下次創(chuàng)建存儲(chǔ)過(guò)程的模板，可單擊“另存為模板”按鈕。（7）完成后，單擊“確定”按鈕即可創(chuàng)建一個(gè)存儲(chǔ)過(guò)程。8.2.3使用向?qū)?chuàng)建

（1）在企業(yè)管理器中，執(zhí)行“工具”下拉菜單中的“向?qū)А泵?，打開(kāi)“選擇向?qū)А睂?duì)話(huà)框。（2）在“數(shù)據(jù)庫(kù)”文件夾選擇“創(chuàng)建存儲(chǔ)過(guò)程”向?qū)В瑔螕簟按_定”按鈕，出現(xiàn)“創(chuàng)建存儲(chǔ)過(guò)程向?qū)А睔g迎對(duì)話(huà)框。（3）單擊“下一步”按鈕，出現(xiàn)選擇數(shù)據(jù)庫(kù)對(duì)話(huà)框，如圖8-3所示。（4）在“數(shù)據(jù)庫(kù)名稱(chēng)”下拉列表中選擇數(shù)據(jù)庫(kù)后，單擊“下一步”按鈕，出現(xiàn)選擇存儲(chǔ)過(guò)程對(duì)話(huà)框，如圖8-4所示。圖8-3選擇數(shù)據(jù)庫(kù)對(duì)話(huà)框圖8-4選擇存儲(chǔ)過(guò)程對(duì)話(huà)框（5）單擊“下一步”按鈕，出現(xiàn)完成創(chuàng)建存儲(chǔ)過(guò)程對(duì)話(huà)框。若單擊“完成”按鈕，即可完成存儲(chǔ)過(guò)程的創(chuàng)建。（6）單擊“編輯”按鈕，可編輯存儲(chǔ)過(guò)程，如圖8-6所示。圖8-5完成創(chuàng)建存儲(chǔ)過(guò)程對(duì)話(huà)框圖8-6編輯存儲(chǔ)過(guò)程對(duì)話(huà)框（7）單擊“編輯SQL”按鈕，即可打開(kāi)“編輯存儲(chǔ)過(guò)程SQL”對(duì)話(huà)框，其中的列表框顯示了創(chuàng)建該存儲(chǔ)過(guò)程的Transact-SQL語(yǔ)句?？梢栽谝延械腡ransact-SQL語(yǔ)句的基礎(chǔ)上進(jìn)行編輯，也可以單擊“分析”按鈕來(lái)執(zhí)行語(yǔ)法檢查。如圖8-7所示。圖8-7“編輯存儲(chǔ)過(guò)程SQL”對(duì)話(huà)框（8）單擊“確定”按鈕，返回到圖8-5所示的對(duì)話(huà)框，再單擊“確定”按鈕即可。8.3執(zhí)行存儲(chǔ)過(guò)程1．語(yǔ)法格式[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]2．參數(shù)說(shuō)明@return_status：一個(gè)可選的整型變量，保存存儲(chǔ)過(guò)程的返回狀態(tài)。此變量在用于EXECUTE語(yǔ)句前，必須在批處理、存儲(chǔ)過(guò)程或函數(shù)中已聲明。procedure_name：調(diào)用的存儲(chǔ)過(guò)程名稱(chēng)。number：可選的整數(shù)，用于將相同名稱(chēng)的過(guò)程進(jìn)行組合，使得它們可以用一句DROPPROCEDURE語(yǔ)句除去。該參數(shù)不能用于擴(kuò)展存儲(chǔ)過(guò)程。在同一應(yīng)用程序中使用的過(guò)程一般都以該方式組合。@procedure_name_var：局部定義變量名，代表存儲(chǔ)過(guò)程名稱(chēng)。@parameter：過(guò)程參數(shù)，在[CREATEPROCEDURE]語(yǔ)句中定義。參數(shù)名稱(chēng)前必須加上符號(hào)@。在以“@parameter_name=value”格式使用時(shí)，參數(shù)名稱(chēng)和常量不一定按照[CREATEPROCEDURE]語(yǔ)句中定義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用“@parameter_name=value”格式，則其他所有參數(shù)都必須使用這種格式。

默認(rèn)情況下，參數(shù)可為空。如果傳遞NULL參數(shù)值，且該參數(shù)用于CREATE或ALTERTABLE語(yǔ)句中不允許為NULL的列（例如，插入至不允許為NULL的列），SQLServer就會(huì)報(bào)錯(cuò)。為避免將NULL參數(shù)值傳遞給不允許為NULL的列，可以在過(guò)程中添加程序設(shè)計(jì)邏輯或采用默認(rèn)值（使用CREATE或ALTERTABLE語(yǔ)句中的DEFAULT關(guān)鍵字）。value：過(guò)程中參數(shù)的值。如果參數(shù)名稱(chēng)沒(méi)有指定，參數(shù)值必須以CREATEPROCEDURE語(yǔ)句中定義的順序給出。@variable：用來(lái)保存參數(shù)或者返回參數(shù)的變量。OUTPUT：指定存儲(chǔ)過(guò)程必須返回一個(gè)參數(shù)。該存儲(chǔ)過(guò)程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。

DEFAULT：根據(jù)過(guò)程的定義提供參數(shù)的默認(rèn)值。當(dāng)過(guò)程需要的參數(shù)值沒(méi)有事先定義好默認(rèn)值或缺少參數(shù)或指定了[DEFAULT]關(guān)鍵字，就會(huì)出錯(cuò)。n：占位符，表示在它前面的項(xiàng)目可以多次重復(fù)執(zhí)行。例如，EXCUTE語(yǔ)句可以指定一個(gè)或者多個(gè)@parameter、value或@variable。WITHRECOMPILE：強(qiáng)制編譯新的計(jì)劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項(xiàng)，在以后的程序執(zhí)行中使用更改過(guò)的計(jì)劃。該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過(guò)程。建議盡量少使用該選項(xiàng)，因?yàn)樗妮^多的系統(tǒng)資源。3．注意事項(xiàng)①如果存儲(chǔ)過(guò)程名的前三個(gè)字符為sp_，SQLServer會(huì)在Master數(shù)據(jù)庫(kù)中尋找該過(guò)程。如果沒(méi)能找到合法的過(guò)程名，SQLServer會(huì)尋找所有者名稱(chēng)為dbo的過(guò)程。②參數(shù)可以通過(guò)value或@parametername：value提供。③執(zhí)行存儲(chǔ)過(guò)程時(shí)，若語(yǔ)句是批處理中的第一個(gè)語(yǔ)句，則不一定要指定EXECUTE關(guān)鍵字。8.4存儲(chǔ)過(guò)程的參數(shù)8.4.1參數(shù)傳遞方式1．順序法在傳遞參數(shù)時(shí)，使傳遞的參數(shù)和定義時(shí)的參數(shù)順序一致，對(duì)于使用默認(rèn)值的參數(shù)可以用DEFAULT值代替。USEStudentGoCREATEPROCEDUREAdd_class(@class_idint,@class_namechar(20),@directorchar(10),@professionchar(14))ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計(jì)算機(jī)應(yīng)用022','張波','計(jì)算機(jī)應(yīng)用'select*fromclass_infoUSEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_score_pro'ANDtype='P')DROPPROCEDUREstu_score_proGoCREATEPROCEDUREstu_score_pro@snamechar(10),@cnamechar(10)ASSELECTa.student_name,c.course_name,c.course_start,b.result,c.course_scoreFROMstudent_infoaJOINresult_infobONa.student_id=b.student_idJOINcourse_infocONb.course_no=c.course_noWHEREc.course_name=@cnameanda.student_name=@snameEXECstu_score_pro'陳白露','馬克思主義'2．提示法

在傳遞參數(shù)時(shí)，采用提示的方法，如“@class_id=200202”的形式，此時(shí)，各個(gè)參數(shù)的順序可以任意排列。EXECAdd_class@class_id=200202,@class_name='計(jì)算機(jī)應(yīng)用022',@director='張波',@profession='計(jì)算機(jī)應(yīng)用'EXECAdd_class@class_name='計(jì)算機(jī)應(yīng)用022',@class_id=200202,@profession='計(jì)算機(jī)應(yīng)用',@director='張波'8.4.2使用默認(rèn)參數(shù)

創(chuàng)建存儲(chǔ)過(guò)程時(shí)，可以為參數(shù)提供一個(gè)默認(rèn)值，默認(rèn)值必須為常量或者NULL。USEStudentGoCREATEPROCEDUREAdd_class@class_idint,@class_namechar(20),@directorchar(10)='無(wú)',@professionchar(14)='無(wú)'ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計(jì)算機(jī)應(yīng)用022'Goselect*fromclass_infoGo8.4.3使用帶OUTPUT的返回參數(shù)

在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，可以定義返回參數(shù)。在執(zhí)行存儲(chǔ)過(guò)程時(shí)，可以將結(jié)果返回給返回參數(shù)。USEStudentGoCREATEPROCEDUREQuery_student(@student_idint,@student_namechar(10)OUTPUT,@addressvarchar(50)OUTPUT)ASSELECT@student_name=student_name,@address=addressFROMstudent_infoWHEREstudent_id=@student_idDECLARE@student_namechar(10)DECLARE@addressvarchar(50)EXECQuery_student20000203,@student_nameOUTPUT,@addressOUTPUTSELECT'姓名'=@student_name,'家庭住址'=@addressGO8.5存儲(chǔ)過(guò)程的返回值

存儲(chǔ)過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值。如果執(zhí)行成功，則返回0；否則返回?1～99之間的數(shù)值。8.5.1RETURN語(yǔ)句1．語(yǔ)法RETURN[整數(shù)表達(dá)式]2．功能從查詢(xún)或過(guò)程中無(wú)條件退出。[RETURN]語(yǔ)句可在任何時(shí)候從過(guò)程、批處理或語(yǔ)句塊中退出，不執(zhí)行位于[RETURN]之后的語(yǔ)句。3．說(shuō)明①[整數(shù)表達(dá)式]：返回的整型值。存儲(chǔ)過(guò)程可以給調(diào)用過(guò)程或應(yīng)用程序返回整型值。②在建立存儲(chǔ)過(guò)程的時(shí)候，需要定義任意的出錯(cuò)條件，并把它們與整型的出錯(cuò)代碼聯(lián)系起來(lái)。③用于存儲(chǔ)過(guò)程時(shí)，[RETURN]不能返回空值。如果過(guò)程試圖返回空值，系統(tǒng)將生成警告信息并返回0值。USEStudentGoCREATEPROCEDUREtest_return(@input_numint=0)ASIF@input_num=0RETURN0IF@input_num>0RETURN1IF@input_num<0RETURN-18.5.2捕獲存儲(chǔ)過(guò)程的返回值

若要正確接收存儲(chǔ)過(guò)程的返回值，可使用EXECUTE語(yǔ)句。1．語(yǔ)法EXECUTE@return_status=procedure_name2．功能

執(zhí)行存儲(chǔ)過(guò)程，將RETURN語(yǔ)句返回的值送狀態(tài)變量@return_status。3．說(shuō)明

在執(zhí)行EXECUTE語(yǔ)句之前，首先要聲名變量@return_status。DECLARE@ret_numintEXEC@ret_num=test_return100SELECT'返回值'=@ret_numGO8.6存儲(chǔ)過(guò)程的查看、修改和刪除8.6.1存儲(chǔ)過(guò)程的查看1．使用sp_helptext查看存儲(chǔ)過(guò)程EXECsp_helptexttest_return2．使用企業(yè)管理器（1）打開(kāi)企業(yè)管理器，展開(kāi)服務(wù)器組，并展開(kāi)相應(yīng)的服務(wù)器；（2）打開(kāi)數(shù)據(jù)庫(kù)文件夾，然后選擇存儲(chǔ)過(guò)程所在的數(shù)據(jù)庫(kù)“student”；（3）打開(kāi)“存儲(chǔ)過(guò)程”文件夾，在右側(cè)詳細(xì)信息窗格中右擊存儲(chǔ)過(guò)程add_class，執(zhí)行“屬性”命令，打開(kāi)“存儲(chǔ)過(guò)程屬性”對(duì)話(huà)框，如圖8-13所示；圖8-13存儲(chǔ)過(guò)程屬性對(duì)話(huà)框（4）可以在此對(duì)話(huà)框中，直接修改存儲(chǔ)過(guò)程的定義，也可以設(shè)置存儲(chǔ)過(guò)程的權(quán)限。完成后，單擊“確定”按鈕即可。8.6.2存儲(chǔ)過(guò)程的修改

修改存儲(chǔ)過(guò)程使用ALTERPROCEDURE語(yǔ)句。1．功能更改先前通過(guò)執(zhí)行CREATEPROCEDURE語(yǔ)句創(chuàng)建的存儲(chǔ)過(guò)程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。2．語(yǔ)法ALTERPROC[EDURE]procedure_name[；number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]3．說(shuō)明

各參數(shù)含義與CREATEPROCEDURE命令相同。如果原來(lái)的過(guò)程定義是用WITHENCRYPTION或WITHRECOMPILE創(chuàng)建的，那么只有在ALTERPROCEDURE中也包含這些選項(xiàng)時(shí)，這些選項(xiàng)才有效。ALTERPROCEDURE權(quán)限默認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色成員以及過(guò)程的所有者且不可轉(zhuǎn)讓。用ALTERPROCEDURE更改后，過(guò)程的權(quán)限和啟動(dòng)屬性保持不變。8.6.3存儲(chǔ)過(guò)程的刪除1．通過(guò)企業(yè)管理器刪除在要?jiǎng)h除的存儲(chǔ)過(guò)程中右擊鼠標(biāo)，然后執(zhí)行“刪除”命令，在彈出的對(duì)話(huà)框中單擊“全部除去”按鈕即可。2．通過(guò)DROPPROCEDURE語(yǔ)句來(lái)完成語(yǔ)法格式：

DROPPROCEDURE{procedure}[，…n]功能：從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組。參數(shù)含義：①procedure：指要?jiǎng)h除的存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組的名稱(chēng)；②n：表示可以指定多個(gè)存儲(chǔ)過(guò)程同時(shí)刪除。③默認(rèn)情況下，將DROPPROCEDURE權(quán)限授予過(guò)程所有者，該權(quán)限不可轉(zhuǎn)讓。db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色成員和sysadmin固定服務(wù)器角色成員可以通過(guò)在DROPPROCEDURE內(nèi)指定所有者刪除任何對(duì)象。

DROPPROCEDUREtest_return8.7觸發(fā)器的概念

觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，它不能顯示地被調(diào)用，它是在指定的表中插入記錄、更改記錄或者刪除記錄時(shí)，被自動(dòng)激活。所以，觸發(fā)器可以用來(lái)對(duì)表實(shí)施復(fù)雜的完整性約束，當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會(huì)自動(dòng)被激活，從而防止對(duì)數(shù)據(jù)的不正確的修改。

在觸發(fā)器中可以查詢(xún)其他表，也可以查詢(xún)其他更復(fù)雜的T-SQL語(yǔ)句。觸發(fā)器和引起觸發(fā)器執(zhí)行的T-SQL語(yǔ)句被當(dāng)作一次事務(wù)處理，因此可以在觸發(fā)器中回滾這個(gè)事務(wù)。

如果發(fā)現(xiàn)引起觸發(fā)器執(zhí)行的T-SQL語(yǔ)句執(zhí)行了一個(gè)非法操作，則可以通過(guò)回滾事務(wù)使語(yǔ)句不能執(zhí)行，回滾后SQLServer會(huì)自動(dòng)返回到此事務(wù)執(zhí)行前的狀態(tài)。8.8觸發(fā)器的創(chuàng)建8.8.1使用企業(yè)管理器創(chuàng)建觸發(fā)器（1）打開(kāi)企業(yè)管理器，展開(kāi)服務(wù)器組，并展開(kāi)相應(yīng)的服務(wù)器。（2）打開(kāi)“數(shù)據(jù)庫(kù)”文件夾，選擇要?jiǎng)?chuàng)建觸發(fā)器的數(shù)據(jù)庫(kù)。（3）選擇“表”文件夾，然后在要?jiǎng)?chuàng)建觸發(fā)器的表上右擊鼠標(biāo)，執(zhí)行“所有任務(wù)”子菜單下的“管理觸發(fā)器”命令，打開(kāi)“觸發(fā)器屬性”對(duì)話(huà)框。在此對(duì)話(huà)框中的“文本”列表框中，輸入用于創(chuàng)建觸發(fā)器的Transact-SQL語(yǔ)句。單擊“檢查語(yǔ)法”按鈕可以檢查SQL語(yǔ)句的語(yǔ)法是否正確。注意：如果在“名稱(chēng)”文本框中選擇已經(jīng)創(chuàng)建的觸發(fā)器，則單擊下面的“刪除”按鈕即可刪除該觸發(fā)器。（4）輸入完成后，單擊“確定”按鈕，即可創(chuàng)建觸發(fā)器。8.8.2使用Transact-SQL語(yǔ)句創(chuàng)建觸發(fā)器1．語(yǔ)法格式CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][，][INSERT][，][UPDATE]}[NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][…n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[…n]}]sql_statement[…n]}}2．參數(shù)說(shuō)明參數(shù)trigger_name用于指定觸發(fā)器名。觸發(fā)器名必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫(kù)中必須惟一，可以包含觸發(fā)器所有者名。

tablelview是觸發(fā)器表或觸發(fā)器視圖，即在其上執(zhí)行觸發(fā)器的表或視圖。有時(shí)，可以包含表或視圖的所有者名。關(guān)鍵字WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復(fù)制的一部分發(fā)布。

AFTER關(guān)鍵字用于說(shuō)明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，AFTER是默認(rèn)設(shè)置，不能在視圖上定義AFTER觸發(fā)器。INSTEADOF指定用觸發(fā)器中的操作代替觸發(fā)語(yǔ)句的操作，在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。如果觸發(fā)器表存在約束，則在INSTEADOF觸發(fā)器執(zhí)行之后和AFTER觸發(fā)器執(zhí)行之前檢查這些約束。如果違反了約束，則回滾INSTEADOF觸發(fā)器操作且不執(zhí)行AFTER觸發(fā)器，INSTEADOF觸發(fā)器不能在WITHCHECKOPTION可更新視圖上定義。關(guān)鍵字DELETE、INSERT和UPDATE用于指定在表或視圖上執(zhí)行這一操作時(shí)將激活相應(yīng)的觸發(fā)器，必須指定一項(xiàng)或多項(xiàng)，項(xiàng)與項(xiàng)之間用逗號(hào)分隔。關(guān)鍵字選項(xiàng)NOTFORREPLICATION指該觸發(fā)器對(duì)于復(fù)制進(jìn)程無(wú)效。IFUPDATE(column)子句用于測(cè)試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作；UPDATE(column)中的column為表或者視圖中的列名稱(chēng)，說(shuō)明這一列的數(shù)據(jù)是否被INSERT或者UPDATE操作修改過(guò)。如果修改過(guò)，則返回TRUE，否則返回FALSE。IF(COLUMNS_UPDATED())子句用于測(cè)試是否插入或更新了指定的列。返回的二進(jìn)制位數(shù)據(jù)，表示插入或更新了表中的哪些列，若對(duì)應(yīng)位為0，表示沒(méi)有插入或更新；若對(duì)應(yīng)位為1，表示對(duì)該列進(jìn)行了插入或更新。關(guān)于表文件的列與二進(jìn)制位的對(duì)應(yīng)關(guān)系為：如果表的列從左向右分別為C0，C1，C2，C3，C4…則分別對(duì)應(yīng)二進(jìn)制位的第0位、第1位、第2位、第3位、第4位……依此類(lèi)推。

如果在表上創(chuàng)建的觸發(fā)器包含8列以上，則COLUMNS_UPDATED()返回多個(gè)字節(jié)。在INSERT操作中，COLUMNS_UPDATED將對(duì)所有列返回TRUE值，IF(COLUMNS_UPDATED())僅用于INSERT或UPDATE觸發(fā)器。bitwise_opemtor為用于比較運(yùn)算的位運(yùn)算符。updated_bitmask為整型的位屏蔽碼，假定該表上有UPDATE觸發(fā)器，若要檢查列C1、C2、C4是否都有更新，可指定updated_bitmask的值為00010110（即22）；若要檢查是否只有列C1有更新，可指定updated_bitmask的值為00000010（即2）。comparison_operator為比較運(yùn)算符。使用等號(hào)（=）檢查updated_bitmask中指定的所有列是否都實(shí)際進(jìn)行了更新。使用大于號(hào)（>）檢查update_bitmask中指定的任一列或某些列是否已更新。

column_bitmask為列屏蔽碼，用來(lái)檢查是否已更新或插入了對(duì)應(yīng)列。參數(shù)sql_statement為觸發(fā)器的T-SQL語(yǔ)句，當(dāng)執(zhí)行DELETE、INSERT或UPDATE操作時(shí)，對(duì)應(yīng)的觸發(fā)器操作將生效。

n表示觸發(fā)器中可以包含多條T-SQL語(yǔ)句。USEStudentGO/*如果表B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='B1')DROPTABLEB1GOCREATETABLEB1(student_numberint,student_namechar(30))GO/*如果觸發(fā)器Query1_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query1_B1'ANDtype='TR')DROPTRIGGERQuery1_B1GO/*創(chuàng)建觸發(fā)器Query1_B1*/CREATETRIGGERQuery1_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMB1GOINSERTB1VALUES(200401,'張山')8.9觸發(fā)器的使用8.9.1inserted表和deleted表

在觸發(fā)器執(zhí)行的時(shí)候，會(huì)產(chǎn)生兩個(gè)臨時(shí)表：inserted表和deleted表。它們的結(jié)構(gòu)和觸發(fā)器所在的表的結(jié)構(gòu)相同，SQLServer2000自動(dòng)創(chuàng)建和管理這些表?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件；然而，不能直接對(duì)表中的數(shù)據(jù)進(jìn)行更改。inserted表用于存儲(chǔ)INSERT和UPDATE語(yǔ)句所影響的行的副本。在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到inserted表和觸發(fā)器表中。inserted表中的行是觸發(fā)器表和新行的副本。deleted表用于存儲(chǔ)DELETE和UPDATE語(yǔ)句所影響的行的復(fù)本。在執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。delete表和觸發(fā)器表通常沒(méi)有相同的行。執(zhí)行INSERT操作：插入到觸發(fā)器表中的新行被插入到inserted表中。執(zhí)行DELETE操作：從觸發(fā)器表中刪除的行被插入到deleted表中。執(zhí)行UPDATE操作：先從觸發(fā)器表中刪除舊行，然后再插入新行。其中被刪除的舊行被插入到deleted表中，插入的新行被插入到inserted表中。/*如果觸發(fā)器Query2_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query2_B1'ANDtype='TR')DROPTRIGGERQuery2_B1GO/*創(chuàng)建觸發(fā)器Query2_B1*/CREATETRIGGERQuery2_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeletedGOUPDATEB1SETstudent_name='張峰'WHEREstudent_number=2004018.9.2INSERT觸發(fā)器和UPDATE觸發(fā)器

當(dāng)向表中插入或者更新記錄時(shí)，INSERT或者UPDATE觸發(fā)器被執(zhí)行。一般情況下，這兩種觸發(fā)器常用來(lái)檢查插入或者修改后的數(shù)據(jù)是否滿(mǎn)足要求。USEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_insert'ANDtype='TR')DROPTRIGGERcheck_insertGOCREATETRIGGERcheck_insertONresult_infoFORINSERTASIFEXISTS(SELECT*FROMinsertedaWHEREa.student_idNOTIN(SELECTb.student_idFROMstudent_infob)ORa.course_noNOTIN(SELECTc.course_noFROMcourse_infoc))BEGINRAISERROR('違背數(shù)據(jù)的完整性',16,1)ROLLBACKTRANSACTION/*回滾事務(wù)*/ENDinsertresult_infoVALUES('020',20040101,'31',20)USEStudentGO/*檢查是否存在score表，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='score')DROPTABLEscoreGO/*創(chuàng)建score表*/CREATETABLEscore(student_noint,scoreint)/*檢查是否存在check_score觸發(fā)器，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_score'ANDtype='TR')DROPTRIGGERche