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

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

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

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

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

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

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

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

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

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

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

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

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

13、器視為單一事務(wù)中的一部分，因此可以由原觸發(fā)器復(fù)原事務(wù)，如果在事務(wù)過程中檢測到嚴(yán)重錯誤，則會自動復(fù)原整個事務(wù)。一個語句只能觸動一次觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器使用T-SQL語句創(chuàng)建觸發(fā)器只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器創(chuàng)建時可以相用其他數(shù)據(jù)庫中的對象。只有表的所有者創(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表中對觸發(fā)器定義文本加密后存儲。AFTER：表示當(dāng)所有操作，包括約束執(zhí)行完成后再激發(fā)觸發(fā)器。該關(guān)鍵字不能用于視圖。INS

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

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

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

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

19、性別_1。當(dāng)執(zhí)行存儲過程時，輸入性別，該存儲過程就會根據(jù)性別顯示學(xué)生信息。（使用參數(shù)） use 實例 go create proc 根據(jù)性別顯示學(xué)生信息 性別_1 char(4) as select 學(xué)號,姓名 from 學(xué)生表 where 性別=性別_1 return 改進(jìn)在建立存儲過程前，要先確定是否有重復(fù)的名稱存在，必須用未存在的名稱命名存儲過程，或是先刪除已經(jīng)存在的名稱后再重新命名。use 實例 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é)號,姓名 from 學(xué)生表 wh