MySQL數(shù)據(jù)庫應(yīng)用教程 課件 第9、10章 存儲過程、異常處理和游標；觸發(fā)器和事件

第9章存儲過程、異常處理和游標《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源目錄第9章存儲過程、異常處理和游標9.1存儲過程9.2異常處理9.3使用游標處理結(jié)果集9.1.1存儲過程的概念9.1.2創(chuàng)建存儲過程CREATEPROCEDUREsp_name([proc_parameter1,proc_parameter2,…])[characteristic…]routine_body;9.1存儲過程【例9-1】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個顯示student表中所有記錄的存儲過程。CREATEPROCEDUREproc_display_all_student()READSSQLDATABEGINSELECT*FROMstudent;END;9.1存儲過程9.1存儲過程9.1.3執(zhí)行存儲過程CALL[db_name.]sp_name([parameter1,parameter2,…]);【例9-2】執(zhí)行proc_display_all_student過程。執(zhí)行存儲過程的SQL語句如下：CALLproc_display_all_student();9.1存儲過程9.1.4創(chuàng)建存儲過程的步驟1.實現(xiàn)存儲過程的功能【例9-3】在studentinfo數(shù)據(jù)庫中，創(chuàng)建不帶參數(shù)的存儲過程proc_selectcourse_avg，顯示selectcourse表中的學(xué)號和每位學(xué)生的平均成績。編寫下面語句，SQL語句如下：SELECTstudentID,avg(Score)平均分

FROMselectcourseGROUPBYStudentID;9.1存儲過程2.創(chuàng)建存儲過程CREATEPROCEDUREproc_selectcourse_avg()READSSQLDATACOMMENT'顯示學(xué)號和每位學(xué)生的平均成績'BEGINSELECTstudentID,avg(Score)平均分FROMselectcourseGROUPBYStudentID;END;9.1存儲過程3.執(zhí)行存儲過程CALLproc_selectcourse_avg();9.1存儲過程9.1.5存儲過程的管理1.查看存儲過程的狀態(tài)和定義（1）查看存儲過程的狀態(tài)SHOWPROCEDURESTATUS[LIKE'pattern'];例如，SQL語句如下：SHOWPROCEDURESTATUSLIKE'pro%';9.1存儲過程（2）查看存儲過程的定義SHOWCREATEPROCEDUREsp_name;例如，SQL語句如下：SHOWCREATEPROCEDUREproc_display_all_student;9.1存儲過程（3）查看所有的存儲過程SELECT*FROMinformation_schema.routines[WHEREroutine_name='名稱'];例如SELECT*FROMinformation_schema.routines;SELECT*FROMinformation_schema.routinesWHEREroutine_name='proc_display_all_student';9.1存儲過程2.修改存儲過程ALTERPROCEDUREsp_name[characteristic…]【例9-4】修改存儲過程up_display_all_student的定義，將特性改為MODIFIESSQLDATA，并指明權(quán)限調(diào)用者可以執(zhí)行。ALTERPROCEDUREproc_display_all_studentMODIFIESSQLDATASQLSECURITYINVOKER;9.1存儲過程3.刪除存儲過程DROPPROCEDURE[IFEXISTS]sp_name;【例9-5】刪除存儲過程proc_display_all_student。SQL語句如下：DROPPROCEDUREIFEXISTSproc_display_all_student;9.1存儲過程3.使用Navicat管理存儲過程9.1存儲過程9.1.6存儲過程的各種參數(shù)應(yīng)用1.不帶參數(shù)的存儲過程（1）創(chuàng)建不帶參數(shù)的存儲過程CREATEPROCEDUREsp_name()[characteristic…]routine_body;9.1存儲過程（2）執(zhí)行不帶參數(shù)的存儲過程執(zhí)行不帶參數(shù)的存儲過程的語法格式為：CALLsp_name();9.1存儲過程【例9-6】在studentinfo數(shù)據(jù)庫中，創(chuàng)建不帶參數(shù)的存儲過程proc_student_age，查詢學(xué)生表student中的全體學(xué)生，顯示姓名、性別和年齡。CREATEPROCEDUREproc_student_age()READSSQLDATACOMMENT'查詢學(xué)生表student中的全體學(xué)生，顯示姓名、性別和年齡'BEGINSELECTStudentNameAS姓名,SexAS性別,YEAR(NOW())-YEAR(Birthday)AS年齡

FROMstudent;END;CALLproc_student_age();9.1存儲過程2.帶IN參數(shù)的存儲過程CREATEPROCEDUREsp_name(INparam_name1type1[,INparam_name2type2,…])[characteristic…]routine_body;在執(zhí)行調(diào)用存儲過程時，實參要給出具體的值。執(zhí)行帶IN參數(shù)的存儲過程的語法格式為：CALLsp_name(parameter1[,parameter2,…]);9.1存儲過程【例9-7】創(chuàng)建帶有輸入?yún)?shù)的存儲過程proc_student_class，給定班級編號，查詢出該班級的所有學(xué)生記錄。1）CREATEPROCEDUREproc_student_class(INvClassIDCHAR(10))READSSQLDATABEGINSELECT*FROMstudentWHEREClassID=vClassID;END;2）CALLproc_student_class('2022600103');或SET@ClassID='2022600103';CALLproc_student_class(@ClassID);9.1存儲過程3.帶OUT參數(shù)的存儲過程CREATEPROCEDUREsp_name(INparam_name1type1[,…],OUTparam_name2type2[,…])[characteristic…]routine_body執(zhí)行帶OUT參數(shù)的存儲過程的語法格式為：SET@variable_name=表達式;CALLsp_name(parameter1[,…],@variable_name[,…]);9.1存儲過程【例9-8】創(chuàng)建帶有輸入?yún)?shù)和輸出參數(shù)的存儲過程proc_selectcourse，給定學(xué)號，查詢出該學(xué)生選修課程的數(shù)量和平均分，并通過輸出參數(shù)返回。1）CREATEPROCEDUREproc_selectcourse(INvStudentIDCHAR(12),OUTvCountCourseINT,OUTvAvgScoreFLOAT)READSSQLDATABEGINSELECTCOUNT(CourseID)INTOvCountCourseFROMselectcourseWHEREStudentID=vStudentID;SELECTAVG(Score)INTOvAvgScoreFROMselectcourseWHEREStudentID=vStudentID;END;9.1存儲過程2）SQL語句如下：CALLproc_selectcourse('202263050132',@CountCourse,@AvgScore);SQL語句如下：SET@StudentID='202263050132',@CountCourse=NULL,@AvgScore=NULL;CALLproc_selectcourse(@StudentID,@CountCourse,@AvgScore);SQL語句如下：SELECT@CountCourse,@AvgScore;9.1存儲過程9.1存儲過程【例9-9】創(chuàng)建帶有輸入?yún)?shù)和輸出參數(shù)的存儲過程proc_getscores，給定學(xué)號，統(tǒng)計該學(xué)生的考試課程數(shù)和合格的課程數(shù)，并通過輸出參數(shù)返回。1）CREATEPROCEDUREproc_getscores(INvStudentIDCHAR(12),OUTvCountCourseINT,OUTvCountCoursesPassINT)READSSQLDATABEGINSELECTCOUNT(CourseID)INTOvCountCourseFROMselectcourseWHEREStudentID=vStudentID;SELECTCOUNT(CourseID)INTOvCountCoursesPassFROMselectcourseWHEREStudentID=vStudentIDANDScore>=60;END;9.1存儲過程2）SET@StudentID='202263050132',@CountCourse=NULL,@CountCoursePass=NULL;CALLproc_getscores(@StudentID,@CountCourse,@CountCoursePass);SELECT@CountCourseAS考試課程數(shù),@CountCoursePassAS合格的課程數(shù);9.1存儲過程【例9-10】創(chuàng)建存儲過程proc_query_score，傳入學(xué)號，顯示該學(xué)號學(xué)生的成績，如果全部成績>=60，則返回“Allpasses”；否則返回通過的課程門數(shù)和不通過的課程門數(shù)。CREATEPROCEDUREproc_query_score(INst_idCHAR(12),OUTstrCHAR(30))BEGINDECLAREpass,notpassTINYINTDEFAULT0;SELECTCOUNT(*)INTOpassFROMselectcourseWHEREStudentID=st_idANDScore>=60;SELECTCOUNT(*)INTOnotpassFROMselectcourseWHEREStudentID=st_idANDScore<60;IFnotpass=0THENBEGINSETstr='Allpasses';END;ELSEBEGINSETstr=CONCAT('Pass:',CONVERT(pass,CHAR(2)),'Notpass:',CONVERT(notpass,CHAR(2)));END;ENDIF;SELECT*FROMselectcourseWHEREStudentID=st_id;END;9.1存儲過程CALLproc_query_score('202263050132',@str);

SELECT@str;

CALLproc_query_score('202263050133',@str);

SELECT@str;9.1存儲過程4.帶INOUT參數(shù)的存儲過程CREATEPROCEDUREsp_name(INOUTparam_nametype[,…])[characteristic…]routine_body;SET@variable_name=表達式;CALLsp_name(@variable_name[,…]);9.1存儲過程【例9-11】創(chuàng)建帶有INOUT參數(shù)的存儲過程proc_ispass，給定學(xué)號、課程號，查詢得到對應(yīng)的成績?nèi)绻笥诨虻扔?0，則為1，否則為0，通過INOUT參數(shù)返回該值。CREATEPROCEDUREproc_ispass(INvStudentIDCHAR(12),INvCourseIDCHAR(10),INOUTpassINT)READSSQLDATABEGINDECLAREvScoreFLOAT;SELECTScoreINTOvScoreFROMselectcourseWHEREStudentID=vStudentIDANDCourseID=vCourseID;IFvScore>=60THENSETpass=1;ELSESETpass=0;ENDIF;END;9.1存儲過程2）調(diào)用存儲過程proc_ispass，INOUT參數(shù)保存在@pass中。SQL語句如下：SET@pass=0;CALLproc_ispass('202263050132','630575',@pass);SELECT@pass;9.1存儲過程9.2.1自定義異常名稱語句DECLAREcondition_nameCONDITIONFORcondition_value;SQLSTATEsqlstate_value|mysql_error_code;【例9-12】用名字定義“1062(23000)”這個錯誤，名稱為error_insert。可以用兩種不同的方法定義。方法一：使用sqlstate_value，SQL語句如下：DECLAREerror_insertCONDITIONFORSQLSTATE'23000';方法二：使用mysql_error_code，SQL語句如下：DECLAREerror_insertCONDITIONFOR1062;9.2異常處理9.2.2自定義異常處理程序DECLAREhandler_typeHANDLERFORcondition_valuesp_statement;condition_name|mysql_error_code|SQLSTATEsqlstate_value|SQLWARNING|NOTFOUND|SQLEXCEPTION9.2異常處理9.2.3異常處理實例【例9-13】在studentinfo數(shù)據(jù)庫中創(chuàng)建一個表users，該表的u_id列為主鍵，當插入相同的主鍵值時觸發(fā)異常。1）USEstudentinfo;DROPTABLEIFEXISTSusers;CREATETABLEusers(u_idINTPRIMARYKEY,u_nameCHAR(10));2）DROPPROCEDUREIFEXISTSproc_insert_userCREATEPROCEDUREproc_insert_user(INuidINT,INunameCHAR(10),OUTinfoCHAR(20))MODIFIESSQLDATABEGININSERTINTOusers(u_id,u_name)VALUES(uid,uname);SETinfo='Insertcomplete'; END;9.2異常處理3）調(diào)用存儲過程。①CALLproc_insert_user(123,'Jack',@info);

SELECT*FROMusers;9.2異常處理②CALLproc_insert_user(123,'Lily',@info);

SELECT@info;9.2異常處理4）DROPPROCEDUREIFEXISTSproc_insert_user1;CREATEPROCEDUREproc_insert_user1(INuidINT,INunameCHAR(10),OUTinfoCHAR(20))MODIFIESSQLDATABEGIN#方式1DECLAREerror1CONDITIONFOR1062;

DECLAREEXITHANDLERFORerror1SETinfo='Cannotinsert';

#方式2#DECLAREEXITHANDLERFOR1062SETinfo='Cannotinsert';INSERTINTOusers(u_id,u_name)VALUES(uid,uname);SETinfo='Insertcomplete';END;9.2異常處理3）調(diào)用存儲過程。①CALLproc_insert_user1(301,'Lily',@info);

SELECT*FROMusers;9.2異常處理②CALLproc_insert_user1(301,'Alex',@info);

SELECT@info;

SELECT*FROMusers;9.2異常處理9.3.1游標的概念9.3.2定義游標DECLAREcursor_nameCURSORFORselect_statement;9.3使用游標處理結(jié)果集【例9-14】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個游標，從student表中查詢出學(xué)號、姓名和班級號列的記錄。DECLAREcur_studentCURSORFORSELECTStudentID,StudentName,BirthdayFROMstudent;SELECTStudentID,StudentName,BirthdayFROMstudent;9.3使用游標處理結(jié)果集9.3.3打開游標OPENcursor_name;例如，打開前面例題創(chuàng)建的cur_student游標，SQL語句如下：OPENcur_student;9.3使用游標處理結(jié)果集9.3.4使用游標FETCHcursor_nameINTOvar_name1[,var_name2,…];9.3使用游標處理結(jié)果集終止游標執(zhí)行：DECLAREdoneBOOLEANDEFAULT0; --DECLAREdoneINTDEFAULTFALSE;DECLAREcurCURSORFORSELECT…;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;--DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;--DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;9.3使用游標處理結(jié)果集遍歷游標第1種使用WHILE循環(huán)。OPENcur;FETCHcurINTO…;WHILE(done!=1)DO #WHILE(NOTdone)DO#處理語句;FETCHcurINTO…;ENDWHILE;CLOSEcur; #關(guān)閉游標9.3使用游標處理結(jié)果集第2種使用REPEAT循環(huán)。OPENcur;REPEATFETCHcurINTO…;IFdone!=1THEN #IF(NOTdone)THEN#處理語句;ENDIF;UNTILdoneENDREPEAT;CLOSEcur; #關(guān)閉游標9.3使用游標處理結(jié)果集9.3.5關(guān)閉游標CLOSEcursor_name;9.3使用游標處理結(jié)果集9.3.6游標的應(yīng)用【例9-15】在studentinfo數(shù)據(jù)庫中，創(chuàng)建存儲過程up_cur_student，用游標獲取student表中北京籍學(xué)生的學(xué)號、姓名和出生日期。1）創(chuàng)建存儲過程up_cur_student，SQL語句如下：DROPPROCEDUREIFEXISTSproc_cur_student;CREATEPROCEDUREproc_cur_student()READSSQLDATABEGIN#定義接收游標數(shù)據(jù)的變量DECLAREvIDCHAR(12);DECLAREvNameVARCHAR(20);DECLAREvBirthdayDATE;DECLAREdoneBOOLEANDEFAULT0; #定義結(jié)束循環(huán)的標志變量9.3使用游標處理結(jié)果集#定義游標DECLAREcur_stCURSORFORSELECTStudentID,StudentName,BirthdayFROMstudentWHEREAddress='北京';DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENcur_st; #打開游標#開始循環(huán)游標中的記錄REPEATFETCHcur_stINTOvID,vName,vBirthday;#游標指針指向一條記錄

IFdone!=1THEN #判斷游標的循環(huán)是否結(jié)束

SELECTvID,vName,vBirthday;ENDIF;UNTILdoneENDREPEAT;CLOSEcur_st; #關(guān)閉游標END;9.3使用游標處理結(jié)果集DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;2）執(zhí)行存儲過程，SQL語句如下：CALLproc_cur_student();9.3使用游標處理結(jié)果集【例9-16】用游標計算student表中的男生數(shù)，此功能可以直接使用COUNT()函數(shù)，此例為演示游標的使用方法。CREATEPROCEDUREproc_cur_sum(OUTsumINT)BEGINDECLAREvSexCHAR(20); #定義接收游標數(shù)據(jù)的變量DECLAREdoneINTDEFAULT0; #定義結(jié)束循環(huán)的標志變量DECLAREcurCURSORFORSELECTSexFROMstudentWHERESex='男';DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;SETsum=0; #計算人數(shù)的初始值OPENcur; #打開游標ww:LOOPFETCHcurINTOvSex; #使用游標，游標指針指向第1條記錄

#如果發(fā)生異常

IFdone=1THENLEAVEww;ENDIF;IFvSex='男'THENSETsum=sum+1;

ENDIF;ENDLOOP;CLOSEcur;END; 9.3使用游標處理結(jié)果集2）執(zhí)行存儲過程CALLproc_cur_count，SQL語句如下：CALLproc_cur_sum(@n);SELECT@nAS男生數(shù);9.3使用游標處理結(jié)果集祝賀你完成了最難的一章《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源第10章觸發(fā)器和事件《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源目錄第10章觸發(fā)器和事件10.1觸發(fā)器10.2事件10.1.1觸發(fā)器的基本概念1.觸發(fā)器概念2.觸發(fā)器的分類3.觸發(fā)器的特點10.1觸發(fā)器10.1.2創(chuàng)建觸發(fā)器CREATETRIGGERtrigger_name{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ONtable_nameFOREACHROW[trigger_order]trigger_body;10.1觸發(fā)器【例10-1】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個觸發(fā)器tr_student_insert_sex，當向student表中插入記錄時，檢查性別是否為“男”或“女”，如果不是，則設(shè)置為“男”。1）創(chuàng)建觸發(fā)器。#DROPTRIGGERIFEXISTStr_student_insert_sex;CREATETRIGGERtr_student_insert_sexBEFOREINSERTONstudentFOREACHROWBEGINIFNEW.Sex!='男'&&NEW.Sex!='女'THENSETNEW.Sex='男';ENDIF;END;10.1觸發(fā)器2）測試觸發(fā)器。①INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202270010121','吳琪妙','F','2003-04-11','陜西','2022700101');

10.1觸發(fā)器②SELECT*FROMstudentWHEREStudentID='202270010121';

10.1觸發(fā)器10.1.3觸發(fā)程序中的NEW和OLD1.OLD關(guān)鍵字與NEW關(guān)鍵字的方式（1）INSERT型觸發(fā)器（2）DELETE型觸發(fā)器（3）UPDATE型觸發(fā)器10.1觸發(fā)器2.訪問觸發(fā)器NEW和OLD表的語法訪問觸發(fā)器NEW和OLD表的語法格式為：OLD.column_nameNEW.column_name10.1觸發(fā)器【例10-2】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個觸發(fā)器tr_student_insert_classnum，當向student表中插入記錄時，自動更新class表中的班級人數(shù)。1）創(chuàng)建觸發(fā)器。#DROPTRIGGERIFEXISTStr_student_insert_classnumCREATETRIGGERtr_student_insert_classnumAFTERINSERTONstudentFOREACHROWBEGINDECLAREnINTDEFAULT0; #保存插入記錄前的班級人數(shù)

#取出班級表中保存的班級人數(shù)

SETn=(SELECTClassNumFROMclassWHEREClassID=NEW.ClassID);UPDATEclassSETClassNum=n+1WHEREClassID=NEW.ClassID;END;10.1觸發(fā)器2）測試觸發(fā)器。

UPDATEclassSETClassNum=30WHEREClassID='2022700101';#給該班人數(shù)設(shè)置一個初始值SELECT*FROMclassWHEREClassID='2022700101';

INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202270010166','張蕊','女','2003-05-08','河北','2022700101');

SELECT*FROMclassWHEREClassID='2022700101';

10.1觸發(fā)器10.1.4查看觸發(fā)器1.使用SHOWTRIGGERS語句查看觸發(fā)器信息SHOWTRIGGERS[{FROM|IN}db_name];【例10-3】查看數(shù)據(jù)庫studentinfo中已有的觸發(fā)器的狀態(tài)等信息。USEstudentinfo;SHOWTRIGGERS;10.1觸發(fā)器2.在triggers表中查看觸發(fā)器詳細信息SELECT*FROMinformation_schema.triggers[WHERETRIGGER_NAME='trigger_name'];【例10-4】使用SELECT語句查詢triggers表中的信息。1）SELECT*FROMinformation_schema.triggers;10.1觸發(fā)器2）SELECT*FROMinformation_schema.triggersWHERETRIGGER_NAME='tr_student_insert';10.1觸發(fā)器10.1.5刪除觸發(fā)器DROPTRIGGER[IFEXISTS][schema_name.]trigger_name;【例10-5】刪除數(shù)據(jù)庫studentinfo中的觸發(fā)器。DROPTRIGGERIFEXISTStr_student_insert_sex;DROPTRIGGERIFEXISTStr_student_insert_classnum;DROPTRIGGERIFEXISTSstudentinfo.tr_student_insert;10.1觸發(fā)器10.3.6觸發(fā)器的類型和執(zhí)行順序1.觸發(fā)器的類型（1）INSERT觸發(fā)器（2）DELETE觸發(fā)器（3）UPDATE觸發(fā)器10.1觸發(fā)器2.觸發(fā)器的6種形式1）BEFOREINSERT2）AFTERINSERT3）BEFOREDELETE4）AFTERDELETE5）BEFOREUPDATE6）AFTERINSERT10.1觸發(fā)器3.觸發(fā)器的執(zhí)行順序【例10-6】在T_reader表上分別創(chuàng)建BEFOREINSERT和AFTERINSERT觸發(fā)器，當向T_reader表中插入記錄時，通過兩個觸發(fā)器向T_borrow表中分別插入一行記錄，觀察這兩個觸發(fā)器的觸發(fā)順序。1）創(chuàng)建T_reader表，SQL語句如下：USEstudentinfo;DROPTABLEIFEXISTST_reader;CREATETABLET_reader(ReaderIDCHAR(6),ReaderNameVARCHAR(10));10.1觸發(fā)器2）創(chuàng)建T_borrow表，SQL語句如下：DROPTABLEIFEXISTST_borrow;CREATETABLET_borrow(ReaderIDCHAR(6),BookIDCHAR(10),TriggerTimeTIMESTAMPNOTNULLDEFAULTNOW());10.1觸發(fā)器3）創(chuàng)建T_reader表上的觸發(fā)器tr_before_insert，SQL語句如下：DROPTRIGGERIFEXISTStr_before_insert;CREATETRIGGERtr_before_insertBEFOREINSERTONT_readerFOREACHROWBEGININSERTINTOT_borrowSETReaderID='111111',BookID='AAAAAAAAAA';END;10.1觸發(fā)器4）創(chuàng)建T_reader表上的tr_after_insert觸發(fā)器，SQL語句如下：DROPTRIGGERIFEXISTStr_after_insert;CREATETRIGGERtr_after_insertAFTERINSERTONT_readerFOREACHROWBEGININSERTINTOT_borrowSETReaderID='222222',BookID='BBBBBBBBBB';END;10.1觸發(fā)器6）測試觸發(fā)器，向T_reader表中插入一條記錄，SQL語句如下：INSERTINTOT_reader(ReaderID,ReaderName)VALUES('666666','孟琳');7）查看T_borrow表中插入記錄的順序和時間，SQL語句和運行結(jié)果如下：SELECT*FROMT_borrow;10.1觸發(fā)器4.觸發(fā)器發(fā)生錯誤時的處理方式10.1觸發(fā)器10.3.7觸發(fā)器的使用實例BEFOREINSERT觸發(fā)器使用方法【例10-7】創(chuàng)建觸發(fā)器tr_student_insert，在向student表插入學(xué)生記錄前先檢查待插入學(xué)生記錄的學(xué)號，如果該學(xué)號在student表中不存在則插入，否則返回錯誤信息。1）創(chuàng)建觸發(fā)器。DROPTRIGGERIFEXISTStr_student_insert;CREATETRIGGERtr_student_insertBEFOREINSERTONstudentFOREACHROWBEGINDECLAREmessage_textCHAR(10)DEFAULT"";DECLAREidCHAR(12)DEFAULTNULL;SETid=(SELECTStudentIDFROMstudentWHEREStudentID=NEW.StudentID);IF(idISNOTNULL)THENSIGNALSQLSTATE'45000'SETmessage_text='該學(xué)號已存在';#返回錯誤信息ENDIF;END;10.1觸發(fā)器2）測試觸發(fā)器。SELECT*FROMstudentWHEREStudentID='202263050133';

INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202263050133','陳一杰','男','2003-06-01','浙江','2022630501');

SELECT*FROMstudentWHEREStudentID='202263050133';10.1觸發(fā)器③INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202263050188','陳一杰','男','2003-06-01','浙江','2022630501');

SELECT*FROMstudentWHEREStudentID='202263050188';10.1觸發(fā)器2.AFTERINSERT觸發(fā)器使用方法【例10-8】在student表中插入新學(xué)生記錄后，將插入記錄成功的信息寫入到student_status表中。1）創(chuàng)建一個student_status表，用于保存student表中學(xué)生的備注信息。DROPTABLEIFEXISTSstudent_status;CREATETABLEstudent_status(StudentIDCHAR(12)PRIMARYKEY,StatusNotesVARCHAR(10))ENGINE=INNODB;10.1觸發(fā)器2）創(chuàng)建觸發(fā)器。DROPTRIGGERIFEXISTStr_student_status_insert;CREATETRIGGERtr_student_status_insertAFTERINSERTONstudentFOREACHROWBEGININSERTINTOstudent_status(StudentID,StatusNotes)VALUES(NEW.StudentID,'學(xué)生記錄插入成功');END;10.1觸發(fā)器2）測試觸發(fā)器。①INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202263050199','高琳','女','2003-07-01','天津','20226305');②SELECT*FROMstudent_status;10.1觸發(fā)器3.BEFOREUPDATE觸發(fā)器使用方法【例10-9】在selectcourse表上創(chuàng)建一個用于檢查修改成績的觸發(fā)器tr_selectcourse_cheek，使得成績位于0~100的范圍內(nèi)，如果分數(shù)大于100則為100；如果分數(shù)小于0則為0。1）CREATETRIGGERttr_selectcourse_cheekBEFOREUPDATEONselectcourseFOREACHROWBEGINIFNEW.Score<0THENSETNEW.Score=0;ELSEIFNEW.Score>100THENSETNEW.Score=100;ENDIF;END;10.1觸發(fā)器2）測試觸發(fā)器。①SELECT*FROMselectcourseWHEREStudentID='202263050133';

②UPDATEselectcourseSETScore=120WHEREStudentID='202263050133'ANDCourseID='630575';UPDATEselectcourseSETScore=-10WHEREStudentID='202263050133'ANDCourseID='630572';10.1觸發(fā)器②UPDATEselectcourseSETScore=120WHEREStudentID='202263050133'ANDCourseID='630575';UPDATEselectcourseSETScore=-10WHEREStudentID='202263050133'ANDCourseID='630572';③SELECT*FROMselectcourseWHEREStudentID='202263050133';10.1觸發(fā)器4.AFTERUPDATE觸發(fā)器使用方法【例10-10】創(chuàng)建一個觸發(fā)器tri_update_courseid，當修改課程表course中某門課的課程號時，同時修改成績表selectcourse中的相同的全部課程號。1）創(chuàng)建觸發(fā)器。CREATETRIGGERtri_update_courseidAFTERUPDATEONcourseFOREACHROWBEGINUPDATEselectcourseSETCourseID=NEW.CourseIDWHERECourseID=OLD.CourseID;END;10.1觸發(fā)器2）ALTERTABLEselectcourseDROPFOREIGNKEYFK_selectcourse_course;3）測試觸發(fā)器。①SELECT*fromcourseWHERECourseID='100101';

SELECT*fromselectcourseWHERECourseID='100101';10.1觸發(fā)器②UPDATEcourseSETCourseID='100111'WHERECourseID='100101';

SELECT*fromcourseWHERECourseID='100111';

SELECT*fromselectcourseWHERECourseID='100111';

10.1觸發(fā)器【例10-11】在selectcourse表上創(chuàng)建一個觸發(fā)器，當在成績表selectcourse中修改了某一學(xué)生的某一課程的成績后，則把修改時間、學(xué)號、課程編號、修改前成績、修改后成績保存到log_trigger日志表中。1）CREATETABLElog_trigger(ExecTimeDATETIME,StudentIDCHAR(12),CourseIDCHAR(6),ScoreOldDECIMAL(4,1),ScoreNewDECIMAL(4,1));10.1觸發(fā)器2）CREATETRIGGERtr_selectcourse_updateAFTERUPDATEONselectcourseFOREACHROWBEGININSERTINTOlog_trigger(ExecTime,StudentID,CourseID,ScoreOld,ScoreNew)VALUES(NOW(),NEW.StudentID,NEW.CourseID,OLD.Score,NEW.Score);END;10.1觸發(fā)器2）測試觸發(fā)器。SELECT*FROMselectcourseWHEREStudentID='202263050132'ANDCourseID='630572';

UPDATEselectcourseSETScore=99WHEREStudentID='202263050132'ANDCourseID='630572';10.1觸發(fā)器SELECT*FROMselectcourseWHEREStudentID='202263050132'ANDCourseID='630572';

SELECT*FROMlog_trigger;10.1觸發(fā)器5.BEFOREDELETE觸發(fā)器使用方法【例10-12】在student表上，創(chuàng)建一個觸發(fā)器，在student表中刪除一行記錄之前，先在selectcourse表中刪除該學(xué)生的成績記錄。1）創(chuàng)建觸發(fā)器。DROPTRIGGERIFEXISTStr_student_delete_score;CREATETRIGGERtr_student_delete_scoreBEFOREDELETEONstudentFOREACHROWBEGIN#先在成績表selectcourse中刪除該學(xué)生的成績記錄

DELETEFROMselectcourseWHEREStudentID=(SELECTStudentIDFROMstudentWHEREStudentID=OLD.StudentID);END;10.1觸發(fā)器2）測試觸發(fā)器。①SELECT*FROMstudentWHEREStudentID='202263050133';

SELECT*FROMselectcourseWHEREStudentID='202263050133';10.1觸發(fā)器②DELETEFROMstudentWHEREStudentID='202263050133';

③SELECT*FROMstudentWHEREStudentID='202263050133';

SELECT*FROMselectcourseWHEREStudentID='202263050133';

10.1觸發(fā)器6.AFTERDELETE觸發(fā)器使用方法【例10-13】在例10-12的基礎(chǔ)上，在表student中再創(chuàng)建一個觸發(fā)器，每次在student表中刪除學(xué)生記錄后，都把被刪除記錄的學(xué)號列StudentID的值賦值給用戶變量@old_stuID，@count記錄刪除記錄的個數(shù)。1）創(chuàng)建觸發(fā)器。SET@old_StuID="",@count=0;#記錄被刪除學(xué)生的學(xué)號和個數(shù)DROPTRIGGERIFEXISTStr_student_delete;CREATETRIGGERtr_student_deleteAFTERDELETEONstudentFOREACHROWBEGINSET@old_StuID=CONCAT_WS(',',@old_StuID,OLD.StudentID);SET@count=@count+1;END;10.1觸發(fā)器2）測試觸發(fā)器。①SELECT*FROMstudentWHEREStudentID='202263050135';

SELECT*FROMselectcourseWHEREStudentID='202263050135';

10.1觸發(fā)器②DELETEFROMstudentWHEREStudentID='202263050135';

③SELECT@old_StuID,@count;

10.1觸發(fā)器④SELECT*FROMstudentWHEREStudentID='202260010306';SELECT*FROMselectcourseWHEREStudentID='202260010306';DELETEFROMstudentWHEREStudentID='202260010306';SELECT@old_StuID,@count;10.1觸發(fā)器【例10-14】在studentinfo數(shù)據(jù)庫中，創(chuàng)建回收站觸發(fā)器，當刪除員工表employee中的記錄時，把刪除的記錄保存到回收站表trash中。1）①創(chuàng)建員工表，SQL語句如下：CREATETABLEemployee(idBIGINT(20)NOTNULLAUTO_INCREMENT,nameVARCHAR(20)DEFAULTNULL,ageINT(11)DEFAULTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;10.1觸發(fā)器②INSERTINTOemployee(name,age)VALUES('張三',19),('李四',18),('王五',20),('趙六',21),('陳七',19),('錢八',20);10.1觸發(fā)器③CREATETABLEtrash(idBIGINT(20)NOTNULLAUTO_INCREMENT,dataVARCHAR(255)DEFAULTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;10.1觸發(fā)器2）--DROPTRIGGERIFEXISTStrigger_del_employee;CREATETRIGGERtrigger_del_employeeAFTERDELETEONemployeeFOREACHROWINSERTINTOtrash(data)VALUES(CONCAT('employee刪除：',OLD.id,'|',OLD.name,'|',OLD.age));10.1觸發(fā)器3）測試觸發(fā)器。①DELETEFROMemployeeWHEREid=3;②SELECT*FROMtrash;10.1觸發(fā)器10.2.1事件的概念1.查看事件調(diào)度器SHOWVARIABLESLIKE'event_scheduler';SELECT@@event_scheduler;10.2事件2.開啟事件調(diào)度器SETGLOBALevent_scheduler=ON;打開事件調(diào)度器SET@@GLOBAL.event_scheduler=ON;在my.ini中開啟事件：SETGLOBALevent_scheduler=ON10.2事件10.2.2創(chuàng)建事件CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][{ENABLE|DISABLE|DISABLEONSLAVE}][COMMENT'comment']DOevent_body;10.2事件①AT子句。ATtimestamp[+INTERVALinterval]...quantity{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}10.2事件EVERYinterval[STARTStimestamp[+INTERVALinterval]...][ENDStimestamp[+INTERVALinterval]...]10.2事件10.2.3事件的使用實例1.創(chuàng)建某個時刻發(fā)生的事件【例10-15】在studentinfo數(shù)據(jù)庫中，創(chuàng)建一個現(xiàn)在立即執(zhí)行的事件ev_create_user，事件執(zhí)行創(chuàng)建一個表t_user。1）創(chuàng)建事件。USEstudentinfo;DROPEVENTIFEXISTSev_create_user;CREATEEVENTev_create_userONSCHEDULEATNOW()DOBEGINDROPTABLEIFEXISTSt_user;

CREATETABLEt_user(T_IdINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用戶編號',T_NameCHAR(10)COMMENT'用戶名',T_CreateTimeTIMESTAMPCOMMENT'創(chuàng)建時間')COMMENT='用戶表';END;10.2事件2）查看事件結(jié)果。SELECT*FROMt_user;10.2事件【例10-16】創(chuàng)建一個事件ev_insert_user30，30秒后啟動事件，向t_user表中插入一行記錄。1）CREATEEVENTev_insert_user30ONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL30SECONDDOINSERTINTOt_user(T_Name,T_CreateTime)VALUES('AAA',NOW());2）SELECT*FROMt_user;10.2事件SELECT*FROMt_user;

SHOWEVENTS;10.2事件2.創(chuàng)建在指定區(qū)間周期性發(fā)生的事件（1）常用的時間間隔1）ONSCHEDULEEVERY5SECOND2）ONSCHEDULEEVERY1MINUTE10.2事件3）ONSCHEDULEEVERY1DAYSTARTSDATE_ADD(DATE_ADD(CURDATE(),INTERVAL1DAY),INTERVAL1HOUR)4）ONSCHEDULEEVERY1MONTHSTARTSDATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTE