ISBN978-7-111-50122-0第11章

存儲過程、觸發(fā)器和游標(biāo)在大型數(shù)據(jù)庫系統(tǒng)中，存儲過程和觸發(fā)器具有很重要的作用。無論是存儲過程還是觸發(fā)器，都是SQL語句和流程控制語句的集合。就本質(zhì)而言，觸發(fā)器也是一種存儲過程，它在特定語言事件發(fā)生時自動執(zhí)行。存儲過程在運算時生成執(zhí)行代碼，所以，以后對其再運行時，其執(zhí)行效率很高。SQLServer2012不僅提供了用戶自定義存儲過程的功能，而且也提供了許多可作為工具使用的系統(tǒng)存儲過程。在存儲過程中還經(jīng)常使用一種稱為游標(biāo)的機制，來處理數(shù)據(jù)。本章主要介紹存儲過程、觸發(fā)器和游標(biāo)的概念、分類，以及它們的使用方法。11.1存儲過程使用SQLServer2012創(chuàng)建應(yīng)用程序時，SQL語言是應(yīng)用程序和SQLServer數(shù)據(jù)庫之間的主要編程接口。使用SQL程序時，可以將程序存儲在本地，然后創(chuàng)建向SQLServer發(fā)送命令并處理結(jié)果的應(yīng)用程序。也可以將SQL程序作為存儲過程存儲在SQLServer中，創(chuàng)建執(zhí)行存儲過程并處理結(jié)果的應(yīng)用程序。11.1.1存儲過程概述SQLServer推薦使用第二種方法，即在SQLServer中使用存儲過程而不是在客戶計算機上調(diào)用T-SQL編寫的一段程序，原因在于存儲過程具有以下優(yōu)點。1．存儲過程允許標(biāo)準(zhǔn)組件式編程存儲過程在被創(chuàng)建以后可以在程序中被多次調(diào)用，而不必重新編寫該存儲過程的T-SQL語句。這可以改進應(yīng)用程序的可維護性，并允許應(yīng)用程序統(tǒng)一訪問數(shù)據(jù)庫。而且數(shù)據(jù)庫專業(yè)人員可隨時對存儲過程進行修改，但對應(yīng)用程序源代碼毫無影響（因為應(yīng)用程序源代碼只包含調(diào)用存儲過程的語句），從而極大地提高了程序的可移植性。2．存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度如果某一操作包含大量的T-SQL代碼或被多次執(zhí)行，那么存儲過程要比T-SQL代碼批處理的執(zhí)行速度快很多。因為存儲過程是預(yù)編譯的，在首次運行一個存儲過程時，查詢優(yōu)化器對其進行分析、優(yōu)化，并得到執(zhí)行計劃存儲在系統(tǒng)表中。而批處理的T-SQL語句在每次運行時都要進行編譯和優(yōu)化，因此速度相對要慢。3．存儲過程能夠減少網(wǎng)絡(luò)流量對于同一個針對數(shù)據(jù)數(shù)據(jù)庫對象的操作（如查詢、修改），如果這一操作所涉及到的T-SQL語句被組織成一個存儲過程，那么當(dāng)在客戶計算機上調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只是調(diào)用存儲過程的語句，而不是多條T-SQL語句。4．存儲過程可被作為一種安全機制來充分利用數(shù)據(jù)庫系統(tǒng)管理員可以對執(zhí)行某一存儲過程的權(quán)限進行限制，從而實現(xiàn)對相應(yīng)的數(shù)據(jù)訪問權(quán)的限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。11.1.2存儲過程的類型在SQLServer2012中，存儲過程分為3類：系統(tǒng)存儲過程、用戶自定義存儲過程和擴展存儲過程。1．系統(tǒng)存儲過程SQLServer2012中的許多管理活動都是通過一種特殊的存儲過程執(zhí)行的，這種存儲過程被稱為系統(tǒng)存儲過程。系統(tǒng)存儲過程主要存儲在master數(shù)據(jù)庫中并以sp_為前綴，并且系統(tǒng)存儲過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫系統(tǒng)管理員管理SQLServer提供支持。2．用戶自定義存儲過程用戶自定義存儲過程是由用戶創(chuàng)建并能完成某一特定功能的存儲過程，是封裝了可重用代碼的T-SQL語句模塊。存儲過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言和數(shù)據(jù)操作語言語句，以及返回輸出參數(shù)。在SQLServer中，用戶自定義的存儲過程有兩種類型：T-SQL存儲過程或CLR（公共語言運行時）存儲過程。3．臨時存儲過程臨時存儲過程又分為局部臨時存儲過程和全局臨時存儲過程。局部臨時存儲過程名稱以“#”開頭，存放在tempdb數(shù)據(jù)庫中，只由創(chuàng)建并連接的用戶使用，當(dāng)該用戶斷開連接時將自動刪除局部臨時存儲過程。全局臨時存儲過程名稱以“##”開頭，也存放在tempdb數(shù)據(jù)庫中，允許所有連接的用戶使用，在所有用戶斷開連接時自動被刪除。4．?dāng)U展存儲過程擴展存儲過程允許使用高級編程語言（例如C語言）創(chuàng)建應(yīng)用程序的外部例程，從而使得SQLServer的實例可以動態(tài)地加載和運行DLL。擴展存儲過程直接在SQLServer實例的地址空間中運行。5．遠(yuǎn)程存儲過程遠(yuǎn)程存儲過程值位于遠(yuǎn)程服務(wù)器上的存儲過程。11.1.3創(chuàng)建存儲過程創(chuàng)建存儲過程，可以單擊“存儲過程”選項，選擇“新建存儲過程”選項創(chuàng)建存儲過程。如同用戶定義函數(shù)一樣，在“查詢編輯器”窗口里面已經(jīng)存在有一個存儲過程模板程序。如圖11-2所示。也可以在“查詢編輯器”窗口使用T-SQL語句創(chuàng)建存儲過程。當(dāng)創(chuàng)建存儲過程時，需要確定存儲過程的3個組成部分：1）所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。

2）被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。

3）返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。T-SQL提供了CREATEPROCEDURE語句創(chuàng)建存儲過程。其語法格式如下：CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter[type_schema_name.]data_type}[VARYING][=default][OUT|OUTPUT][READONLY]][,...n][WITH<procedure_option>[,...n]][FORREPLICATION]AS{<sql_statement>[;][...n]|<method_specifier>}11.1.3創(chuàng)建存儲過程CREATEPROCEDURE語句語法說明：1）CREATEPROCEDURE定義自身可以包括任意數(shù)量和類型的T-SQL語句，但下列語句不能在存儲過程的任何位置使用：CREATEAGGREGATE、CREATERULE、CREATEDEFAULT、CREATESCHEMA、CREATE或ALTERFUNCTION、CREATE或ALTERTRIGGER、CREATE或ALTERPROCEDURE、CREATE或ALTERVIEW、SETPARSEONLY、SETSHOWPLAN_ALL、SETSHOWPLAN_TEXT、SETSHOWPLAN_XML、USEdatabase_name。2）@parameter是過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。3）OUTPUT指示參數(shù)是輸出參數(shù)。此選項的值可以返回給調(diào)用EXECUTE的語句。使用OUTPUT參數(shù)將值返回給過程的調(diào)用方。4）數(shù)據(jù)庫對象均可在存儲過程中創(chuàng)建。可以引用在同一存儲過程中創(chuàng)建的對象，只要引用時已經(jīng)創(chuàng)建了該對象即可。5）可以在存儲過程內(nèi)引用臨時表。如果在存儲過程內(nèi)創(chuàng)建本地臨時表，則臨時表僅為該存儲過程而存在；退出該存儲過程后，臨時表將消失。11.1.3創(chuàng)建存儲過程6）如果執(zhí)行的存儲過程調(diào)用另一個存儲過程，則被調(diào)用的存儲過程可以訪問由第一個存儲過程創(chuàng)建的所有對象，包括臨時表在內(nèi)。7）如果執(zhí)行對遠(yuǎn)程SQLServer實例進行更改的遠(yuǎn)程存儲過程，則不能回滾這些更改。遠(yuǎn)程存儲過程不參與事務(wù)處理。8）在存儲過程內(nèi)，如果用于語句（例如SELECT或INSERT）的對象名沒有限定架構(gòu)，則架構(gòu)將默認(rèn)為該存儲過程所在的架構(gòu)。在存儲過程內(nèi)，如果創(chuàng)建該存儲過程的用戶沒有限定SELECT、INSERT、UPDATE或DELETE語句中引用的表名或視圖名，則默認(rèn)情況下，通過該存儲過程對這些表或視圖進行的訪問將受到該過程創(chuàng)建者的權(quán)限的限制。9）如果希望其他用戶無法查看存儲過程的定義，則可以使用WITHENCRYPTION子句創(chuàng)建存儲過程。這樣，過程定義將以不可讀的形式存儲。10）不要以sp_為前綴創(chuàng)建任何存儲過程。sp_前綴是SQLServer用來命名系統(tǒng)存儲過程的，使用這樣的名稱可能會與以后的某些系統(tǒng)存儲過程發(fā)生沖突。11.1.3創(chuàng)建存儲過程【例11-1】在COLLEGE數(shù)據(jù)庫中創(chuàng)建存儲過程，將Mark表中所有226號課程的分?jǐn)?shù)加5分。USECOLLEGEGOCREATEPROCPROC_AddScoreASUPDATEMarkSETScore=Score+5WHERECourseID='226'GO存儲過程創(chuàng)建成功。用戶可以在該數(shù)據(jù)庫的“可編程性”選項的“存儲過程”子選項中的查看到該存儲過程。如同用戶定義函數(shù)一樣，存儲過程名前系統(tǒng)也自動加上“dbo.”作為前綴。11.1.3創(chuàng)建存儲過程【例11-2】進一步增強PROC_AddScore存儲過程的功能，由參數(shù)指定給Mark表中226號課程的分?jǐn)?shù)加分。USECOLLEGEGOCREATEPROCPROC_AddScore1@addscoINTASUPDATEMarkSETScore=Score+@addscoWHERECourseID='226'GO11.1.3創(chuàng)建存儲過程【例11-3】

創(chuàng)建帶有通配符參數(shù)的存儲過程。下面的存儲過程只從Student表中返回指定的一些學(xué)生（提供名字和姓氏）的信息。該存儲過程對傳遞的參數(shù)進行模式匹配，如果沒有提供參數(shù)，則返回所有學(xué)生的信息。USECOLLEGEGOCREATEPROCPROC_Name@stunameNCHAR(20)='%'ASSELECT*FROMStudentWHERENameLIKE@stunameGO11.1.3創(chuàng)建存儲過程【例11-4】

創(chuàng)建使用OUTPUT參數(shù)的存儲過程。以下是創(chuàng)建的存儲過程，它返回參數(shù)指定學(xué)院學(xué)生的平均年齡。通過使用OUTPUT參數(shù)，外部過程或T-SQL語句可以訪問在過程執(zhí)行期間設(shè)置的值。OUTPUT變量必須在過程創(chuàng)建和變量使用期間進行定義。參數(shù)名稱和變量名稱不一定要匹配，不過，數(shù)據(jù)類型和參數(shù)類型必須匹配。USECOLLEGEGOCREATEPROCEDUREPROC_GetStudentAveAge@schidNCHAR(2),@aveageINTOUTPUTASSet@aveage=(SELECTAVG(YEAR(GETDATE())-YEAR(Birthday))FROMStudentWHERESchoolID=@schid)GO11.1.4調(diào)用存儲過程和函數(shù)一樣，存儲過程需要通過調(diào)用才能執(zhí)行。T-SQL語句提供了EXECUTE語句執(zhí)行存儲過程?！纠?1-5】

調(diào)用【例11-1】創(chuàng)建的存儲過程。USECOLLEGEGOEXECUTEdbo.PROC_AddScoreGO【例11-6】

調(diào)用【例11-2】創(chuàng)建的存儲過程。USECOLLEGEGOEXECdbo.PROC_AddScore12GOEXECUTE可以縮寫為EXEC，由于PROC_AddScore1有參數(shù)，所以在調(diào)用時需要輸入?yún)?shù)值。參數(shù)值與存儲過程名中間用空格分開。11.1.4調(diào)用存儲過程【例11-7】

調(diào)用【例11-3】創(chuàng)建的存儲過程。USECOLLEGEGOEXECdbo.PROC_Name'王%'GO【例11-8】

執(zhí)行【例11-4】創(chuàng)建的存儲過程。USECOLLEGEGODECLARE@stubiravgINTEXECdbo.PROC_GetStudentAveAge'1',@stubiravgOUTPUTPRINT'2號學(xué)院的學(xué)生平均年齡是'+STR(@stubiravg)+'歲'GO11.1.5獲取存儲過程信息用戶可以定義存儲過程，也可以查看存儲過程。SQLServer2012提供了sp_helptext系統(tǒng)存儲過程查看存儲過程的源代碼【例11-9】

使用sp_helptext系統(tǒng)存儲過程查看存儲過程的源代碼。USECOLLEGEGOEXECsp_helptextPROC_GetStudentAveAgeGO顯示了PROC_GetStudentAveAge存儲過程的代碼。11.1.5獲取存儲過程信息還可以從系統(tǒng)視圖INFORMATION_SCHEMA.ROUTINES中獲取存儲過程信息。INFORMATION_SCHEMA.ROUTINES各列的含義如表11-1。表11-1INFORMATION_SCHEMA.ROUTINES各列的含義屬性描

述ROUTINE_CATALOG存儲過程或函數(shù)所屬的數(shù)據(jù)庫ROUTINE_SCHEMA存儲過程或函數(shù)所屬的架構(gòu)ROUTINE_NAME存儲過程或函數(shù)名ROUTINE_TYPE為存儲過程返回PROCEDURE；為函數(shù)返回FUNCTIONDATA_TYPE函數(shù)返回值的數(shù)據(jù)類型NUMERIC_PRECISION返回值的數(shù)字精度NUMERIC_PRECISION_RADIX返回值的數(shù)字精度基數(shù)NUMERIC_SCALE返回值的小數(shù)位數(shù)DATATIME_PRECISION如果返回值datetime類型，則表示秒的小數(shù)精度。否則，返回NULLROUTINE_BODY對于T-SQL函數(shù)，返回SQL；對于外部編寫的函數(shù)，返回EXTERNALROUTINE_DEFINITION如果函數(shù)或存儲過程未加密，返回函數(shù)或存儲過程的定義文本最前面的4000字符。否則，返回NULLCREATED創(chuàng)建例程的時間LAST_ALTERED最后一次修改函數(shù)的時間11.1.5獲取存儲過程信息【例11-10】

使用INFORMATION_SCHEMA.ROUTINES系統(tǒng)存儲過程查看存儲過程信息。USECOLLEGEGOSELECT*FROMINFORMATION_SCHEMA.ROUTINESGO11.1.6修改和重命名存儲過程與系統(tǒng)存儲過程不同，用戶定義存儲過程可以隨時修改和重命名。修改和重命名可以使用管理工具界面方式，也可使用命令行方式。1.管理工具界面方式單擊需要修改的用戶定義存儲過程，選擇右鍵菜單“修改”選項，即可進入“查詢編輯器”窗口存儲過程。單擊需要重命名的用戶存儲過程，選擇“重命名”選項，即可重命名存儲過程。11.1.6修改和重命名存儲過程2.命令行方式T-SQL提供了ALTERPROCEDURE語句修改用戶定義存儲過程。ALTERPROCEDURE語句的使用方法與CREATEPROCEDURE語句相似。T-SQL還提供了sp_rename系統(tǒng)存儲過程重命名存儲過程。例如重命名dbo.PROC_Name存儲過程：EXECsp_rename'dbo.PROC_Name','dbo.PROC_Name1'11.1.7重新編譯存儲過程SQLServer中，強制重新編譯存儲過程的方式有以下3種：1）sp_recompile系統(tǒng)存儲過程強制在下次執(zhí)行存儲過程時對其重新編譯。例如，下面的代碼使得存儲過程PROC_Name在下次執(zhí)行時被重新編譯：sp_recompilePROC_Name2）創(chuàng)建存儲過程時在其定義中指定WITHRECOMPILE選項，可以指明SQLServer將不為該存儲過程緩存計劃，在每次執(zhí)行該存儲過程時對其重新編譯。當(dāng)存儲過程的參數(shù)值在各次執(zhí)行間都有較大差異，導(dǎo)致每次均需創(chuàng)建不同的執(zhí)行計劃時，可使用WITHRECOMPILE選項。此選項并不常用，因為每次執(zhí)行存儲過程時都必須對其重新編譯，這樣會導(dǎo)致存儲過程的執(zhí)行速度變慢。3）可以在執(zhí)行存儲過程時指定WITHRECOMPILE選項，強制對其重新編譯。僅當(dāng)所提供的參數(shù)是非典型參數(shù)，或自創(chuàng)建該存儲過程后數(shù)據(jù)發(fā)生顯著變化時，才應(yīng)使用此選項。11.1.8刪除存儲過程如果數(shù)據(jù)庫中不再需要存儲過程了，則可以刪除。1.管理工具界面方式單擊需要刪除的用戶定義存儲過程，選擇右鍵菜單“刪除”選項。進入“刪除對象”對話框，單擊“確定”按鈕即可刪除存儲過程。11.1.8刪除存儲過程2.命令行方式T-SQL提供了DROPPROCEDURE語句刪除用戶定義存儲過程?！纠?1-11】

使用DROPPROCEDURE語句刪除存儲過程。DROPPROCEDUREPROC_Name11.2觸發(fā)器觸發(fā)器是一種特殊的存儲過程，它在特定語言事件發(fā)生時自動執(zhí)行。11.2.1觸發(fā)器概述觸發(fā)器的主要作用就是實現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)一致性。除此之外，觸發(fā)器還有其他許多不同的功能。1．強化約束（Enforcerestriction）觸發(fā)器能夠?qū)崿F(xiàn)比CHECK語句更為復(fù)雜的約束。2．跟蹤變化（Auditingchanges）觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫中未經(jīng)許可的特定更新和變化。3．級聯(lián)運行（Cascadedoperation）觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫的各項內(nèi)容。例如，某個表上的觸發(fā)器中包含有對另外一個表的數(shù)據(jù)操作（如刪除、更新、插入）,而該操作又導(dǎo)致該表上的觸發(fā)器被觸發(fā)。4．存儲過程的調(diào)用（Storedprocedureinvocation）為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多個存儲過程，甚至可以通過外部過程的調(diào)用，從而在數(shù)據(jù)庫管理系統(tǒng)本身之外進行操作。由此可見，觸發(fā)器可以實現(xiàn)高級形式的業(yè)務(wù)規(guī)則、復(fù)雜行為限制和定制記錄等功能。11.2.2觸發(fā)器的類型SQLServer包括兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。1．DML觸發(fā)器當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時，將調(diào)用DML觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的T-SQL語句。系統(tǒng)將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務(wù)對待，如果檢測到錯誤（例如，磁盤空間不足），則整個事務(wù)就自動回滾。DML觸發(fā)器可用于強制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性、查詢其他表并包括復(fù)雜的T-SQL語句。DML觸發(fā)器在以下幾方面非常有用：1）DML觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改。不過，通過級聯(lián)引用完整性約束可以更有效地進行這些更改。2）DML觸發(fā)器可以防止惡意或錯誤的INSERT、UPDATE以及DELETE操作，并強制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其他限制。與CHECK約束不同，DML觸發(fā)器可以引用其他表中的列。3）DML觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。4）一個表中的多個同類DML觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個不同的操作來響應(yīng)同一個修改語句。11.2.2觸發(fā)器的類型用戶可設(shè)計以下類型的DML觸發(fā)器。（1）AFTER觸發(fā)器在執(zhí)行INSERT、UPDATE或DELETE語句的操作之后執(zhí)行AFTER觸發(fā)器。如果違反了約束，則永遠(yuǎn)不會執(zhí)行AFTER觸發(fā)器；因此，這些觸發(fā)器不能用于任何可能防止違反約束的處理。該類型觸發(fā)器要求只有執(zhí)行某一操作（如INSERT、UPDATE或DELETE）之后，觸發(fā)器才被觸發(fā)，且只能在表上定義?？梢詾獒槍Ρ淼耐徊僮鞫x多個觸發(fā)器。（2）INSTEADOF觸發(fā)器使用INSTEADOF觸發(fā)器可以代替通常的觸發(fā)動作。還可為帶有一個或多個基表的視圖定義INSTEADOF觸發(fā)器，而這些觸發(fā)器能夠擴展視圖可支持的更新類型。INSTEADOF觸發(fā)器執(zhí)行時并不執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE），而僅是執(zhí)行觸發(fā)器本身。11.2.2觸發(fā)器的類型AFTER觸發(fā)器和INSTEADOF觸發(fā)器的功能比較，如表11-2所示。表11-2AFTER觸發(fā)器和INSTEADOF觸發(fā)器的功能比較功能AFTER觸發(fā)器INSTEADOF觸發(fā)器適用范圍表表和視圖每個表或視圖包含觸發(fā)器的數(shù)量每個觸發(fā)操作（UPDATE、DELETE和INSERT）包含多個觸發(fā)器每個觸發(fā)操作（UPDATE、DELETE和INSERT）包含一個觸發(fā)器級聯(lián)引用無任何限制條件不允許在作為級聯(lián)引用完整性約束目標(biāo)的表上使用INSTEADOFUPDATE和DELETE觸發(fā)器。執(zhí)行晚于：約束處理聲明性引用操作創(chuàng)建插入的和刪除的表觸發(fā)操作早于：約束處理替代：觸發(fā)操作晚于：創(chuàng)建插入的和刪除的表執(zhí)行順序可指定第一個和最后一個執(zhí)行不適用插入的和刪除的表中的varchar(max)、nvarchar(max)和varbinary(max)列引用。允許允許插入的和刪除的表中的text、ntext和image列引用。不允許允許11.2.2觸發(fā)器的類型2．DDL觸發(fā)器像常規(guī)觸發(fā)器一樣，DDL觸發(fā)器將激發(fā)，以響應(yīng)各種數(shù)據(jù)定義語言（DDL）事件。但與DML觸發(fā)器不同的是，它們不會為響應(yīng)針對表或視圖的UPDATE、INSERT或DELETE語句而觸發(fā)，相反，它們會為響應(yīng)多種數(shù)據(jù)定義語言語句而激發(fā)。這些語句主要是以CREATE、ALTER、DROP、GRANT、DENY、REVOKE或UPDATESTATISTICS開頭的T-SQL語句對應(yīng)。執(zhí)行DDL式操作的系統(tǒng)存儲過程也可以激發(fā)DDL觸發(fā)器。如果要執(zhí)行以下操作，可使用DDL觸發(fā)器：1）要防止對數(shù)據(jù)庫架構(gòu)進行某些更改。2）希望根據(jù)數(shù)據(jù)庫中發(fā)生的操作以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改。3）要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。僅在運行觸發(fā)DDL觸發(fā)器的DDL語句后，DDL觸發(fā)器才會激發(fā)。DDL觸發(fā)器無法作為INSTEADOF觸發(fā)器使用。11.2.3觸發(fā)器的設(shè)計規(guī)則使用T-SQL語句CREATETRIGGER可以創(chuàng)建觸發(fā)器。其語法格式如下：CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITH<dml_trigger_option>[,...n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS{sql_statement[;][...n]|EXTERNALNAME<methodspecifier[;]>}說明：1）CREATETRIGGER語句必須是批處理的第一個語句。2）表的所有者具有創(chuàng)建觸發(fā)器的默認(rèn)權(quán)限，表的所有者不能把該權(quán)限傳給其他用戶。3）觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合數(shù)據(jù)庫對象命名規(guī)則。4）盡管在觸發(fā)器的SQL語句中可以參照其他數(shù)據(jù)庫中的對象，但是，觸發(fā)器只能創(chuàng)建在當(dāng)前數(shù)據(jù)庫中。11.2.3觸發(fā)器的設(shè)計規(guī)則5）雖然觸發(fā)器可以參照視圖或臨時表，但不能在視圖或臨時表上創(chuàng)建觸發(fā)器，而只能在基表或在創(chuàng)建視圖的表上創(chuàng)建觸發(fā)器。6）一個觸發(fā)器只能對應(yīng)一個表，這是由觸發(fā)器的機制決定的。當(dāng)創(chuàng)建一個觸發(fā)器時，必須指定觸發(fā)器的名字，在哪一個表上定義觸發(fā)器，激活觸發(fā)器的修改語句，如INSERT、DELETE、UPDATE。當(dāng)然，兩個或三個不同的修改語句也可以同時觸發(fā)同一個觸發(fā)器，如INSERT和UPDATE語句可以觸發(fā)同一個觸發(fā)器。11.2.4使用觸發(fā)器本節(jié)將通過實例介紹在SQLServer2012中如何使用觸發(fā)器?！纠?1-12】

創(chuàng)建DDL觸發(fā)器。下列代碼說明了如何使用DDL觸發(fā)器來防止數(shù)據(jù)庫中的任一表被修改或刪除。USECOLLEGEGOCREATETRIGGERsafetyONdatabaseFORDROP_TABLE,ALTER_TABLEASPRINT'你必須使safety觸發(fā)器無效才能執(zhí)行對表的操作!'ROLLBACKGO展開“可編程性”下的“數(shù)據(jù)庫觸發(fā)器”，可以看到建立的safety數(shù)據(jù)庫觸發(fā)器。當(dāng)用戶試圖在數(shù)據(jù)庫中修改表的結(jié)構(gòu)或刪除表時，都會觸發(fā)safety觸發(fā)器。該觸發(fā)器顯示提示信息，并且回滾用戶試圖執(zhí)行的操作。11.2.4使用觸發(fā)器【例11-13】

使用包含提醒消息的DML觸發(fā)器。USECOLLEGEGOCREATETRIGGERreminderONStudentAFTERINSERT,UPDATEASRAISERROR('你在插入或修改學(xué)生表的數(shù)據(jù)',16,10)GO執(zhí)行后，在“數(shù)據(jù)庫觸發(fā)器”中并沒有生成新的對象，而是在Student表下的“觸發(fā)器”中生成新的觸發(fā)器對象。當(dāng)用戶向表中新添、或修改數(shù)據(jù)時，系統(tǒng)顯示提示框提示出錯，未更新任何行，按“Esc”鍵退出操作。11.2.4使用觸發(fā)器【例11-14】

顯示觸發(fā)器信息。單擊Student表下的“觸發(fā)器”中的reminder觸發(fā)器對象。選擇右鍵菜單“修改”選項，進入查詢窗口修改觸發(fā)器?！纠?1-15】

使用系統(tǒng)存儲過程sp_help查看觸發(fā)器的一般信息。sp_helpreminder【例11-16】

使用系統(tǒng)存儲過程sp_helptext查看觸發(fā)器正文。sp_helptextreminder【例11-17】

使用系統(tǒng)存儲過程sp_depends查看觸發(fā)器的引用信息。sp_dependsStudent11.2.5啟用、禁用和刪除觸發(fā)器如果觸發(fā)器不再需要，用戶可以啟用、禁用或刪除觸發(fā)器。前提是，只有觸發(fā)器所有者才有權(quán)這樣操作觸發(fā)器。用戶可以通過選擇右鍵菜單的“禁用”、“啟用”和“刪除”選項來禁用、啟用或刪用系統(tǒng)命令TRIGGER禁用指定的觸發(fā)器，其語法形式如下：DISABLETRIGGER觸發(fā)器名字ON表名用系統(tǒng)命令TRIGGER啟用指定的觸發(fā)器，其語法形式如下：ENABLETRIGGER觸發(fā)器名字ON表名也可以用系統(tǒng)命令DROPTRIGGER刪除指定的觸發(fā)器，其語法形式如下：DROPTRIGGER觸發(fā)器名字【例11-18】

禁用并刪除reminder觸發(fā)器。USECOLLEGEGODISABLETRIGGERdbo.reminderONStudentGODROPTRIGGERdbo.reminderGO刪除觸發(fā)器所在的表時，SQLServer將自動刪除與該表相關(guān)的觸發(fā)器。除觸發(fā)器。11.2.6嵌套觸發(fā)器和遞歸觸發(fā)器如果一個觸發(fā)器在執(zhí)行操作時激發(fā)了另一個觸發(fā)器，而這個觸發(fā)器又接著引發(fā)下一個觸發(fā)器，所有觸發(fā)器一次觸發(fā)，這些觸發(fā)器就是嵌套觸發(fā)器。觸發(fā)器最深可以嵌套至32層，如果嵌套鏈條中的任何觸發(fā)器引發(fā)一個無限循環(huán)，則超過最大嵌套級的觸發(fā)器將被終止，并且回滾整個事務(wù)。系統(tǒng)默認(rèn)配置允許嵌套觸發(fā)器。用戶可以通過調(diào)用系統(tǒng)存儲過程sp_configure和通過服務(wù)器實例屬性配置選項指定是否使用嵌套觸發(fā)器。調(diào)用系統(tǒng)存儲過程sp_configure的語法格式如下：EXECsp_configure'option_name','{0|1}'當(dāng)設(shè)置為1時，允許嵌套。否則不允許嵌套。或者在“服務(wù)器屬性”對話框中，選擇“高級”選項，將其中的“允許觸發(fā)器激發(fā)其他觸發(fā)器”選項設(shè)置為“True”。11.3游標(biāo)在SQLServer2012等關(guān)系數(shù)據(jù)庫中的操作會對整個行集起作用。例如，由SELECT語句返回的行集包括滿足該語句的WHERE子句中條件的所有行。這種由語句返回的完整行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的對結(jié)果集的一種擴展。11.3.1游標(biāo)概述游標(biāo)就是一種定位并控制結(jié)果集的機制，可以減少客戶端應(yīng)用程序的工作量和訪問數(shù)據(jù)庫的次數(shù)，通常在存儲過程中使用。在存儲過程中使用SELECT語句查詢數(shù)據(jù)庫時，查詢放回的數(shù)據(jù)存放在結(jié)果集中。用戶在得到結(jié)果集后，需要逐行逐列地獲取其中包含的數(shù)據(jù)，從而在應(yīng)用程序中使用這些值。游標(biāo)具有以下功能：1）允許定位在結(jié)果集的特定行。2）從結(jié)果集的當(dāng)前位置檢索一行或多行。3）支持對結(jié)果集中當(dāng)前位置的行進行數(shù)據(jù)修改。4）如果其他用戶需要對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)進行修改，游標(biāo)可以提供不同級別的可見性支持。5）提供腳本、存儲過程、觸發(fā)器中使用的、訪問結(jié)果集中的數(shù)據(jù)的T-SQL語句。在SQLServer2012中，游標(biāo)被看作是一個結(jié)果集中的記錄指針，該指針與某個查詢結(jié)果相聯(lián)系。在某一時刻，該指針只指向一條記錄，即游標(biāo)是通過移動指向記錄的指針來處理數(shù)據(jù)的。就如同用戶在瀏覽記錄時，表的全記錄就是一個結(jié)果集。用戶查看記錄通常是一行一行的，而且總有一條記錄的前面有一個黑色的三角標(biāo)識，該標(biāo)識就好像是一個記錄指針。11.3.2游標(biāo)的類型在SQLServer2012中，根據(jù)游標(biāo)的用途、使用方式等不同，可以將游標(biāo)分為多種類型。根據(jù)游標(biāo)用途的不同，SQLServer2012將游標(biāo)分為3種。（1）T-SQL游標(biāo)基于DECLARECURSOR語法，主要用于T-SQL腳本、存儲過程和觸發(fā)器。T-SQL游標(biāo)在服務(wù)器上實現(xiàn)并由從客戶端發(fā)送到服務(wù)器的T-SQL語句管理。它們還可能包含在批處理、存儲過程或觸發(fā)器中。本書只介紹T-SQL游標(biāo)的使用。（2）應(yīng)用程序編程接口（API）服務(wù)器游標(biāo)支持OLEDB和ODBC中的API游標(biāo)函數(shù)。API服務(wù)器游標(biāo)在服務(wù)器上實現(xiàn)。每次客戶端應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時，SQLNativeClientOLEDB訪問接口或ODBC驅(qū)動程序?qū)颜埱髠鬏數(shù)椒?wù)器，以便對API服務(wù)器游標(biāo)進行操作。（3）客戶端游標(biāo)由SQLNativeClientODBC驅(qū)動程序和實現(xiàn)ADOAPI的DLL在內(nèi)部實現(xiàn)?？蛻舳擞螛?biāo)通過在客戶端高速緩存所有結(jié)果集行來實現(xiàn)。每次客戶端應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時，SQLNativeClientODBC驅(qū)動程序或ADODLL就對客戶端上高速緩存的結(jié)果集行執(zhí)行游標(biāo)操作。11.3.2游標(biāo)的類型由于T-SQL游標(biāo)和API服務(wù)器游標(biāo)都在服務(wù)器上實現(xiàn)，所以它們統(tǒng)稱為服務(wù)器游標(biāo)。用服務(wù)器游標(biāo)代替客戶端游標(biāo)有以下幾個優(yōu)點：1）性能更高。在訪問游標(biāo)中的部分?jǐn)?shù)據(jù)時，使用服務(wù)器游標(biāo)能夠提供最佳的性能，因為只通過網(wǎng)絡(luò)發(fā)送提取的數(shù)據(jù)?？蛻舳擞螛?biāo)則將整個結(jié)果集高速緩存在客戶端。2）更精確的定位更新。服務(wù)器游標(biāo)直接支持定位操作，客戶端游標(biāo)可以模擬定位游標(biāo)更新，如果有多個行滿足UPDATE語句的WHERE子句的條件，這將導(dǎo)致意外更新。

3）內(nèi)存使用效率更高。在使用服務(wù)器游標(biāo)時，客戶端無需高速緩存大量數(shù)據(jù)或維護游標(biāo)位置的信息，因為這些工作由服務(wù)器完成。11.3.2游標(biāo)的類型SQLServer2012支持4種API服務(wù)器游標(biāo)類型。（1）靜態(tài)游標(biāo)靜態(tài)游標(biāo)的完整結(jié)果集在打開游標(biāo)時建立在tempdb中。靜態(tài)游標(biāo)總是按照打開游標(biāo)時的原樣顯示結(jié)果集。（2）動態(tài)游標(biāo)動態(tài)游標(biāo)與靜態(tài)游標(biāo)相反。當(dāng)滾動游標(biāo)時，動態(tài)游標(biāo)反映結(jié)果集中所做的所有更改。結(jié)果集中的行數(shù)據(jù)值、順序和成員在每次提取時都會改變。所有用戶做的全部UPDATE、INSERT和DELETE語句均通過游標(biāo)可見。（3）只進游標(biāo)只進游標(biāo)不支持滾動，它只支持游標(biāo)從頭到尾順序提取。行只在從數(shù)據(jù)庫中提取出來后才能檢索。（4）由鍵集驅(qū)動游標(biāo)打開由鍵集驅(qū)動的游標(biāo)時，該游標(biāo)中各行的成員身份和順序是固定的。由鍵集驅(qū)動的游標(biāo)由一組惟一標(biāo)識符（鍵）控制，這組鍵稱為鍵集。鍵是根據(jù)以惟一方式標(biāo)識結(jié)果集中各行的一組列生成的。鍵集是打開游標(biāo)時來自符合SELECT語句要求的所有行中的一組鍵值。由鍵集驅(qū)動的游標(biāo)對應(yīng)的鍵集是打開該游標(biāo)時在tempdb中生成的。11.3.3游標(biāo)的使用使用T-SQL游標(biāo)的流程是首先聲明游標(biāo)，然后打開游標(biāo)，再讀取游標(biāo)中的數(shù)據(jù)，獲取游標(biāo)的屬性和狀態(tài)，最后一定要關(guān)閉游標(biāo)，釋放游標(biāo)占用的資源。如果游標(biāo)不再使用，還應(yīng)該刪除游標(biāo)。1.聲明游標(biāo)聲明游標(biāo)是指利用SELECT查詢語句創(chuàng)建游標(biāo)的結(jié)構(gòu)，指明游標(biāo)的結(jié)果集中包括哪些數(shù)據(jù)。聲明游標(biāo)有兩種方式：SQL-92方式和T-SQL擴展方式。（1）SQL-92方式提供了聲明游標(biāo)語句DECLARECURSOR。其語法格式如下：DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]11.3.3游標(biāo)的使用說明：1）cursor_name為游標(biāo)名。2）INSENSITIVE表示聲明一個靜態(tài)游標(biāo)。3）SCROLL表示聲明一個滾動游標(biāo)，可使用所有的提取選項滾動，包括FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE。如果省略SCROLL，則只能使用NEXT提取選項。4）select_statement表示SELECT查詢語句。5）READONLY表示聲明一個只讀游標(biāo)。6）UPDATE指定游標(biāo)中可以更新的列。如果有OFcolumn_name，則只能修改指定的列。如果沒有，則可以修改所有列。11.3.3游標(biāo)的使用（2）T-SQL擴展方式也提供了聲明游標(biāo)語句DECLARECURSOR。其語法格式如下：DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWORD_only|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARING]FORselect_list[FORUPDATE[OFcolumn_name[,…n]]]說明：1）LOCAL為定義游標(biāo)的作用域僅限在其所在的存儲過程、觸發(fā)器或批處理中。當(dāng)建立游標(biāo)的存儲過程執(zhí)行結(jié)束后，游標(biāo)會被自動釋放。

2）GLOBAL為定義游標(biāo)的作用域，說明所聲明的游標(biāo)是全局游標(biāo)，作用于整個會話層中。只有當(dāng)用戶脫離數(shù)據(jù)庫時，該游標(biāo)才會被自動釋放。如果既未使用GLOBAL，也未使用LOCAL，那么SQLServer2012將默認(rèn)為LOCAL。11.3.3游標(biāo)的使用3）FORWARD_ONLY選項指明在從游標(biāo)中提取數(shù)據(jù)記錄時，只能按照從第一行到最后一行的順序，此時只能選用FETCHNEXT操作。4）STATIC選項的含義與INSENSITIVE選項一樣，SQLServer2012會將游標(biāo)定義所選取出來的數(shù)據(jù)記錄存放在一個臨時表內(nèi)（建立在tempdb數(shù)據(jù)庫下）。對該游標(biāo)的讀取操作皆由臨時表來應(yīng)答。5）KEYSET指出當(dāng)游標(biāo)被打開時，游標(biāo)中列的順序是固定的，并且SQLServer2012會在tempdb內(nèi)建立一個表，該表即為KEYSETKEYSET的鍵值可惟一識別游標(biāo)中的某行數(shù)據(jù)。6）DYNAMIC指明基礎(chǔ)表的變化將反映到游標(biāo)中，使用這個選項會最大程度地保證數(shù)據(jù)的一致性。然而，與KEYSET和STATIC類型游標(biāo)相比較，此類型游標(biāo)需要大量的游標(biāo)資源。7）FAST_FORWARD指明一個FORWARD_ONLY、READ_ONLY型游標(biāo)。此選項已為執(zhí)行進行了優(yōu)化。如果SCROLL或FOR_UPDATE選項被定義，則FAST_FORWARD選項不能被定義。8）SCROLL_LOCKS指明鎖被放置在游標(biāo)結(jié)果集所使用的數(shù)據(jù)上。當(dāng)數(shù)據(jù)被讀入游標(biāo)中時，就會出現(xiàn)鎖。這個選項確保對一個游標(biāo)進行的更新和刪除操作總能被成功執(zhí)行。如果FAST_FORWARD選項被定義，則不能選擇該選項。另外，由于數(shù)據(jù)被游標(biāo)鎖定，所以當(dāng)考慮到數(shù)據(jù)并發(fā)處理時，應(yīng)避免使用該選項。9）OPTIMISTIC指明在數(shù)據(jù)被讀入游標(biāo)后，如果游標(biāo)中的某行數(shù)據(jù)已發(fā)生變化，那么對游標(biāo)數(shù)據(jù)進行更新或刪除可能會導(dǎo)致失敗。如果使用了FAST_FORWARD選項，則不能使用該選項。10）TYPE_WARNING指明若游標(biāo)類型被修改成與用戶定義的類型不同時，將發(fā)送一個警告信息給客戶端。11.3.3游標(biāo)的使用【例11-19】

利用標(biāo)準(zhǔn)方式聲明一個游標(biāo)。USECOLLEGEGODECLAREStu_CurCURSORFORSELECTStudentID,Name,Sex,SchoolNameFROMStudent,SchoolWHEREStudent.SchoolID=School.SchoolIDANDSchoolName='計算機學(xué)院'FORREADONLYGO本例利用標(biāo)準(zhǔn)方式聲明一個名為Stu_Cur的游標(biāo)，是只讀的，游標(biāo)只能從頭到尾順序讀取數(shù)據(jù)。11.3.3游標(biāo)的使用【例11-20】

利用T-SQL擴展方式聲明一個游標(biāo)。USEPUBLISHGODECLAREPro_CurCURSORDYNAMICFORSELECTName,SexFROMAuthorWHEREProvinces='北京'FORUPDATEOFNameGO例利用T-SQL擴展方式聲明一個名為Pro_Cur的游標(biāo)。該游標(biāo)與單個表的查詢結(jié)果集關(guān)聯(lián)，是動態(tài)的，可前后滾動，其中Name列數(shù)據(jù)可以修改。11.3.3游標(biāo)的使用2.打開游標(biāo)聲明了游標(biāo)后，必須首先打開才能使用。T-SQL提供了打開游標(biāo)語句OPEN。其語法格式如下：OPEN[GLOBAL]cursor_name如果指定了GLOBAL，該游標(biāo)是全局游標(biāo)。【例11-21】

打開Stu_Cur游標(biāo)。USECOLLEGEGOOPENStu_CurGO【例11-22】

打開Pro_Cur游標(biāo)。USEPUBLISHGOOPENPro_CurGO11.3.3游標(biāo)的使用3.讀取游標(biāo)打開游標(biāo)后，就可以從結(jié)果集中提取數(shù)據(jù)了。T-SQL提供了讀取游標(biāo)語句FETCH。其語法格式如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]11.3.3游標(biāo)的使用說明：1）如果SCROLL選項未在標(biāo)準(zhǔn)方式的DECLARECURSOR語句中指定，則NEXT是惟一受支持的FETCH選項。如果在標(biāo)準(zhǔn)方式的DECLARECURSOR語句中指定了SCROLL選項，則支持所有FETCH選項。如果使用T-SQL擴展方式聲明游標(biāo)，則如果指定了FORWARD_ONLY或FAST_FORWARD，則NEXT是惟一受支持的FETCH選項。如果未指定DYNAMIC、FORWARD_ONLY或FAST_FORWARD選項，并且指定了KEYSET、STATIC或SCROLL中的某一個，則支持所有FETCH選項。DYNAMICSCROLL游標(biāo)支持除ABSOLUTE以外的所有FETCH選項。@@FETCH_STATUS函數(shù)報告上一個FETCH語句的狀態(tài)。相同的信息記錄在由sp_describe_cursor返回的游標(biāo)中的fetch_status列中。這些狀態(tài)信息應(yīng)該用于在對由FETCH語句返回的數(shù)據(jù)進行任何操作之前，以確定這些數(shù)據(jù)的有效性。11.3.3游標(biāo)的使用2）NEXT為緊跟當(dāng)前行返回的結(jié)果行，并且當(dāng)前行遞增為返回行。如果FETCHNEXT為對游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認(rèn)的游標(biāo)提取選項。PRIOR為返回緊