MySQL數(shù)據(jù)庫技術(shù)及應用第7章存儲過程與觸發(fā)器存儲過程和觸發(fā)器都是嵌入在MySQL一段程序。存儲過程可以將經(jīng)常使用的SQL語句封裝起來，進行多次使用。游標可以實現(xiàn)數(shù)據(jù)檢索。觸發(fā)器是由插入、修改和刪除命令來觸發(fā)某個操作，當執(zhí)行這些命令時，就會激發(fā)觸發(fā)器執(zhí)行相應的操作。事件是規(guī)定計劃任務執(zhí)行的時間和頻率。前言學習要點了解存儲過程概念掌握存儲過程使用方法掌握游標的使用方法。了解觸發(fā)器概念掌握觸發(fā)器的使用方法了解事件概念熟悉事件的使用方法第7章存儲過程和觸發(fā)器學習內(nèi)容7.1存儲過程7.2游標7.3觸發(fā)器7.4事件第7章存儲過程和觸發(fā)器存儲過程是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL語句集，它存儲在數(shù)據(jù)庫中，一次編譯后永久有效，用戶通過指定存儲過程的名字并給出參數(shù)(如果該存儲過程帶有參數(shù))來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象。在數(shù)據(jù)量特別龐大的情況下利用存儲過程能提升效率。第7章存儲過程和觸發(fā)器7.1存儲過程7.1.1創(chuàng)建存儲過程創(chuàng)建存儲過程與創(chuàng)建函數(shù)相同，需要先臨時修改語句結(jié)束符號，再使用CREATE語句創(chuàng)建，其基本語法格式如下。CREATEPROCEDUREsp_name([IN|OUT|INOUT)param_nameTYPE])[characteristic…]routinebody;語法說明：·sp_name：創(chuàng)建存儲過程的名稱。·IN|OUT|INOUTparam_nametype：IN表示輸入?yún)?shù)，OUT表示輸出參數(shù)，INOUT表示既可以輸入也可以輸出；param_name表示參數(shù)名稱；TYPE表示參數(shù)的類型，該類型可以是MySQL數(shù)據(jù)庫中的任意類型。·routinebody：是SQL代碼的內(nèi)容，可以用BEQIN···END來表示SQL代碼的開始和結(jié)束。第7章存儲過程和觸發(fā)器7.1存儲過程7.1.1創(chuàng)建存儲過程【例7-1】創(chuàng)建名稱為proSumCno存儲過程。統(tǒng)計course表中課程的門數(shù)。DELIMITER$$CREATEPROCEDUREproSumCno(OUTsumcINT)BEGINSELECTCOUNT(*)INTOsumcFROMcourse;END$$DELIMITER;第7章存儲過程和觸發(fā)器7.1存儲過程7.1.2調(diào)用存儲過程創(chuàng)建完存儲過程后，需要調(diào)用存儲過程才能實現(xiàn)其功能。SQL采用CALL語句用來調(diào)用一個已創(chuàng)建存儲過程，基本語法格式如下。CALLsp_name([parameter[,…]]}語法說明：·sp_name：已創(chuàng)建存儲過程的名稱。·parameter[,…]：實數(shù)必須與該過程定義時形參相對應。當形參是IN時，則實參可以是變量或數(shù)據(jù)，當形參被指定是INOUT或OUT時，則實參必須是變量。第7章存儲過程和觸發(fā)器7.1存儲過程7.1.2調(diào)用存儲過程【例7-2】創(chuàng)建名稱為procStu存儲過程，根據(jù)學號，查詢某個學生的信息。DELIMITER$$CREATEPROCEDUREprocStu(INidVARCHAR(20))BEGINSELECT*FROMstudentWHERESno=id;END$$DELIMITER;CALLprocStu(‘20231160');第7章存儲過程和觸發(fā)器7.1存儲過程7.1.3查看存儲過程1.查看存儲過程的狀態(tài)使用SHOWPROCEDURESTATUS語句查看存儲過程的狀態(tài)，語法格式如下。SHOWPROCEDURESTATUS[LIKE'pattern'];【例7-3】查看procStu存儲過程的狀態(tài)。SHOWPROCEDURESTATUSLIKE'pro%'\G;第7章存儲過程和觸發(fā)器7.1存儲過程7.1.3查看存儲過程2.查看存儲過程的信息使用SHOWCREATEPROCEDURE查看存儲過程的信息，語法格式如下。SHOWCREATEPROCEDUREsp_name;語法說明：·sp_name：存儲過程名稱。【例7-4】查看procStu存儲過程信息。SHOWCREATEPROCEDUREprocStu\G;第7章存儲過程和觸發(fā)器7.1存儲過程7.1.3查看存儲過程3.通過表查看存儲過程查詢information_schema表可以查詢相關(guān)存儲過程的信息，語法格式如下。SELECT*FROMinformation_schema.ROUTINESWHEREroutine_name='sp_name’;語法說明：·ROUNTINE_NAME：ROUTINES表的字段名，該字段存儲所屬有存儲過程的名稱。【例7-5】通過表查看procStu存儲過程信息。SELECT*FROMinformation_schema.routinesWHEREroutine_name='procStu'\G;第7章存儲過程和觸發(fā)器7.1存儲過程7.1.4修改存儲過程語法結(jié)構(gòu)如下。ALTERPROCEDUREsp_name[characteristic…];語法說明如下：·characteristic：此項可選，與存儲過程創(chuàng)建時的“characteristic”功能相同第7章存儲過程和觸發(fā)器7.1存儲過程7.1.4修改存儲過程【例7-6】修改存儲過程procStu的定義，將讀寫權(quán)限改為MODIFIESSQLDATA，并執(zhí)行權(quán)限由定義者修改為調(diào)用者。首先查看procStu修改前的信息。SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu’;修改存儲過程procStu的定義。ALTERPROCEDUREprocStuMODIFIESSQLDATASQLSECURITYINVOKER;查看procStu修改后的信息。SELECTspecific_name,sql_data_access,security_typeFROMinformation_schema.routinesWHEREroutine_name='procStu';第7章存儲過程和觸發(fā)器7.1存儲過程7.1.5刪除存儲過程如果對數(shù)據(jù)庫中已創(chuàng)建的存儲過程需要刪除時，用戶可以通過SQL中DROP語句刪除存儲過程，語法格式如下。DROPPROCEDURE[IFEXISTS]sp_name;【例7-7】刪除存儲過程procStu。DROPPROCEDUREIFEXISTSprocStu;第7章存儲過程和觸發(fā)器7.1存儲過程學習內(nèi)容7.1存儲過程7.2游標7.3觸發(fā)器7.4事件第7章存儲過程和觸發(fā)器在MySQL中，存儲過程或函數(shù)中的查詢有時會返回多條記錄，而使用簡單的SELECT語句，沒有辦法得到第一行、下一行或前十行的數(shù)據(jù)，這時可以使用游標來逐條讀取查詢結(jié)果集中的記錄。游標是一種對數(shù)據(jù)進行遍歷的機制，當查詢返回結(jié)果集后，通過游標來逐行訪問結(jié)果集，并針對每一行執(zhí)行相應的操作。第7章存儲過程和觸發(fā)器7.2游標7.2.1游標操作1.創(chuàng)建游標基本語法結(jié)構(gòu)如下。DECLAREcursor_nameCURSORFORselectstatement;·cursor_name：創(chuàng)建游標的名稱。·selectstatement：查詢語句，返回個結(jié)果集，聲明的光標基于該結(jié)果集進行操作。第7章存儲過程和觸發(fā)器7.2游標7.2.1游標操作1.創(chuàng)建游標【例7-8】定義游標cur_Zame。DECLAREcur_ZameCURSORFORSELECTZno,ZnameFROMspecialty;第7章存儲過程和觸發(fā)器7.2游標7.2.1游標操作2.打開游標游標定義后，在使用之前必須先打開游標，其基本語法格式如下。OPENcursor_name;【例7-9】打開游標cur_Zame。OPENcur_Zame;第7章存儲過程和觸發(fā)器7.2游標7.2.1游標操作3.取游標中的結(jié)果集在打開游標后，使用MySQL提供的FETCH語句檢索SELECT結(jié)果集的記錄。FETCH語句每執(zhí)行一次，就獲取一條記錄，然后游標的內(nèi)部指針就向前移動指向下一條記錄，以確保每次得到的記錄不同。獲取結(jié)果的基本語法格式如下。FETCHcursor_nameINTOvar_name1[,var_name];【例7-10】使用游標cur_Zame，將查詢?nèi)〉玫慕Y(jié)果集分別存入標量id和name。FETCHcur_ZameINTOid,name;第7章存儲過程和觸發(fā)器7.2游標7.2.1游標操作4.關(guān)閉游標游標檢索完記錄后，應使用MySQL提供的語句關(guān)閉游標，釋放游標占用的內(nèi)存資源。其基本語法格式如下。CLOSEcursor_name;【例7-11】關(guān)閉游標cur_Zame。CLOSEcur_Zame;第7章存儲過程和觸發(fā)器7.2游標7.2.2游標使用【例7-12】在jxxx數(shù)據(jù)庫中，定義proc_curc存儲過程，使用游標curc將學分大于3的課程號和課程名存儲。DELIMITER$$CREATEPROCEDUREproc_curc()BEGINDECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREcurcCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>=3;OPENcurc;FETCHcurcINTOid,NAME;SELECTid,NAME;CLOSEcurc;END$$DELIMITER;CALLproc_curc();第7章存儲過程和觸發(fā)器7.2游標7.2.2游標使用【例7-13】在jxxx數(shù)據(jù)庫中，定義proc_while_cur存儲過程，使用游標locur及循環(huán)語句，將課程表的課程號和課程名逐一顯示。DELIMITER$$CREATEPROCEDUREproc_while_cur()BEGINDECLAREflagINTDEFAULTFALSE;DECLAREidVARCHAR(20)CHARACTERSETutf8;DECLARENAMEVARCHAR(20)CHARACTERSETutf8;DECLAREloccurCURSORFORSELECTCno,CnameFROMcourseWHERECcredit>2;DECLARECONTINUEHANDLERFORNOTFOUNDSETflag=TRUE;OPENloccur;FETCHloccurINTOid,NAME;WHILE(NOTflag)DOSELECTid,NAME;FETCHloccurINTOid,NAME;ENDWHILE;CLOSEloccur;SELECTid,NAME;END$$DELIMITER;CALLproc_while_cur();第7章存儲過程和觸發(fā)器7.2游標學習內(nèi)容7.1存儲過程7.2游標7.3觸發(fā)器7.4事件第7章存儲過程和觸發(fā)器觸發(fā)器是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作(INSERT、DELETE、UPDATE)時就會激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務規(guī)則等。第7章存儲過程和觸發(fā)器7.3觸發(fā)器7.3.1創(chuàng)建觸發(fā)器本語法格式如下CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtb1_nameFOREACHROWtrigger_stmt;語法說明：·trigger_name：觸發(fā)器的名稱。·trigger_time：觸發(fā)時間，可以指定為BEFORE或AFTER，BEFORE表示觸發(fā)器的命令在操作數(shù)據(jù)之前執(zhí)行，AFTER表示觸發(fā)器的命令在操作數(shù)據(jù)之后執(zhí)行。·trigger_event：觸發(fā)事件，包括INSERT、UPDATE和DELET。·tb1_name：建立觸發(fā)器的表名。·FOREACHROW：表示任何一條記錄上的操作，只要滿足條件，就會激活觸發(fā)器程序體。·trigger_stmt：觸發(fā)器程序體，可以是一條或多條語句，使用BEGIN…END將其封裝。第7章存儲過程和觸發(fā)器7.3觸發(fā)器7.3.1創(chuàng)建觸發(fā)器【例7-14】創(chuàng)建觸發(fā)器trig_in_cou，實現(xiàn)當向課程表Course添加一條記錄時，用戶變量c的值加1。SET@c=0;CREATETRIGGERtrig_in_couAFTERINSERTONcourseFOREACHROWSET@c=@c+1;INSERTINTOCourseVALUES('20110114','計算機組成',3,'計算機');SELECT@c;使用BEGIN…END將其封裝。第7章存儲過程和觸發(fā)器7.3觸發(fā)器7.3.1創(chuàng)建觸發(fā)器【例7-15】創(chuàng)建觸發(fā)器trig_delete_score，實現(xiàn)當向?qū)W生表student刪除一條記錄時，sc表中與該學生相關(guān)的記錄也刪除。CREATETRIGGERtrig_delete_sourceAFTERDELETEONstudentFOREACHROWDELETEFROMscWHERESno=OLD.Sno;SELECT*FROMscWHERESno=‘20231160';DELETEFROMstudentWHERESno=‘20231160';SELECT*FROMscWHERESno=‘20231160';第7章存儲過程和觸發(fā)器7.3觸發(fā)器7.3.2查看觸發(fā)器可以使用SHOWTRIGGERS語句查看數(shù)據(jù)庫中存在的觸發(fā)器的定義、狀態(tài)和語法信息等。【例7-16】查看jxxx數(shù)據(jù)庫中觸發(fā)器。SHOWTRIGGERS\G;第7章存儲過程和觸發(fā)器7.3觸發(fā)器7.3.2查看觸發(fā)器刪除觸發(fā)器是指刪除MySQL已經(jīng)定義的觸發(fā)器，其基本語法格式如下。DROPTRIGGER[schema_name][IFEXISTS]trigger_name;語法說明：·schema_name：可選項，表示數(shù)據(jù)庫的名稱。若省略，則默認為當前數(shù)據(jù)庫。·trigger_name：觸發(fā)器名稱·IFEXISTS：可選項，若將要刪除的觸發(fā)器不存在，系統(tǒng)將相應的提示信息。【例7-17】刪除trig_delete_source觸發(fā)器。DROPTRIGGERIFEXISTStrig_delete_source;第7章存儲過程和觸發(fā)器7.3觸發(fā)器學習內(nèi)容7.1存儲過程7.2游標7.3觸發(fā)器7.4事件第7章存儲過程和觸發(fā)器事件是指在某個特定的時間或每隔一段時間根據(jù)計劃自動完成指定的任務。MySQL的事件可以實現(xiàn)每秒鐘執(zhí)行一個任務，這在一些對實時性要求較高的環(huán)境下是非常實用的。事件是由MySQL提供的事件調(diào)度器定時觸發(fā)執(zhí)行的，從這個角度上看也可以稱作是“臨時觸發(fā)器”。但它與觸發(fā)器又有所區(qū)別，觸發(fā)器只針對某張表產(chǎn)生的DELETE、INSERT、UPDATE事件，執(zhí)行特定語句。而事件調(diào)度器則是在某一段(間隔)時間執(zhí)行有固定操作的任務。第7章存儲過程和觸發(fā)器7.4事件7.4.1開啟事件調(diào)度器事件調(diào)度器一個特定的線程，事件是由它來執(zhí)行和管理。在默認情況下事件調(diào)度器處于關(guān)閉狀態(tài)，因此，創(chuàng)建事件必須先查看和設(shè)置事件調(diào)度器的狀態(tài)。其基本語法格式如下。SHOWVARIABLESLIKE'event_scheduler';語法說明：·event_scheduler：全局變量，保存事件調(diào)度器的狀態(tài)，有OFF和ON兩個值。OFF表示事件調(diào)度器關(guān)閉；ON表示事件調(diào)度器打開。【例7-18】查看事件調(diào)度器狀態(tài)，若為關(guān)閉，將其設(shè)置為打開。SHOWVARIABLESLIKE'event_scheduler';SETGLOBALevent_scheduler=ON;第7章存儲過程和觸發(fā)器7.4事件7.4.2創(chuàng)建事件當事件調(diào)度器處于打開時，就可以創(chuàng)建事件。其基本語法格式如下。CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE|DISABLEONSLAVE][COMMENT'comment']DOevent_body;第7章存儲過程和觸發(fā)器7.4事件7.4.2創(chuàng)建事件語法說明：·event_name：指定事件名稱，它的最大長度為64個字符，不區(qū)分大小寫。·ONSCHEDULE：指定任務執(zhí)行的時間和時間間隔，有AT和EVERY兩種形式進行選擇，后面詳細說明。·ONCOMPLETION[NOT]PRESERVE：可選項，定義事件是否循環(huán)執(zhí)行，即是一次執(zhí)行還是永久執(zhí)行，默認NOTPRESERVE，為一次執(zhí)行。·ENABLE|DISABLE：可選項，ENABLE表示該事件可用，DISABLE表示該事件不可用。·COMMENT：事件設(shè)置注釋。·DO：指定事件啟動時所要執(zhí)行SQL語句，如果包含多條語句，使用BEGIN...END結(jié)構(gòu)將其包含其中。第7章存儲過程和觸發(fā)器7.4事件7.4.2創(chuàng)建事件參數(shù)schedule的值為一個AT子句，用于指定事件在某個時刻發(fā)生。另一個EVERY子句，表示指定事件的重復發(fā)生。其語法格式如下。ATtimestamp[+INTERVALinterval]...|EVERYinterval[STARTStimestamp[+INTERVALinterval]...][ENDStimestamp[+INTERVALinterval]...]第7章存儲過程和觸發(fā)器7.4事件7.4.2創(chuàng)建事件【例7-19】創(chuàng)建名稱為insert_course_event事件，實現(xiàn)從現(xiàn)在開始10分鐘30秒后向Course表添加一條記錄。CREATEEVENTinsert_course_eventONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL10MINUTE+INTERVAL30SECONDONCOMPLETIONPRESERVEDOINSERTINTOcourseVALUES('58130080','云計算技術(shù)',3);第7章存儲過程和觸發(fā)器7.4事件7.4.2創(chuàng)建事件【例7-20】創(chuàng)建名稱為delete_sc_event的事件，用于每隔20秒鐘向sc表中刪除一條成績小于或等于0的數(shù)據(jù)。CREATEEVENTdelete_sc_eventONSCHEDULEEVERY5SECONDONCOMPLETIONPRESERVEDODELETEFROMscWHEREGrade<=0;第7章存儲過程和觸發(fā)器7.4事件7.4.3查看事件事件