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

第8章

存儲過程與觸發(fā)器8.1設(shè)計和管理存儲過程存儲過程與函數(shù)和vfp中的過程類似，是SQL服務(wù)器上一組預(yù)編譯的T-SQL語句；用于完成某項(xiàng)任務(wù)。1、存儲過程的類型（1）系統(tǒng)存儲過程:存在master數(shù)據(jù)庫中，名稱以sp開頭（2）用戶定義存儲過程:（3）擴(kuò)展存儲過程:存在于DDL(動態(tài)鏈接庫)中，名稱以xp_開頭2、存儲過程的主要優(yōu)點(diǎn)（1）模塊化編程。（2）快速執(zhí)行（3）減少網(wǎng)絡(luò)通信量（4）提供安全機(jī)制8.1.1存儲過程概述8.1.2創(chuàng)建存儲過程1．格式CREATEPROCEDURE[架構(gòu)名稱.]存儲過程名

[{@parameter數(shù)據(jù)類型}[=default]--設(shè)置默認(rèn)值。[OUTPUT]--說明@parameter參數(shù)為一返回值。[,..n][WITHencryption|recompile]--對過程加密。[FORREPLICATION]--不能在服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程AS

<SQL語句>]2．存儲過程的各選項(xiàng)設(shè)置規(guī)則1）@parameter是過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。2）如果定義了default值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或NULL3）OUTPUT選項(xiàng)指示參數(shù)是輸出參數(shù)。4）如果創(chuàng)建存儲過程時，使用WITHencryption子句，過程定義將以不可讀的形式存儲。5）FORREPLICATION不能在服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。6）<SQL語句>指定過程要執(zhí)行的操作7）可以在存儲過程內(nèi)引用臨時表。8.1.2創(chuàng)建存儲過程【例8.1】創(chuàng)建一個存儲過程proc_student1用于顯示學(xué)號為“J0402”的學(xué)生基本信息（包括學(xué)生學(xué)號、姓名、性別、系）。CREATEPROCEDUREproc_student1ASSELECT學(xué)號,姓名,性別,系

FROMsWHERE學(xué)號=’J0402’GO8.1.2創(chuàng)建存儲過程1.格式

EXEC|EXECUTE[@返回狀態(tài)=]存儲過程名稱[@形參=]{value|@變量[OUTPUT]|[DEFAULT][,...n]2.說明(1)“@返回狀態(tài)”是保存過程的返回狀態(tài)值。“@形參”是在定義存儲過程時的參數(shù)。(2)在采用“@形參=value”格式時，參數(shù)名稱和常量不必按順序提供。但是，如果參數(shù)使用了“@形參=value”格式，則對后續(xù)的所有參數(shù)均必須使用該格式。(3)“value”是傳遞給存儲過程的參數(shù)值。如果參數(shù)名稱沒有指定，參數(shù)值必須以在存儲過程中定義的順序提供。8.1.3執(zhí)行存儲過程【例8.2】現(xiàn)在我們就來執(zhí)行剛才創(chuàng)建好的存儲過程proc_student1，顯示學(xué)號為“J0402”的學(xué)生基本信息（包括學(xué)生學(xué)號、姓名、性別、系）。方法一：在SQLServerManagementStudio中執(zhí)行存儲過程方法二：使用ＳＱＬ命令在新建的查詢窗口輸入命令：Executeproc_student18.1.3執(zhí)行存儲過程存儲過程和調(diào)用程序之間通過參數(shù)來傳遞數(shù)據(jù)！1.參數(shù)存儲過程的參數(shù)在創(chuàng)建時聲明，SQLServer支持兩種參數(shù)：輸入?yún)?shù)和輸出參數(shù)。1）輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯^程傳送數(shù)據(jù)值。但必須事先在CREATEPROCEDURE語句中聲明一個或多個形參。2）輸出參數(shù)輸出參數(shù)允許存儲過程將形參的數(shù)據(jù)值返回給調(diào)用程序中的實(shí)參。OUTPUT關(guān)鍵字用來指出輸出參數(shù)。8.1.4存儲過程的參數(shù)和狀態(tài)值1)輸入?yún)?shù)例8.3:創(chuàng)建一個有輸入?yún)?shù)的存儲過程proc_student2，顯示指定學(xué)號的學(xué)生基本信息（包括學(xué)生學(xué)號、姓名、性別、系）。執(zhí)行該存儲過程顯示學(xué)號為J0404的學(xué)生信息。createprocedureproc_student2@numchar(6)asSELECT學(xué)號,姓名,性別,系FROMsWHERE學(xué)號=@numexecproc_student2@num='J0404'8.1.4存儲過程的參數(shù)和狀態(tài)值例8.4:創(chuàng)建存儲過程s_info。根據(jù)學(xué)生姓名和學(xué)號查詢學(xué)生的學(xué)號，姓名，性別和所在系。ifexists(selectnamefromsysobjectswherename='s_info'andtype='p')dropprocedures_infocreateprocs_info@stnamevarchar(8),@stsnovarchar(10)asSELECT學(xué)號,姓名,性別,系FROMsWHERE姓名=@stnameand學(xué)號=@stsnogoexecs_info李麗,J0401

8.1.4存儲過程的參數(shù)和狀態(tài)值例8.7:

創(chuàng)建存儲過程s_like，根據(jù)姓名，查詢學(xué)生的姓名和平均成績。如果執(zhí)行時，沒帶參數(shù)，則顯示姓陳的學(xué)生平均成績。Createprocedures_like@stnamevarchar(8)=‘陳%’Asselect姓名,平均成績=avg(sc.成績)fromsc,swheres.學(xué)號=sc.學(xué)號ands.姓名like@stname

groupby姓名GoExecutes_likeExecutes_like‘李麗’8.1.4存儲過程的參數(shù)和狀態(tài)值例8.8:創(chuàng)建存儲過程s_count，根據(jù)課程名，檢索選修某門課程的學(xué)生人數(shù)。Createprocdure

s_count@ctnamevarchar(30)=NULLAsif@ctnameisNULLprint‘請輸入課程名！’

elseselect課程名,學(xué)生選修人數(shù)=count(學(xué)號)fromsc,cwherec.課程號=sc.課程號andc.課程名=@ctname

groupby課程名GoExecutes_count‘c語言’8.1.4存儲過程的參數(shù)和狀態(tài)值2)輸出參數(shù)

作用：將過程定義中的形參的值返回到調(diào)用程序中。例8.11：建立一個過程，用于顯示指定學(xué)號的各門課程平均成績，并返回該生的平均成績。createprocproc_student3@numchar(16),@savgsmallintoutputasselect@savg=avg(成績)fromsjoinscons.學(xué)號=sc.學(xué)號wheres.學(xué)號=@numgodeclare@savg_valuesmallintexecproc_student3@num='J0401',@savg=@savg_valueoutputselect@savg_valueas‘平均成績’8.1.4存儲過程的參數(shù)和狀態(tài)值如果沒有output關(guān)鍵字結(jié)果會怎么樣2)輸出參數(shù)例8.12：創(chuàng)建過程sg,根據(jù)輸入的學(xué)號和課程號，顯示某個同學(xué)指定課程的成績并返回該成績。Createprocsg@snvarchar(8)=‘j0401’,@cnvarchar(3)=‘c02’,@grsmallintoutputAsSelect學(xué)號,課程號,成績fromscwheresc.學(xué)號=@snandsc.課程號=@cnSelect@gr=成績fromscwheresc.學(xué)號=@snandsc.課程號=@cngoDeclare@myscore

smallintset@myscore=0Executesg@sn=‘j0402’,@gr=@myscoreoutput8.1.4存儲過程的參數(shù)和狀態(tài)值2．返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行。默認(rèn)返回代碼為0，表示成功執(zhí)行；若返回-1到-99之間的整數(shù)，表示執(zhí)行失敗。可以使用RETURN語句，用大于0或小于-99之間的整數(shù)來定義自己的返回狀態(tài)值，以表示不同執(zhí)行結(jié)果１）RETURN語句格式RETURN[返回整型值的表達(dá)式]２）功能RETURN語句將無條件地從過程、批處理或語句塊中退出并返回整型值。8.1.4存儲過程的參數(shù)和狀態(tài)值2．返回值例8.14:

創(chuàng)建存儲過程checkstate，查詢指定課程的最高成績，如果最高成績大于90分，則返回狀態(tài)代碼1。否則，返回狀態(tài)代碼2createproccheckstate@cnovarchar(3)asif(selectmax(成績)fromscwhere課程號=@cno)>90return1elsereturn0godeclare@mystateintExecute@mystate=checkstate'c01'select@mystate8.1.4存儲過程的參數(shù)和狀態(tài)值8.1.5修改存儲過程1.格式ALTERPROCEDURE[架構(gòu)名稱.]存儲過程名[@parameter數(shù)據(jù)類型][=default]--設(shè)置默認(rèn)值。[OUTPUT]--說明定義的存儲過程參數(shù)為一返回值。[,..n][WITHencryption|recompile]--對過程進(jìn)行加密。[FORREPLICATION]AS<SQL語句>2.功能其語法和CREATEPROCEDURE很相似?！纠?.18】現(xiàn)在我們就來修改創(chuàng)建好的存儲過程proc_student3，用于顯示指定學(xué)號的學(xué)生各門課程的最高成績，執(zhí)行該存儲過程返回指定學(xué)生的最高成績.ALTERPROCEDUREc_student3 @numchar(6),@maxsmallintoutputASBEGIN SELECT@max=max(成績)FROMsJOINscONs.學(xué)號=sc.學(xué)號

WHEREs.學(xué)號=@numEND8.1.5修改存儲過程1．格式DROPPROCEDURE{存儲過程名}[,...n]2．功能從當(dāng)前數(shù)據(jù)庫中刪除一個或多個存儲過程【例8.19】現(xiàn)在我們就來刪除剛才創(chuàng)建的存儲過程proc_student4。方法一：在SQLServerManagementStudio中刪除存儲過程方法二：使用SQL命令新建一個查詢窗口，在里面輸入命令：DROPPROCEDUREproc_student48.1.6刪除存儲過程【例8.23】建立存儲過程sc_look_delete，查詢某個同學(xué)的所有課程成績，如果存在不及格課程，則刪除不及格成績記錄，否則顯示所有課程成績。CREATEPROCEDURE

sc_look_delete@sno

varchar(6)ASIFEXISTS(SELECT學(xué)號FROMscWHERE學(xué)號=@sno

and成績<60)

DELETEFROMscWHERE學(xué)號=@sno

AND成績<60ELSESELECT學(xué)號,課程號,成績FROMscWHERE學(xué)號=@snoExecutesc_look_delete‘j0401’8.1.6刪除存儲過程方法一：在ManagementStudio中查看存儲過程的定義方法二：使用命令1．sp_help格式：sp_help[[@objname=]name]2．sp_helptext格式：sp_helptext[[@objname=]name]3．sp_depends

格式：sp_depends[@objname=]’name’4．sp_stored_procedures格式：sp_stored_procedures8.1.7查看存儲過程的定義1．格式SP_RENAME

原存儲過程名，新存儲過程名2．功能將存儲過程名更改為新存儲過程名。【例8.25】將存儲過程sg

更名為student_proc方法一：使用SQL命令SP_RENAMEsg,student_proc注意:更改對象名可能破壞腳本和存儲過程。方法二：在ManagementStudio重命名存儲過程8.1.8重命名存儲過程觸發(fā)器是特殊的存儲過程，它也定義了一組Transact-SQL語句，用于完成某項(xiàng)任務(wù)。

觸發(fā)器的主要作用是能強(qiáng)制數(shù)據(jù)完整性，保證數(shù)據(jù)一致性，主要表現(xiàn)為：強(qiáng)化約束保證參照完整性級聯(lián)運(yùn)行跟蹤變化創(chuàng)建觸發(fā)器時需指定：名稱、在其上定義觸發(fā)器的表、觸發(fā)器將何時激發(fā)、激活觸發(fā)器的數(shù)據(jù)修改語句。8.2設(shè)計和管理觸發(fā)器8.2.1觸發(fā)器概述1．格式CREATETRIGGER[架構(gòu)的名稱.]觸發(fā)器名

ON表名|視圖[WITHencryption]--對文本進(jìn)行加密。{FOR|AFTER|INSTEADOF}[delete][,insert][,update]AS[SQL語句]2．功能AFTER：指定觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。INSTEADOF：指定執(zhí)行觸發(fā)器而不是執(zhí)行“觸發(fā)SQL語句”，從而替代“觸發(fā)語句”的操作。對于表或視圖，每個INSERT、UPDATE或DELETE語句最多可定義一個INSTEADOF觸發(fā)器。8.2.2創(chuàng)建觸發(fā)器【例8.26】在學(xué)生選課表sc上創(chuàng)建一個觸發(fā)器trigger_student1，該觸發(fā)器被INSERT操作觸發(fā)，當(dāng)用戶向sc表插入一條新記錄時，判斷該記錄的學(xué)號在學(xué)生基本信息表s中是否存在，如果存在插入成功，否則插入失敗。CREATETRIGGERtrigger_student1ONscAFTERINSERTASBEGINIF(SELECTcount(*)FROMinsertedJOINsONinserted.學(xué)號=s.學(xué)號)=0BEGIN ROLLBACKTRAN–取消所做的插入操作 PRINT'插入記錄無效！' ENDEND8.2.2創(chuàng)建觸發(fā)器【例8.26】在學(xué)生信息表S上創(chuàng)建一個觸發(fā)器trigger_stu2,該觸發(fā)器被delete操作觸發(fā)。當(dāng)在表S中刪除一條記錄時，判斷該生是否在選課成績表sc中有數(shù)據(jù)，如果沒有則允許刪除，否則不允許刪除。Createtriggertrigger_stu2onsafterdeleteAsbegin

if(exists(select*fromdeletedjoinscondeleted.學(xué)號=sc.學(xué)號))beginrollbacktranprint‘不允許刪除該生信息’endend8.2.2創(chuàng)建觸發(fā)器注意：1、在觸發(fā)器執(zhí)行過程中，SQLSERVER會建立和管理兩個臨時的虛擬表：deleted表和inserted表。2、當(dāng)向表中插入數(shù)據(jù)時，insert觸發(fā)器會執(zhí)行，并將新記錄插入到inserted表中。3、當(dāng)從表中刪除數(shù)據(jù)時，delete觸發(fā)器會執(zhí)行，并將被刪除的記錄存放到deleted表中。4、對于修改操作，update觸發(fā)器會執(zhí)行，并將更新前的記錄存儲在deleted表中，然后將更新后的記錄儲在inserted表中。8.2.2創(chuàng)建觸發(fā)器【例8.28】在學(xué)生信息表s上創(chuàng)建一個觸發(fā)器my_edit，該觸發(fā)器被update操作觸發(fā)。當(dāng)用戶在S表修改某個學(xué)生的學(xué)號時，同時自動更新選課成績表SC中該生對應(yīng)記錄的學(xué)號Createtriggermy_edit

onSafterupdateAsbeginupdatescset學(xué)號=(select學(xué)號frominserted)where學(xué)號in(select學(xué)號fromdeleted)end8.2.2創(chuàng)建觸發(fā)器【例8.28】創(chuàng)建一個觸發(fā)器reminder，如果修改、刪除和插入學(xué)生信息表S中的任何數(shù)據(jù)，則向用戶顯示信息“不能對數(shù)據(jù)表進(jìn)行任何修改！”Ifexists(selectnamefromsysobjectswherename=‘reminder’andtype=‘tr’)droptriggerreminderCreatetriggerreminderonsforinsert,update,deleteAsbegin

raiserror(‘不能對數(shù)據(jù)表進(jìn)行任何修改！’,16,10)end8.2.2創(chuàng)建觸發(fā)器返回用戶定義的錯誤提示信息，記錄發(fā)生的錯誤。１.格式RAISERROR({msg_id|msg_str}{,嚴(yán)重級別,狀態(tài)}２.功能（1）msg_id是存儲于sysmessages