存儲過程和觸發(fā)器課件

第7章存儲過程存儲過程存儲過程是SQL語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強大的編程功能。存儲過程可包含程序流、邏輯以及對數(shù)據(jù)庫的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及返回值。存儲過程優(yōu)點可以在單個存儲過程中執(zhí)行一系列SQL語句?？梢詮淖约旱拇鎯^程內(nèi)引用其它存儲過程，這可以簡化一系列復(fù)雜語句。存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比單個SQL語句快。@parameter，表示存儲過程中的參數(shù),可以聲明一個或多個參數(shù)。data_type，參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型均可以用作存儲過程的參數(shù)。游標(biāo)數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為游標(biāo)，也必須同時指定VARYING和OUTPUT關(guān)鍵字。VARYING，指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。default，默認(rèn)值(常量或NULL)。如果過程將對該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[]和[^]）。OUTPUT，表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。不使用任何參數(shù)的存儲過程

中查詢有關(guān)學(xué)生的信息，返回學(xué)生學(xué)號、姓名、課程名、成績和學(xué)分。CREATEPROCEDUREstudent_info AS SELECTa.Sno,Sname,Cname,score FROMStudentaINNERJOINStudyb ONa.Sno=b.SnoINNERJOINCourset ONb.Cno=o對于創(chuàng)建的student_info存儲過程可以通過以下方法執(zhí)行：EXECUTEstudent_info或者EXECstudent_info使用帶參數(shù)的存儲過程

查詢某人指定課程的成績和學(xué)分。該存儲過程接受與傳遞參數(shù)精確匹配的值。CREATEPROCEDUREstudent_info1@namechar(8),@cnamechar(16)

ASSELECTa.Sno,Sname,Cname,score,CreditFROMStudentaINNERJOINStudybONa.Sno=b.SnoINNERJOINCoursetONb.Cno=oWHEREa.Sname=@nameandt.Cname=@cnameEXECstudent_info1'王林',’計算機基礎(chǔ)’或者EXECau_info@cname=’計算機基礎(chǔ)’,@name='王林'使用帶有通配符參數(shù)的存儲過程返回指定學(xué)生的學(xué)號、姓名、所選課程名稱及該課程的成績。該存儲過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。CREATEPROCEDUREst_info@namevarchar(30)='劉%'ASSELECTa.Sno,Sname,Cname,scoreFROMStudentaINNERJOINStudybONa.Sno=b.SnoINNERJOINCoursetONb.Cno=oWHERESnameLIKE@nameEXECUTEst_info'王%'/*傳遞給@name的實參為'王%'*/或者EXECUTEst_info'[王張]%'使用OUTPUT游標(biāo)參數(shù)的存儲過程

使用OUTPUT游標(biāo)參數(shù)用于返回存儲過程的局部游標(biāo)。在教務(wù)管理信息系統(tǒng)數(shù)據(jù)庫（JWGL）的Student表上聲明并打開一個游標(biāo)。CREATEPROCEDUREst_cursor@st_cursorCURSORVARYINGOUTPUTASSET@st_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMStudentOPEN@st_cursor在如下的批處理中，聲明一局部游標(biāo)變量，執(zhí)行上述存儲過程過程并將游標(biāo)賦值給局部游標(biāo)變量，然后通過該游標(biāo)變量讀取記錄。DECLARE@MyCursorCURSOREXECst_cursor@st_cursor=@MyCursorOUTPUTWHILE(@@FETCH_STATUS=0)BEGIN FETCHNEXTFROM@MyCursorENDCLOSE@MyCursorDEALLOCATE@MyCursor修改存儲過程ALTERPROCEDUREstudent_info1@namechar(8),@cnamechar(16)ASSELECTa.sno,sname,cname,t.CreditFROMStudentaINNERjoinStudybONo=oINNERJOINCoursetONo=oWHEREa.sname=@ame=@cname刪除存儲過程DROPPROCEDUREstudent_info1觸發(fā)器觸發(fā)器（Trigger）是一種特殊類型的存儲過程，它定義在一個表上，實現(xiàn)指定功能的SQL語句序列。當(dāng)使用UPDATE、INSERT或DELETE的一種或多種語句，對數(shù)據(jù)進行修改時，觸發(fā)器就會生效。觸發(fā)器與存儲過程的主要區(qū)別存儲過程的定義可有參數(shù)，而觸發(fā)器的定義不能有參數(shù)；執(zhí)行方式不同，觸發(fā)器由引起表數(shù)據(jù)變化的操作（增、刪、改）觸發(fā)而自動執(zhí)行，而存儲過程必須通過具體的語句調(diào)用。創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語法格式如下所示：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}ASsql_statement[...n]}}AFTER，指定觸發(fā)器在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。INSTEADOF，指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)語句的操作。觸發(fā)器中使用的特殊表inserted邏輯表：當(dāng)向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。deleted邏輯表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄存放到deleted邏輯表中。創(chuàng)建UPDATE類型的觸發(fā)器在表Study中創(chuàng)建一個UPDATE觸發(fā)器，如果修改記錄的學(xué)號值在表Student中不存在對應(yīng)的學(xué)生，則不執(zhí)行更新操作，并提示用戶，具體代碼如下：CREATETRIGGERupdate_trigONStudyFORUPDATEASDECLARE@xhvarchar(10)DECLARE@namevarchar(20)SELECT@xh=a.snoFROMinsertedSELECT@name=b.snameFROMStudentbWHEREb.sno=@xhIF@nameISNULLBEGIN PRINT'指定的學(xué)生不存在，請重新指定.' --提示錯誤信息 ROLLBACKTRANSACTION --回滾插入操作END創(chuàng)建DELETE類型的觸發(fā)器在表Study中創(chuàng)建一個DELETE觸發(fā)器，如果刪除記錄的學(xué)號值在表Student中存在對應(yīng)的學(xué)生，則不執(zhí)行刪除操作，并提示用戶，具體代碼如下：CREATETRIGGERdelete_trigONStudyFORDELETEASDECLARE@xhvarchar(10)DECLARE@namevarchar(20)SELECT@xh=a.snoFROMdeletedSELECT@name=b.snameFROMStudentbWHEREb.sno=@xhIF@nameISNOTNULLBEGINPRINT'指定的學(xué)生已存在，不能被刪除.' --提示錯誤信息ROLLBACKTRANSACTION --回滾插入操作END創(chuàng)建INSTEADOF類型的觸發(fā)器

在表Study中創(chuàng)建一個INSTEADOF觸發(fā)器，如果刪除記錄的學(xué)號值在表Student中存在對應(yīng)的學(xué)生，則不執(zhí)行刪除操作，并提示用戶，具體代碼如下：CREATETRIGGERinstead_of_trigONStudyINSTEADOFDELETEASDECLARE@xhvarchar(10)DECLARE@namevarcha