MySQL数据库技术与应用（微课版）教案 4 数据库编程

第3章面向对象（上）备课纸（章）课题：项目模块4数据库编程篇课时教学目的要求：熟悉自定义函数掌握存储过程及变量的使用熟悉流程控制及游标的用法掌握特定事件的定时处理教学重点：存储过程的创建与执行系统变量游标的作用及操作流程教学难点：存储过程的创建与执行存储过程的错误处理触发器事件思政素养要点：积极迎接挑战不畏难的心理素质，自主学习意识；培养精益求精的大国工匠精神；科技报国的家国情怀和使命担当。项目数据库教学项目：高校教学质量分析管理系统数据库db_teaching实践项目：怡贝银行业务管理系统数据库db_ebank教学方法：讲解、演示、微课、课中课后实训实践教具：理实一体化机房、MySQL8.0、Workbench课时安排节次内容总课时其中讲授实训课堂作业1任务1创建使用存储过程2任务2创建使用自定义函数3任务3创建使用触发器4任务4使用游标5任务5创建使用事件67备课纸（节）课题：任务1创建使用存储过程课时教学目的要求：掌握存储过程及变量的使用熟悉流程控制教学重点：存储过程的创建与执行系统变量条件控制语句和循环控制语句教学难点：存储过程的创建与执行存储过程的错误处理教具：理实一体化机房、MySQL8.0、MySQLWorkbench课后小结教学过程：引【学生线上微课学习，引出讨论题】学生如何随时查询自己的课程评学成绩？用命令行和图形化工具怎么操作解决数据的查询需求？导【教师导入任务情境】通过“高校教学质量分析管理系统”，学生需要查询自己的课程评学成绩分数，所需查询的数据源来自班级信息表、学生信息表、课程信息表和评学评教成绩表。每位学生每次查询成绩时，系统都要创建SQL语句来完成,网络流量大、性能低。为了提高效率，可将查询语句放置在指定的存储过程中，每次查询评学成绩时只需调用存储过程即可。析【教师带动学生】分析任务解决实施的技术要点，精讲任务解决的要点MySQL的变量类型MySQL的流程控制语句定义带输入参数、输出参数和输入输出参数的存储过程调用执行存储过程存储过程的错误处理机制查看、修改和删除存储过程一、MySQL的变量1.系统变量系统变量有两个范围：全局变量（GLOBAL）和会话变量（SESSION）。全局变量影响服务器的整体运行，全局变量由系统定义，当MySQL服务器启动时，由服务器自动初始化为默认值，调用全局变量时，需在其名称前加上“@@”前缀标识符，但在调用系统日期、系统时间、用户名等特定的全局变量时需要省略“@@”符号。会话变量影响单个客户端连接的操作，会话变量是在每次建立一个新连接时，由MySQL服务器将当前所有全局变量值复制一份给会话变量完成初始化的，会话变量也只在当前连接中有效，在当前连接断开后其所设置的所有会话变量都会失效。2.查看系统变量查什么SHOW[GLOBAL|SESSION]VARIABLES查啥样[LIKE'匹配模式’];3.修改系统变量的值SETGLOBAL|SESSION变量名=变量值;或者SET@@GLOBAL.|@@SESSION.变量名=变量值;4.用户变量用户变量即用户定义的变量，用户变量的名称要由“@”字符作为前缀标识符。SET@用户变量名=表达式;或者SET@用户变量名:=表达式;SELECT@变量1:=字段1[,@变量2:=字段2...]FROM数据表[WHERE条件];或者SELECT字段1[,字段2...]FROM数据表[WHERE条件]INTO@变量1[,@变量2,...]5.定义局部变量局部变量一般用在SQL语句块中，比如存储过程等程序的BEGIN...END之中。局部变量的作用范围为该语句块。在该语句块执行完毕后，局部变量就消失了。DECLARE变量名[,...]数据类型(长度)[DEFAULT默认值];6.为局部变量赋值在MySQL中，不仅可以在定义局部变量时为其指定默认值，也可以使用SET或SELECT关键字为其赋值。SET局部变量名1=表达式1[,...];SELECT字段名列表INTO局部变量名列表FROM数据表[WHERE条件];【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例1：查询所有系统变量SHOWGLOBALVARIABLES;例2：查询所有会话变量的变量名和变量值SHOWSESSIONVARIABLES;例3：查询含“size”字符的系统变量。SHOWGLOBALVARIABLESLIKE'%size%';例4：查看当前MySQL服务器版本信息（即查看@@version全局变量值）。SELECT@@version;例5：将系统全局变量最大连接数设置为200。##查看系统变量max_connection最大连接数SHOWGLOBALVARIABLESLIKE'max_connections';##将系统全局变量最大连接数设置为200SETGLOBALmax_connections=200;##重新打开客户端连接MySQL服务器，再次查看系统全局变量最大连接数SHOWGLOBALVARIABLESLIKE'max_connections';例6：将会话全局变量auto_increment_increment的值设置为2。##命令查看会话变量auto_increment_increment的值SHOWSESSIONVARIABLESLIKE'auto%';##将会话变量auto_increment_increment的值设置为2SETSESSIONauto_increment_increment=2;##查询会话变量auto_increment_increment的值，该值为2SHOWSESSIONVARIABLESLIKE'auto%';##但打开另外一个客户端连接MySQL服务器，查看会话变量auto_increment_increment的值，其值仍然为1。SHOWSESSIONVARIABLESLIKE'auto%';例7：定义用户变量@age，并设置其值为20。SET@age=20;SELECT@age;例8：从学生表tb_student中获取学号为201802015905的学生的姓名和出生日期，将这些信息保存在变量@name和@birthday中。SELECT@name:=Stu_Name,@birthday:=BirthdayFROMtb_studentWHEREStu_No='201802015905';或者SELECTStu_Name,BirthdayFROMtb_studentWHEREStu_No='201802015905'INTO@name,@birthday;例9：定义一个整型局部变量age，默认值为15，之后将局部变量age的值改设为20。（注意：该语句需放置在程序语句块中才能执行）。##将SQL语句默认结束符修改为//DELIMITER//##创建存储过程proc1，在其中定义和赋值局部变量ageCREATEPROCEDUREproc1()BEGINDECLAREageINTDEFAULT15;SETage=20;SELECTage;END//##将SQL语句默认结束符修改为;DELIMITER;##调用执行存储过程proc1，查询显示局部变量age的值CALLproc1;【学生课堂实践练习】二、流程控制在MySQL数据库编程中，可以使用流程控制语句控制程序的执行流程。常见的有条件分支流程控制语句：IF语句、CASE语句；循环流程控制语句：WHILE语句、LOOP语句、和REPEAT语句等。1.IF...ELSE语句IF...ELSE语句是最常见的条件分支语句，只能使用在存储过程、自定义函数、触发器等程序中，实现非此即彼的逻辑。IF根据条件表达式的值来决定执行哪一组SQL语句。IF条件表达式1THEN语句块1;[ELSEIF条件表达式2THEN语句块2;]...[ELSE语句块n;]ENDIF;2.简单CASE语句当条件判断的范围较多、分支较多时，使用CASE会使程序的结构更为简洁，适用于需要根据同一个表达式的不同取值来决定执行那一个分支的场合。CASE语句有简单结构和搜索结构两种语法。简单CASE结构是将一个条件表达式与一组表达式值进行相等比较，以确定执行相应分支下的结果。CASE条件表达式WHEN表达式1THEN语句1;WHEN表达式2THEN语句2;...[ELSE语句n;]ENDCASE;3.搜索CASE结构简单CASE结构是将CASE条件表达式依次与多个值进行比较，相等则执行相应的语句，为了执行更复杂的条件判断，可使用搜索结构的CASE语句，即搜索不同的条件表达式的值是否为真，以确定相应的分支操作。CASEWHEN条件表达式1THEN语句1;WHEN条件表达式2THEN语句2;...[ELSE语句n;]ENDCASE;4.WHILE语句WHILE语句用于创建一个带条件判断的循环语句，当条件满足时，执行循环体中的语句块，否则终止循环。[开始标签:]WHILE条件表达式DO语句块ENDWHILE[结束标签];5.LOOP语句LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但LOOP语句本身没有停止循环的语句，必须和LEAVE语句结合使用来停止循环。[开始标签:]LOOP语句块ENDLOOP[结束标签];6.REPEAT语句REPEAT语句也是有条件控制的循环语句，是在每次执行完循环体中的语句块后，再判断循环执行的条件，当满足条件时退出循环。[开始标签:]REPEAT语句块UNTIL条件表达式ENDREPEAT[结束标签];【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例10：判断指定学号为202002015901、课程号为900001的学生的评学成绩是否在90分以上（包括90分），是则输出“优秀”。##创建存储过程proc2，IF分支结构判断指定学生评学等级DELIMITER//CREATEPROCEDUREproc2()BEGINDECLAREnumCHAR(12);DECLAREcouCHAR(6);DECLAREscoDECIMAL(4,1);SELECTStu_No,Course_No,ScoreINTOnum,cou,scoFROMtb_gradeWHEREStu_No='202002015901'ANDCourse_No='900001';IFsco>=90THEN SELECTnum,cou,sco,'优秀';ENDIF;END//DELIMITER;##执行存储过程proc2，查询评学等级CALLproc2;例11：判断指定学号202003014003学生的评学等级，若评学分90以上，输出“优秀”，若评学分低于90高于60，输出“及格”，若评学分低于60，输出“不及格”。##创建存储过程proc3，IF分支结构判断指定学生评学等级DELIMITER//CREATEPROCEDUREproc3()BEGINDECLAREnumCHAR(12);DECLAREcouCHAR(6);DECLAREscoDECIMAL(4,1);SELECTStu_No,Course_No,ScoreINTOnum,cou,scoFROMtb_gradeWHEREStu_No='201902016203'ANDCourse_No='900004';IFsco>=90THEN SELECTnum,cou,sco,'优秀';ELSEIFsco>=60THENSELECTnum,cou,sco,'及格';ELSE SELECTnum,cou,sco,'不及格';ENDIF;END//DELIMITER;##执行存储过程proc3，查询评学等级CALLproc3;例12：判断指定学号201902016203学生的性别，若为男，输出“男学生”，若为女，输出“女学生”，否则输出“性别不确定”。##创建存储过程proc4，简单CASE分支结构判断指定学生的性别DELIMITER//CREATEPROCEDUREproc4()BEGINDECLAREnumCHAR(12);DECLAREsexCHAR(2);SELECTStu_No,GenderINTOnum,sexFROMtb_studentWHEREStu_No='201902016203';CASEsexWHEN'男'THENSELECTnum,'男学生';WHEN'女'THENSELECTnum,'女学生';ELSESELECTnum,'性别不确定';ENDCASE;END//DELIMITER;##执行存储过程proc4，查询学生性别CALLproc4;例13：使用CASE语句判断指定学号为201902016203、课程号为900004的学生的评学等级，若评学分90以上，输出“优秀”，若评学分低于90高于75，输出“中等”，若评学分低于75高于60，输出“及格”，若评学分低于60，输出“不及格”。##创建存储过程proc5，搜索CASE分支结构判断指定学生评学等级DELIMITER//CREATEPROCEDUREproc5()BEGINDECLAREnumCHAR(12);DECLAREcouCHAR(6);DECLAREscoDECIMAL(4,1);SELECTStu_No,Course_No,ScoreINTOnum,cou,scoFROMtb_gradeWHEREStu_No='201902016203'ANDCourse_No='900004';CASEWHENsco>=90THENSELECTnum,cou,sco,'优秀';WHENsco>=75THENSELECTnum,cou,sco,'中等';WHENsco>=60THENSELECTnum,cou,sco,'及格';ELSESELECTnum,cou,sco,'不及格';ENDCASE;END//DELIMITER;##执行存储过程proc5，查询学生成绩CALLproc5;例14：学校启动讲师职称教师工资调整计划，如果讲师职称教师平均工资低于2500元，开始启动工资加倍，直到讲师职称教师平均工资高于2500元为止。##查询讲师职称教师工资SELECTTeacher_No,Positional_Title,WagesFROMTB_TeacherWHEREPositional_Title='讲师';##创建存储过程proc6，调整讲师职称教师工资直至其平均工资超过2500元。CREATEPROCEDUREproc6()BEGINWHILE(SELECTAVG(wages)FROMTB_TeacherWHEREPositional_Title='讲师')<2500DOUPDATETB_TeacherSETwages=wages*2WHEREPositional_Title='讲师';ENDWHILE;END//DELIMITER;##调用存储过程proc6，调整讲师职称教师工资CALLproc6;##再次查询讲师职称教师工资SELECTTeacher_No,Positional_Title,WagesFROMTB_TeacherWHEREPositional_Title='讲师';例15：学校启动副教授职称教师工资调整计划，如果副教授职称教师平均工资低于3500元，开始启动工资加倍，如果其中的最高工资还低于5000元，则继续工加倍，直至副教授职称教师中最高工资有超过5000元的。##创建存储过程proc7，调整副教授职称教师工资。DELIMITER//CREATEPROCEDUREproc7()BEGINad:WHILE(SELECTAVG(wages)FROMTB_TeacherWHEREPositional_Title='副教授')<3500DOUPDATETB_TeacherSETwages=wages*2WHEREPositional_Title='副教授';IF(SELECTMAX(wages)FROMTB_TeacherWHEREPositional_Title='副教授')<5000THENITERATEad;ELSELEAVEad;ENDIF;ENDWHILE;END//##调用存储过程proc7，调整副教授职称教师工资CALLproc7;##查询副教授职称教师工资SELECTTeacher_No,Positional_Title,WagesFROMTB_TeacherWHEREPositional_Title='副教授';例16：学校启动助教职称教师工资调整计划，如果助教职称教师平均工资低于1500元，开始启动工资加倍，直到助教职称教师平均工资高于1500元为止。##创建存储过程proc8，调整助教职称教师工资直至其平均工资超过1500元。CREATEPROCEDUREproc8()BEGINREPEATUPDATETB_TeacherSETwages=wages*2WHEREPositional_Title='助教';UNTIL(SELECTAVG(wages)FROMTB_TeacherWHEREPositional_Title='助教')>=1500ENDREPEAT;END//DELIMITER;##调用存储过程proc8，调整助教职称教师工资CALLproc8;##查询助教职称教师工资SELECTTeacher_No,Positional_Title,WagesFROMTB_TeacherWHEREPositional_Title='助教';【学生课堂实践练习】三、创建与执行存储过程1.创建存储过程创建存储过程前，需先使用DELIMITER语句修改系统默认的语句结束符，创建完存储过程后，还需再使用DELIMITER语句改回成默认结束符“;”。完成特定功能的SQL语句集合放置在存储过程体中，存储过程体以关键字BEGIN开始，以关键字END结束。DELIMITER新结束符CREATEPROCEDURE存储过程名([IN输入参数|OUT输出参数|INOUT输入输出参数])BEGIN过程体语句块END新结束符DELIMITER;2.执行存储过程MySQL中使用CALL语句调用存储过程，调用存储过程时，MySQL数据库系统将执行存储过程中的SQL语句，并将执行结果返回给输出。CALL存储过程名([过程参数[,…]]);【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例17：软件学院有多名教师讲授“Python程序设计”这门课程，他们想知道哪些学生这门课程的评学成绩分数在90分以上，如果每次查询数据库都需要重新编写MySQL语句，效率非常低，因此，可以将查询语句放置在存储过程中，每次查询只需调用存储过程即可。##创建存储过程proc9，查询课程“Python程序设计”评学成绩在90分以上的学生信息。DELIMITER//CREATEPROCEDUREproc9()BEGINSELECTcla.Class_Name,stu.Stu_Name,g.Course,g.ScoreFROMtb_studentstuJOINtb_classclaONstu.Class_No=cla.Class_NoJOIN(SELECTgra.Stu_No,cou.Course,gra.ScoreFROMtb_gradegraJOINtb_coursecouONgra.Course_No=cou.Course_No)gONstu.Stu_No=g.Stu_NoWHEREg.Course='Python程序设计'ANDg.Score>=90;END//DELIMITER;##执行存储过程proc9，查询课程“Python程序设计”评学成绩90分以上的学生信息。CALLproc9;【学生课堂实践练习】四、参数化存储过程存储过程的参数有三种类型：输入参数、输出参数和输入输出参数。IN关键字表示输入参数，用于传递值给存储过程；OUT关键字表示输出参数，由存储过程返回给调用者；INOUT关键字表示输入输出参数，既可以作为输入参数又可以作为输出参数。参数的声明由参数方向、参数名、参数类型3部分构成，注意参数名称不能与数据表中的列名相同。1.输入参数【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例18：定义带2个输入参数的存储过程proc10，其中一个参数为教师编号num，另一个参数为学位info，在存储过程，对指定编号的教师、将其学位修改为指定学位。并将输入参数info的值改成“博士”，查询修改前和修改后以及执行完存储过程后输入参数info的值。##定义用户变量教师编号@thNo、学位@deg。SET@thNo='000001',@deg='学士';##创建带参数的存储过程proc10，通过输入参数修改指定教师的学位信息。DELIMITER//CREATEPROCEDUREproc10(INnumCHAR(6),INinfoCHAR(2))BEGIN##查询输入参数info的值SELECTinfoAS'查询输入参数';##按输入参数的值修改指定编号教师的学位UPDATEtb_teacherSETDegree=infoWHERETeacher_No=num;##查询修改后的教师信息SELECTTeacher_No,DegreeFROMtb_teacherWHERETeacher_No=num;##将输入参数info的值修改为博士SETinfo='博士';##查询修改后的输入参数info的值SELECTinfoAS'再次查询修改后的输入参数';END//##执行存储过程proc10，传递输入参数。CALLproc10(@thNo,@deg);##再次查询用户变量@deg的值SELECT@degAS学位;例19：定义带2个参数的存储过程proc11，一个为输入参数教师编号num，另一个为输入输出参数职称title，在存储过程中将指定编号教师的职称修改为指定职称，并将输入输出参数职称title的值改成“副教授”，查询修改前、修改后以及执行完存储过程后输入输出参数的值。##定义用户变量教师编号@thNo、职称@ttlSET@teachNo='000001',@ttl='助教';##创建带参数的存储过程proc11，通过输入参数和输入输出参数修改指定教师的职称。DELIMITER//CREATEPROCEDUREproc11(INnumCHAR(6),INOUTtitleVARCHAR(4))BEGIN##查询输入输出参数title的值SELECTtitleAS'查询输入输出参数';##按参数的值修改指定编号教师的职称UPDATEtb_teacherSETPositional_Title=titleWHERETeacher_No=num;##查询修改后的教师信息SELECTTeacher_No,Positional_TitleFROMtb_teacherWHERETeacher_No=num;##将输入输出参数职称title的值修改为副教授SETtitle='副教授';##查询修改后的输入输出参数职称title的值SELECTtitleAS'再次查询修改后的输入输出参数';END//DELIMITER;##执行存储过程proc11，传递参数，修改指定教师的职称信息。CALLproc11(@teachNo,@ttl);##再次查询用户变量@ttl的值SELECT@ttlAS'职称';【学生课堂实践练习】五、错误处理机制1.自定义错误名称由于MySQL的错误代码或状态代码可读性差，为了便于用户操作，可以定义与错误代码或状态代码一一对应的错误名称。DECLARE错误名称CONDITIONFOR错误类型;2.错误处理程序当执行MySQL语句，出现自定义错误名称的错误信息时，可使用MySQL的DECLARE...HANDLER语句处理错误信息。DECLARE错误处理方式HANDLERFOR错误类型[,...]程序语句段;【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例18：为ERROR1062(23000)服务器错误代码定义一个错误名称duplicate_primary_key。DELIMITER//CREATEPROCEDUREproc_err_23()BEGINDECLAREduplicate_primary_keyCONDITIONFORSQLSTATE'23000';END//DELIMITER;上述存储过程将状态码‘23000’命名为duplicate_primary_key，也可以使用错误名称来定义。DECLAREduplicate_primary_keyCONDITIONFOR1062;例20：为错误代码ERROR1062(23000)定义错误处理程序,如果出现23000错误，则显示tb_department学院表中的最大学院编号，否则显示插入成功。DELIMITER//CREATEPROCEDUREproc_handle_err23(INNumCHAR(4),INDeptVARCHAR(20),INDireCHAR(4),INSecrCHAR(4))BEGINDECLAREEXITHANDLERFORSQLSTATE'23000'SELECTMAX(Dep_No)AS'最大部门编号'FROMTB_Department;INSERTTB_Department(Dep_No,Department,Director,Secretary)VALUES(Num,Dept,Dire,Secr);SELECT'插入成功';END//DELIMITER;##调用存储过程proc_handle_err23向表TB_Department中插入学院记录，该学院编号已经存在，提示当前的最大部门编号值。CALLproc_handle_err23('0004','马克思理论研究学院','李院长','陈书记');##调用存储过程proc_handle_err23向表TB_Department中插入学院记录，并将学院编号修改成当前最大编号值+1，提示插入成功。CALLproc_handle_err23('0006','马克思理论研究学院','李院长','陈书记');【学生课堂实践练习】六、管理存储过程1.查看存储过程的状态创建好存储过程后，使用SHOWSTATUS语句查看存储过程的状态。SHOWPROCEDURESTATUSLIKE‘过程名称’;2.查看存储过程的创建信息创建好存储过程后，使用SHOWCREATE语句查看存储过程的创建信息。SHOWCREATEPROCEDURE‘过程名称’;3.修改存储过程特性实际开发过程中，用户需求时有变化，这就需要修改MySQL中的存储过程的特性，MySQL中使用ALTERPROCEDURE语句修改存储过程特性，注意存储过程的创建内容即参数和主体是不可以修改的。4.删除存储过程存储过程被创建后，会一直保存在MySQL数据库服务器中，直到被删除。可使用DROPPROCEDURE语句将存储过程从数据库服务器中删除。DROPPROCEDURE[IFEXISTS]<过程名称>【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例21：查看存储过程proc1的状态。SHOWPROCEDURESTATUSLIKE'proc1';例22：查看存储过程proc1的状态。SHOWCREATEPROCEDUREproc1\G;例23：为存储过程proc1添加选项COMMENT和READSSQLDATA。ALTERPROCEDUREproc1READSSQLDATACOMMENT'定义局部变量';##查看存储过程proc1的定义。SHOWCREATEPROCEDUREproc1\G;例24：为存储过程proc1添加选项COMMENT和READSSQLDATA。删除存储过程proc1。DROPPROCEDUREIFEXISTSproc1;##调用存储过程proc1，提示该存储过程不存在，说明存储过程已被删除。CALLproc1();【学生课堂实践练习】探【教师指导，学生小组讨论本节任务解决方案】学生如何随时查询自己的课程评学成绩？解【教师指导，学生操作实施解决任务】命令行客户端和MySQLWorkbench图形化客户端，都可创建存储过程，执行存储过程完成任务要求的查询：##定义存储过程DELIMITER//CREATEPROCEDUREproc_exec(INstuNoCHAR(12))BEGIN SELECTcla.Class_Name,stu.Stu_Name,g.Course,g.Scorefromtb_studentstu INNERJOINtb_classclaONstu.Class_No=cla.Class_No INNERJOIN(SELECTgra.Stu_No,cou.Course,gra.ScoreFROMtb_gradegra INNERJOINtb_coursecouONgra.Course_No=cou.Course_No)gONstu.Stu_No=g.Stu_No WHEREstu.Stu_No=stuNo;END//DELIMITER;##执行存储过程CALLproc_exec('201803014002');践【学生综合实践，运用本节学习技术完成工作任务单】1.“高校教学质量分析管理系统”数据库db_teaching“创建使用存储过程”实践工作任务单2.“怡贝银行业务管理系统”数据库db_ebank“创建使用存储过程”实践工作任务单备课纸（节）课题：任务2创建使用自定义函数课时教学目的要求：理解函数与存储过程的区别掌握自定义函数的实现教学重点：掌握自定义函数的创建和使用掌握修改、删除自定义函数教学难点：自定义函数的定义教具：理实一体化机房、MySQL8.0、MySQLWorkbench课后小结教学过程：引【学生线上微课学习，引出讨论题】：教务处教师如何随时查询某位教师某门课程的学生平均成绩了解学生的学习情况？导【教师导入任务情境】：教务处教师通过“高校教学质量分析管理系统”，随时查询某位教师某门课程的学生平均成绩了解学生的学习情况，而查询过程需要访问教师信息表、课程信息表和评学评教成绩表，如果每次查询每位教师每门课程的平均成绩时，系统都要创建SQL语句来完成,网络流量大、性能低。G-EDU格诺博公司系统研发小组为了提高开发效率，要将该功能下的查询语句放置在自定义的函数中，这样每次的平均成绩查询只需调用函数即可。析【教师带动学生】分析任务解决实施的技术要点，精讲：任务解决的要点创建并使用自定义函数查看自定义函数的状态和创建信息修改自定义函数删除自定义函数一、创建与调用用户自定义函数自定义函数是一种与存储过程十分类似的数据库对象，它们都由多条SQL语句和过程式语句组成，并且可以被应用程序和其他SQL语句调用，自定义函数与存储过程有以下几点区别：自定义函数没有输出参数，而存储过程可以有输出参数。自定义函数中必须包含一条RETURN语句，而存储过程中不能包含RETURN语句。可以直接调用自定义函数，而调用存储过程需要使用CALL语句。1.创建用户自定义函数自定义函数主要用于计算并返回一个值。在MySQL中，使用CREATEFUNCTION语句创建自定义函数。DELIMITER新结束符CREATEFUNCTION函数名([函数参数[,...]])RETURNS返回值类型[特性]BEGIN函数体语句块END新结束符DELIMITER;2.调用用户自定义函数用户自定义函数的调用方法与MySQL系统函数的使用方法是一样的。调用用户自定义函数时，参数要与定义函数时的参数对应，主要使用SELECT关键字。SELECT函数名([参数列表]);【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例1：创建自定义函数func_query_tch()，用以查询教师的评教总分。##创建自定义函数func_query_tch()查询教师的评教总分DELIMITER//CREATEFUNCTIONfunc_query_tch(tchNoCHAR(6))RETURNSDECIMAL(5,1)READSSQLDATABEGINRETURN(SELECTSUM(Evalu_Score)FROMtb_teach_evaluationevalJOINtb_teacherteachONteach.Teacher_No=eval.Teacher_NoWHEREteach.Teacher_No=tchNo);END//QueryOK,0rowsaffected(0.02sec)DELIMITER;##调用函数func_query_tch()，查询编号为'000003'的教师的评教总分。SELECTfunc_query_tch('000003')AS'000003教师的评教总分';【学生课堂实践练习】二、管理用户自定义函数创建好用户自定义函数后，用户可以查看、修改和删除用户自定义函数。1.查看自定义函数的状态创建好自定义函数后，使用SHOWSTATUS语句查看自定义函数的状态。SHOWFUNCTIONSTATUSLIKE‘函数名’;2.查看自定义函数的定义创建好自定义函数后，使用SHOWCREATE语句查看用户自定义函数的创建信息。SHOWCREATEFUNCTION函数名;3.修改自定义函数实际开发过程中，用户对自定义函数需求时有变化，这就需要修改自定义函数的特性。与存储过程一样，MySQL可以使用ALTERFUNCTION语句修改自定义函数特性，但函数的创建内容即参数或语句块不能改。自定义函数的特性值与表4-1的存储过程特性值一样。ALTERFUNCTION函数名[特性...]4.删除自定义函数自定义函数被创建后，会一直保存在MySQL数据库服务器中，直到被删除。使用DROPFUNCTION语句将用户自定义函数从数据库中删除。DROPFUNCTION[IFEXISTS]函数名;【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例2：查看自定义函数func_query_tch()的状态。SHOWFUNCTIONSTATUSLIKE'func_query_tch'\G;例3：查看自定义函数func_query_tch的创建信息。SHOWCREATEFUNCTIONfunc_query_tch\G;例4：为函数func_query_tch()添加选项COMMENT。ALTERFUNCTIONfunc_query_tchCOMMENT'查询某个教师的评教总分';##查看函数func_query_tch的创建信息。SHOWCREATEFUNCTIONfunc_query_tch/G;例5：删除自定义函数func_query_tch()。DROPFUNCTIONIFEXISTSfunc_query_tch;##查询函数func_query_tch的创建信息，提示该函数不存在，说明函数已被删除。SHOWCREATEFUNCTIONfunc_query_tch;【学生课堂实践练习】探【学生小组讨论本节任务解决方案】：教务处教师如何随时查询某位教师某门课程的学生平均成绩，从而了解学生的学习情况，教师、教务处、质量管理处可依此分析该门课的学习情况，有助于教师、学生制定提升进步的学习策略计划。解【学生操作实施解决任务】命令行客户端和MySQLWorkbench图形化客户端，都可创建function函数，执行function函数完成任务要求的查询：DELIMITER//CREATEFUNCTIONfunc_course_avg(tchNoCHAR(6),couNoCHAR(6))RETURNSVARCHAR(66)READSSQLDATABEGINDECLAREtchNameCHAR(4);DECLAREcouNameVARCHAR(50);DECLAREscoDECIMAL(5,1); SELECTtch.Teacher_Name,cou.Course,AVG(Score) INTOtchName,couName,sco FROMtb_gradegrd JOINtb_teachertchONtch.Teacher_No=grd.Teacher_NoJOINtb_coursecouONgrd.Course_No=cou.Course_No WHEREtch.Teacher_No=tchNoANDcou.Course_No=couNo;RETURNCONCAT_WS('|',tchName,couName,sco);END//DELIMITER;SELECTfunc_course_avg('000002','900004')AS'平均成绩';践【学生综合实践，运用本节学习技术完成工作任务单】1.“高校教学质量分析管理系统”数据库db_teaching“创建使用自定义函数”实践工作任务单2.“怡贝银行业务管理系统”数据库db_ebank“创建使用自定义函数”实践工作任务单备课纸（节）课题：任务3创建使用触发器课时教学目的要求：理解触发器的作用和特点掌握触发器的使用教学重点：掌握触发器的创建、查看和删除掌握触发器的注意事项教学难点：理解触发器old表和new表的作用和运用教具：理实一体化机房、MySQL8.0、MySQLWorkbench课后小结教学过程：引【学生线上微课学习，引出讨论题】：如何确保对学校的各类教学数据信息进行变更增删时，相应表中的对应数据在变更增删后保持一致？导【教师导入任务情境】：质量督导部门、教务处通过“高校教学质量分析管理系统”对学校的各类教学数据信息进行变更增删时，应保障相应表中的对应数据在变更增删后要一致。比如如果有学生退学、从学生信息表中删除该生记录的话，那么学生所在班级的班级人数也应相应减少一人。可以在学生信息表中删除学生时，使用触发器激活相应事件，自动修改对应的班级信息表中的班级人数，以保证数据一致性和完整性。析【教师带动学生】分析任务解决实施的技术要点，精讲：任务解决的要点触发器的作用和特点创建触发器查看触发器删除触发器使用触发器的注意事项一、触发器概述1.触发器的特点触发器是一种特殊的存储过程，也是一段程序语句块代码，不同的是，存储过程需要使用CALL语句来调用执行，而触发器不需要调用，只要一个预定义的INSERT、UPDATE或DELETE事件发生就会触发而被MySQL自动调用。创建触发器时需要与数据表相关联，触发器可以设置为在插入数据、更新数据、删除数据等特定事件的之前或之后激活，就会自动执行触发器中的SQL代码，实现这些事件发生时强制检验数据、调整数据等复杂控制操作，保证数据的一致和安全。触发器使用时的优点包括：自动执行；可以完成比外键约束、CHECK约束更复杂的检查和操作；可以实现数据表的级联更改，在一定程度上确保了数据的完整性。触发器使用时的缺点包括：多个触发器出现业务逻辑问题时很难定位，维护困难；大量使用触发器容易打乱代码结构，增加程序复杂性；改动数据量较大时，触发器效率低。2.触发器的逻辑表NEW和OLD定义在触发器中的SQL代码语句可以关联数据表中的任意列，但不能直接使用表中的列名，那样会使系统混淆。因此MySQL提供了两个逻辑表“NEW”表和“OLD”表，这两个逻辑表的表结构与触发器所在的数据表的结构完全一致。NEW表用来存放更新后的记录。对于INSERT语句，NEW表中存放的是要插入的新记录；对于UPDATE语句，NEW表中存放的是要更新的记录。OLD表用来存放更新前的记录。对于DELETE语句，OLD表中存放的是要删除的记录；对于UPDATE语句，OLD表中存放的是更新前的记录。访问NEW表或OLD表的字段，需在字段名前加上“NEW.”或“OLD.”标识。当触发器执行完成之后，NEW表和OLD表也会被自动删除。二、创建触发器实现数据完整性控制1.可以使用CREATETRIGGER语句创建触发器。DELIMITER新结束符CREATETRIGGER[IFNOTEXISTS]触发器名触发时机触发事件ON数据表FOREACHROW[触发顺序]BEGIN触发器主体语句块END新结束符DELIMITER;【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例1：创建一个更新触发器，当更新班级开课表中的教师编号时，自动更新评学评教成绩表中该班学生本门课程的教师编号信息。##查询评学评教成绩表中班级号为'2019030101'、课程号为'900001'的任课教师及评教分SELECTstu.Class_No,grd.Stu_No,Course_No,Teacher_No,Teach_evalu_scoreFROMTB_GradegrdJOINTB_StudentstuONgrd.Stu_No=stu.Stu_NoWHEREstu.Class_No='2019030101'ANDCourse_NO='900001';##查询结果可见'2019030101'号班级的'900001'号课程任课教师“000004”。##创建触发器，当更新开课信息表中的教师编号时，自动更新评教评学成绩表中的教师编号DELIMITER//CREATETRIGGERtrigger_update_teachAFTERUPDATEONTB_Class_CourseFOREACHROWBEGINUPDATETB_GradegrdJOINTB_StudentstuONgrd.Stu_No=stu.Stu_NoSETTeacher_No=NEW.Teacher_NoWHEREstu.Class_No=OLD.Class_NoANDTeacher_No=OLD.Teacher_NoANDCourse_No=OLD.Course_No;END//DELIMITER;##将开课信息表中班级号为'2019030101'、课程号为'900001'的任课教师编号改为'000006'UPDATETB_Class_CourseSETTeacher_No='000006'WHEREClass_No='2019030101'ANDTeacher_No='000004'ANDCourse_No='900001';##再次在评学评教成绩表中查询'2019030101'号班级的'900001'号课程的教师评教分，任课教师的编号变为了'000006'SELECTstu.Class_No,grd.Stu_No,Course_No,Teacher_No,Teach_evalu_score,ScoreFROMTB_GradegrdJOINTB_StudentstuONgrd.Stu_No=stu.Stu_NoWHEREstu.Class_No='2019030101'ANDCourse_NO='900001';例2：创建一个插入触发器，在教师教学评价表中新增记录时，记录中的评价评分范围应为0-100，若超出范围，则不允许新增该错误记录。##创建插入触发器DELIMITER//CREATETRIGGERtrigger_insert_evalBEFOREINSERTONtb_teach_evaluationFOREACHROWBEGINIFNEW.Evalu_Score>100ORNEW.Evalu_Score<0THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='ScoreOutofrange';ENDIF;END//##在tb_teach_evaluation表中插入一条评价评分为120分的记录，插入失败。INSERTINTOtb_teach_evaluation(Teacher_No,Appraiser_No,Appraiser,Evalu_Score,Evalu_Comment,Evalu_Term)VALUES('000010','000004','同行教师',120.0,'授课生动，逻辑性强','2019-2020学年二');##在tb_teach_evaluation表中插入一条评价评分为90分的记录，插入成功。INSERTINTOtb_teach_evaluation(Teacher_No,Appraiser_No,Appraiser,Evalu_Score,Evalu_Comment,Evalu_Term)VALUES('000010','000004','同行教师',90.0,'授课生动','2019-2020学年二');【学生课堂实践练习】三、管理触发器1.查看当前数据库或指定表中全部触发器可以使用SHOWTRIGGER语句查看当前数据库或指定某表中的全部触发器的信息。SHOWTRIGGERS[FROM数据库名LIKE‘表名’][\G];2.查看触发器的创建语句可使用SHOWCREATETRIGGER语句查看触发器创建语句。SHOWCREATETRIGGER触发器名[\G];3.删除触发器可以使用DROPTRIGGER语句删除数据库中已经定义好的触发器。DROPTRIGGER[IFEXIST][数据库名]触发器名;【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例3：查看当前数据库db_teaching中的所有触发器。SHOWTRIGGERS\G;例4：查看触发器trigger_insert_eval的创建语句。SHOWCREATETRIGGERtrigger_insert_eval\G;例5：删除触发器trigger_insert_eval。DROPTRIGGERIFEXISTStrigger_insert_eval;【学生课堂实践练习】四、使用触发器注意事项1.同一个表中不能创建两个相同触发时机、触发事件的触发器。2.如果触发器中包含SELECT语句，该SELECT语句不能返回结果集。3.触发器是针对记录进行操作，因此，当批量更新记录数据时，引入触发器会导致批量更新操作的性能降低。4.触发器程序中不能使用以显式或隐式方式打开、开始或结束事务的语句。5.MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执。如果对事务表进行的操作，当出现错误则将会被回滚，如果是对非事务表进行操作，就不能回滚，数据可能会出错。6.触发器是基于行触发的，新增、修改或删除行数据操作可能都会激活触发器，因此不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。7.一个MySQL触发器可能会关联到另外一张表或几张表的操作。因此，会导致数据库服务器负荷也会相应的增加一倍或几倍，如果出现因为触发器问题导致的性能问题，会很难定位问题位置和原因。8.在基于锁的操作中，触发器可能会导致锁等待或死锁。触发器执行失败，原来执行的SQL语句也会执行失败。而因为触发器导致的失败，往往很难排查。探【学生小组讨论本节任务解决方案】：质量督导部门、教务处对学校的各类教学数据信息进行变更增删操作时，如何确保相应表中的对应数据在变更增删之后保持一致？解【学生操作实施解决任务】命令行客户端和MySQLWorkbench图形化客户端，都可创建删除触发器，执行删除学生操作后，班级人数自动减一：##创建删除触发器DELIMITER//CREATETRIGGERtrigger_delete_stuAFTERDELETEONTB_StudentFOREACHROWBEGIN UPDATETB_ClassSETPer_Quantity=Per_Quantity-1WHEREClass_No=OLD.Class_No;END//DELIMITER;##查询班级编号为2018020101的班级信息，班级人数为3人。SELECTClass_NameAS'班级名称',Stu_NoAS'学号',Stu_NameAS'学生姓名',Per_QuantityAS'班级人数'FROMTB_Class INNERJOINTB_studentONTB_Class.Class_No=TB_student.Class_NoWHERETB_Class.Class_No='2018020101';##删除学生表TB_Student中学号为201802015906的学生信息。DELETEFROMTB_StudentWHEREStu_No='201802015906';##再次查询班级编号为2018020101的班级信息，班级人数减少一个，共2人。SELECTClass_NameAS'班级名称',Stu_NoAS'学号',Stu_NameAS'学生姓名',Per_QuantityAS'班级人数'FROMTB_Class INNERJOINTB_studentONTB_Class.Class_No=TB_student.Class_NoWHERETB_Class.Class_No='2018020101';践【学生综合实践，运用本节学习技术完成工作任务单】1.“高校教学质量分析管理系统”数据库db_teaching“创建使用触发器”实践工作任务单2.“怡贝银行业务管理系统”数据库db_ebank“创建使用触发器”实践工作任务单备课纸（节）课题：任务4使用游标课时教学目的要求：理解游标的作用及操作流程掌握使用游标检索数据教学重点：掌握利用游标检索数据教学难点：游标操作流程教具：理实一体化机房、MySQL8.0、MySQLWorkbench课后小结教学过程：引【学生线上微课学习，引出讨论题】：辅导员老师和教务处需要获得一个班级中各科评学分数在90分以上的学生记录，并一条一条查看审核这些学生的记录，以评定国家奖学金。导【教师导入任务情境】：辅导员老师和教务处需要通过“高校教学质量分析管理系统”获得一个班级中各科评学分数在90分以上的学生记录，形成待审核表，并一条一条查看审核这些学生的记录，以评定国家奖学金。在查询班级学生评学成绩的存储过程中，需要采用游标机制来逐条获取记录，实现高效逐行查询结果。析【教师带动学生】分析任务解决实施的技术要点，精讲：任务解决的要点游标的访问机制声明游标游标的使用的步骤实施游标逐条处理数据使用触发器的注意事项一、游标访问机制在MySQL中，游标是一种数据访问机制，允许用户访问数据集中某一行，类似于C语言中的指针。在存储过程或自定义函数中，使用游标逐条读取SELECT结果集中的记录时，游标指向结果集中的第一条记录之前，首先判断结果集中是否有记录，如果有，则读取第一条记录，并将游标的指针指向下一条记录，接下来再判断是否有下一条记录，如果有，则读取该记录，并将游标的指针再指向下一条记录，依此类推，直至读完记录集中的所有记录。二、游标的使用游标操作一般分为4个步骤：声明游标、打开游标、使用游标获取数据、关闭游标。1.声明游标游标必须先声明定义再使用，在MySQL中通过DECLARECURSOR语句声明游标，且游标必须声明在变量和条件之后、在处理程序之前。DECLARE游标名CURSORFOR定义访问结果集语句;2.打开游标声明游标后，要使用游标从中提取数据，就必须先通过OPEN语句打开游标。OPEN游标名;3.使用游标获取数据打开游标后，就可以使用FETCH语句获取结果集中游标当前指针的记录，并将记录值传给指定变量列表。FETCH语句每次只能获取结果集中的一条记录，所以通常与WHILE、REPEAT等循环结构配合使用，来遍历结果集中的所有记录。而且MySQL中游标是仅向前的、只读的，即游标只能顺序地从前往后一条条读取结果集。FETCH[[NEXT]FROM]游标名INTO变量名[,变量名]...;4.关闭游标游标使用完毕后，要使用CLOSE语句及时关闭。CLOSE游标名;【教师举例实操演示】对高校教学质量分析管理系统数据库db_teaching——例1：使用游标将大学语文（课程编号为900001）评学分数大于等于55分且小于60分的学生的改成60分。##查询大学语文评学分数在55～60分之间的学生信息。SELECTcla.Class_Name,stu.Stu_Name,g.Course,g.ScoreFROMtb_studentstuJOINtb_classclaONstu.Class_No=cla.Class_NoJOIN(SELECTgra.Stu_No,cou.Course,gra.Score,gra.Course_NoFROMtb_gradegraJOINtb_coursecouONgra.Course_No=cou.Course_No)gONstu.Stu_No=g.Stu_NoWHEREg.Course_No='900001'ANDg.ScoreBETWEEN55AND60;##使用游标将大学语文（课程编号900001）评学分数在55～60分之间的统一改成60分。DELIMITER//CREATEPROCEDUREproc_cursor()BEGINDECLAREstuNo,courseNovarchar(12)DEFAULT'';DECLAREnumdecimal(4,1)DEFAULT0;DECLAREerr_markINTDEFAULT0;##声明游标DECLAREcurCURSORFORSELECTStu_No,Course_No,ScoreFROMtb_gradeWHERECourse_No='900001';##定义错误处理程序，结束游标的遍历DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETerr_mark=1;##打开游标OPENcur;WHILEerr_mark=0DO##游标获取数据FETCHcurINTOstuNo,courseNo,num;IFnum>=55ANDnum<60THENSETnum=60;UPDATEtb_gradeSETScore=numWHEREStu_No=stuNoANDCourse_No=courseNo;ENDIF;ENDWHILE;##关闭游标CLOSEcur;END//##调用执行存储过程proc_cursor。CALLproc_cursor();##再次查询大学语文评学分数在55～60分之间的学生信息，分数已修改。SELECTcla.Class_Name,stu.Stu_Name,g.Course,g.ScoreFROMtb_studentstuJOINtb_classclaONstu.Class_No=cla.Class_NoJOIN(SELECTgra.Stu_No,cou.Course,gra.Score,gra.Course_NoFROMtb_gradegraJOINtb_coursecouONgra.Course_No=cou.Course_No)gONstu.Stu_No=g.Stu_NoWHEREg.Course_No='900001'ANDg.ScoreBETWEEN55AND60;【学生课堂实践练习】探【学生小组讨论本节任务解决方案】：辅导员老师和教务处如何获得一个班级中各科评学分数在90分以上的学生记录，并一条一条查看审核这些学生的记录，这些记录用来作为评定国家奖学金的依据。解【学生操作实施解决任务】命令行客户端和MySQLWorkbench图形化客户端，都可创建表和存储过程，在存储过程中使用游标逐条获取记录，并将记录保持在表中：##1、创建一个某班级的学生成绩表tb_course_ninety，用来存放成绩超过90分的学生成绩信息。CREATETABLEtb_course_ninety( Class_Namevarchar(20)comment'班级名称'NOTNULL, Stu_Namechar(4)comment'学生姓名'NOTNULL,Coursevarchar(50)comment'课程名称'NOTNULL, Scoredecimal(4,1)CHECK(Scorebetween90.0and100.0)comment'成绩'NULL);##2.创建一个存储过程proc_ninety_cursor，在该存储过程中查询某班级学生成绩超过90分的成绩信息，##并使用游标将查询到的记录逐条插入到表tb_stu_sco_ninety中。DELIMITER//CREATEPROCEDUREproc_ninety_cursor(INclassNamevarchar(20))BEGIN DECLAREstuName,courseNamevarchar(50)DEFAULT'';DECLAREnumdecimal(4,1)DEFAULT0;DECLAREerr_markINTDEFAULT0;#定义游标DECLAREcurCURSORFORSELECTcla.Class_Name,stu.Stu_Name,g.Course,g.ScoreFROMtb_studentstu JOINtb_classclaONstu.Class_No=cla.Class_No JOIN(SELECTgra.Stu_No,cou.Course,gra.Score,gra.Course_NoFROMtb_gradegra JOINtb_coursecouONgra.Course_No=cou.Course_No)g ONstu.Stu_No=g.Stu_No WHEREcla.Class_Name=classNameANDg.Score>=90;#定义错误处理程序，结束游标的遍历DECLARECONTINUEHANDLERFO