第九章 存儲(chǔ)過(guò)程與觸發(fā)器的創(chuàng)建與維護(hù)pptConvertor

1、第九章 存儲(chǔ)過(guò)程與觸發(fā)器的創(chuàng)建與維護(hù) 本章要點(diǎn)：了解存儲(chǔ)過(guò)程和觸發(fā)器的概念和特點(diǎn) 掌握存儲(chǔ)過(guò)程和觸發(fā)器的創(chuàng)建、管理、刪除 掌握存儲(chǔ)過(guò)程和觸發(fā)器的主要區(qū)別本章教學(xué)內(nèi)容:9.1 概述9.2 存儲(chǔ)過(guò)程9.3 觸發(fā)器9.4 存儲(chǔ)過(guò)程與觸發(fā)器的應(yīng)用在大型數(shù)據(jù)庫(kù)系統(tǒng)中，存儲(chǔ)過(guò)程和觸發(fā)器具有很重要的作用。存儲(chǔ)過(guò)程是SQL語(yǔ)句和控制流語(yǔ)句組成的集合；觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程；9.1 概述 定義：將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)過(guò)程，將其保存在數(shù)據(jù)庫(kù)中，并由SQL Server服務(wù)器通過(guò)過(guò)程名來(lái)調(diào)用它們，這些過(guò)程就叫做存儲(chǔ)過(guò)程。使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：實(shí)現(xiàn)了模塊化編程?？稍趩蝹€(gè)存儲(chǔ)過(guò)程中執(zhí)行

2、一系列SQL語(yǔ)句?？蓮淖约旱拇鎯?chǔ)過(guò)程內(nèi)引用其他存儲(chǔ)過(guò)程，簡(jiǎn)化復(fù)雜語(yǔ)句?？赡芙邮茌斎?yún)?shù)并返回輸出值。具有對(duì)數(shù)據(jù)庫(kù)立即訪問功能?？梢詼p少網(wǎng)絡(luò)流量。提高數(shù)據(jù)庫(kù)的安全性。存儲(chǔ)過(guò)程既有參數(shù)又有返回值，其與函數(shù)的區(qū)別：存儲(chǔ)過(guò)程的返回值只是指明執(zhí)行是否成功；函數(shù)的返回值可直接被用在表達(dá)式中；分類：系統(tǒng)存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程用戶自定義存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程：由系統(tǒng)自動(dòng)創(chuàng)建，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)，一般以sp_作為前綴。主要是從系統(tǒng)表中獲取信息。用于管理SQL Server和顯示數(shù)據(jù)庫(kù)和用戶信息?？梢噪S時(shí)調(diào)用，不必在其前加上數(shù)據(jù)庫(kù)名。擴(kuò)展存儲(chǔ)過(guò)程：屬于動(dòng)態(tài)鏈接庫(kù)，SQL Server可以動(dòng)態(tài)加載與執(zhí)行，通常

3、是或語(yǔ)言寫成的。一般以xp_作為前綴。用戶自定義存儲(chǔ)過(guò)程：由用戶創(chuàng)建并能完成某一特定功能的存儲(chǔ)過(guò)程。是一種特殊的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是通過(guò)存儲(chǔ)過(guò)程名被直接調(diào)用，觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行。是一個(gè)功能強(qiáng)大的工具，可使每個(gè)站點(diǎn)可在有數(shù)據(jù)修改時(shí)自動(dòng)強(qiáng)制執(zhí)行其業(yè)務(wù)規(guī)則?？梢杂糜谕暾詸z查，可以實(shí)現(xiàn)更加復(fù)雜的數(shù)據(jù)完整性。分類：After觸發(fā)器Instead of觸發(fā)器After觸發(fā)器在數(shù)據(jù)變動(dòng)（insert、update、delete）完成以后才被觸發(fā)。只能在表上定義。同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)After觸發(fā)器。Instead of觸發(fā)器在數(shù)據(jù)變動(dòng)以前被觸發(fā)，并取代數(shù)據(jù)的操作（insert、up

4、date、delete） ，而去執(zhí)行觸發(fā)器定義的操作。可以在表或視圖上定義。關(guān)于觸發(fā)器的規(guī)定：只在觸發(fā)它的語(yǔ)句完成后執(zhí)行。如果語(yǔ)句在表中執(zhí)行違反條件約束或引起錯(cuò)誤，觸發(fā)器不會(huì)觸動(dòng)。一個(gè)語(yǔ)句只能觸動(dòng)一次觸發(fā)器。9.2 存儲(chǔ)過(guò)程使用方法：使用向?qū)褂闷髽I(yè)管理器使用查詢分析器使用查詢分析器：不能將Create Procedure語(yǔ)句和其它SQL語(yǔ)句組合到單個(gè)批處理中只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象，必須遵守標(biāo)識(shí)符規(guī)則權(quán)限默認(rèn)屬于數(shù)據(jù)庫(kù)所有者，該所有者可將此權(quán)限授予其他用戶創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式為：CREATE PROCEDUREprocedure_name;number param

5、eter data_typeVARYING=defaultOUTPUT,nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statementn 使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程 procedure_name：要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名稱。如果要?jiǎng)?chuàng)建全局臨時(shí)存儲(chǔ)過(guò)程，則需要在名稱前面加#；如果要?jiǎng)?chuàng)建局部臨時(shí)存儲(chǔ)過(guò)程，則需要在名稱前面加#。number ：用于對(duì)同名的過(guò)程進(jìn)行分組的整數(shù)，以方便批量刪除。parameter：語(yǔ)句中聲明的參數(shù)，如果沒有給參數(shù)定義默認(rèn)值，則用戶必須在執(zhí)行過(guò)程時(shí)提供參數(shù)的值，最多可以有1024個(gè)

6、參數(shù)。Data_type：參數(shù)的數(shù)據(jù)類型。其中cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)，且必須同時(shí)指定VARYING 和OUTPUT關(guān)鍵字VARYING：指定作為OUTPUT參數(shù)支持的結(jié)果集，僅使用于cursor。Default：參數(shù)的默認(rèn)值，必須是常量或NULL，可以包含通配符。OUTPUT：表明參數(shù)將返回值。此值可以返回給EXECUTE。RECOMPILE：表示不在緩存中保存存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃。可以在使用臨時(shí)值不希望覆蓋緩存中的執(zhí)行計(jì)劃時(shí)使用。ENCRYPTION：表示對(duì)存儲(chǔ)在表syscomments中的存儲(chǔ)過(guò)程文本進(jìn)行加密，以防止其他用戶查看或修改。 例9-1 創(chuàng)建包含SELECT語(yǔ)

7、句的存儲(chǔ)過(guò)程。 use 實(shí)例 go create procedure 查詢表1 as select * from 學(xué)生表 go 成功創(chuàng)建存儲(chǔ)過(guò)程后，存儲(chǔ)過(guò)程名存儲(chǔ)在sysobjects系統(tǒng)表中，程序文本存儲(chǔ)在syscomments中。一、查看存儲(chǔ)過(guò)程在企業(yè)管理器中查看存儲(chǔ)過(guò)程的定義在企業(yè)管理器中查看存儲(chǔ)過(guò)程的相關(guān)性使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程sp_help：查看有關(guān)數(shù)據(jù)庫(kù)對(duì)象、用戶定義數(shù)據(jù)類型或SQL Server所提供的數(shù)據(jù)類型的信息sp_helptext：顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義函數(shù)、觸發(fā)器或視圖文本。sp_depends：顯示有關(guān)數(shù)據(jù)庫(kù)對(duì)象相關(guān)性的信息。s

8、p_stored_procedures：返回當(dāng)前環(huán)境中的存儲(chǔ)過(guò)程列表。例9-2查看存儲(chǔ)過(guò)程信息 use 實(shí)例 go sp_help 查詢表1 go sp_helptext 查詢表1 go sp_depends 查詢表1 go sp_stored_procedures go如果運(yùn)行單個(gè)系統(tǒng)存儲(chǔ)過(guò)程，結(jié)果如何？運(yùn)行多個(gè)系統(tǒng)存儲(chǔ)過(guò)程時(shí)，如果去掉存儲(chǔ)過(guò)程間的go，運(yùn)行結(jié)果將會(huì)如何？二、重命名存儲(chǔ)過(guò)程定義sp_rename 原存儲(chǔ)過(guò)程名，新存儲(chǔ)過(guò)程名例9-3 sp_rename 查詢表1 ,查詢表使用EXECUTE語(yǔ)句可以在任何情況下執(zhí)行存儲(chǔ)過(guò)程，如果存儲(chǔ)過(guò)程是批處理中的第一條語(yǔ)句，或是批處理操作的唯一

9、語(yǔ)句，那么可以省略EXECUTE關(guān)鍵字。use 實(shí)例exec 查詢表go 或 查詢表go如果需要在啟動(dòng)SQL Server時(shí)，系統(tǒng)自動(dòng)執(zhí)行存儲(chǔ)過(guò)程，可以使用sp_procoption進(jìn)行設(shè)置。 執(zhí)行存儲(chǔ)過(guò)程語(yǔ)句的語(yǔ)法格式為：EXECUTE return_staus=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,nWITH RECOMPILE手動(dòng)執(zhí)行存儲(chǔ)過(guò)程參數(shù)說(shuō)明returm_status：用于保存存儲(chǔ)過(guò)程返回狀態(tài)的整型變量。這個(gè)變量在用于EXECUTE語(yǔ)句前，必須在批處理、存儲(chǔ)過(guò)程

10、或函數(shù)中聲明過(guò)。procedure_name_var：局部定義變量名，代表存儲(chǔ)過(guò)程名稱。Value：存儲(chǔ)過(guò)程的參數(shù)值。variable：用來(lái)保存參數(shù)或者返回參數(shù)的變量。DEFAULT：表示參數(shù)值取默認(rèn)值。 權(quán)限存儲(chǔ)過(guò)程的所有者默認(rèn)擁有對(duì)該存儲(chǔ)過(guò)程指定EXECUTE的權(quán)限，該權(quán)限可以轉(zhuǎn)讓。 如果每次啟動(dòng) SQL Server 2000時(shí)都啟動(dòng)某個(gè)存儲(chǔ)過(guò)程，可以使用sp_procoption進(jìn)行設(shè)置，這對(duì)于需要作為后臺(tái)進(jìn)程運(yùn)行的存儲(chǔ)過(guò)程十分有效。每個(gè)自動(dòng)執(zhí)行存儲(chǔ)過(guò)程都占用一個(gè)連接，所以可以使用過(guò)程嵌套執(zhí)行存儲(chǔ)過(guò)程，以減少資源使用。 sp_procoptionProcName=procedure ,

11、OptionName=option ,OptionValue=value 自動(dòng)執(zhí)行存儲(chǔ)過(guò)程參數(shù)說(shuō)明ProcName=procedure：存儲(chǔ)過(guò)程的名稱。Procedure為nvarchar(766)類型，無(wú)默認(rèn)值。OptionName=option：設(shè)置存儲(chǔ)過(guò)程的選項(xiàng)名稱。其唯一值是startup，表示是否將存儲(chǔ)過(guò)程設(shè)為每次自動(dòng)執(zhí)行。OptionValue=value：表示選項(xiàng)狀態(tài)。其值可以分開（true或on）或者關(guān)（false或off）。Value為varchar(12)類型，無(wú)默認(rèn)值。 權(quán)限sysadmin固定服務(wù)器角色的成員默認(rèn)擁有p_procoption執(zhí)行權(quán)限。 刪除存儲(chǔ)過(guò)程可以將

12、一個(gè)、多個(gè)存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除。對(duì)于sp_開頭的系統(tǒng)存儲(chǔ)過(guò)程，將在master數(shù)據(jù)庫(kù)中刪除。刪除存儲(chǔ)過(guò)程的語(yǔ)法格式為：DROP PROCEDUREprocedure,n參數(shù)說(shuō)明procedure：要?jiǎng)h除的存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組的名稱。權(quán)限存儲(chǔ)過(guò)程的所有者默認(rèn)擁有DROP PROCEDURE權(quán)限，該權(quán)限不可轉(zhuǎn)讓。db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色成員和sysadmin固定服務(wù)器角色成員可以通過(guò)在DROP PROCEDURE內(nèi)指定所有者除去任何對(duì)象。 9.3 觸發(fā)器規(guī)定：觸發(fā)器只在觸發(fā)它的語(yǔ)句完成后執(zhí)行如果語(yǔ)句在表中執(zhí)行違反條件約束或引起錯(cuò)誤，觸發(fā)器不會(huì)觸動(dòng)觸發(fā)

13、器視為單一事務(wù)中的一部分，因此可以由原觸發(fā)器復(fù)原事務(wù)，如果在事務(wù)過(guò)程中檢測(cè)到嚴(yán)重錯(cuò)誤，則會(huì)自動(dòng)復(fù)原整個(gè)事務(wù)。一個(gè)語(yǔ)句只能觸動(dòng)一次觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器使用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器創(chuàng)建時(shí)可以相用其他數(shù)據(jù)庫(kù)中的對(duì)象。只有表的所有者創(chuàng)建觸發(fā)器，且不能將該權(quán)限轉(zhuǎn)給其他用戶。使用方法：使用企業(yè)管理器使用查詢分析器使用查詢分析器CREATE TRIGGER trigger_name ON table|viewWITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE, INSERT, UPDATE WITH APPEND NOT FOR RE

14、PLICATIONAS IF UPDATE(column) AND|ORUPDATE(column) n | IF(COLUMNS_UPDATED()bitwise_operator updated_bitmask) comparison_operator column_bitmaskn sql_statementn trgger_name：觸發(fā)器名稱。Table|view：執(zhí)行觸發(fā)器的表或視圖。WITH ENCRYPTION：指定SQL Server在syscomments表中對(duì)觸發(fā)器定義文本加密后存儲(chǔ)。AFTER：表示當(dāng)所有操作，包括約束執(zhí)行完成后再激發(fā)觸發(fā)器。該關(guān)鍵字不能用于視圖。INS

15、TEAD OF：指定由執(zhí)行觸發(fā)器代替執(zhí)行觸發(fā)SQL語(yǔ)句。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句最多可以定義一個(gè)INSTEAD OF觸發(fā)器。DELETE ,INSERT , UPDATE:指定在表或視圖上激活觸發(fā)器的語(yǔ)句。至少指定一個(gè)選項(xiàng)，如果指定多個(gè)選項(xiàng)，則需要用逗號(hào)分隔。WITH APPENG：說(shuō)明當(dāng)前定義的觸發(fā)器類型載表中已經(jīng)存在。該參數(shù)不能與INSTEAD OF 觸發(fā)器或AFTER觸發(fā)器一起使用。這個(gè)參數(shù)注意為了兼容早期版本，在SQL Server2000 中不必使用。NOT FOR REPLICATION：表示當(dāng)復(fù)制進(jìn)程修改觸發(fā)器所在表時(shí)，不應(yīng)執(zhí)行觸發(fā)器。AS：

16、觸發(fā)器要執(zhí)行的操作。Sql_statement：觸發(fā)器執(zhí)行條件和操作。IF UPDATE():檢測(cè)在指定列上進(jìn)行插入或修改操作,該參數(shù)不能用于刪除操作.可以指定多列。 Column:IF UPDATE子句進(jìn)行檢測(cè)的列名.可以是除計(jì)算機(jī)列以外的其他任何一種數(shù)據(jù)類型。IF （COLUMNS_UPDATED()）：檢測(cè)指定列是否被插入或修改，不能用于刪除操作。Bitwise_operator：用于比較運(yùn)算的位運(yùn)算符。Updated_bitmask：整型位掩碼，表示實(shí)際更新或插入的列。Comparison_operator：比較運(yùn)算符。使用等于比較運(yùn)算符（=）檢查updated_bitmask中指定的

17、所有列是否都實(shí)際進(jìn)行了更新。使用大于運(yùn)算符（>）檢查 updated_bitmask中指定的任何一列或某些列是否已更新。Column_bitmask：要檢測(cè)列的整型位掩碼，用來(lái)檢查是否已更新或插入了這些列。例9-3創(chuàng)建簡(jiǎn)單的觸發(fā)器，當(dāng)向?qū)W生表中插入、更新或刪除數(shù)據(jù)時(shí)，該觸發(fā)器向客戶端顯示一條消息。use 實(shí)例gocreate trigger 更新表2on 學(xué)生表for insert,update,deleteasprint '插入,更新或刪除學(xué)生表'使用企業(yè)管理器查看觸發(fā)器信息使用企業(yè)管理器查看觸發(fā)器的相關(guān)性使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器sp_helptrigger 表名：返

18、回指定表中定義的當(dāng)前數(shù)據(jù)庫(kù)的觸發(fā)器類型。sp_help觸發(fā)器名 ：用于查看觸發(fā)器的一般信息。 sp_helptext觸發(fā)器名 sp_depends觸發(fā)器名表名用DROP TRIGGER語(yǔ)句可以刪除不再需要的觸發(fā)器，此時(shí)原來(lái)的觸發(fā)表以及表中的數(shù)據(jù)不影響。如果刪除表，則表中所有的觸發(fā)器都將被自動(dòng)刪除。刪除觸發(fā)器語(yǔ)法格式為：DROP TRIGGERtriggern參數(shù)說(shuō)明trigger：要?jiǎng)h除觸發(fā)器的名稱。9.4 存儲(chǔ)過(guò)程與觸發(fā)器的應(yīng)用變量的使用值的回傳使用return回傳值存儲(chǔ)過(guò)程執(zhí)行到return時(shí)停止，并回到調(diào)用程序中的下一個(gè)語(yǔ)句，return也可傳回整數(shù)值。使用select回傳值例：建立參數(shù)

19、性別_1。當(dāng)執(zhí)行存儲(chǔ)過(guò)程時(shí)，輸入性別，該存儲(chǔ)過(guò)程就會(huì)根據(jù)性別顯示學(xué)生信息。（使用參數(shù)） use 實(shí)例 go create proc 根據(jù)性別顯示學(xué)生信息 性別_1 char(4) as select 學(xué)號(hào),姓名 from 學(xué)生表 where 性別=性別_1 return 改進(jìn)在建立存儲(chǔ)過(guò)程前，要先確定是否有重復(fù)的名稱存在，必須用未存在的名稱命名存儲(chǔ)過(guò)程，或是先刪除已經(jīng)存在的名稱后再重新命名。use 實(shí)例 if exists(select name from sysobjects where name='根據(jù)性別顯示學(xué)生信息' and type='p') drop procedure 根據(jù)性別顯示學(xué)生信息 go create proc 根據(jù)性別顯示學(xué)生信息 性別_1 char(4) as select 學(xué)號(hào),姓名 from 學(xué)生表 wh