第8章

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

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

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

FROMsWHERE學號=’J0402’GO8.1.2創建存儲過程1.格式

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

8.1.4存儲過程的參數和狀態值例8.7:

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

groupby姓名GoExecutes_likeExecutes_like‘李麗’8.1.4存儲過程的參數和狀態值例8.8:創建存儲過程s_count，根據課程名，檢索選修某門課程的學生人數。Createprocdure

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

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

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

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

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

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

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

sc_look_delete@sno

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

and成績<60)

DELETEFROMscWHERE學號=@sno

AND成績<60ELSESELECT學號,課程號,成績FROMscWHERE學號=@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重命名存儲過程觸發器是特殊的存儲過程，它也定義了一組Transact-SQL語句，用于完成某項任務。

觸發器的主要作用是能強制數據完整性，保證數據一致性，主要表現為：強化約束保證參照完整性級聯運行跟蹤變化創建觸發器時需指定：名稱、在其上定義觸發器的表、觸發器將何時激發、激活觸發器的數據修改語句。8.2設計和管理觸發器8.2.1觸發器概述1．格式CREATETRIGGER[架構的名稱.]觸發器名

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

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

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

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