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