第8章 数据库编程.doc

黑马程序员MySQL数据库入门（第2版）教学设计 课程名称： 授课年级： 授课学期： 教师姓名： 年 月课题名称第8章 数据库编程计划课时6课时教学引入为了提高SQL语句的重用性，MySQL可以将频繁使用的业务逻辑封装成程序进行存储，这类程序主要包括存储过程、函数、触发器等。MySQL在SQL标准的基础上，扩展了一些程序设计语言的元素，如变量、流程控制语句等。这些程序设计语言的元素可以让程序更加健全，以提高数据库系统的性能。本章将针对数据库编程的相关内容进行讲解。教学目标l 使学生掌握存储过程的基本操作，能够创建、查看、调用、修改和删除存储过程。l 使学生掌握存储函数的基本操作，能够创建、查看、调用和删除存储函数。l 使学生掌握变量的使用，能够查看和修改系统变量，对用户变量和局部变量进行定义和赋值。l 使学生掌握流程控制语句的使用，能够在程序中灵活使用判断语句、循环语句和跳转语句控制程序执行流程。l 使学生掌握错误触发条件和错误处理，能够正确定义错误触发条件和错误处理程序。l 使学生掌握游标的使用，能够使用游标检索数据。l 使学生了解触发器，能够说出触发器的概念。l 使学生掌握触发器的基本使用，能够创建、查看和删除触发器。教学重点l 创建存储过程l 调用存储过程l 创建存储函数l 调用存储函数l 判断语句l 循环语句l 跳转语句l 自定义错误处理程序l 使用游标检索数据l 触发器的基本操作教学难点l 自定义错误名称l 自定义错误处理程序l 使用游标检索数据教学方式课堂教学以PPT讲授为主，并结合多媒体进行教学教学过程第一课时（创建存储过程、查看存储过程、调用存储过程、修改存储过程、删除存储过程）一、复习巩固 教师通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、通过直接导入的方式导入新课 开发过程中，经常会遇到重复使用某一功能的情况，为此，MySQL引入了存储过程。存储过程是一组可以完成特定功能的SQL语句集合，它可以将常用或复杂的操作封装成一个代码块存储在数据库服务器中，以便重复使用，大大减少数据库开发人员的工作量。本节将针对存储过程进行详细讲解。三、新课讲解 知识点1-创建存储过程 教师通过PPT结合实际操作的方式讲解创建存储过程。(1) 展示创建存储过程的基本语法格式。CREATE PROCEDURE 存储过程名 (IN | OUT | INOUT 参数名称 参数类型) characteristic . routine_body(2) 创建存储过程的语句中，参数和选项的具体含义如下所示。l IN：表示输入参数，该参数需要在调用存储过程时传入。l OUT：表示输出参数，初始值为NULL，它是将存储过程中的值保存到OUT指定的参数中，返回给调用者。l INOUT：表示输入输出参数，既可以作为输入参数也可以作为输出参数。l characteristic：表示存储过程中的例程可以设置的特征。l routine_body：表示存储过程中的过程体。(3) 通过案例演示存储过程的创建。 知识点2-查看存储过程教师通过PPT结合实际操作的方式讲解查看存储过程。(1) 使用SHOW PROCEDURE STATUS语句显示存储过程的状态信息SHOW PROCEDURE STATUS语句显示存储过程状态信息的基本语法格式如下。SHOW PROCEDURE STATUS LIKE 'pattern'(2) 使用SHOW CREATE PROCEDURE语句显示存储过程的创建信息使用SHOW CREATE PROCEDURE语句可以显示存储过程的创建语句等信息，其基本语法格式如下。SHOW CREATE PROCEDURE 存储过程名;(3) 从information_schema.Routines表中查看存储过程的信息存储过程的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录获取存储过程的信息，查询语句如下。SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='pro_emp' AND ROUTINE_TYPE='PROCEDURE'G 知识点3-调用存储过程教师通过PPT结合实际操作的方式讲解调用存储过程。(1) 调用存储过程的基本语法格式如下。CALL 数据库名称.存储过程名称 (实参列表);(2) 通过一个案例演示存储过程的调用。知识点4-修改存储过程教师通过PPT结合实际操作的方式讲解修改存储过程。(1) 修改存储过程，其基本语法格式如下。ALTER PROCEDURE 过程名称 characteristic .;(2) 通过案例演示修改存储过程的特征值。知识点5-删除存储过程教师通过PPT结合实际操作的方式讲解删除存储过程。(1) 删除存储过程的基本语法格式如下所示。DROP PROCEDURE IF EXISTS 存储过程名称;(2) 通过案例演示存储过程的删除。四、归纳总结 教师回顾本节课所讲的内容，并通过测试题的方式引导学生解答问题并给予指导。五、布置作业 教师通过高校教辅平台（）布置本节课作业以及下节课的预习作业。 第二课时（创建存储函数、查看存储函数、调用存储函数、删除存储函数）一、复习巩固 教师通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、通过直接导入的方式导入新课MySQL支持函数的使用，MySQL中的函数可以分为两种，一种是内置函数，一种是自定义函数。在MySQL中，通常将用户自定义的函数称为存储函数，存储函数和MySQL内置函数性质相同，都用于实现某种功能。本节将对存储函数进行讲解。三、 新课讲解知识点1-创建存储函数教师通过PPT结合实际操作的方式讲解创建存储函数。(1) 创建存储函数的基本语法格式如下所示。CREATE FUNCTION func_name (func_parameter.)RETURNS typecharacteristic . Routine_body(2) 通过一个案例演示存储函数的创建。知识点2-查看存储函数教师通过PPT结合实际操作的方式讲解查看存储函数。(1) 使用SHOW FUNCTION STATUS语句显示存储函数的状态信息，基本语法格式如下。SHOW FUNCTION STATUS LIKE 'pattern'(2) 使用SHOW CREATE FUNCTION语句显示存储函数的创建信息，基本语法格式如下。SHOW CREATE FUNCTION 存储函数名;(3) 在information_schema.Routines表中查看存储函数的信息，基本语法格式如下。SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='存储函数名' AND ROUTINE_TYPE='FUNCTION'G知识点3-调用存储函数教师通过PPT结合实际操作的方式讲解调用存储函数。(1) 调用存储函数的基本语法格式如下所示。SELECT 数据库名.函数名1（实参列表）函数名2 (实参列表);(2) 通过一个案例演示存储函数的调用。知识点4-删除存储函数 教师通过PPT结合实际操作的方式讲解删除存储函数。(1) 删除存储函数的语法格式如下所示。DROP FUNCTION IF EXISTS 函数名称;(2) 通过一个案例演示存储函数的删除。四、归纳总结 教师回顾本节课所讲的内容，并通过测试题的方式引导学生解答问题并给予指导。五、布置作业 教师通过高校教辅平台（）布置本节课作业以及下节课的预习作业。 第三课时（系统变量、用户变量、局部变量）一、复习巩固 教师通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、通过直接导入的方式导入新课变量就是在程序执行过程中，其值可以改变的量。在MySQL中，可以利用变量存储程序执行过程中涉及的数据，如输入的值、计算结果等。根据变量的作用范围可以将其划分为系统变量、用户变量和局部变量。本节将针对这3种变量进行讲解。三、新课讲解知识点1-系统变量教师通过PPT结合实际操作的方式讲解系统变量。(1) 系统变量又分为全局（GLOBAL）变量和会话（SESSION）变量，其中全局变量指的是MySQL系统内部定义的变量，对所有MySQL客户端都有效。(2) 查看系统变量 在MySQL中可以通过SHOW语句显示所有的系统变量，其语法格式如下所示。 SHOW GLOBAL | SESSION VARIABLES LIKE '匹配字符串' | WHERE 表达式;(3) 修改系统变量 在MySQL中，系统变量可以通过SET语句进行修改，修改的语法格式如下所示。SETGLOBAL | GLOBAL.| SESSION |SESSION. 系统变量名 = 新值;知识点2-用户变量教师通过PPT结合实际操作的方式讲解用户变量。用户变量指的是用户自己定义的变量，它和连接有关，即用户变量仅对当前用户使用的客户端生效，不能被其他客户端看到和使用。用户变量由符号和变量名组成，在使用用户变量之前，需要对用户变量进行定义并赋值。MySQL中为用户变量赋值有以下3种方式。(1) 使用SET语句完成赋值。(2) 在SELECT语句中使用赋值符号:=完成赋值。(3) 使用SELECTINTO语句完成赋值。知识点3-局部变量教师通过PPT结合实际操作的方式讲解局部变量。(1) 局部变量的作用范围仅在语句块BEGINEND之间，在语句块BEGINEND之外，局部变量不能被获取和修改。(2) 局部变量使用DECLARE语句定义，定义的基本语法格式如下。DECLARE 变量名1 ,变量名2 数据类型 DEFAULT 默认值;(3) 通过案例演示局部变量的使用。四、归纳总结 教师回顾本节课所讲的内容，并通过测试题的方式引导学生解答问题并给予指导。五、布置作业 教师通过高校教辅平台（）布置本节课作业以及下节课的预习作业。 第四课时（判断语句、循环语句、跳转语句）一、复习巩固 教师通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、通过直接导入的方式导入新课程序在执行时，都会按照程序结构（由业务逻辑决定）对执行流程进行控制，程序的结构主要分为顺序结构，选择结构和循环结构，其中顺序结构会按照代码编写的先后顺序，依次执行；选择结构和循环结构会根据程序的执行情况调整和控制程序的执行顺序。程序执行流程由流程控制语句进行控制，MySQL中的流程控制语句有IF语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句等。这些语句大体可以分为3类，分别为判断语句、循环语句和跳转语句，本节分别对这些语句进行讲解。三、新课讲解知识点1-判断语句教师通过PPT结合实际操作的方式讲解判断语句。(1) IF语句 IF语句的语法格式如下所示。IF 条件表达式1 THEN 语句列表 ELSEIF 条件表达式2 THEN 语句列表. ELSE 语句列表END IF 通过一个案例演示IF语句的使用。(2) CASE语句 CASE语句的语法格式有2种，具体如下所示。# 语法格式1CASE 表达式 WHEN 值1 THEN 语句列表 WHEN 值2 THEN 语句列表. ELSE 语句列表END CASE# 语法格式2CASE WHEN 条件表达式1 THEN 语句列表 WHEN 条件表达式2 THEN 语句列表. ELSE 语句列表END CASE 通过一个案例演示CASE语句的使用知识点2-循环语句教师通过PPT结合实际操作的方式讲解循环语句。(1) LOOP语句 LOOP语句通常用于实现一个简单的循环，其基本语法格式如下所示。标签:LOOP语句列表END LOOP标签; 通过一个案例演示LOOP语句的使用。(2) REPEAT语句 REPEAT语句用于循环执行符合条件的语句列表，REPEAT语句的基本语法格式如下所示。标签: REPEAT 语句列表 UNTIL 条件表达式END REPEAT 标签 通过示例演示REPEAT语句的使用。(3) WHILE语句 WHILE语句也用于循环执行符合条件的语句列表，WHILE 语句的基本语法格式如下所示。标签:WHILE 条件表达式 DO 语句列表END WHILE标签 通过示例演示WHILE语句的使用。知识点3-跳转语句教师通过PPT结合实际操作的方式讲解switch条件语句。(1) 跳转语句用于实现执行过程中的流程跳转，跳转语句的基本语法格式如下所示。ITERATE|LEAVE 标签名;(2) 通过案例演示跳转语句的使用。四、归纳总结 教师回顾本节课所讲的内容，并通过测试题的方式引导学生解答问题并给予指导。五、布置作业 教师通过高校教辅平台（）布置本节课作业以及下节课的预习作业。 第五课时（自定义错误名称、自定义错误处理程序、游标的操作流程、使用游标检索数据、触发器概述、触发器的基本操作）一、复习巩固 教师通过上节课作业的完成情况，对学生吸收不好的知识点进行再次巩固讲解。二、通过情景导入的方式导入新课程序在运行过程中可能会发生错误，发生错误时，默认情况下，MySQL将自动终止程序的执行。有些时候，如果不希望程序因为错误而停止执行，可以通过MySQL中的错误处理机制自定义错误名称和错误处理程序，让程序遇到警告或错误时也能继续执行，从而增强程序处理问题的能力。接下来对错误处理进行讲解。三、新课讲解知识点1-自定义错误名称教师通过PPT结合实际操作的方式讲解自定义错误名称。(1) 在MySQL中可以使用DECLARE语句为错误声明一个名称，声明的基本语法格式如下所示。DECLARE 错误名称 CONDITION FOR 错误类型;(2) 通过案例演示自定义错误名称。知识点2-自定义错误处理程序教师通过PPT结合实际操作的方式讲解自定义错误处理程序。(1) 自定义错误处理程序的基本语法格式如下所示。DECLARE 错误处理方式 HANDLER FOR 错误类型,错误类型. 程序语句段(2) 错误类型有6种可选值，分别如下。l sqlstate_value：匹配SQLSTATE错误代码。l condition_name：匹配DECLARE定义的错误条件名称。l SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。l NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。l SQLEXCEPTION：匹配所有没有被 SQLWARNING或 NOT FOUND捕获的SQLSTATE错误代码。l mysql_error_code：匹配mysql_error_code类型的错误代码。(3) 通过案例演示自定义错误处理程序的应用。知识点3-游标的操作流程教师通过PPT结合实际操作的方式讲解游标的操作流程。(1) 定义游标定义游标的基本语法格式如下所示。DECLARE 游标名称 CURSOR FOR SELECT语句(2) 打开游标声明游标之后，要想从游标中提取数据，需要先打开游标。在 MySQL 中，打开游标通过 OPEN 关键字实现，其语法格式如下。OPEN 游标名称 (3) 利用游标检索数据打开游标之后，就可以通过游标检索SELECT语句返回的结果集中的数据，游标检索数据的基本语法格式如下所示。FETCH 游标名称 INTO 变量名1 ,变量名2.(4) 关闭游标游标检索完数据后，应该利用MySQL提供的语法关闭游标，释放游标占用的MySQL服务器的内存资源，关闭游标的基本语法格式如下所示。CLOSE 游标名称 知识点4-使用游标检索数据教师通过PPT结合实际操作的方式讲解使用游标检索数据。通过具体的案例演示使用游标检索数据。知识点5-触发器概述教师通过PPT结合实际操作的方式讲解触发器概述。(1) 触发器可以看作是一种特殊的存储过程，它与存储过程的区别在于，存储过程使用CALL语句调用时才会执行，而触发器会在预先定义好的事件（例如INSERT、DELETE等操作）发生时自动调用。(2) 触发器具有以下优点。l 触发器自动执行，在触发器相关联的数据表中的数据发生修改，触发器中定义的语句会自动执行。l 触发器对数据进行安全校验，保障数据安全。l 通过和触发器相关联的表，可以实现表数据的级联更改，在一定程度上保证了数据的完整性。知识点6-触发器的基本操作教师通过PPT结合实际操作的方式讲解触发器的基本操作。(1) 创建触发器创建触发器的基本语法格式如下所示。CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 数据表名 FOR EACH ROW 触发程序(2) 查看触发器 利用SHOW TRIGGER语句查看触发器信息的语法格式如下所示。SHOW TRIGGERS; 通过SELECT语句查看该数据表获取触发器信息，通过TRIGGERS数据表查询触发器的语法格式如下所示。SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名称'(3) 触发触发器通过案例演示触发器的触发。(4) 删除触发器MySQL中使用DROP TRIGGER语句删除触发器。 DROP TRIGGER语句的基本语法格式。DROP TRIGGER IF EXISTS 数据库名. 触发器名; 通过案例演示触发器的删除。四、归纳总结 教师回顾本节课所讲的内容，并通过测试题的方式引导学生解答问题并给予指导。五、布置作业 教师通过高校教辅平台（）布置本节课作业以及下节课的预习作业。 第六课时（上机练习）上机：（考察知识点为上机实践：数据库编程实战）形式：单独完成 题目：基于图书管理系统的图书表和用户表进行数据库编程要求：(1) 创建一个存储过程proc_1，执行后获取图书的名称、价格和借阅状态，如果是未借阅的状态，则显示未借阅；如果是已借阅的状态，则显示借阅人。创建完存储过程proc_1后，执行该存储过程查看效果。(2) 创建一个存储过程proc_2，执行后获取所有可借阅的图书信息，图书信息只需显示图书名称、图书价格和上架时间。创建完存储过程proc_2后，执行该存储过程查看效果。(3) 创建一个存储函数func_1，执行时输入用户名称，显示用户当前借阅中的图书名称。创建完存储函数func_1后，执行该存储函数查看效果。(4) 创建一个存储函数func_2，执行时输入图书名称，显示图书当前的价格档位；如果价格小于等于40，显示“平民价格”；如果价格大于40并且小于等于60，则显示“主流价格”；如果价格大于60，则显示“高价格”。创建完存储函数func_2后，执行该存储函数查看效果。(5) 查看当前数据库中存储过程proc_1和存储函数func_1的信息(6) 查看存储过程proc_1和存储函数func_1的信息后，觉得不是自己想要的效果，想要删除这2个程序。(7) 创建一个触发器trig_book，在借阅记录表中插入数据时，自动修改图书表中借阅相关的信息。(8) 创建好触发器trig_book后，查看该触发器的信息。教学后记11