第6章-存儲過程與觸發(fā)器

數(shù)據(jù)庫技術(shù)與應(yīng)用數(shù)據(jù)庫技術(shù)與應(yīng)用教材編寫組第六章存儲過程與觸發(fā)器存儲過程的基本概念存儲過程的特點(diǎn)與作用觸發(fā)器的基本概念觸發(fā)器的特點(diǎn)與作用存儲過程創(chuàng)建、執(zhí)行以及參數(shù)應(yīng)用的方法觸發(fā)器的創(chuàng)建及使用方法存儲過程的參數(shù)應(yīng)用方法2問題提出為什么需要存儲過程？存儲過程是什么？為什么要觸發(fā)器？觸發(fā)器是什么？3？6.1存儲過程概述存儲過程的特點(diǎn)和類型存儲過程的創(chuàng)建和執(zhí)行存儲過程參數(shù)和執(zhí)行狀態(tài)存儲過程的查看和修改存儲過程的刪除46.1.1存儲過程的特點(diǎn)和類型存儲過程是存儲在服務(wù)器上的Transact-SQL語句的命名集合。是封裝重復(fù)任務(wù)的方法存儲過程的特點(diǎn)封裝復(fù)雜操作加快系統(tǒng)運(yùn)行速度實(shí)現(xiàn)代碼重用增強(qiáng)安全性減少網(wǎng)絡(luò)流量調(diào)用方便5查詢通知的工作流數(shù)據(jù)庫監(jiān)視6.1.1存儲過程的特點(diǎn)和類型存儲過程的類型SQLServer2008中常用的存儲過程類型有3種：系統(tǒng)存儲過程（sp_）:由數(shù)據(jù)庫系統(tǒng)自身創(chuàng)建，存儲在master數(shù)據(jù)庫中，以“sp_”前綴標(biāo)識用戶定義存儲過程（本地存儲過程）:

在單獨(dú)的用戶數(shù)據(jù)庫內(nèi)由用戶創(chuàng)建。臨時存儲過程：可以是局部的，名稱以“#”開頭；也可以是全局的，名稱以“##”開頭。擴(kuò)展存儲過程（xp_）:以動態(tài)鏈接庫（DLL）的形式實(shí)現(xiàn)。以“xp_”為前綴，只能添加到master數(shù)據(jù)庫中，在SQLServer環(huán)境外執(zhí)行。66.1.2存儲過程的創(chuàng)建和執(zhí)行創(chuàng)建存儲過程實(shí)際是對存儲過程進(jìn)行定義的過程,主要包含：存儲過程名稱及其參數(shù)的說明和存儲過程的主體（包含執(zhí)行過程操作的T-SQL語句）兩部分?？梢允褂?種方法創(chuàng)建存儲過程：

使用圖形工具使用向?qū)褂肨ransact-SQL語言中的CREATEPROCEDURE語句76.1.2存儲過程的創(chuàng)建和執(zhí)行使用圖形工具創(chuàng)建存儲過程86.1.2存儲過程的創(chuàng)建和執(zhí)行使用CREATEPROCEDURE語句創(chuàng)建存儲過程

語法格式如下：

CREATEPROC[EDURE][schema_name.]procedure_name[;number] [{@parameter[schema_name.]data_type} [VARYINGdefault][OUTPUT]] [WITH{RECOMPILE|ENCRYPTION |RECOMPILE,ENCRYPTION}] [FORREPLICATION] AS

sql_statement[...n]9注意事項(xiàng)：只能在本地數(shù)據(jù)庫中創(chuàng)建存儲過程；可以引用在同一存儲過程中創(chuàng)建的對象，只要引用時已經(jīng)創(chuàng)建了該對象即可可以在存儲過程內(nèi)引用臨時表，如果在存儲過程內(nèi)創(chuàng)建本地臨時表，則臨時表僅為該存儲過程而存在；退出該存儲過程后，臨時表將消失根據(jù)可用內(nèi)存的不同，存儲過程最大可達(dá)128MB

注意：schema_name表示架構(gòu)名,如dbo.student,其中dbo是一個架構(gòu)名，表示系統(tǒng)管理員。ENCRYPTION表示加密；REPLICATION表示復(fù)制6.1.2存儲過程的創(chuàng)建和執(zhí)行【例6.1】在Student數(shù)據(jù)庫中創(chuàng)建一個名為p_Stu的存儲過程，它將從表中返回所有學(xué)生的姓名、性別、班級、電話

存儲過程只能建立在當(dāng)前數(shù)據(jù)庫上，故需先用USE語句來指定數(shù)據(jù)庫USEStudentGo存儲過程的內(nèi)容如下：

CREATEPROCEDUREp_StuAS

SELECTSt_ID,St_Sex,Cl_Name,TelephoneFROMSt_Info

以存儲過程是從單個表中提取數(shù)據(jù)，最終返回了學(xué)生的簡明信息。106.1.2存儲過程的創(chuàng)建和執(zhí)行【例6.2】創(chuàng)建一個帶SELECT查詢語句的名為“Average_Score”的存儲過程。從學(xué)生表、課程表、選課表返回每位修課學(xué)生的課程平均分。

分析：

學(xué)生表與選課表通過“St_ID”關(guān)聯(lián)，

課程表與選課表通過“C_No”關(guān)聯(lián)，

要查到每個學(xué)生的修課平均分，需要通過聚集函數(shù)AVG計算，因?yàn)橐昧司奂瘮?shù)，SELECT查詢中必須使用GROUPBY分組。

11CREATEPROCAverage_Score

ASSELECT

St_Name,AVG(Score)ASAvgScoreFROMSt_Info,S_C_Info,C_InfoWHERESt_Info.St_ID=S_C_Info.St_ID

ANDS_C_Info.C_No=C_Info.C_NoGROUPBY

St_Name

6.1.2存儲過程的創(chuàng)建和執(zhí)行使用EXECUTE(或EXEC)命令執(zhí)行存儲過程

語法格式如下：

[[EXEC[UTE]]{[@return_status=]procedure_name[;number]

|@procedure_name_var} [[@parameter=]{value|@variable

[OUTPUT]|[DEFAULT]][,...n] [WITHRECOMPILE]

注意事項(xiàng)：執(zhí)行存儲過程必須具有執(zhí)行該過程的權(quán)限許可

如果存儲過程是批處理中的第一條語句，EXECUTE命令可以省略存儲過程的最大大小為128MB12WITHRECOMPILE表示過程在運(yùn)行時重新編譯6.1.2存儲過程的執(zhí)行例6.3執(zhí)行例【例6.2】所創(chuàng)建的存儲過程Average_Score。新建查詢，在“查詢設(shè)計器”中輸入并運(yùn)行以下語句：USEStudentGOEXECAverage_Score輸出結(jié)果如右圖所示。136.1.2存儲過程的執(zhí)行使用對象資源管理器中執(zhí)行存儲過程，操作方法如下：

146.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)存儲過程參數(shù)類型有：“輸入”和“輸出”參數(shù)（1）輸入?yún)?shù)

定義存儲過程時，可指定輸入?yún)?shù)，以@作為參數(shù)名稱的前置字符，聲明若干個參數(shù)變量及其數(shù)據(jù)類型，一個存儲過程最多指定1024個參數(shù)。（2）輸出參數(shù)如果要在存儲過程中傳回值給調(diào)用者，可在參數(shù)名稱后使用OUTPUT關(guān)鍵詞。同時，為了使用輸出參數(shù)，必須在創(chuàng)建和執(zhí)行存儲過程時都使用OUTPUT關(guān)鍵詞。156.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)【例6.4】創(chuàng)建一個帶兩個參數(shù)的存儲過程，從St_Info、C_Info、S_C_Info表的相關(guān)聯(lián)接中返回輸入?yún)?shù)的學(xué)生姓名和課程類別、該學(xué)生選課的課程名稱和成績。

CREATEPROCEDUREScoreInfo@stnamevarchar(20),@ctypechar(4)AS

SELECTSt_Name,C_Type,C_Name,Score FROMSt_Infoa,S_C_Infob,C_Infoc WHEREa.St_ID=b.St_IDANDb.C_No=c.C_NoAND

St_Name=@stnameANDC_Type=@ctype在“新建查詢”窗格中輸入并運(yùn)行如下命令：

EXECScoreInfo

'吳中華','必修'輸出結(jié)果:166.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)兩種傳遞參數(shù)的方式：位置標(biāo)識和名字標(biāo)識。位置標(biāo)識傳遞參數(shù)只按順序提供值參數(shù)值必須以參數(shù)的定義順序列出可以忽略有默認(rèn)值的參數(shù)，但不能中斷次序例：EXECScoreInfo'吳中華','必修'

名字標(biāo)識傳遞值在調(diào)用語句中以“@參數(shù)名=值”的格式指定參數(shù)當(dāng)通過參數(shù)名傳遞值時，可以以任何順序指定參數(shù)值，并且可省略允許空值或具有默認(rèn)值的參數(shù)若在“新建查詢”窗格中輸入并運(yùn)行如下命令：

EXECScoreInfo

@ctype='必修',@stname='吳中華'EXECScoreInfo

@ctype='必修',@stname='楊平娟'176.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)-舉例【例6.5】創(chuàng)建帶一個輸入?yún)?shù)和一個輸出參數(shù)的存儲過程，通過輸入?yún)?shù)在St_Info表中查詢指定學(xué)號的學(xué)生，以輸出參數(shù)的形式返回學(xué)生所在的班級名稱（Cl_Name字段）。

創(chuàng)建此存儲過程的語句如下：

CREATEPROCStClass

@stidchar(10),@class_namechar(20)

OUTPUTAS SELECT@class_name

=cl_name

FROMSt_Info

WHERESt_Info.St_ID=@stid

執(zhí)行該存儲過程的語句如下：

DECLARE

@get_clname

char(20)EXECStClass'0603060109',@get_clnameOUTPUTSELECT@get_clname注意：變量被聲明，其值會先被設(shè)為NULL。186.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)-舉例使用“執(zhí)行過程”對話框操作196.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)返回存儲過程狀態(tài)為了增強(qiáng)存儲過程的效率，應(yīng)使用錯誤信息向用戶傳達(dá)事務(wù)狀態(tài)（成功或失?。?。RETURN語句從查詢或存儲過程無條件返回，同時可以返回一個整數(shù)狀態(tài)值（返回碼）返回碼為0表示執(zhí)行成功，返回-1～-99之間的整數(shù)，表示執(zhí)行失敗。206.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)【例6.6】修改【例6.5】示例中的存儲過程，分3種情況返回不同的執(zhí)行狀態(tài)：如果輸入空的學(xué)號參數(shù)值，則返回執(zhí)行狀態(tài)“-1”；如果在St_Info表中不存在指定學(xué)號的學(xué)生，則返回執(zhí)行狀態(tài)“-2”；除前兩種情況之外（即找到了指定學(xué)號的學(xué)生），則返回執(zhí)行狀態(tài)“0”表示執(zhí)行正常。修改此存儲過程的語句如下：

CREATEPROCEDUREStClass_new

@stid

char(10)=NULL,@class_name

char(20)OUTPUTASIF@stidISNULL

RETURN-1--IF或ELSE條件只能影響一個SQL語句（p39）SELECT@class_name=cl_name

FROMSt_Info WHERESt_Info.St_ID=@stidIF@class_nameISNULL

RETURN-2RETURN0216.1.3存儲過程參數(shù)和執(zhí)行狀態(tài)正確接收返回的狀態(tài)的存儲過程執(zhí)行語句形式：

EXEC@status_var=過程名稱調(diào)用帶輸出參數(shù)的存儲過程，根據(jù)返回碼進(jìn)行輸出【例6.6】的執(zhí)行過程：

DECLARE@status_return

intDECLARE@get_clnamechar(20)EXEC@status_return=StClass_new

'0603170109'

,@get_clname

OUTPUT

IF@status_return=-1 PRINT'沒有輸入學(xué)號'ELSE

IF@status_return=-2 PRINT'找不到這個學(xué)號的學(xué)生' ELSE PRINT@get_clname

226.1.4存儲過程的查看和修改使用對象資源管理器查看或修改存儲過程236.1.4存儲過程的查看和修改使用系統(tǒng)存儲過程查看存儲過程24系統(tǒng)存儲過程作

用使用語法sp_helptext查看存儲過程的文本信息sp_helptext[@objname=]存儲過程名sp_depends查看存儲過程的相關(guān)性sp_depends[@objname=]存儲過程名sp_help查看存儲過程的一般信息sp_help[@objname=]存儲過程名6.1.4存儲過程的查看和修改使用ALTERPROCEDURE語句修改存儲過程語法格式：ALTERPROC[EDURE][schema_name.]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]參數(shù)和保留字的含義說明與CREATEPROCEDURE語句一致。256.1.4存儲過程的查看和修改重命名存儲過程可使用系統(tǒng)存儲過程sp_rename，語法格式：

sp_rename'storedprocedureobject_name', 'storedprocedurenew_name'【例6.8】將【例6.1】創(chuàng)建的存儲過程p_Stu更名為Student_proc。完成操作的語句：

sp_rename'p_Stu','Student_proc'注意：通過對象資源管理器也可以修改存儲過程的名稱26存儲過程老名稱存儲過程新名稱6.1.5存儲過程的刪除刪除存儲過程使用DROPPROCEDURE語句從當(dāng)前數(shù)據(jù)庫中移除用戶定義存儲過程語法格式：DROPPROC[EDURE]{procedure_name}[,...n]刪除存儲過程的注意事項(xiàng)在刪除存儲過程之前，執(zhí)行系統(tǒng)存儲過程sp_depends檢查是否有對象依賴此存儲過程。276.1.5存儲過程的刪除【例6.10】刪除例6.2所創(chuàng)建的存儲過程Average_Score。完成操作的語句：USEstudent_dbGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='Average_Score')DROPPROCEDUREAverage_Scoreelseprint'Average_Score存儲過程不存在'注意：不論是重命名存儲過程名稱還是刪除了存儲過程，都會影響到引用該存儲過程的其他數(shù)據(jù)庫對象。28在系統(tǒng)視圖下可以找到系統(tǒng)對象表sysobjects

歸屬于sys架構(gòu)，它保存當(dāng)前數(shù)據(jù)庫的對象，如約束、默認(rèn)值、日志、規(guī)則、存儲過程等6.2觸發(fā)器概述觸發(fā)器的特點(diǎn)和類型觸發(fā)器的創(chuàng)建觸發(fā)器的查看和修改觸發(fā)器的刪除296.2.1觸發(fā)器的特點(diǎn)和類型觸發(fā)器（trigger）是SQLServer數(shù)據(jù)庫中一種特殊類型的存儲過程，不能由用戶直接調(diào)用，而且可以包含復(fù)雜的T-SQL語句。它是一個在修改指定表中的數(shù)據(jù)時執(zhí)行的存儲過程。用戶可以用它來強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)則，以此確保數(shù)據(jù)的完整性。306.2.1觸發(fā)器的特點(diǎn)和類型觸發(fā)器的特點(diǎn)觸發(fā)器與表緊密相連，可以看作表定義的一部分。觸發(fā)器是基于一個表創(chuàng)建的，但是可以針對多個表進(jìn)行操作，實(shí)現(xiàn)數(shù)據(jù)庫中相關(guān)表的級聯(lián)更改。觸發(fā)器不能通過名稱被直接調(diào)用，更不允許帶參數(shù)，而是當(dāng)用戶對表中的數(shù)據(jù)進(jìn)行修改這樣的事件發(fā)生時，自動執(zhí)行的行為。觸發(fā)器可以用于SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查，實(shí)施更為復(fù)雜的數(shù)據(jù)完整性約束。觸發(fā)器可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。一個表中可以存在多個同類觸發(fā)器（INSERT、UPDATE或DELETE），對于同一個修改語句可以有多個不同的對策用以響應(yīng)。316.2.1觸發(fā)器的特點(diǎn)和類型觸發(fā)器的類型按觸發(fā)事件不同分為2類（1）DDL（數(shù)據(jù)定義語言）觸發(fā)器 是指當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生DDL事件時將啟用。DDL事件即指在表或索引中的create、alter、drop語句。（2）DML（數(shù)據(jù)操縱語言）觸發(fā)器 是指觸發(fā)器在數(shù)據(jù)庫中發(fā)生DML事件時將啟用。DML事件即指在表或視圖中修改數(shù)據(jù)的insert、update、delete語句。因此DML觸發(fā)器也可分為3種類型：INSERT觸發(fā)器、UPDATE觸發(fā)器、DELETE觸發(fā)器。326.2.1觸發(fā)器的特點(diǎn)和類型觸發(fā)器的類型按觸發(fā)器被激活的時機(jī)可以分為以下兩種類型：（1）AFTER觸發(fā)器（后觸發(fā)器） 是在觸發(fā)動作之后再觸動，可視為控制觸發(fā)器激活時間的機(jī)制。在引起觸發(fā)器執(zhí)行的更新語句成功完成之后執(zhí)行。如果更新語句因錯誤（如違反約束或語法錯誤）而失敗，觸發(fā)器將不會執(zhí)行。

此類觸發(fā)器只能定義在表上，不能創(chuàng)建在視圖上?？梢詾槊總€觸發(fā)操作（如INSERT、UPDATE或DELETE）創(chuàng)建多個AFTER觸發(fā)器。（2）INSTEADOF觸發(fā)器（替代觸發(fā)器） 將在數(shù)據(jù)變動以前被觸發(fā)，該類觸發(fā)器代替觸發(fā)操作被執(zhí)行。

該類觸發(fā)器既可在表上定義，也可在視圖上定義。對于每個觸發(fā)操作（INSERT、UPDATE和DELETE）只能定義1個INSTEADOF觸發(fā)器336.2.2觸發(fā)器的創(chuàng)建與觸發(fā)器相關(guān)的虛擬表在觸發(fā)器執(zhí)行的時候，系統(tǒng)產(chǎn)生兩個臨時表：inserted表和deleted表。（1）inserted表存儲著被INSERT和UPDATE語句影響的新的數(shù)據(jù)記錄。當(dāng)用戶執(zhí)行INSERT和UPDATE語句時，新數(shù)據(jù)記錄的備份被復(fù)制到inserted臨時表中。（2）deleted表存儲著被DELETE和UPDATE語句影響的舊數(shù)據(jù)記錄。在執(zhí)行DELETE和UPDATE語句過程中，指定的舊數(shù)據(jù)記錄被用戶從基本表中刪除，然后轉(zhuǎn)移到delete表中。346.2.2觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器主要有T-SQL語句和對象資源管理器等方式。1．使用CREATETRIGGER語句創(chuàng)建觸發(fā)器

語法格式：

CREATETRIGGER[schema_name.]trigger_nameON{table_name|view_name}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement[,...n]例：

CREATETRIGGETtrig_stu ONStudent AFTERINSERT,DELETE,UPDATEAS

SELECT*FROMstudent35創(chuàng)建觸發(fā)器必須指定的選項(xiàng)：名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時激發(fā)；激活觸發(fā)器的數(shù)據(jù)修改語句；執(zhí)行觸發(fā)操作的編程語句；對CREATETRIGGER語句的文本加密。

6.2.2觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器注意事項(xiàng)：（1）CREATETRIGGER語句必須是批處理中的第一條語句。（2）只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，一個觸發(fā)器只能對應(yīng)一個表。（3）表的所有者具有創(chuàng)建觸發(fā)器的默認(rèn)權(quán)限，不能將該權(quán)限轉(zhuǎn)給其他用戶。（4）不能在視圖、臨時表、系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用視圖、臨時表，但是不能引用系統(tǒng)表。（5）盡管TRUNCATETABLE語句類似于沒有WHERE子句的DELETE語句，但由于該語句不被記入日志，所以它不會引發(fā)DELETE觸發(fā)器。366.2.2觸發(fā)器的創(chuàng)建【例6.11】創(chuàng)建DelCourse觸發(fā)器的語句如下：CREATETRIGGERDelCourseONC_InfoFORDELETEASDELETES_C_InfoWHEREC_NoIN(SELECTC_NoFROMdeleted)在“新建查詢”窗格中輸入以下語句并執(zhí)行：DELETEFROMC_InfoWHEREC_No='29000011'

注意：該語句從C_Info表中刪除課程編號為“29000011”的數(shù)據(jù)行，觸發(fā)DelCourse觸發(fā)器，產(chǎn)生信息：376.2.2觸發(fā)器的創(chuàng)建2．使用圖形界面方式創(chuàng)建觸發(fā)器在表St_Info上創(chuàng)建觸發(fā)器，操作步驟：38在學(xué)生表St_Info中的INSERT操作上創(chuàng)建了一個名稱為“st_Insert”的觸發(fā)器。當(dāng)在該表上執(zhí)行任何有效的插入操作（不論是否實(shí)際插入了記錄）時，都會激活該觸發(fā)器，將變量@str的值設(shè)為“TRIGGERISWORKING”。PRINT命令的作用是向客戶端返回用戶定義的消息。6.2.3觸發(fā)器的查看和修改1．使用對象資源管理器查看觸發(fā)器信息396.2.3觸發(fā)器的查看和修改2．使用系統(tǒng)存儲過程查看觸發(fā)器信息在SQLServer中，根據(jù)不同需要，可以使用sp_helptext、sp_depends、sp_help等系統(tǒng)存儲過程來查看觸發(fā)器的不同信息。例：406.2.3觸發(fā)器的查看和修改專門查看觸發(fā)器屬性信息的系統(tǒng)存儲過程：sp_helptrigger，語法格式：sp_helptrigger[@tabname=]'table' [,[@tr