MySQL数据库技术与项目应用教程（微课版）（AI助学）（第3版）-教案 项目6-9 数据库编程(基础) -综合应用

备课纸适用班级：_______P.课题项目六编程操作网上商城系统数据（一）课时2+2课时教学目的掌握MySQL中变量和常量的定义掌握MySQL中流程控制语句的使用掌握MySQL常用的内置函数重点MySQL中流程控制语句的使用掌握MySQL常用的内置函数难点MySQL中流程控制语句的使用教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入提出问题数据库编程1.数据库中如何进行业务逻辑处理？的意义2.对于集合数据，数据库中如何单独操纵？任务1数据库编程基础知识描述：任何一种语言都是为了解决实际应用中的问题而存在的。SQL程序的流程控制和游标的使用能够有效解决数据库程序设计中的复杂逻辑问题。本任务在SQL程序语言基础上，详细讨论了SQL的流程控制和游标的使用。备课纸适用班级：_P.SQL程序语言基础带着问题开本任务讨论如下内容：展本章学习1.MySQL中如何定义变量？激发学生兴2.MySQL中怎样控制处理逻辑？趣3.在集合数据中如何对记录进行逐行处理？变量略讲变量是指程序运行过程中会变化的量。用户变量系统变量和服务器变量局部变量略讲（1）用户变量用户变量即用户定义的变量。用户变量可以被赋值。以@字符作为前缀。用户变量名不区分大小写，在MySQL会话末端结束其定义。用户变量使用SET命令和SELECT命令给其赋值。【例6.1】MySQL中用户变量赋值。#语句1SET@id=10;#语句2SELECT@x1:=1,@x2:=@x1+1,@x3:=@x2+1;#语句3SELECTcnameFROMcategoryWHEREcid=1INTO@name;SELECT@name;#语句4SELECT@id:=@id+1,cnameFROMcategory;（2）系统变量系统变量是MySQL的一些特定的参数。当MySQL服务启动时，这些参数将被读取并配置MySQL的运行环境。系统变量使用“@@”作为前缀标识符。MySQL提供了专门查看系统变量的语句：备课纸适用班级：_P.SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配模式'|WHERE条件表达式];GLOBAL用于显示全局系统变量，当变量没有全局值时，则不会显示。SESSION为表示会话变量，是默认值，可以省略，用于显示当前连接中有效的系统可变值。【例6.2】使用SHOWVARIABLES查看所有系统变量。SHOWVARIABLES;【例6.3】设置和查看系统变量。SET@@profiling=0;#设置系统变量，关闭profiles功能SET@@event_scheduler=0;00;#设置系统变量，开启事件调度器SELECT@@global.version;#查看全局变量version,获知当前mysql的版本号SHOWVARIABLESLIKE'ver%'#查看以ver开头的系统变量（3）局部变量局部变量一般用在SQL语句块（如存储过程的BEGIN和END）中。其作用域仅限于语句块，当语句块执行完毕后，局部变量就消失了。局部变量用DECLARE来声明，可以使用DEFAULT来设置初始值。【例6.3】定义名称为proc_add的存储过程，该存储过程有两个int类型的参数，分别为a和b。DELIMITER//#修改默认提交符为“//”CREATEPROCEDUREproc_add(aint,bint)BEGINDECLAREcintDEFAULT0;#定义局部变量变c,初始值为0SETc=a+b;SELECTcAS'Result';END//#提交学习提示MySQL默认代码提交符为分号“;”，由于程序体中有多个语句，这时需要通过DELIMITER语句修改默认的代码提交符号。笔者这里修改为“//”符号，读者可以根据个人习惯设置。备课纸适用班级：_P.2、常量常量是指在程序运行过程中，值不会改变的量。（1）字符串常量字符串是指用单引号或双引号括起来的字符序列（2）数值常量数值常量可以分为整数常量和浮点数常量（3）日期时间常量用单引号将表示日期时间的字符串括起来就是日期时间常量例如，'2008-05-1214:26：24:00'就是一个合法的日期时间常量。（4）布尔值常量布尔值只包含TRUE和FALSE两个值，其中TRUE表示真，数字值为1,FALSE表示假，数字值为0。（5）NULL值常量NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义3、运算符运算符是执行数学运算、字符串连接以及列、常量和变量之间进行比较的符号。同其他高级语言相同SQL的流程控制语句使用SQL语言也像其他程序设计语言一样有顺序结构、分支结构和循环结构等流程控制语句。1.条件分支语句条件分支语句是通过对特定条件的判断，选择一个分支的语句执行。SQL中可以实现条件分支的语句的方法（1）IF…ELSE语句备课纸适用班级：_P.IF条件表达式1THEN 语句块1;[ELSEIF条件表达式2THEN语句块2;]……[ELSE 语句块n+1;]ENDIF;【例6.5】查询uid为3的用户是否购买过商品（判断其是否有订单）。实例演示DELIMITER//CREATEPROCEDUREproc_orders()#定义存储过程，用于包含IF语句BEGINDECLAREnumint;#定义局部变量#计算订单数并存储num中SELECTcount(*)INTOnumFROMordersWHEREuid=3;IFnum>0THEN#IF语句判断num值SELECT'有订单';ELSESELECT'无订单';ENDIF;#结束IF语句END//（2）CASE语句=1\*GB3①CASE简单结构。语法知识CASE表达式WHEN数值1THEN语句块1;[WHEN数值2THEN语句块2;]……[ELSE 语句块n+1;]ENDCASE;=2\*GB3②CASE搜索结构语法知识CASEWHEN条件表达式1THEN语句块1;[WHEN条件表达式2THEN语句块2;]……[ELSE 语句块n+1;]ENDCASE;备课纸适用班级：_P.【例6.6】判断参数grade，当值为A时返回“优秀”，值为B时返回“良好”，其他值返回“一般”。精讲+演示DELIMITER//CREATEPROCEDUREproc_grade1(gradechar)#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEgradeWHEN'A'THENSETresult='优秀';WHEN'B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//【例6.7】使用CASE搜索结构实现【例6.6】精讲+演示DELIMITER//CREATEPROCEDUREproc_grade2(gradechar(1))#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEWHENgrade='A'THENSETresult='优秀';WHENgrade='B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//2.循环语句（1）WHILE语句MySQL中循环语句可以在函数、存储过程或者触发器等内容中使用。MySQL中提供WHILE、REPEAT和LOOP三种。多种方法实（1）WHILE语句现流程控制[开始标签:]WHILE条件表达式DO 语句块;ENDWHILE[结束标签];备课纸适用班级：_P.【例6.8】使用WHILE语句，求1到100的和。实例演示DELIMITER//CREATEPROCEDUREproc_doWhile()BEGINDECLAREiintdefault1;#定义局部变量iDECLAREsintdefault0;#定义局部变量s WHILEi<=100DO#开始循环 SETs=s+i; SETi=i+1; ENDWHILE;#结束循环 SELECTs;END//（2）REPEAT语句[开始标签:]REPEAT 语句块; UNTIL条件表达式ENDREPEAT[结束标签];学习提示REPEAT语句是在执行循环体里的语句块后再执行“条件表达式”的比较，不管条件是否满足，循环体至少执行一次；而WHILE语句则是先执行“条件表达式”的比较，当结果为TRUE时再执行循环体中的语句块。（3）LOOP语句[开始标签:]LOOP 语句块ENDLOOP[结束标签];【例6.9】LOOP语句示例。add_num:LOOP SETi=i+1;ENDLOOPadd_num;备课纸适用班级：_P.（4）LEAVE语句LEAVE语句主要用于跳出循环控制，与高级语言中的BREAK语句相似LEAVE标签名;【例6.10】修改【例6.9】，使用LEAVE语句跳出循环。add_num:LOOP SETi=i+1; IFi=100THENLEAVEadd_num;ENDLOOPadd_num;（4）ITERATE语句ITERATE语句也可用于跳出循环，与高级语言中的CONTINUE语句相似。ITERATE标签名;学习提示LEAVE语句和ITERATE语句都是用来跳出循环语句，但两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环外的程序语句；ITERATE语句是跳出本次循环，进入下一次循环。MySQL常用内置函数1.数学函数数学函数主要用于处理数字，包括整数、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数和随机函数等【例6.11】以1为基数，每天进步0.01，计算一年后的进步有多大?实例演示mysql>SELECTpower((1+0.01),365)asprogress;++|progress|++|37.78343433288728|++1rowinset(0.00sec)2.字符串函数备课纸适用班级：_P.字符串函数主要用于处理字符串。字符串函数包括字符串长度、合并字符串、在字符串中插入子串和大小字母之间切换等函数【例6.12】输出合并的两个字符串，并在两个子串之间插入1个空格。实例演示mysql>SELECTCONCAT('Hunan',SPACE(1),'Changsha')asstr;++|str|++|HunanChangsha|++1rowinset(0.00sec)【例6.13】从字符串“mysql”取出“sql”的子串。实例演示SELECTmid('mysql',3,3);3.日期时间函数日期时间函数主要用于处理日期和时间数据。日期时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等【例6.14】获取系统当前日期时间的年份、月份、日期、小时和分钟。实例演示SET@mydate=CURDATE();SET@mytime=CURTIME();SELECTYEAR(@mydate),MONTH(@mydate),DAYOFMONTH(@mydate),HOUR(@mytime),MINUTE(@mytime);【例6.15】计算100天后的日期。实例演示SELECTdate_add(current_date(),interval100day);4.数据类型转换函数知识点介绍【例6.16】数据类型转换函数示例。备课纸适用班级：_P.实例演示SELECTCAST('2021-10-0116:50:21'asdate); #输出2021-10-01SELECTconvert('132str',SIGNED); #输出123SELECTconvert('大'USINGutf8mb4),convert('大'USINGascii);#输出大,？5.条件控制函数知识点介绍【例6.17】条件控制函数示例。实例演示SELECTif(TRUE,'A','B'),if(FALSE,'A','B'); #输出ABSELECTifnull('A','B'),ifnull(null,'B'); #输出ABSELECTnullif('A','B'),nullif('A','A'); #输出AnullSELECTisnull(null),isnull('A'); #输出10【例6.18】查询图书类商品的名称和销售状态(sale_status)，若gishot为1，显示“热销”，否则显示为“一般”。实例演示mysql>SELECTgname,if(gishot=1,'热销','一般')ASsale_status->FROMgoodsJOINcategoryUSING(cid)->WHEREcname='图书';+++|gname|sale_status|+++|林清玄启悟人生系列：愿你，归来仍是少年|一般||平凡的世界：全三册（激励青年的不朽经典）|热销||曾国藩全集（全六卷绸面精装插盒珍藏版）|一般||中外文化文学经典系列红岩导读与赏析|一般|+++4rowsinset(0.00sec)6.加密和散列函数备课纸适用班级：_P.知识点介绍加密和散列函数主要用于对存储的数据进行加密，相对于明文存储，加密后的字符串不会被管理员直接看到，以保证数据的安全性，实际应用中对于敏感数据的存储都要进行加密处理。学习提示md5()和sha()经常用于敏感数据的非明文存储，加密过程不可逆，数据验证时需要将验证文本按同样的计算后再比较是否一致。【例6.19】加密和散列函数示例。实例演示mysql>SELECTmd5('abc'),sha1('abc'),->convert(aes_decrypt(aes_encrypt('abc','z'),'z')usingascii)str\G;***************************1.row***************************md5('abc'):900150983cd24fb0d6963f7d28e17f72sha1('abc'):a9993e364706816aba3e25717850c26c9cd0d89dstr:abc1rowinset(0.00sec)7.JSON函数知识点介绍备课纸适用班级：_P.【例6.20】JSON函数示例。实例演示#创建一个json数组SELECTjson_array('id',12,'name','李明');#输出["id",12,"name","李明"]#创建一个json对象SELECTjson_object('id',12,'name','李明');#输出{"id":12,"name":"李明"}#定义用户变量@infoSET@info='{"name":[{"id":12,"name":"李明"},{"id":13,"name":"刘立"}]}';#取json对象的所有键SELECTjson_keys(@info,"$.name[0]");#输出["id","name"]#取json对象的键值SELECTjson_value(@info,"$.name[2]");#输出{"id":12,"name":"李明"}【例6.21】将category表中cid小于4的每一行记录，生成成一个JSON对象。实例演示mysql>SELECTjson_object('cid',cid,'cname',cname)->FROMcategory->WHEREcid<4;++|json_object('cid',cid,'cname',cname)|++|{"cid":1,"cname":"图书"}||{"cid":2,"cname":"乐器"}||{"cid":3,"cname":"蔬菜水果"}|++3rowsinset(0.00sec)【例6.22】将category表中cid小于4的记录，生成成一个JSON数组。实例演示mysql>SELECTjson_objectagg(json_object('cid',cid,'cname',cname))cjson->FROMcategory->WHEREcid<4\G;***************************1.row***************************cjson:[{"cid":1,"cname":"图书"},{"cid":2,"cname":"乐器"},{"cid":3,"cname":"蔬菜水果"}]1rowinset(0.00sec)实际开发中，若要直接返回给应用程序使用，通常需要封装成JSON对象。实例演示SELECT@j=(SELECTjson_objectagg(json_object('cid',cid,'cname',cname))FROMcategoryWHEREcid<4);SET@category_json=(SELECTjson_object("category",@j));SELECT@category_json;执行上述代码，变量@category_json的值如下。'{"category":[{"cid":1,"cname":"图书"},{"cid":2,"cname":"乐器"},{"cid":3,"cname":"蔬菜水果"}]}'【例6.23】将【例6.22】生成的@category_json变量值还原成关系表。实例演示SELECT*FROMjson_table(@category_json,'$.category[*]'COLUMNS(cidintpath'$.cid',cnamevarchar(30)path'$.cname'))ast8.系统信息函数知识点介绍【例6.24】获取当前登录用户、连接id和数据库名。实例演示mysql>SELECTuser(),connection_id(),database();++++|user()|connection_id()|database()|++++|root@localhost|12|onlinedb|++++1rowinset(0.00sec)9.其他常用函数知识点介绍【例6.25】IP地址转换函数示例。实例演示SELECTinet_aton(''); #输出3232235521SELECTinet_ntoa(3232235521); #输出备课纸适用班级：_______P.课题项目六编程操作网上商城系统数据（二）课时2+4课时教学目的会创建和调用存储函数会创建和调用存储过程重点创建和调用函数创建和调用存储过程难点存储过程中数据访问逻辑控制教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入提出问题数据库编程1.如何对程序代码的进行重用？同样要实现2.存储函数和存储过程有什么区别？封装任务2使用函数实现数据访问知识描述：实际开发中，为了让应用程序专注业务处理，数据库层常定义存储函数和存储过程来封装数据处理逻辑，以提高代码的重用性及数据访问效率。本任务主要介绍MySQL中存储函数的创建、调用和管理的方法，有效实现数据库中模块化数据访问。备课纸适用班级：_P.带着问题开本任务讨论如下内容：展本章学习（1）为什么要使用存储函数和存储过程？激发学生兴（2）怎样定义和创建存储函数?趣（3）怎样定义和创建存储过程?创建存储函数用户使用自定义函数，可以避免重复编写相同的SQL语句，减少客户端和服务器的数据传输。知识点精讲CREATEFUNCTION函数名([参数列表])RETURNS数据类型[存储函数特征]函数体;

函数名：存储函数的名称。不能与数据库中其他对象名相同。

参数列表：存储函数的输入参数，每个参数由参数名称和参数类型组成；参数列表中参数的定义格式如下。param_nametype

RETURNS数据类型：指定函数返回值的数据类型。

函数体：存储函数的主体，可以是单个SELECT语句，若包含多条语句时，必须使用BEGIN...END来标识SQL代码的开始和结束。函数体中必须包含RETURN关键字将结果返回给调用者，且返回的结果值必须为标量值。DETERMINISTIC|NOSQL|READSSQLDATA：为函数特征值，至少选择三者之一。DETERMINISTIC：指明函数的确定性，当设置为确定性函数时，表示每次执行函数时，相同的输入会得到相同的输出，且不会修改数据；NOSQL表示函数体中不包含SQL语句；READSSQLDATA说明函数体中只包含读语句。若不设置任何特征，就需要设置系统全局变量@@GLOBAL.log_bin_trust_function_creators的值为ON。【例6.26】创建函数func_count，返回商品类别的数量。实例演示CREATEFUNCTIONfunc_count()RETURNSintegerDETERMINISTICRETURN(SELECTCOUNT(*)FROMcategory);备课纸适用班级：_P.【例6.27】创建函数func_getName，根据指定的商品id，查询商品名称。实例演示CREATEFUNCTIONfunc_getName(in_idint)#定义参数in_id用于接受商品id值RETURNSvarchar(50)DETERMINISTICRETURN(SELECTgnameFROMgoodsWHEREgid=in_id);【例6.28】创建函数func_getRandStr，返回指定长度的字母数字随机串。实例演示DELIMITER//CREATEFUNCTIONfunc_getRandStr(nint)#定义参数nRETURNSvarchar(255)NOSQL#表示函数体中不包括查询语句BEGIN--定义字符库，由字母和数据组成DECLAREchars_strvarchar(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';DECLAREreturn_strvarchar(255)DEFAULT'';#定义局部变量return_strDECLAREiintDEFAULT0;#定义局部变量i,用于循环计数WHILEi<nDOSET@len=length(chars_str);#计算chars_str字符串长度SET@pos=ceiling(rand()*@len);#随机生成0-@len范围内的数#取字符串chars_str中的@pos位的字母，并使用concat函数连接成串SETreturn_str=concat(return_str,mid(chars_str,@pos,1));SETi=i+1;#循环变量加1ENDWHILE;RETURNreturn_str;#返回字符串return_strEND//调用存储函数用户自定义函数的使用方法与MySQL内部函数的使用方法是一样的语法格式如下SELECT函数名([参数列表]);备课纸适用班级：_P.【例6.29】调用函数func_count。实例演示SELECTmysql>SELECTfunc_count();++|count(*)|++|6|++1rowinset(0.01sec)fnCount();【例6.30】调用函数func_getName，查询商品ID为1的商品名称。实例演示mysql>SELECTfunc_getName(2);++|gname|++|平凡的世界：全三册（激励青年的不朽经典）|++1rowinset(0.01sec)【例6.31】调用函数func_getRandStr，输出产生长度为3、5、10的随机字符串。实例演示mysql>SELECTfunc_getRandStr(3),func_getRandStr(5),func_getRandStr(10);++++|func_getRandStr(3)|func_getRandStr(5)|func_getRandStr(10)|++++|UJL|rNuRJ|RZaK52Jnxo|++++1rowinset,2warnings(0.01sec)管理存储函数1.查看存储函数的状态和定义SHOWFUNCTIONSTATUS[LIKE匹配模式];备课纸适用班级：_P.【例6.32】查看存储函数func_count的状态信息。mysql>SHOWCREATEFUNCTIONlike'func_count'\G;***************************1.row***************************Db:onlinedbName:func_countType:FUNCTIONDefiner:root@localhostModified:2021-09-0511:25:49Created:2021-09-0511:25:49Security_type:DEFINERComment:character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)MySQL也可以通过SHOWCREATE语句来查看函数的定义SHOWCREATEFUNCTION存储函数名;【例6.33】查看函数func_count的定义。mysql>SHOWCREATEFUNCTIONfunc_count\G;***************************1.row***************************Function:func_countsql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreateFunction:CREATEDEFINER=`root`@`localhost`FUNCTION`func_count`()RETURNSintDETERMINISTICRETURN(SELECTCOUNT(*)FROMcategory)character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)2.删除函数DROPFUNCTION[IFEXISTS]存储函数名;【例6.34】删除函数func_count。备课纸适用班级：_P.mysql>DROPFUNCTIONIFEXISTSfunc_count;QueryOK,0rowsaffected(0.05sec)任务3使用存储过程实现数据访问任务描述存储过程与存储函数一样，也可以封装具有一定功能的语句块，不同之处在于存储过程可以将预编译并保存在数据库中，供用户重复调用。本任务从存储过程的优点着手，详细介绍创建、执行、修改和删除存储过程的方法，有效实现数据库中模块化数据访问。课程引入：回顾程序设计中的模块化设计方法和SQL语句的书写，阐述存储过程的作用及其在数据库程序设计中的重要性学习提示：存储过程极大提高了常用SQL语句的执行速度。并且从架构上为数据库程序员提供了安全方便的途径，设计通用的编程接口，为应用程序和终端用户提供需要已经经过逻辑处理的信息。带着问题开本任务讨论如下内容：展本章学习（1）存储过程的作用及与简单的SQL语句的区别？激发学习兴（2）怎么建立、操作存储过程？趣（3）存储过程的应用场合？存储过程概述存储过程是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需简单的调用。设问存储过程就是一堆SQL语句而已啊？那么存储过程与一般SQL语句的区别?存储过程的主要优点有：阐术存储过（1）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码即可，提高了系统性能。备课纸适用班级：_P.程的主要优（2）存储函数必须使用RETURN返回数据，且只能返回标量数据；而存储过程没有RETURN子句，其数据返回方式可以通过SELECT语句和输出参数实现。点（3）存储过程可以嵌套在触发器或事件中，运用灵活。（4）数据库管理员能够对存储过程进行单独的权限控制，避免非授权用户对数据的访问。此外，若普通用户无权直接访问数据库表时，也可通过权限控制使用存储过程间接访问数据，以屏蔽数据库中表的细节，从而保证数据的安全性。存储过程的初始处理创建——执行——优化——编译看图分析处图存储过程的初始处理过程理过程创建存储过程1.创建存储过程语法格式CREATEPROCEDURE存储过程名([参数1[,参数2…]])[存储过程特性]程序体参数的定义:[IN|OUT|INOUT]参数名称参数类型精讲

存储过程名：定义的存储过程的名称。

存储过程特性：与存储函数定义里该参数的说明相同。备课纸适用班级：_P.

程序体：封装的SQL代码集合，用BEGIN...END来标识SQL代码的开始和结束。

IN|OUT|INOUT：表示参数方向，其中IN表示输入参数；OUT表示输出参数；INOUT表示输入输出参数。【例6.35】创建存储过程，查询goods表中前3条商品的gid,gname和gprice。实例演示DELIMITER//CREATEPROCEDUREproc_getgoods()READSSQLDATABEGINSELECTgid,gname,gpriceFROMgoodsLIMIT3;END//2.调用存储过程CALL存储过程名([参数列表]);【例6.36】调用名为proc_getgoods的存储过程，输出相应商品的名称和价格。实例演示mysql>CALLproc_getgoods();++++|gid|gname|gprice|++++|1|林清玄启悟人生系列：愿你，归来仍是少年|29.00||2|平凡的世界：全三册（激励青年的不朽经典）|94.00||3|曾国藩全集（全六卷绸面精装插盒珍藏版）|255.00|++++3rowsinset(0.00sec)QueryOK,0rowsaffected(0.01sec)参数化存储过程存储过程可以指定一个或多个参数，参数的声明由参数方向、参数名和参数类型3部分构成，一般至少提供参数名和参数类型。参数方向是指数据传输方向，在没有指定的情况下默认为输入参数。1.创建和调用带输入参数的存储过程备课纸适用班级：_P.【例6.37】创建存储过程proc_getGoodsPage，根据指定的页码(假定每页3件商品)，显示该页中商品的gid,gname和gprice。实例精讲DELIMITER//CREATEPROCEDUREproc_getGoodsPage(pageint)READSSQLDATABEGINDECLAREstartposint;#定义局部变量startposSETstartpos=(1)*5;#计算查询记录的开始位置SELECTgid,gname,gpriceFROMgoodsLIMITstartpos,5;END//【例6.38】调用存储过程proc_getGoodsPage，查询第2页商品的信息。实例演示mysql>CALLproc_getGoodsPage(2);++++|gid|gname |gprice|++++|4|中外文化文学经典系列红岩导读与赏析|29.00||5|古琴老杉木乐器伏羲式_七弦琴|3299.00||6|专业演奏级乐器洞箫_8孔正手G调|549.00|++++3rowsinset(0.04sec)QueryOK,0rowsaffected(0.05sec)2.创建和调用带输入输出参数的存储过程【例6.39】创建存储过程proc_getGoodsPages，在返回【例6.38】结果的基础上，返回总页数。实例精讲DELIMITER//CREATEPROCEDUREproc_getGoodsPages(pageint,outtotal_pagesint)READSSQLDATABEGINDECLAREstartposint;#定义局部变量startposSETstartpos=(1)*3;#计算查询记录的开始位置SELECTgid,gname,gpriceFROMgoodsLIMITstartpos,3;#计算总页数等于商品总数除以总页数向上取整SETtotal_pages=ceiling((SELECTcount(*)FROMgoods)/3);END//备课纸适用班级：_P.【例6.40】调用存储过程proc_getGoodsPages，结果如下。实例演示mysql>CALLproc_getGoodsPages(2,@pages);++++|gid|gname|gprice|++++|4|中外文化文学经典系列红岩导读与赏析|29.00||5|古琴老杉木乐器伏羲式_七弦琴|3299.00||6|专业演奏级乐器洞箫_8孔正手G调|549.00|++++3rowsinset(0.00sec)QueryOK,0rowsaffected(0.04sec)mysql>SELECT@pages;#查询返回的输出参数@page++|@pages|++|4|++1rowinset(0.00sec)管理化存储过程1.查看存储过程的定义SHOWSTATUS语句来查看存储过程的状态略讲SHOWPROCEDURESTATUS[LIKE匹配模式];SHOWCREATE语句来查看存储过程的定义SHOWCREATEPROCEDUREproc_getGoodsPages;2.删除存储过程略讲DROPPROCEDURE[IFEXISTS]存储过程名;【例6.42】删除名为proc_getGoodsPage的存储过程。DROPPROCEDUREIFEXISTSproc_getGoodsPage;备课纸适用班级：_P.错误处理MySQL提供的DECLARE…HANDLERFOR语句可以指定的错误名称或代码定义相应的处理程序语法格式DECLARE错误处理方式HANDLERFOR错误类型处理程序;精讲

错误处理方式取值为CONTINUE（遇到错误时不处理，继续执行）和EXIT（遇到错误时马上中断执行并退出）；

错误类型主要有如下4种。

MySQL的错误代码，比如1305或服务器内部错误42000。

SQLWARNING：表示所有以01开头的SQLSTATE错误代码。

NOTFOUND：表示所有以02开头的SQLSTATE错误代码。

SQLEXCEPTION：表示除01和02开头外的所有SQLSTATE错误代码。

处理程序，表示当遇到时，需要执行的存储过程的代码段。【例6.43】错误处理的应用示例。实例精讲DELIMITER//CREATEPROCEDUREproc_errorHandler()#创建存储过程proc_errorHandlerBEGIN#当遇到状态码为23000的错误时，设置@flag值为10，程序继续执行 DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@flag=10; INSERTINTOcategoryvalues(7,'鲜花'); SET@flag=1; INSERTINTOcategoryvalues(7,'鲜花');#重复插入商品类别 SET@flag=@flag+1;END//【例6.44】调用存储过程proc_errorHandler，查看用户变量@flag的值。实例演示mysql>CALLproc_errorHandler();QueryOK,0rowsaffected(0.00sec)mysql>SELECT@flag;#查询用户变量@flag++|@flag|++|11|++1rowinset(0.00sec)备课纸适用班级：_______P.课题项目六编程操作网上商城系统数据（三）课时2+4课时教学目的会使用SQL创建和管理触发器会使用SQL创建和管理事件重点数据库自动化任务难点触发器和事件的应用教具多媒体+机房课型新授作业课后习题课后项目实战教学过程课时分配教学方法教学内容课程引入提出问题1.对于繁杂的数据处理任务如何自动完成？2.相关数据的一致性如何保障？任务4使用触发器实现自动任务知识描述：触发器是数据库中的独立对象，为了确保数据完整性，设计人员可以用触发器实现复杂的业务逻辑。例如，当用户选购好商品之后，并完成了订单，那么用户所选购的商品的库存量应该根据用户订单中商品的数量进行减少。备课纸适用班级：_P.带着问题开本任务讨论如下内容：展本章学习（1）触发器与外键约束的区别？激发学生兴（2）怎样定义和管理触发器?趣（3）怎样定义和管理事件?触发器概述触发器是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，以保证关联数据的完整性。MySQL中，激活触发器的事件：INSERT事件UPDATE事件DELETE事件详细阐述触发器中的两个逻辑表：NEW表：用来存放更新后的记录。OLD表：表用来存放更新前的记录。学习提示NEW和OLD的表结构与触发器所在数据表的结构完全一致，当触发器的执行完成之后，这两个表也会被自动删除。创建触发器语法格式CREATETRIGGER触发器名称触发时间触发事件ON表名FOREACHROW程序体触发器名称：指要创建的触发器的名称；触发时间：指触发器执行的时间，它可以是BEFORE或AFTER，以指明触发器是在激活它的语句之前或之后触发；触发事件：指激活触发程序的语句类型，包括INSERT、UPDATE和DELETE表名：是指触发事件操作的表名称；备课纸适用班级：_P.FOREACHROW：表示任何一条记录上的操作满足触发事件都会触发该触发器；程序体：指触发器被触发后执行的语句集。【例6.45】创建触发器trig_ins_addnum，当uid为1的用户向购物车中添加商实例精讲CREATETRIGGERtrig_ins_addnum#建议命名以trig_开头，其中ins表示是insertAFTERINSERT#数据插入完成后执行ONcart#触发器建立的表为cartFOREACHROWSET@total=@total+NEW.cnum;#累加新增记录的购买数量【例6.46】向users表中添加新用户，判断输入的密码长度，若长度小于6，则拒绝插入新用户，并提示“密码长度小于6，请重新输入“。若长度大于等于6，则按md5算法对密码加密处理。实例演示DELIMITER//CREATETRIGGERtrig_ins_checkpwdBEFOREINSERT#向users表插入数据前执行ONusersFOREACHROWBEGIN IFlength(new.upwd)>=6THEN#执行判断密码长度 SETnew.upwd=md5(upwd);#符合条件时的处理逻辑 ELSE #自定义错误提示，数据插入失败 SIGNALSQLSTATE'45000'SETmessage_text='密码长度小于6，请重新输入'; ENDIF;END//学习提示触发器中对表本身执行INSERT和UPDATE操作时，触发器的动作时间只能用BEFORE不能用AFTER。当触发程序的语句类型是INSERT或者UPDATE时，在触发器里不能再用UPDATESET，应直接使用SET，避免出现UPDATESET重复错误。备课纸适用班级：_P.管理触发器1.查看触发器的定义SHOWTRIGGERS语句来查看触发器的基本信息SHOWTRIGGERS[{FROM|IN}数据库名][LIKE匹配模式|WHERE条件表达式]【例6.47】查看cart表上定义的触发器。mysql>SHOWTRIGGERSlike'cart%'\G;***************************1.row***************************Trigger:trig_ins_addnumEvent:INSERTTable:cartStatement:SET@total=@total+NEW.cnumTiming:AFTERCreated:2021-09-0710:35:34.06sql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONDefiner:root@localhostcharacter_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.00sec)2.删除触发器DROPTRIGGER[IFEXISTS]trigger_name;【例6.48】删除名称为trig_ins_addnum的触发器。mysql>DROPTRIGGERtrig_ins_addnum;QueryOK,0rowsaffected(0.02sec)任务5使用事件实现自动任务任务描述数据库管理是一项重要且烦琐的工作，许多日常管理任务往往会频繁地、周期性地执行，例如定期维护索引、定时刷新数据、定时关闭帐户、定义打开或关数据库等操作，实际应用中，数据库管理员会定义事件对象以自动化完成这些任务。本任务将详细介绍MySQL中事件的创建、维护和管理等。备课纸适用班级：_P.事件概述事件是在特定时刻调用的\o"MySQL知识库"数据库对象。一个事件可调用一次，也可周期性的被调用，它由一个特定的线程来管理，也就是“事件调度器”。类比事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动。事件取代了原先只能由\o"操作系统知识库"操作系统的计划任务来执行的工作，而且\o"MySQL知识库"MySQL的事件调度器可以精确到每秒钟执行一个任务。1.开启事件调度器事件调度器是MySQL数据库服务器的一部分，负责事件的调度，它不断监视某个事件是否需要被调用。在创建事件前，必须先打开事件调度器。MySQL中的全局变量@@GLOBAL.EVENT_SCHEDULER用于监控事件调度器是否开启。【例6.46】查看MySQL服务器事件调度器的状态。mysql>SHOWVARIABLESLIKE'event_scheduler';+++|Variable_name|Value|+++|event_scheduler|OFF|+++1rowinset,1warning(0.01sec)【例6.47】打开MySQL服务器事件调度器。mysql>SET@@GLOBAL.event_scheduler=ON;QueryOK,0rowsaffected(0.00sec)mysql>SHOWVARIABLESLIKE'event_scheduler';+++|Variable_name|Value|+++|event_scheduler|ON|+++备课纸适用班级：_P.学习提示事件调试器的状态值也可以用1和0来表示，其中1表示ON，0表示OFF。当服务器重启时，事件调度器的状态会恢复到默认值。若要想永久改变事件调度器的状态，可以修改my.ini文件，并在[mysqld]部分添加如下内容，然后重启MySQL。创建事件每个事件由事件调度（eventschedule）和事件动作（eventaction）两个主要部分组成。语法格式精讲CREATEEVENT[IFNOTEXISTS]事件名称ONSCHEDULE时间与频率[ONCOMPLETION[NOT]PRESERVE][ENABLE|DISABLE][COMMENT事件注释]DO程序体;

ONSCHEDULE时间和频率：定义事件执行的开始和结束时间、执行的频率与持续时间。

ONCOMPLETION[NOT]PRESERVE：默认情况事件执行完后会自动删除，若想保留事件定义，则要设置ONCOMPLETIONPRESERVE。

ENABLE|DISABLE：用于启用或禁用事件。创建时默认为ENABLE。

DO程序体：用于指定事件执行的SQL语句集。可以是简单的INSERT或者UPDATE语句，还可以调用存储过程或者BEGIN…END的语句块。事件在创建时会根据时间和频率的不同设置，确定事件仅执行一次或是定期重复执行。（1）定义执行一次的事件，时间和频率设置语法如下。【例6.51】从当前时间开始的5分钟后，修改乐器类商品的价格为原价9折。实例精讲CREATEEVENTevent_discountONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL5MINUTEDOUPDATEgoodsgJOINcategorycONg.cid=c.cidSETgprice=gprice*0.9WHEREcname='乐器';备课纸适用班级：_P.学习提示：使用AT设置时间戳时，不能设置为过期时间。（2）定义重复执行的事件，时间和频率设置语法如下。EVERY时间间隔[STARTS开始时间[+INTERVAL时间间隔]][ENDS结束时间[+INTERVAL时间间隔]]【例6.52】创建名为event_huge_sales事件，从2022年1月1日起，每天晚上8点后，蔬菜水果类商品的价格修改为原价的8折。实例精讲CREATEEVENTevent_huge_salesONSCHEDULEEVERY1DAYSTARTS'2022-1-120:00:00'ENDS'2022-12-3123:59:59'DOUPDATEgoodsgJOINcategorycONg.cid=c.cidSETgprice=gprice*0.8WHEREcname='蔬菜水果';【例6.53】创建名为event_reindex_goods事件，每周调用存储过程proc_reindex_goods,用于重建goods表上索引ix_gname。首先，创建存储过程proc_reindex_goods，用于重建索引ix_gname。实例演示DELIMITER//CREATEPROCEDUREproc_reindex_goods()DETERMINISTICBEGINIFEXISTS(SELECT* FROMinformation_schema.statistics WHEREtable_schema='onlinedb'#筛选数据库名 ANDtable_name='goods'#筛选表名 ANDindex_name='ix_gname')#筛选索引名 DROPINDEXix_gnameongoods; ENDIF; CREATEINDEXix_gnameongoods(gname);#建立索引END//备课纸适用班级：_P.创建事件，用于每周一次调用该存储过程CREATEEVENTevent_reindex_goodsONSCHEDULEEVERY1WEEK#执行频率为每周一次STARTS'2022-1-103:00:00'DOCALLproc_reindex_goods();管理事件1.查看事件语法格式SHOWEVENTS[{FROM|IN}数据库名][LIKE匹配模式|WHERE条件表达式]【例6.54】查看onlinedb数据库中的所有事件，并格式化显示。实例演示mysql>SHOWEVENTSFROMonlinedb\G;***************************1.row***************************Db:onlinedbName:event_reindex_goodsDefiner:root@localhostTimezone:SYSTEMType:RECURRINGExecuteat:NULLIntervalvalue:1Intervalfield:WEEKStarts:2022-01-0103:00:00Ends:NULLStatus:ENABLEDOriginator:1character_set_client:utf8mb4collation_connection:utf8mb4_0900_ai_ciDatabaseCollation:utf8_bin1rowinset(0.03sec)要查看事件的创建信息，其语法格式如下：SHOWCREATEEVENTevent_name;2.修改事件备课纸适用班级：_P.语法格式ALTEREVENT事件名称ONSCHEDULE时间与频率[ONCOMPLETION[NOT]PRESERVER][RENAMETO新事件名称][ENABLE|DISABLE][COMMENT事件注释][DO程序体];略讲【例6.55】禁用event_reindex_goods的事件。ALTEREVENTevent_reindex_goodsDISABLE;【例6.56】启用名为event_reindex_goods的事件。ALTEREVENTevent_reindex_goodsENABLE;QueryOK,0rowsaffected(0.01sec)【例6.57】修改事件event_reindex_goods的为即时事件，事件执行完后不删除，并重新命名为event_reindex。实例演示mysql>ALTEREVENTevent_reindex_goods->ONSCHEDULEATCURRENT_TIMESTAMP->ONCOMPLETIONPRESERVE->RENAMETOevent_reindex->DO->CALLproc_reindex_goods();QueryOK,0rowsaffected(0.02sec)3.删除事件略讲DROPEVENT[IFEXISTS]事件名称【例6.58】删除名为event_reindex的事件。mysql>DROPEVENTevent_reindex;QueryOK,0rowsaffected(0.01sec)项目实战

存储函数备课纸适用班级：_P.（1）创建并调用存储函数func_users_count，查询2021年1月1日以后注册的用户总数。（2）使用SQL语句查看用户自定义函数func_users_count。

存储过程（3）创建并调用存储过程proc_get_integer，输入100以内能够同时被3和5整除的整数。（4）创建并调用存储过程proc_rand_record，为users表添加10000条测试记录。（5）创建并调用存储过程proc_user_order，根据指定的uID查询该用户的订单总数。（6）删除存储过程proc_user_order（7）创建存储过程proc_orders_count，统计查询每个用户的订单数。

触发器（8）创建触发器trig_order_num,当用户下