MySQL数据库应用教程 课件 第9、10章 存储过程、异常处理和游标；触发器和事件

第9章存储过程、异常处理和游标《MySQL数据库应用教程》刘瑞新主编配套资源目录第9章存储过程、异常处理和游标9.1存储过程9.2异常处理9.3使用游标处理结果集9.1.1存储过程的概念9.1.2创建存储过程CREATEPROCEDUREsp_name([proc_parameter1,proc_parameter2,…])[characteristic…]routine_body;9.1存储过程【例9-1】在studentinfo数据库中，创建一个显示student表中所有记录的存储过程。CREATEPROCEDUREproc_display_all_student()READSSQLDATABEGINSELECT*FROMstudent;END;9.1存储过程9.1存储过程9.1.3执行存储过程CALL[db_name.]sp_name([parameter1,parameter2,…]);【例9-2】执行proc_display_all_student过程。执行存储过程的SQL语句如下：CALLproc_display_all_student();9.1存储过程9.1.4创建存储过程的步骤1.实现存储过程的功能【例9-3】在studentinfo数据库中，创建不带参数的存储过程proc_selectcourse_avg，显示selectcourse表中的学号和每位学生的平均成绩。编写下面语句，SQL语句如下：SELECTstudentID,avg(Score)平均分

FROMselectcourseGROUPBYStudentID;9.1存储过程2.创建存储过程CREATEPROCEDUREproc_selectcourse_avg()READSSQLDATACOMMENT'显示学号和每位学生的平均成绩'BEGINSELECTstudentID,avg(Score)平均分FROMselectcourseGROUPBYStudentID;END;9.1存储过程3.执行存储过程CALLproc_selectcourse_avg();9.1存储过程9.1.5存储过程的管理1.查看存储过程的状态和定义（1）查看存储过程的状态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，并指明权限调用者可以执行。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存储过程的各种参数应用1.不带参数的存储过程（1）创建不带参数的存储过程CREATEPROCEDUREsp_name()[characteristic…]routine_body;9.1存储过程（2）执行不带参数的存储过程执行不带参数的存储过程的语法格式为：CALLsp_name();9.1存储过程【例9-6】在studentinfo数据库中，创建不带参数的存储过程proc_student_age，查询学生表student中的全体学生，显示姓名、性别和年龄。CREATEPROCEDUREproc_student_age()READSSQLDATACOMMENT'查询学生表student中的全体学生，显示姓名、性别和年龄'BEGINSELECTStudentNameAS姓名,SexAS性别,YEAR(NOW())-YEAR(Birthday)AS年龄

FROMstudent;END;CALLproc_student_age();9.1存储过程2.带IN参数的存储过程CREATEPROCEDUREsp_name(INparam_name1type1[,INparam_name2type2,…])[characteristic…]routine_body;在执行调用存储过程时，实参要给出具体的值。执行带IN参数的存储过程的语法格式为：CALLsp_name(parameter1[,parameter2,…]);9.1存储过程【例9-7】创建带有输入参数的存储过程proc_student_class，给定班级编号，查询出该班级的所有学生记录。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参数的存储过程CREATEPROCEDUREsp_name(INparam_name1type1[,…],OUTparam_name2type2[,…])[characteristic…]routine_body执行带OUT参数的存储过程的语法格式为：SET@variable_name=表达式;CALLsp_name(parameter1[,…],@variable_name[,…]);9.1存储过程【例9-8】创建带有输入参数和输出参数的存储过程proc_selectcourse，给定学号，查询出该学生选修课程的数量和平均分，并通过输出参数返回。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】创建带有输入参数和输出参数的存储过程proc_getscores，给定学号，统计该学生的考试课程数和合格的课程数，并通过输出参数返回。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考试课程数,@CountCoursePassAS合格的课程数;9.1存储过程【例9-10】创建存储过程proc_query_score，传入学号，显示该学号学生的成绩，如果全部成绩>=60，则返回“Allpasses”；否则返回通过的课程门数和不通过的课程门数。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参数的存储过程CREATEPROCEDUREsp_name(INOUTparam_nametype[,…])[characteristic…]routine_body;SET@variable_name=表达式;CALLsp_name(@variable_name[,…]);9.1存储过程【例9-11】创建带有INOUT参数的存储过程proc_ispass，给定学号、课程号，查询得到对应的成绩如果大于或等于60，则为1，否则为0，通过INOUT参数返回该值。CREATEPROCEDUREproc_ispass(INvStudentIDCHAR(12),INvCourseIDCHAR(10),INOUTpassINT)READSSQLDATABEGINDECLAREvScoreFLOAT;SELECTScoreINTOvScoreFROMselectcourseWHEREStudentID=vStudentIDANDCourseID=vCourseID;IFvScore>=60THENSETpass=1;ELSESETpass=0;ENDIF;END;9.1存储过程2）调用存储过程proc_ispass，INOUT参数保存在@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数据库中创建一个表users，该表的u_id列为主键，当插入相同的主键值时触发异常。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）调用存储过程。①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）调用存储过程。①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使用游标处理结果集【例9-14】在studentinfo数据库中，创建一个游标，从student表中查询出学号、姓名和班级号列的记录。DECLAREcur_studentCURSORFORSELECTStudentID,StudentName,BirthdayFROMstudent;SELECTStudentID,StudentName,BirthdayFROMstudent;9.3使用游标处理结果集9.3.3打开游标OPENcursor_name;例如，打开前面例题创建的cur_student游标，SQL语句如下：OPENcur_student;9.3使用游标处理结果集9.3.4使用游标FETCHcursor_nameINTOvar_name1[,var_name2,…];9.3使用游标处理结果集终止游标执行：DECLAREdoneBOOLEANDEFAULT0; --DECLAREdoneINTDEFAULTFALSE;DECLAREcurCURSORFORSELECT…;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;--DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;--DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;9.3使用游标处理结果集遍历游标第1种使用WHILE循环。OPENcur;FETCHcurINTO…;WHILE(done!=1)DO #WHILE(NOTdone)DO#处理语句;FETCHcurINTO…;ENDWHILE;CLOSEcur; #关闭游标9.3使用游标处理结果集第2种使用REPEAT循环。OPENcur;REPEATFETCHcurINTO…;IFdone!=1THEN #IF(NOTdone)THEN#处理语句;ENDIF;UNTILdoneENDREPEAT;CLOSEcur; #关闭游标9.3使用游标处理结果集9.3.5关闭游标CLOSEcursor_name;9.3使用游标处理结果集9.3.6游标的应用【例9-15】在studentinfo数据库中，创建存储过程up_cur_student，用游标获取student表中北京籍学生的学号、姓名和出生日期。1）创建存储过程up_cur_student，SQL语句如下：DROPPROCEDUREIFEXISTSproc_cur_student;CREATEPROCEDUREproc_cur_student()READSSQLDATABEGIN#定义接收游标数据的变量DECLAREvIDCHAR(12);DECLAREvNameVARCHAR(20);DECLAREvBirthdayDATE;DECLAREdoneBOOLEANDEFAULT0; #定义结束循环的标志变量9.3使用游标处理结果集#定义游标DECLAREcur_stCURSORFORSELECTStudentID,StudentName,BirthdayFROMstudentWHEREAddress='北京';DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENcur_st; #打开游标#开始循环游标中的记录REPEATFETCHcur_stINTOvID,vName,vBirthday;#游标指针指向一条记录

IFdone!=1THEN #判断游标的循环是否结束

SELECTvID,vName,vBirthday;ENDIF;UNTILdoneENDREPEAT;CLOSEcur_st; #关闭游标END;9.3使用游标处理结果集DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;2）执行存储过程，SQL语句如下：CALLproc_cur_student();9.3使用游标处理结果集【例9-16】用游标计算student表中的男生数，此功能可以直接使用COUNT()函数，此例为演示游标的使用方法。CREATEPROCEDUREproc_cur_sum(OUTsumINT)BEGINDECLAREvSexCHAR(20); #定义接收游标数据的变量DECLAREdoneINTDEFAULT0; #定义结束循环的标志变量DECLAREcurCURSORFORSELECTSexFROMstudentWHERESex='男';DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;SETsum=0; #计算人数的初始值OPENcur; #打开游标ww:LOOPFETCHcurINTOvSex; #使用游标，游标指针指向第1条记录

#如果发生异常

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

ENDIF;ENDLOOP;CLOSEcur;END; 9.3使用游标处理结果集2）执行存储过程CALLproc_cur_count，SQL语句如下：CALLproc_cur_sum(@n);SELECT@nAS男生数;9.3使用游标处理结果集祝贺你完成了最难的一章《MySQL数据库应用教程》刘瑞新主编配套资源第10章触发器和事件《MySQL数据库应用教程》刘瑞新主编配套资源目录第10章触发器和事件10.1触发器10.2事件10.1.1触发器的基本概念1.触发器概念2.触发器的分类3.触发器的特点10.1触发器10.1.2创建触发器CREATETRIGGERtrigger_name{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ONtable_nameFOREACHROW[trigger_order]trigger_body;10.1触发器【例10-1】在studentinfo数据库中，创建一个触发器tr_student_insert_sex，当向student表中插入记录时，检查性别是否为“男”或“女”，如果不是，则设置为“男”。1）创建触发器。#DROPTRIGGERIFEXISTStr_student_insert_sex;CREATETRIGGERtr_student_insert_sexBEFOREINSERTONstudentFOREACHROWBEGINIFNEW.Sex!='男'&&NEW.Sex!='女'THENSETNEW.Sex='男';ENDIF;END;10.1触发器2）测试触发器。①INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202270010121','吴琪妙','F','2003-04-11','陕西','2022700101');

10.1触发器②SELECT*FROMstudentWHEREStudentID='202270010121';

10.1触发器10.1.3触发程序中的NEW和OLD1.OLD关键字与NEW关键字的方式（1）INSERT型触发器（2）DELETE型触发器（3）UPDATE型触发器10.1触发器2.访问触发器NEW和OLD表的语法访问触发器NEW和OLD表的语法格式为：OLD.column_nameNEW.column_name10.1触发器【例10-2】在studentinfo数据库中，创建一个触发器tr_student_insert_classnum，当向student表中插入记录时，自动更新class表中的班级人数。1）创建触发器。#DROPTRIGGERIFEXISTStr_student_insert_classnumCREATETRIGGERtr_student_insert_classnumAFTERINSERTONstudentFOREACHROWBEGINDECLAREnINTDEFAULT0; #保存插入记录前的班级人数

#取出班级表中保存的班级人数

SETn=(SELECTClassNumFROMclassWHEREClassID=NEW.ClassID);UPDATEclassSETClassNum=n+1WHEREClassID=NEW.ClassID;END;10.1触发器2）测试触发器。

UPDATEclassSETClassNum=30WHEREClassID='2022700101';#给该班人数设置一个初始值SELECT*FROMclassWHEREClassID='2022700101';

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

SELECT*FROMclassWHEREClassID='2022700101';

10.1触发器10.1.4查看触发器1.使用SHOWTRIGGERS语句查看触发器信息SHOWTRIGGERS[{FROM|IN}db_name];【例10-3】查看数据库studentinfo中已有的触发器的状态等信息。USEstudentinfo;SHOWTRIGGERS;10.1触发器2.在triggers表中查看触发器详细信息SELECT*FROMinformation_schema.triggers[WHERETRIGGER_NAME='trigger_name'];【例10-4】使用SELECT语句查询triggers表中的信息。1）SELECT*FROMinformation_schema.triggers;10.1触发器2）SELECT*FROMinformation_schema.triggersWHERETRIGGER_NAME='tr_student_insert';10.1触发器10.1.5删除触发器DROPTRIGGER[IFEXISTS][schema_name.]trigger_name;【例10-5】删除数据库studentinfo中的触发器。DROPTRIGGERIFEXISTStr_student_insert_sex;DROPTRIGGERIFEXISTStr_student_insert_classnum;DROPTRIGGERIFEXISTSstudentinfo.tr_student_insert;10.1触发器10.3.6触发器的类型和执行顺序1.触发器的类型（1）INSERT触发器（2）DELETE触发器（3）UPDATE触发器10.1触发器2.触发器的6种形式1）BEFOREINSERT2）AFTERINSERT3）BEFOREDELETE4）AFTERDELETE5）BEFOREUPDATE6）AFTERINSERT10.1触发器3.触发器的执行顺序【例10-6】在T_reader表上分别创建BEFOREINSERT和AFTERINSERT触发器，当向T_reader表中插入记录时，通过两个触发器向T_borrow表中分别插入一行记录，观察这两个触发器的触发顺序。1）创建T_reader表，SQL语句如下：USEstudentinfo;DROPTABLEIFEXISTST_reader;CREATETABLET_reader(ReaderIDCHAR(6),ReaderNameVARCHAR(10));10.1触发器2）创建T_borrow表，SQL语句如下：DROPTABLEIFEXISTST_borrow;CREATETABLET_borrow(ReaderIDCHAR(6),BookIDCHAR(10),TriggerTimeTIMESTAMPNOTNULLDEFAULTNOW());10.1触发器3）创建T_reader表上的触发器tr_before_insert，SQL语句如下：DROPTRIGGERIFEXISTStr_before_insert;CREATETRIGGERtr_before_insertBEFOREINSERTONT_readerFOREACHROWBEGININSERTINTOT_borrowSETReaderID='111111',BookID='AAAAAAAAAA';END;10.1触发器4）创建T_reader表上的tr_after_insert触发器，SQL语句如下：DROPTRIGGERIFEXISTStr_after_insert;CREATETRIGGERtr_after_insertAFTERINSERTONT_readerFOREACHROWBEGININSERTINTOT_borrowSETReaderID='222222',BookID='BBBBBBBBBB';END;10.1触发器6）测试触发器，向T_reader表中插入一条记录，SQL语句如下：INSERTINTOT_reader(ReaderID,ReaderName)VALUES('666666','孟琳');7）查看T_borrow表中插入记录的顺序和时间，SQL语句和运行结果如下：SELECT*FROMT_borrow;10.1触发器4.触发器发生错误时的处理方式10.1触发器10.3.7触发器的使用实例BEFOREINSERT触发器使用方法【例10-7】创建触发器tr_student_insert，在向student表插入学生记录前先检查待插入学生记录的学号，如果该学号在student表中不存在则插入，否则返回错误信息。1）创建触发器。DROPTRIGGERIFEXISTStr_student_insert;CREATETRIGGERtr_student_insertBEFOREINSERTONstudentFOREACHROWBEGINDECLAREmessage_textCHAR(10)DEFAULT"";DECLAREidCHAR(12)DEFAULTNULL;SETid=(SELECTStudentIDFROMstudentWHEREStudentID=NEW.StudentID);IF(idISNOTNULL)THENSIGNALSQLSTATE'45000'SETmessage_text='该学号已存在';#返回错误信息ENDIF;END;10.1触发器2）测试触发器。SELECT*FROMstudentWHEREStudentID='202263050133';

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

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

SELECT*FROMstudentWHEREStudentID='202263050188';10.1触发器2.AFTERINSERT触发器使用方法【例10-8】在student表中插入新学生记录后，将插入记录成功的信息写入到student_status表中。1）创建一个student_status表，用于保存student表中学生的备注信息。DROPTABLEIFEXISTSstudent_status;CREATETABLEstudent_status(StudentIDCHAR(12)PRIMARYKEY,StatusNotesVARCHAR(10))ENGINE=INNODB;10.1触发器2）创建触发器。DROPTRIGGERIFEXISTStr_student_status_insert;CREATETRIGGERtr_student_status_insertAFTERINSERTONstudentFOREACHROWBEGININSERTINTOstudent_status(StudentID,StatusNotes)VALUES(NEW.StudentID,'学生记录插入成功');END;10.1触发器2）测试触发器。①INSERTINTOstudent(StudentID,StudentName,Sex,Birthday,Address,ClassID)VALUES('202263050199','高琳','女','2003-07-01','天津','20226305');②SELECT*FROMstudent_status;10.1触发器3.BEFOREUPDATE触发器使用方法【例10-9】在selectcourse表上创建一个用于检查修改成绩的触发器tr_selectcourse_cheek，使得成绩位于0~100的范围内，如果分数大于100则为100；如果分数小于0则为0。1）CREATETRIGGERttr_selectcourse_cheekBEFOREUPDATEONselectcourseFOREACHROWBEGINIFNEW.Score<0THENSETNEW.Score=0;ELSEIFNEW.Score>100THENSETNEW.Score=100;ENDIF;END;10.1触发器2）测试触发器。①SELECT*FROMselectcourseWHEREStudentID='202263050133';

②UPDATEselectcourseSETScore=120WHEREStudentID='202263050133'ANDCourseID='630575';UPDATEselectcourseSETScore=-10WHEREStudentID='202263050133'ANDCourseID='630572';10.1触发器②UPDATEselectcourseSETScore=120WHEREStudentID='202263050133'ANDCourseID='630575';UPDATEselectcourseSETScore=-10WHEREStudentID='202263050133'ANDCourseID='630572';③SELECT*FROMselectcourseWHEREStudentID='202263050133';10.1触发器4.AFTERUPDATE触发器使用方法【例10-10】创建一个触发器tri_update_courseid，当修改课程表course中某门课的课程号时，同时修改成绩表selectcourse中的相同的全部课程号。1）创建触发器。CREATETRIGGERtri_update_courseidAFTERUPDATEONcourseFOREACHROWBEGINUPDATEselectcourseSETCourseID=NEW.CourseIDWHERECourseID=OLD.CourseID;END;10.1触发器2）ALTERTABLEselectcourseDROPFOREIGNKEYFK_selectcourse_course;3）测试触发器。①SELECT*fromcourseWHERECourseID='100101';

SELECT*fromselectcourseWHERECourseID='100101';10.1触发器②UPDATEcourseSETCourseID='100111'WHERECourseID='100101';

SELECT*fromcourseWHERECourseID='100111';

SELECT*fromselectcourseWHERECourseID='100111';

10.1触发器【例10-11】在selectcourse表上创建一个触发器，当在成绩表selectcourse中修改了某一学生的某一课程的成绩后，则把修改时间、学号、课程编号、修改前成绩、修改后成绩保存到log_trigger日志表中。1）CREATETABLElog_trigger(ExecTimeDATETIME,StudentIDCHAR(12),CourseIDCHAR(6),ScoreOldDECIMAL(4,1),ScoreNewDECIMAL(4,1));10.1触发器2）CREATETRIGGERtr_selectcourse_updateAFTERUPDATEONselectcourseFOREACHROWBEGININSERTINTOlog_trigger(ExecTime,StudentID,CourseID,ScoreOld,ScoreNew)VALUES(NOW(),NEW.StudentID,NEW.CourseID,OLD.Score,NEW.Score);END;10.1触发器2）测试触发器。SELECT*FROMselectcourseWHEREStudentID='202263050132'ANDCourseID='630572';

UPDATEselectcourseSETScore=99WHEREStudentID='202263050132'ANDCourseID='630572';10.1触发器SELECT*FROMselectcourseWHEREStudentID='202263050132'ANDCourseID='630572';

SELECT*FROMlog_trigger;10.1触发器5.BEFOREDELETE触发器使用方法【例10-12】在student表上，创建一个触发器，在student表中删除一行记录之前，先在selectcourse表中删除该学生的成绩记录。1）创建触发器。DROPTRIGGERIFEXISTStr_student_delete_score;CREATETRIGGERtr_student_delete_scoreBEFOREDELETEONstudentFOREACHROWBEGIN#先在成绩表selectcourse中删除该学生的成绩记录

DELETEFROMselectcourseWHEREStudentID=(SELECTStudentIDFROMstudentWHEREStudentID=OLD.StudentID);END;10.1触发器2）测试触发器。①SELECT*FROMstudentWHEREStudentID='202263050133';

SELECT*FROMselectcourseWHEREStudentID='202263050133';10.1触发器②DELETEFROMstudentWHEREStudentID='202263050133';

③SELECT*FROMstudentWHEREStudentID='202263050133';

SELECT*FROMselectcourseWHEREStudentID='202263050133';

10.1触发器6.AFTERDELETE触发器使用方法【例10-13】在例10-12的基础上，在表student中再创建一个触发器，每次在student表中删除学生记录后，都把被删除记录的学号列StudentID的值赋值给用户变量@old_stuID，@count记录删除记录的个数。1）创建触发器。SET@old_StuID="",@count=0;#记录被删除学生的学号和个数DROPTRIGGERIFEXISTStr_student_delete;CREATETRIGGERtr_student_deleteAFTERDELETEONstudentFOREACHROWBEGINSET@old_StuID=CONCAT_WS(',',@old_StuID,OLD.StudentID);SET@count=@count+1;END;10.1触发器2）测试触发器。①SELECT*FROMstudentWHEREStudentID='202263050135';

SELECT*FROMselectcourseWHEREStudentID='202263050135';

10.1触发器②DELETEFROMstudentWHEREStudentID='202263050135';

③SELECT@old_StuID,@count;

10.1触发器④SELECT*FROMstudentWHEREStudentID='202260010306';SELECT*FROMselectcourseWHEREStudentID='202260010306';DELETEFROMstudentWHEREStudentID='202260010306';SELECT@old_StuID,@count;10.1触发器【例10-14】在studentinfo数据库中，创建回收站触发器，当删除员工表employee中的记录时，把删除的记录保存到回收站表trash中。1）①创建员工表，SQL语句如下：CREATETABLEemployee(idBIGINT(20)NOTNULLAUTO_INCREMENT,nameVARCHAR(20)DEFAULTNULL,ageINT(11)DEFAULTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;10.1触发器②INSERTINTOemployee(name,age)VALUES('张三',19),('李四',18),('王五',20),('赵六',21),('陈七',19),('钱八',20);10.1触发器③CREATETABLEtrash(idBIGINT(20)NOTNULLAUTO_INCREMENT,dataVARCHAR(255)DEFAULTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;10.1触发器2）--DROPTRIGGERIFEXISTStrigger_del_employee;CREATETRIGGERtrigger_del_employeeAFTERDELETEONemployeeFOREACHROWINSERTINTOtrash(data)VALUES(CONCAT('employee删除：',OLD.id,'|',OLD.name,'|',OLD.age));10.1触发器3）测试触发器。①DELETEFROMemployeeWHEREid=3;②SELECT*FROMtrash;10.1触发器10.2.1事件的概念1.查看事件调度器SHOWVARIABLESLIKE'event_scheduler';SELECT@@event_scheduler;10.2事件2.开启事件调度器SETGLOBALevent_scheduler=ON;打开事件调度器SET@@GLOBAL.event_scheduler=ON;在my.ini中开启事件：SETGLOBALevent_scheduler=ON10.2事件10.2.2创建事件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.创建某个时刻发生的事件【例10-15】在studentinfo数据库中，创建一个现在立即执行的事件ev_create_user，事件执行创建一个表t_user。1）创建事件。USEstudentinfo;DROPEVENTIFEXISTSev_create_user;CREATEEVENTev_create_userONSCHEDULEATNOW()DOBEGINDROPTABLEIFEXISTSt_user;

CREATETABLEt_user(T_IdINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户编号',T_NameCHAR(10)COMMENT'用户名',T_CreateTimeTIMESTAMPCOMMENT'创建时间')COMMENT='用户表';END;10.2事件2）查看事件结果。SELECT*FROMt_user;10.2事件【例10-16】创建一个事件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.创建在指定区间周期性发生的事件（1）常用的时间间隔1）ONSCHEDULEEVERY5SECOND2）ONSCHEDULEEVERY1MINUTE10.2事件3）ONSCHEDULEEVERY1DAYSTARTSDATE_ADD(DATE_ADD(CURDATE(),INTERVAL1DAY),INTERVAL1HOUR)4）ONSCHEDULEEVERY1MONTHSTARTSDATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTE