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

第8章

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

sc_look_delete@sno

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

and成績(jī)<60)

DELETEFROMscWHERE學(xué)號(hào)=@sno

AND成績(jī)<60ELSESELECT學(xué)號(hào),課程號(hào),成績(jī)FROMscWHERE學(xué)號(hào)=@snoExecutesc_look_delete‘j0401’8.1.6刪除存儲(chǔ)過程方法一：在ManagementStudio中查看存儲(chǔ)過程的定義方法二：使用命令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查看存儲(chǔ)過程的定義1．格式SP_RENAME

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

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

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

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

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

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

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