MySQL数据库技术与项目应用教程电子教案 项目六-2 数据库编程(函数和存储过程)

备课纸适用班级：_______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;备课纸适用班级：_