MySQL数据库应用技术与实战电子教案 单元7 MySQL存储过程

单元7MySQL存储过程课程名称：MYSQL数据库应用课程类别：必修适用专业：计算机技术类相关专业总学时：64学时（其中理论31学时，实践33学时）总学分：4.0学分本章学时：8学时（其中理论4学时，实践4学时）学情分析：学生已完成单元六的学习，掌握了数学、字符串、日期时间、系统信息四大类函数，能够在SQL语句中灵活使用函数处理数据。但到目前为止，学生编写的SQL语句都是“一次性”的——每条语句独立执行，无法保存复用，也无法实现“多条语句按逻辑顺序执行”的业务需求。例如教务系统中“教师信息添加”功能，需要校验、插入、记录日志等多个步骤，用单条SQL无法完成。本单元将学习存储过程——把多条SQL语句封装成可重复调用的程序单元。存储过程引入了参数、变量、条件判断等编程元素，是学生从“写SQL语句”到“写SQL程序”的重要跨越。学生对编程概念（如变量、参数、流程控制）可能感到陌生，需要放慢节奏、多演示多练习。材料清单《MYSQL数据库应用技术与实战（微课版）》教材。配套PPT。代码及SQL脚本。引导性提问。探究性问题。拓展性问题。教学目标与基本要求教学目标先掌握存储过程的概念以及创建、查看、修改、删除的方法，然后掌握存储过程中变量和参数的使用，最后了解存储函数的创建和调用方法，能够使用存储过程封装业务逻辑。基本要求掌握创建存储过程的方法掌握带参数存储过程的创建和调用方法掌握存储过程中变量和条件判断语句的使用掌握查看存储过程状态和定义的方法掌握修改和删除存储过程的方法掌握存储函数的创建和调用方法问题引导性提问引导性提问需要教师根据教材内容和学生实际水平，提出问题，启发引导学生思考解决问题，帮助学生理解掌握知识，提升专业实践能力。教务系统中，添加一条教师信息通常需要执行多条SQL语句，如果每次都重新编写这些语句，会带来哪些问题？存储过程有什么优点？什么情况下适合使用存储过程？存储过程中的IN、OUT、INOUT三种参数类型有什么区别？探究性问题探究性问题需要教师深入钻研教材的基础上精心设计，在提问的角度或者在引导性提问的基础上，从重点、难点问题切入，进行插入式提问。或者是对引导式提问中尚未涉及但在课文中又是重要的问题加以设问。存储过程和视图有什么区别？视图能保存多条SQL语句的逻辑吗？存储过程中的局部变量（DECLARE）和会话变量（@）在使用上有什么不同？为什么说存储过程可以提高执行效率？它的一次编译是什么含义？存储过程和存储函数有什么区别？在什么场景下应该使用存储函数？拓展性问题拓展性问题需要教师深刻理解教材的意义、学生的学习动态后，根据学生学习层次，提出切实可行的关乎实际的可操作问题。亦可以提供拓展资料供学生研习探讨，完成拓展性问题。存储过程支持哪些流程控制语句？除了IF判断，还有哪些循环语句可以用于存储过程？在大型项目中，使用存储过程有哪些注意事项？如何避免存储过程带来的维护困难？存储过程的权限管理是如何工作的？普通用户能否调用DBA创建的存储过程？主要知识点、重点与难点主要知识点存储过程概述（优点与缺点）存储过程的创建（CREATEPROCEDURE）参数类型（IN/OUT/INOUT）存储过程中的变量（局部变量DECLARE/会话变量@）条件判断语句（IF...THEN...ELSE）查看存储过程（SHOWPROCEDURESTATUS/SHOWCREATEPROCEDURE）修改存储过程（ALTERPROCEDURE）删除存储过程（DROPPROCEDURE）存储函数的创建与调用（CREATEFUNCTION...RETURNS）重点存储过程的概念与作用CREATEPROCEDURE创建存储过程的语法IN/OUT/INOUT参数类型的使用DECLARE局部变量和@会话变量的使用IF...THEN...ELSE条件判断语句查看、修改、删除存储过程的方法难点IN、OUT、INOUT参数类型的区别与应用存储过程中变量的作用域IF...THEN...ELSE条件判断语句的编写存储函数与存储过程的区别及RETURNS的使用教学过程设计第一次课（4课时：理论2学时+实践2学时）理论教学（2课时，80分钟）第1课时存储过程概述与创建（40分钟）1.课堂导入（5分钟）提问：“在Navicat中手动执行一条INSERT语句可以添加一条教师信息，但教务系统添加教师时还需要检查教工号是否重复、记录操作日志等，这些操作需要依次执行多条SQL语句。如果每条语句都在命令行中重复输入，会怎样？”引导学生思考重复编写SQL语句的弊端，引出本课主题——存储过程。2.存储过程概述（10分钟）讲解存储过程的概念：存储过程是一组为了完成特定功能的SQL语句集合，经过编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来调用它。（1）存储过程的优点：封装性好——把多条SQL语句封装成一个整体，调用方便；提高执行效率——存储过程在首次执行时编译，后续调用直接执行编译后的版本；提高安全性——可以通过存储过程限制用户直接操作数据表；减少网络流量——只需传递存储过程名和参数，无需传递整条SQL语句。（2）存储过程的缺点：可移植性差——不同数据库系统的存储过程语法不完全兼容；维护成本高——业务逻辑分散在数据库中，修改需要数据库管理员配合；调试困难——存储过程的调试手段有限。通过“把经常做的菜写成菜谱，以后按菜谱做菜”的比喻帮助学生理解存储过程的封装思想。3.创建存储过程——CREATEPROCEDURE（15分钟）讲解创建存储过程的语法格式：CREATEPROCEDURE存储过程名(参数列表)BEGINSQL语句;END;（1）最简单的存储过程：CREATEPROCEDUREpro_test()BEGINSELECT*FROMstuinfo;END;存储过程名后必须加括号，即使没有参数也要写空括号。（2）注意：MySQL中默认以分号作为语句结束符，而存储过程内部包含多条以分号结尾的SQL语句，直接执行会报错。需要先用DELIMITER$$把结束符临时修改为$$，创建完存储过程后再用DELIMITER;恢复。完整写法：DELIMITER$$CREATEPROCEDUREpro_test()BEGINSELECT*FROMstuinfo;END$$DELIMITER;（3）调用存储过程：CALL存储过程名(参数);示例：CALLpro_test();执行存储过程返回学生信息表的所有记录。逐步演示DELIMITER的使用和存储过程的完整创建、调用过程，强调：DELIMITER修改的是命令行客户端的语句结束符，不是SQL语法的一部分。4.调用存储过程（5分钟）强调调用存储过程的语法：CALL存储过程名(实参列表);即使没有参数，括号也不能省略。（1）示例：CALLpro_test();（2）存储过程可以像函数一样出现在其他语句中吗？不能，存储过程必须使用CALL单独调用，这一点与后面学习的存储函数不同。5.课堂小结（5分钟）总结本课核心知识：存储过程是编译后存储在数据库中的SQL语句集合，具有封装、高效、安全等优点，也存在可移植性差等缺点；创建存储过程使用CREATEPROCEDURE...BEGIN...END，需要配合DELIMITER修改结束符；调用存储过程使用CALL语句。下节课学习存储过程的参数和变量。第2课时参数类型与变量、条件判断语句（40分钟）1.复习导入（5分钟）回顾存储过程的创建和调用。提问：“刚才创建的存储过程没有参数，只能查询固定的内容。如果想让存储过程根据传入的学号查询对应学生的信息，怎么做？如果想让存储过程把查询结果返回给调用者，又怎么做？”引出参数和变量的学习。2.参数类型——IN/OUT/INOUT（12分钟）讲解存储过程的三种参数类型：（1）IN参数：输入参数，调用时传入值给存储过程使用，存储过程内部不能修改该值并返回给调用者。默认参数类型，可以省略不写。（2）OUT参数：输出参数，存储过程内部为它赋值，调用结束后调用者可以获取该值，用于把处理结果返回给调用者。（3）INOUT参数：输入输出参数，既可以在调用时传入值，也可以在存储过程内部修改并返回。示例：CREATEPROCEDUREpro_query(INsnoVARCHAR(20))BEGINSELECT*FROMstuinfoWHEREstuNo=sno;END;调用：CALLpro_query(‘2022A01001’);根据传入的学号查询学生信息。示例：CREATEPROCEDUREpro_count(OUTcntINT)BEGINSELECTCOUNT(*)INTOcntFROMstuinfo;END;调用：CALLpro_count(@n);SELECT@n;通过OUT参数返回学生总数。逐一演示IN和OUT参数的用法，强调：IN是传入、OUT是传出、INOUT是既传又出；OUT和INOUT参数必须在调用时使用会话变量（@变量名）接收。3.存储过程中的变量（8分钟）讲解存储过程中两类变量的定义和使用：（1）局部变量：使用DECLARE声明，必须在存储过程（BEGIN...END）开头声明，作用范围是整个存储过程。语法：DECLARE变量名数据类型[DEFAULT默认值];赋值：SET变量名=值;或SELECT字段INTO变量名FROM...;示例：DECLAREv_numINTDEFAULT0;SETv_num=100;（2）会话变量：使用@符号定义，不需要声明，在整个会话期间有效。示例：SET@v=5;调用存储过程时常用会话变量接收OUT参数的值。（3）对比：局部变量只在存储过程内部有效，会话变量在整个数据库会话中有效。4.条件判断语句——IF...THEN...ELSE（10分钟）讲解存储过程中的条件判断语句，语法格式：IF条件THEN语句;ELSEIF条件THEN语句;ELSE语句;ENDIF;（1）示例：创建根据成绩返回等级的存储过程——CREATEPROCEDUREpro_grade(INscFLOAT,OUTgradeVARCHAR(10))BEGINIFsc>=90THENSETgrade=‘优秀’;ELSEIFsc>=80THENSETgrade=‘良好’;ELSEIFsc>=60THENSETgrade=‘及格’;ELSESETgrade=‘不及格’;ENDIF;END;（2）调用：CALLpro_grade(85,@g);SELECT@g;返回‘良好’。（3）强调：IF语句必须以ENDIF结尾（注意IF和ENDIF之间有一个空格，写成ENDIF）；ELSEIF是一个整体，不能写成ELSEIF；条件中可以使用比较运算符和逻辑运算符。演示完整案例，让学生理解条件判断在存储过程中如何实现业务逻辑分支。5.任务书7.1讲解与课堂小结（5分钟）任务书7.1要求完成教务系统教师管理模块的存储过程——创建实现教师信息添加功能的存储过程，并调用存储过程进行测试。任务要点：使用IN参数接收教师信息（教工号、姓名、性别等），在存储过程中使用INSERT语句插入数据。总结本课核心知识：IN/OUT/INOUT三种参数类型分别表示输入、输出、输入输出；局部变量用DECLARE声明、会话变量用@定义；IF...THEN...ELSEIF...ELSE...ENDIF实现条件判断。下节课将在实践课中创建教师信息添加存储过程。实践教学（2课时，80分钟）1.任务导入（5分钟）明确本节课的实践任务：完成任务书7.1——创建实现教师信息添加功能的存储过程，并调用存储过程进行测试。本节课是学生第一次编写完整的存储过程，重点掌握DELIMITER的使用、参数的传递和CALL调用。2.任务书7.1讲解（10分钟）讲解任务书7.1的要求：教师信息表teacherinfo包含教工号、姓名、性别、年龄、联系方式等字段。要求创建存储过程pro_add_teacher，通过IN参数接收教师信息，使用INSERT语句将教师信息插入表中。提示设计思路：存储过程参数与教师信息字段一一对应；插入语句的字段顺序必须与参数顺序一致。3.创建教师信息添加存储过程演示（20分钟）演示完整创建过程：DELIMITER$$CREATEPROCEDUREpro_add_teacher(INt_noVARCHAR(20),INt_nameVARCHAR(20),INt_sexCHAR(2),INt_ageINT)BEGININSERTINTOteacherinfo(teacherNo,teacherName,teacherSex,teacherAge)VALUES(t_no,t_name,t_sex,t_age);END$$DELIMITER;演示调用测试：CALLpro_add_teacher(‘T2026001’,‘王老师’,‘女’,35);调用后使用SELECT语句验证教师信息是否插入成功。演示错误排查：讲解DELIMITER书写错误、参数数量不匹配、字段顺序不一致等常见错误及排查方法。4.学生实操与测试（30分钟）学生自主完成任务书7.1：创建教师信息添加存储过程，并调用至少3次，添加3条以上教师测试数据。巡回指导，重点关注：DELIMITER修改结束符是否成对出现、CREATEPROCEDURE语法是否完整（BEGIN...END）、参数类型和数量是否与INSERT语句匹配、调用时CALL语句的参数顺序是否正确。要求学生将存储过程创建语句、调用语句和验证结果截图保存。5.课堂小结（15分钟）总结本节课实践内容：存储过程的完整创建流程（DELIMITER修改结束符→CREATEPROCEDURE→BEGIN...END→恢复结束符）、IN参数传递数据、CALL语句调用测试。强调：存储过程是封装业务逻辑的核心工具，添加功能只是最简单的应用，后续将学习带输出参数、带判断逻辑的存储过程。要求学生在课后复习参数类型和变量的用法，预习存储过程的查看、修改和删除方法。第二次课（4课时：理论2学时+实践2学时）理论教学（2课时，80分钟）第3课时查看、修改与删除存储过程（40分钟）1.复习导入（5分钟）回顾存储过程的创建和调用。提问：“数据库中的存储过程越来越多，如何查看当前数据库有哪些存储过程？如何查看某个存储过程的定义？存储过程创建后发现逻辑需要调整，除了删除重建还有别的办法吗？”引出本课主题——存储过程的查看、修改和删除。2.查看存储过程（10分钟）讲解查看存储过程的两种方法：（1）SHOWPROCEDURESTATUSLIKE‘存储过程名’;查看存储过程的状态信息（名称、类型、创建时间、修改时间、字符集等）。示例：SHOWPROCEDURESTATUSLIKE‘pro_add_teacher’;（2）SHOWCREATEPROCEDURE存储过程名;查看存储过程的创建定义语句。示例：SHOWCREATEPROCEDUREpro_add_teacher;结果中可以看到完整的SQL定义文本。逐一演示两种查看方法，强调：SHOWPROCEDURESTATUS不带LIKE条件时查看所有存储过程；查看定义语句有助于分析和维护已有存储过程。3.修改存储过程（5分钟）讲解修改存储过程的语法：ALTERPROCEDURE存储过程名特征;注意：ALTERPROCEDURE只能修改存储过程的特征（如注释COMMENT、安全属性SQLSECURITY等），不能修改存储过程的SQL逻辑。（1）示例：ALTERPROCEDUREpro_add_teacherCOMMENT‘教师信息添加存储过程’;为存储过程添加注释。（2）强调：如果需要修改存储过程的SQL语句逻辑，只能先DROP删除，再重新CREATE创建。这一点与视图不同——视图的ALTERVIEW可以修改定义，而存储过程的ALTER不能修改BEGIN...END中的内容。4.删除存储过程（5分钟）讲解删除存储过程的语法：DROPPROCEDURE存储过程名;（1）示例：DROPPROCEDUREpro_test;（2）强调：删除存储过程后，相关的调用语句将无法执行；可以使用DROPPROCEDUREIFEXISTS存储过程名;避免因存储过程不存在而报错。（3）删除与重新创建的配合：修改存储过程逻辑的标准流程是DROP后再CREATE。5.综合示例与课堂小结（15分钟）演示一个完整案例：将带OUT参数的存储过程与查询结合——创建一个返回指定教师信息的存储过程，然后查看、修改注释、删除并重建。（1）创建：CREATEPROCEDUREpro_query_teacher(INt_noVARCHAR(20),OUTt_nameVARCHAR(20))BEGINSELECTteacherNameINTOt_nameFROMteacherinfoWHEREteacherNo=t_no;END;（2）调用：CALLpro_query_teacher(‘T2026001’,@tn);SELECT@tn;通过OUT参数返回教师姓名。（3）查看：SHOWCREATEPROCEDUREpro_query_teacher;（4）删除重建：DROPPROCEDUREpro_query_teacher;再重新创建修改后的版本。总结本课核心知识：SHOWPROCEDURESTATUS和SHOWCREATEPROCEDURE查看存储过程；ALTERPROCEDURE只能修改特征不能修改逻辑；修改逻辑需先DROP再CREATE；DROPPROCEDURE删除存储过程。第4课时存储函数（40分钟）1.复习导入（5分钟）回顾存储过程的知识。提问：“MySQL中除了存储过程，还有一类可以封装的数据库对象叫存储函数。它与存储过程很相似，但调用方式不同。存储函数和存储过程有什么区别？什么情况下用存储函数更方便？”引出本课主题——存储函数。2.存储函数的概念与区别（8分钟）讲解存储函数的概念：存储函数是一种返回单个值的数据库对象，类似于MySQL内置函数（如ROUND、NOW），但由用户自定义。创建后可以像内置函数一样在SQL语句中直接调用。（1）存储过程与存储函数的区别：存储过程可以有0个或多个返回值（通过OUT参数），存储函数必须有且只有一个返回值（通过RETURNS声明）；存储过程使用CALL调用，存储函数可以直接在SELECT等表达式中调用；存储过程参数类型可以是IN/OUT/INOUT，存储函数参数只能是IN类型。（2）对比表格展示：调用方式（CALLvs表达式直接调用）、返回值（多个/无vs必须有且只有一个）、参数（IN/OUT/INOUTvs仅IN）、使用场景（复杂业务流程vs返回单值计算）。3.创建存储函数——CREATEFUNCTION（12分钟）讲解创建存储函数的语法格式：CREATEFUNCTION函数名(参数列表)RETURNS返回类型函数体;（1）示例：DELIMITER$$CREATEFUNCTIONfn_get_age(birthDATE)RETURNSINTBEGINDECLAREageINT;SETage=YEAR(NOW())-YEAR(birth);RETURNage;END$$DELIMITER;创建根据出生日期计算年龄的函数。（2）注意：RETURNS子句必须声明返回的数据类型；函数体内必须有RETURN语句返回一个值，且返回值类型与RETURNS声明一致；存储函数同样需要配合DELIMITER使用。（3）示例：创建判断成绩等级的存储函数——CREATEFUNCTIONfn_grade(scFLOAT)RETURNSVARCHAR(10)BEGINIFsc>=90THENRETURN‘优秀’;ELSEIFsc>=60THENRETURN‘及格’;ELSERETURN‘不及格’;ENDIF;END;4.调用存储函数（5分钟）讲解存储函数的调用方式：存储函数可以像内置函数一样在SELECT语句、WHERE条件等表达式中直接调用。（1）示例：SELECTfn_get_age(‘2004-05-20’);直接返回计算结果。（2）示例：SELECTstuName,fn_get_age(birthDate)FROMstudetail;在查询中调用函数计算每个学生的年龄。（3）示例：SELECTfn_grade(85);返回‘及格’。强调：存储函数返回单个值，所以可以嵌入到任何允许表达式的位置，这是与存储过程最大的使用差异。5.任务书7.4讲解与课堂小结（10分钟）任务书7.2要求分析数据库已有存储过程；任务书7.3要求修改存储过程，使其返回新插入数据的id（使用LAST_INSERT_ID()函数结合OUT参数）；任务书7.4要求使用存储函数实现学生登录功能（根据学号和密码判断登录是否成功，返回布尔值）。简要说明各任务要点，为实践课做准备。总结本课核心知识：存储函数必须有且只有一个返回值，参数只能是IN；创建用CREATEFUNCTION...RETURNS...BEGIN...RETURN...END；调用时像内置函数一样在表达式中直接使用；存储过程用CALL调用，存储函数用表达式调用，这是两者的核心区别。实践教学（2课时，80分钟）1.任务导入（5分钟）明确本节课的实践任务：完成任务书7.2（分析数据库已有存储过程）、任务书7.3（修改存储过程返回新插入数据的id）和任务书7.4（使用存储函数实现学生登录功能）。本节课综合运用存储过程的查看、修改和存储函数的创建。2.任务书7.2演示与实操：分析已有存储过程（15分钟）演示使用SHOWPROCEDURESTATUS和SHOWCREATEPROCEDURE查看数据库中的存储过程，分析存储过程的功能、参数和逻辑。（1）SHOWPROCEDURESTATUS;查看所有存储过程的状态列表。（2）SHOWCREATEPROCEDUREpro_add_teacher;查看教师添加存储过程的定义，逐行分析参数和SQL逻辑。学生自主完成任务书7.2，分析自己创建的存储过程，要求截图保存分析结果。3.任务书7.3演示与实操：修改存储过程返回新插入数据的id（20分钟）讲解任务书7.3的实现思路：修改教师信息添加存储过程，使其通过OUT参数返回新插入记录的id。关键点：使用MySQL的LAST_INSERT_ID()函数获取最近一次INSERT操作自动生成的id。演示修改过程（先DROP再CREATE）：DELIMITER$$CREATEPROCEDUREpro_add_teacher(INt_noVARCHAR(20),INt_nameVARCHAR(20),OUTnew_idINT)BEGININSERTINTOteacherinfo(teacherNo,teacherName)VALUES(t_no,t_name);SETnew_id=LAST_INSERT_ID();END$$DELIMITER;演示调用测试：CALLpro_add_teacher(‘T2026002’,‘李老师’,@id);SELECT@id;查看返回的新插入数据的id。强调：ALTERPROCEDURE不能修改逻辑，修改逻辑必须DROP后重建；LAST_INSERT_ID()返回的是本会话最近一次自动增长id。学生自主完成任务书7.3，巡回指导，重点关注：OUT参数的声明位置、LAST_INSERT