數據庫原理與應用教程-MySQL8.0第9章

游標、觸發器和事件第9章

游標、觸發器和事件在MySQL應用操作中，游標、觸發器和事件都扮演著相當重要的角色。游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。觸發器是一種數據庫保護機制，當有操作影響到觸發器保護的數據時才，觸發器就會自動觸發執行。事件是指定的時刻被執行的過程式數據庫對象，它可以作為定時任務調度器，取代部分原來只能用操作系統的計劃任務才能執行的工作。9.1游標游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。9.1.1游標概述關系數據庫中的操作會對整個行集產生影響，由語句所返回的這一完整的行集被稱為結果集。而應用程序并不總能將整個結果集作為一個單元來有效地處理，這些應用程序需要一種機制以便每次處理一行或一部分行。因此我們必須借助于游標來進行面向單行記錄的數據處理。我們可以把游標當作一個指針，它可以指定結果中的任何位置，然后允許用戶對指定位置的數據進行處理。9.1.2游標的操作操作游標有4個基本的步驟。1.聲明游標使用一個游標之前，首先應當聲明它。語法格式：DECLAREcursor_nameCURSORFORselect_statement;【例9-1】聲明一個名為S_Cursor的游標，用以查詢計算機科學與技術專業的所有學生的信息。DECLARES_CursorCURSORFORSELECT*FROMstudentWHEREmajor='計算機科學與技術';9.1.2游標的操作2.打開游標聲明了游標后，如果想使用游標必須先打開它。語法格式：OPENcursor_name;【例9-2】打開例9-1聲明的游標。OPENS_Cursor;3.提取游標打開游標后，就可以從游標中逐行地提取數據，以進行相關處理。語法格式：FETCHcursor_nameINTOvariable_name[,…n];9.1.2游標的操作【例9-3】從例9-1聲明的游標中提取一行數據。FETCHS_CursorINTOc_sno,c_sname,c_ssex,c_sbirthday,c_major,c_grade;4.關閉游標在處理完游標中數據之后必須關閉游標來釋放數據結果集，關閉游標的語法格式：CLOSEcursor_name;【例9-4】關閉S_Cursor游標。CLOSES_Cursor;9.1.2游標的操作5.游標的完整實例與多數DBMS不同，MySQL游標只能應用于存儲過程、函數等子程序內部?！纠?-5】創建一個存儲過程p_c1，首先顯示某個身份證號的儲戶的全部賬號信息；然后聲明Sh1_Cursor游標，如果此儲戶只有一個賬戶，則將其余額加5，如果有兩個或更多的賬戶，則將此儲戶的第一個賬戶的余額加10，第二個賬戶的余額加5；最后，再次顯示此身份證號的儲戶的全部賬號信息。9.1.2游標的操作USEbankcard;DELIMITER//CREATEPROCEDUREp_c1(id1char(18))BEGINDECLAREanochar(20);DECLARESh1_CursorCURSORFORSELECTAccNOFROMaccountWHEREIDNO=id1;SELECT*FROMaccountWHEREIDNO=id1;OPENSh1_Cursor;FETCHSh1_CursorINTOano;9.1.2游標的操作IF(SELECTCOUNT(*)FROMaccountWHEREIDNO=id1)>1THENUPDATEaccountSETBalance=Balance+10WHEREACCNO=ano;FETCHSh1_CursorINTOano;ENDIF;UPDATEaccountSETBalance=Balance+5WHEREACCNO=ano;CLOSESh1_Cursor;SELECT*FROMaccountWHEREIDNO=id1;END//9.1.2游標的操作【例9-6】分別用身份證號133***198812110101和130***198003010912調用p_c1存儲過程。CALLp_c1('133***198812110101');CALLp_c1('130***198003010912');【例9-7】創建一個存儲過程p_c2，聲明Sh2_Cursor游標，計算某個身份證號的儲戶的全部賬號的總收入和支出差額。9.1.2游標的操作USEbankcard;DELIMITER//CREATEPROCEDUREp_c2(id1char(18))BEGINDECLAREexp,inc,ex_infloat;DECLAREi,totalint;DECLARESh2_CursorCURSORFORSELECTExpense,IncomeFROMaccount,trecordWHEREaccount.AccNO=trecord.AccNOANDIDNO=id1;SELECTCOUNT(*)INTOtotalFROMaccount,trecordWHEREaccount.AccNO=trecord.AccNOANDIDNO=id1;OPENSh2_Cursor;SETi=1;SETex_in=0;9.1.2游標的操作WHILEi<=totalDOFETCHSh2_CursorINTOexp,inc;IFexpISNULLTHENSETex_in=ex_in+inc;ELSESETex_in=ex_in-exp;ENDIF; SETi=i+1;ENDWHILE;SELECTex_in;CLOSESh2_Cursor;END//9.1.2游標的操作【例9-8】用身份證號133***198812110101調用p_c2存儲過程。CALLp_c2('133***198812110101');游標的優點：可以對查詢結果集中的數據一行一行的操作。游標的缺點：處理大數據量時，一行一行的操作效率非常低下，占用內存空間大。除非使用while循環、子查詢、臨時表、自定義函數或其他方式都無法處理某種操作，才考慮使用游標。9.2觸發器就本質而言，觸發器也是一種存儲過程，它在特定語言事件發生時自動執行。9.2.1觸發器概述在MySQL數據庫系統中，存儲過程和觸發器都是SQL語句和可選流程控制語句的集合。觸發器是一種特殊的存儲過程，它是一種在基本表中數據被更新時自動執行的內嵌過程，主要通過事件進行觸發而被執行，而存儲過程可以通過存儲過程名字而被直接調用。9.2.1觸發器概述當對某一張表進行諸如UPDATE、INSERT、DELETE這些操作時，MySQL就會自動執行觸發器所定義的SQL語句，從而確保對數據的處理符合由這些SQL語句所定義的規則，觸發器還能夠實現由主鍵和外鍵所不能保證的復雜的參照完整性和數據的一致性，有助于強制引用完整性，以便在添加、更新或刪除表中的行時保留表之間已定義的關系。9.2.1觸發器概述觸發器用來保持低級的數據的完整性，而不是返回大量的查詢結果。主要可以實現以下操作：(1)強制比CHECK約束更復雜的數據完整性(2)使用自定義的錯誤提示信息(3)實現數據庫中多張表的級聯修改(4)比較數據庫修改前后數據的狀態(5)調用存儲過程

(6)維護非規范化數據9.2.2創建觸發器【例9-9】為student表創建一個針對DELETE事件的觸發器，在刪除數據時，顯示提示信息。USEteaching;CREATETRIGGERdel_stuAFTERDELETEONstudentFOREACHROWSET@del1='有人正在刪除數據！';【例9-10】刪除2022010005號學生的信息。DELETEFROMstudentWHEREsno='2022010005';SELECT@del1;9.2.2創建觸發器語法格式：CREATETRIGGERtrigger_name{BEFORE|AFTER}{INSERT|DELETE|UPDATE}ONtable_nameFOREACHROW[{FOLLOWS|PRECEDES}other_trigger_name]trigger_body;9.2.2創建觸發器在MySQL中，為每個觸發器都定義了兩個特殊的表，一個是new表，一個是old表。這兩個表是建在數據庫服務器的內存中的，是由系統管理的邏輯表，即臨時表，而不是真正存儲在數據庫中的物理表。在觸發器的執行過程中，MySQL建立和管理這兩個臨時表。這兩個表的結構與觸發器所在數據表的結構是完全一致的，其中包含了在激發觸發器的操作中插入或刪除的所有記錄。當觸發器的工作完成之后，這兩個表也將會從內存中刪除。9.2.2創建觸發器在用戶執行INSERT語句時，所有被添加的記錄都會存儲在new表中；在用戶執行DELETE語句時，從觸發程序表刪除的行會發送到old表；對于UPDATE語句，MySQL先將要進行修改的記錄存儲到old表中，然后將修改后數據插入到new表。在觸發器的語句中訪問兩個臨時表的某個字段，方式：“new.字段名”或“old.字段名”。觸發器對于new表中的數據可以修改，但對于old中的數據只有讀取的權限，沒有修改的權限。9.2.2創建觸發器【例9-11】在bankcard數據庫中使用流程控制語句創建insert觸發器。當向交易記錄表（trecord）中添加了一條交易信息時，如果今天某賬號交易支出某個金額，則其余額減去此金額；如果今天某賬號收入某個金額，則其余額加上此金額。USEbankcard;DELIMITER//CREATETRIGGERTransactionsAFTERINSERTONTrecordFOREACHROWBEGINIFnew.ExpenseISNOTNULLTHENUPDATEaccountSETBalance=Balance-new.ExpenseWHEREAccNO=new.AccNO;9.2.2創建觸發器ELSEUPDATEaccountSETBalance=Balance+new.IncomeWHEREAccNO=new.AccNO;ENDIF;END//【例9-12】向交易記錄表（Trecord）中添加了兩條交易信息記錄，觸發此觸發器。INSERTINTOTrecordVALUES(NULL,CURDATE(),'412542800335120***02',50,NULL,'412542800335120***08','北國超市','消費支出');INSERTINTOTrecordVALUES(NULL,CURDATE(),'412542800335120***08',NULL,50,'412542800335120***02','北國超市','銷售收入');9.2.2創建觸發器觸發器可以實現復雜的約束。首先在teaching數據庫中創建3個表：(1)教師表：包括教師號、姓名和職稱。CREATETABLEteacher(tnointprimarykey,snamechar(6),prof_titlechar(10));插入數據：INSERTINTOteacherVALUES(1,'鄭浩','教授');INSERTINTOteacherVALUES(2,'王偉','副教授');INSERTINTOteacherVALUES(3,'李平','講師');9.2.2創建觸發器(2)教師工資表：包括教師號、姓名和工資。CREATETABLEteacher_salary(tnoint,tnamechar(12),salaryint,primarykey(tno),foreignkey(tno)referencesteacher(tno));9.2.2創建觸發器(3)工資級別表：包括職稱、最小工資和最大工資。CREATETABLEsalary_level(prof_titlechar(10)primarykey,minsalaryint,maxsalaryint);插入數據：INSERTINTOsalary_levelVALUES('教授',8000,9900);INSERTINTOsalary_levelVALUES('副教授',5900,7700);INSERTINTOsalary_levelVALUES('講師',4500,5900);INSERTINTOsalary_levelVALUES('助教',3900,4900);9.2.2創建觸發器【例9-13】在教師工資表上創建一個觸發器，用于實現復雜的約束：在對教師的工資進行錄入時，按職稱級別進行約束，如果錄入數據違反約束，則觸發器拋出一個異常并報告錯誤原因，錄入操作也將被終止。USEteaching;DELIMITER//CREATETRIGGERteacher_sala1BEFOREINSERTONteacher_salaryFOREACHROWBEGINSELECTminsalary,maxsalary,f_titleINTO@mins,@maxs,@prof9.2.2創建觸發器FROMsalary_levels,teachertWHEREf_title=f_titleandt.tno=new.tno;IFNOT(new.salaryBETWEEN@minsAND@maxs)THENSIGNALSQLSTATE'ERROR'SETMESSAGE_TEXT='工資不是本人職稱應該的工資范圍！';/*拋出異常并報告錯誤原因*/ENDIF;END//【例9-14】利用命令觸發例9-13的觸發器。INSERTINTOteacher_salaryVALUES(1,'鄭浩',7800);SELECT@prof,@mins,@maxs;9.2.2創建觸發器【例9-15】在教師工資表上創建一個觸發器，實現特殊的約束：規定每月的10號前發工資，即對教師的工資進行錄入時，觸發此觸發器，時間不對不能錄入。USEteaching;DELIMITER//CREATETRIGGERteacher_sala2BEFOREINSERTONteacher_salaryFOREACHROWPRECEDESteacher_sala1BEGINset@d=DAY(CURDATE());9.2.2創建觸發器IF@d>10THENSIGNALSQLSTATE'ERROR'SETMESSAGE_TEXT='必須在每月的10號前發工資！';ENDIF;END//【例9-16】利用命令觸發例9-15的觸發器。USEteaching;INSERTINTOteacher_salaryVALUES(2,

'王偉',6200);9.2.2創建觸發器【例9-17】觸發器中調用存儲過程。首先創建一個存儲過程p1：USEbankcard;DELIMITER//CREATEPROCEDUREp1(anoCHAR(19),balfloat)BEGINDECLAREbalaFLOAT;SELECTbalanceINTObalaFROMaccountWHEREACCNO=ano;IFbala<balTHENSIGNALSQLSTATE'ERROR'SETMESSAGE_TEXT='余額不足！';ENDIF;END//9.2.2創建觸發器為account表創建一個觸發器tr1；修改數據(比如轉賬或消費)時，觸發觸發器調用存儲過程p1。USEteaching;CREATETRIGGERtr1BEFOREUPDATEONaccountFOREACHROWCALLp1(new.ACCNO,old.balance-new.balance);【例9-18】“4125428003351200002”賬號消費20元，執行時觸發例9-17的觸發器。USEbankcard;UPDATEaccountSETBalance=Balance-20WHEREAccNO='4125428003351200002';9.2.2創建觸發器【例9-19】用于實施監控的觸發器。首先，在teaching數據庫建立登記修改人賬號的表change_user，表結構如下：9.2.2創建觸發器創建觸發器tr_change，用于登記修改數據者及修改時間等信息。

USEteaching;CREATETRIGGERtr_changeAFTERUPDATEONCOURSEFOREACHROWINSERTINTOchange_userVALUES(NOW(),USER());觸發該觸發器：USEteaching;UPDATEcourseSETclasshour=5WHEREcno='C004';9.2.2創建觸發器注意事項：（1）觸發器不接受參數。（2）觸發器中如果包含SELECT語句，該SELECT語句不能返回結果集。（3）

MySQL5.7以下的版本一個表上同一時間、同一事件的觸發器只能有一個，所以最多可有6個觸發器，但MySQL5.7及以上版本不再有這個限制。（4）在一個表上的

觸發器越多，對在該表上的DML操作的性能影響就越大。（5）創建好的觸發器要進行反復測試，以避免其產生意想不到的負面影響。9.2.4刪除觸發器(1)在可視化管理工具的菜單界面的“表設計器”中找到相應的觸發器，鼠標右擊，選擇“刪除觸發器”。(2)使用SQL命令DROPTRIGGER刪除指定的觸發器DROPTRIGGER[database_name]trigger_name;【例9-20】刪除student表上的del_stu觸發器。USEteaching;DROPTRIGGERdel_stu;注：刪除觸發器所在的表時，MySQL將自動刪除與該表相關的觸發器。9.3事件從MySQL5.1開始新增了一個特色功能事件調度器（EventScheduler），簡稱事件。9.3.1事件簡介事件是指定的時刻才被執行的過程式數據庫對象，它可以作為定時任務調度器，取代部分原來只能用操作系統的計劃任務才能執行的工作。MySQL的事件可以實現每秒鐘執行一個任務，這在一些對實時性要求較高的環境下是非常實用的，比如火車售票系統、股票交易系統、各種比賽的實時統計分析等等。9.3.1事件簡介事件調度器是定時觸發執行的，從這個角度上看也可以稱作是“臨時觸發器”。但是它與觸發器又有所區別，觸發器只針對某個表產生的事件執行一些語句，而事件調度器則是在某一段（間隔）時間執行一些語句。一個事件可調用一次，也可周期性的啟動，它是由一個特定的線程來管理的，也就是所謂的“事件調度器”。使用事件調度器之前，必須確保它處于開啟狀態。9.3.1事件簡介（1）查看事件調度器查看事件調度器是否已開啟，執行以下SQL語句：SHOWVARIABLESLIKE'event_scheduler';也可以直接查看系統變量：SELECT@@event_scheduler;“ON”，表示已開啟；“OFF”，表示未開啟。9.3.1事件簡介（2）開啟事件調度器如果事件調度器未開啟，用以下SQL語句開啟：SETGLOBALevent_scheduler=1;或SET@@global.event_scheduler=TRUE;9.3.2創建事件可以創建在某一時刻發生的事件、指定時間區間內周期性發生的事件，事件還可以調用存儲過程或函數。MySQL創建事件的SQL語句語法：CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULE<schedule>[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE|DISABLEONSLAVE][COMMENT‘comment’]DOevent_body;9.3.2創建事件【例9-21】每天凌晨3點自動刪除bankcard數據庫account表中過期的賬戶的數據。CREATEEVENTevent_del_records_expireONSCHEDULEEVERY1DAYSTARTS'2022-01-0103:00:00'DODELETEFROMaccountWHEREDATEDIFF(ExpiryDate,CURDATE())<=0;9.3.2創建事件【例9-22】創建事件，實現每個月的第一天凌晨1點統計一次儲戶人數和賬戶個數，并插入到統計表中。（1）創建名稱為p_t