《MySQL数据库应用案例教程》651-5（马洁）教案 第17课 存储过程和函数（一）

PAGE12PAGE12PAGE13PAGE13

课题存储过程和函数（一）课时2课时（90min）教学目标知识技能目标：（1）了解存储过程和函数的相关概念（2）掌握创建并调用存储过程和函数的方法（3）掌握变量、条件和处理程序、游标及流程控制的使用素质目标：（1）培养做事要了解全貌的习惯（2）了解数据库前沿技术，紧跟时代发展教学重难点教学重点：存储过程和函数的相关概念教学难点：创建并调用存储过程和函数的方法教学方法案例分析法、问答法、讨论法、讲授法、实践法教学用具电脑、投影仪、多媒体课件、教材教学设计第1节课：→→→传授新知（38min）第2节课：→传授新知（20min）→课堂实训（15min）→课堂小结（3min）→作业布置（2min）教学过程主要教学内容及步骤设计意图第一节课课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，预习本节课要学习的知识【学生】完成课前任务通过课前任务，使学生预习本节课要学习的知识，增加学生的学习兴趣考勤（2min）【教师】使用文旌课堂APP进行签到【学生】班干部报请假人员及原因培养学生的组织纪律性，掌握学生的出勤情况问题导入（5min）【教师】提出以下问题：存储过程和函数是什么，对比单条SQL语句有什么优势？·通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（38min）【教师】通过学生的回答引入要讲的知识，讲解存储过程和函数的相关概念，以及创建并调用存储过程和函数的相关知识13.1存储过程和函数概述通过前面章节的学习，相信用户已经能够编写操作单表或者多表的单条SQL语句，但是针对表的一个完整操作往往不是单条SQL语句就能实现的，而是需要一组SQL语句来实现。✈【教师】随机邀请学生回答以下问题：要完成一个购买商品的订单的处理，一般需要考虑哪些问题？✈【学生】聆听、思考、回答✈【教师】总结学生的回答例如，要完成一个购买商品的订单的处理，一般需要考虑以下几步：（1）在生成订单之前，首先需要查看商品库存中是否有相应商品；（2）如果商品库存中不存在相应商品，需要向供应商订货；（3）如果商品库存中存在相应商品，需要预定商品，并修改库存数量。对于上述完整操作，显然不是单条SQL语句就能实现的。在实际应用中，一个完整的操作会包含多条SQL语句，并且在执行过程中还需要根据前面语句的执行结果有选择的执行后面的语句。为此，可将一个完整操作中所包含的多条SQL语句创建为存储过程或函数，以方便应用。存储过程和函数可以简单地理解为一组经过编译并保存在数据库中的SQL语句的集合，可以随时被调用。✈【教师】随机邀请学生回答以下问题：存储过程和函数具有哪些优点和缺点？✈【学生】聆听、思考、回答✈【教师】总结学生的回答存储过程和函数具有以下优点：允许标准组件式编程：存储过程和函数在创建后可以在程序中被多次调用，有效提高了SQL语句的重用性、共享性和可移值性。较快的执行速度：如果某一操作包含大量的事务处理代码，并且被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器会对其进行分析优化，并将最终执行计划存储在系统中，而批处理的事务处理语句在每次运行时都要进行编译和优化。减少网络流量：对于大量的SQL语句，将其组织成存储过程，会比一条一条地调用SQL语句要大大节省网络流量，降低网络负载。安全：数据库管理员通过设置执行某一存储过程的权限，从而限制相应数据的访问权限，避免非授权用户对数据的访问，保证数据的安全。除上述优点外，存储过程和函数也存在一定的缺陷。首先，存储过程和函数的编写比单个SQL语句的编写要复杂很多，需要用户具有更高的技能和更丰富的经验；其次，在编写存储过程和函数时，需要创建这些数据库对象的权限。13.2创建并调用存储过程和函数✈【教师】组织学生扫码观看“创建并调用存储过程和函数”视频（详见教材），让学生对相关知识有一个大致了解存储程序可以分为存储过程和函数。存储过程和函数的操作主要包括创建存储过程和函数、调用存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。本节主要介绍如何创建和调用存储过程和函数。13.2.1创建存储过程创建存储过程使用SQL语句CREATEPROCEDURE来实现，其语法形式如下：CREATEPROCEDUREproc_name([proc_parameter[,…])[characteristic…]routine_body（1）CREATEPROCEDURE为创建存储过程的关键字。（2）proc_name表示要创建的存储过程名。（3）proc_parameter表示存储过程的参数，参数形式如下：[IN|OUT|INOUT]parameter_nameTYPE其中，IN表示输入参数，可把外界的数据传递到存储过程当中；OUT表示输出参数，可把存储过程的运算结果传递到外界；INOUT表示输入输出参数，既可以把外界的数据传递到存储过程当中，又可以把存储过程的运算结果传递到外界；parameter_name表示参数名；TYPE表示参数的数据类型。【提示】需要注意的是，存储过程的参数名不要与数据表中的字段名重复，否则系统会报错。（4）characteristic表示存储过程的特性，可取值及其意义如下：LANGUAGESQL：表示存储过程的routine_body部分使用SQL语言编写，当前系统支持的语言为SQL。[NOT]DETERMINISTIC：DETERMINISTIC表示存储过程的执行结果是确定的，就是每次输入相同的参数并执行存储过程后，得到的结果是相同的；默认为NOTDETERMINISTIC，表示执行结果不确定，即相同的输入可能得到不同的结果。{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL语句的限制。CONTAINSSQL为默认值，表示子程序包含SQL语句，但不包含读或写数据的语句；NOSQL表示子程序不包含SQL语句；READSSQLDATA表示子程序包含读取数据的语句，但不包含写数据的语句；MODIFIESSQLDATA表示子程序包含写入数据的语句。SQLSECURITY{DEFINER|INVOKER}：指定可执行存储过程的用户，DEFINER表示只有创建者才能执行，INVOKER表示拥有权限的调用者可以执行。COMMENT'string'：表示存储过程或者函数的注释信息。（5）routine_body表示需要执行的SQL语句的集合，可以使用BEGIN表示开始，使用END表示结束。【实例13-1】创建一个名为proc的简单存储过程，用于获取goods表中的记录数。在创建存储过程前首先登录MySQL，并选择数据库db_shop。然后执行以下语句：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc(OUTnumINT)->BEGIN->SELECTCOUNT(*)INTOnumFROMgoods;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;【提示】“DELIMITER$$”的作用是将语句的结束符“;”修改为“$$”，这样存储过程中的SQL语句结束符“;”就不会被MySQL解释成语句的结束而提示错误。在存储过程创建完成后，应使用“DELIMITER;”语句将结束符修改为默认结束符。13.2.2创建存储函数创建存储函数使用SQL语句CREATEFUNCTION来实现，其语法形式如下：CREATEFUNCTIONfunc_name([parameter_name[,…])RETURNSTYPE[characteristic…]routine_bodyCREATEFUNCTION为创建存储函数的关键字；func_name表示存储函数名；parameter_name表示存储函数参数名；TYPE表示函数返回值的数据类型；characteristic指定存储函数的特性，取值与创建存储过程时相同；routine_body表示函数体。【实例13-2】创建一个名为func的简单存储函数，用于获取goods表中的记录数。在创建存储函数前首先登录MySQL，并选择数据库db_shop。SQL语句的执行结果如下：mysql>DELIMITER$$mysql>CREATEFUNCTIONfunc()->RETURNSINT(11)->RETURN(SELECTCOUNT(*)FROMgoods)->$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;【提示】RETURNSTYPE子句对于存储函数而言是必须存在的，如果RETURN子句返回值的数据类型与RETURNSTYPE子句指定的数据类型不同，MySQL会将返回值强制转换为RETURNSTYPE子句指定的类型。13.2.3调用存储过程和函数存储过程必须使用关键字CALL调用，而存储函数与MySQL内置函数的调用相同，使用关键字SELECT。1．调用存储过程通常使用关键字CALL调用存储过程，其语法形式如下：CALLprocedure_name([parameter[,…]]);其中的parameter表示变量名，存储过程的返回值将赋予该变量。【实例13-3】调用实例13-1创建的存储过程proc()，查看其返回值。首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：mysql>CALLproc(@num);QueryOK,1rowaffected(0.06sec)mysql>SELECT@num;++|@num|++|10|++1rowinset(0.01sec)上述语句中，使用CALLproc(@num);语句调用存储过程后，系统会将返回值赋予变量@num；使用SELECT语句查看变量@num值，结果为10。调用存储过程的执行结果与直接执行查询语句SELECTCOUNT(*)FROMgoods;的执行结果相同，但是存储过程的好处在于处理逻辑都封装在数据库端，调用者不需要了解中间的处理逻辑，当处理逻辑发生变化时，只需要修改存储过程即可，而对调用者的程序完全没有影响。2．调用存储函数通常使用关键字SELECT调用存储函数，其语法形式如下：SELECTfunction_name([parameter[,……]]);【实例13-4】调用实例13-2创建的存储函数func()，查看其返回值。首先登录MySQL，并选择数据库db_shop。然后创建存储过程并定义变量，SQL语句及其执行结果如下：mysql>SELECTfunc();++|func()|++|10|++1rowinset(0.04sec)其返回值同样为10。13.2.4使用图形化工具创建存储过程和函数使用NavicatforMySQL也可以创建存储过程和函数，具体操作如下。步骤

1使用NavicatforMySQL连接MySQL后，双击需要操作的数据库“test_db”，然后单击“函数”按钮。步骤

2单击“新建函数”按钮，选择需要创建的类型，此处选择创建存储过程，如图13-1所示。步骤

3在编辑区填写存储过程需要的参数，单击编辑区左下方的“+”按钮可以添加参数，单击“－”按钮可以删除参数，如果存储过程没有参数，直接单击“完成”按钮即可……（详见教材）【学生】聆听、思考、记录通过教师的讲解和演示，使学生了解存储过程和函数的相关概念，以及创建并调用存储过程和函数的相关知识第二节课问题导入（5min）【教师】提出以下问题：什么是表达式？【学生】思考、举手回答通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（20min）【教师】通过学生的回答引入新知，讲解存储过程和函数的表达式的相关知识13.3关于存储过程和函数的表达式✈【教师】组织学生扫码观看“变量、条件和游标的使用”视频（详见教材），让学生对相关知识有一个大致了解13.2

节介绍了存储过程和函数的创建方法，本节详细介绍存储过程和函数中所包含的表达式语句。此处的表达式同其他高级语言中一样，主要由变量、运算符和流程控制语句构成。13.3.1变量变量是表达式中最基本的元素，可用于存储临时数据。本节简单介绍变量的分类，以及在存储过程和函数中应用变量的方法。1．变量的分类✈【教师】随机邀请学生回答以下问题：变量有哪些类型？✈【学生】聆听、思考、回答✈【教师】总结学生的回答MySQL中的变量可分为以下三类。用户变量（User-DefinedVariables）：带有前缀@，只能被定义它的用户使用，作用于当前整个连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。局部变量（LocalVariables）：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN…END中。其作用域仅限于该语句块，在语句块执行完毕后，局部变量就会被释放。局部变量使用前需要先通过DECLARE声明。如没有声明，则初始值为NULL。系统变量（ServerSystemVariables）：带有前缀@@，MySQL有许多已经设置默认值的系统变量。系统变量包含全局变量和会话变量。全局变量会影响整个服务器，而会话变量只影响个人客户端连接。2．在存储过程和函数中应用变量局部变量可以在子程序中定义并应用，其作用范围是BEGIN…END语句块。（1）定义变量。在存储过程中使用DECLARE语句定义局部变量，其语法形式如下：DECLAREvar_name[,…]type[DEFAULTvalue];上述语句中，var_name为局部变量名称，type为变量的数据类型，DEFAULTvalue是为变量指定的默认值。如果没有DEFAULTvalue，初始值为NULL。例如，定义一个INT类型的变量，名称为var1：DECLAREvar1INT;【提示】变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，DECLARE语句在存储过程和函数中使用时，必须出现在BEGIN…END语句块的最前面，并且变量名不区分大小写。可以一次声明多个相同类型的变量。（2）为变量赋值。定义变量之后，可以使用SET关键字为变量赋值，语法形式如下：SETvar_name=expr[,var_name=expr]…;为前面定义的变量var1赋值，具体如下：SETvar1=3;变量值可以为常量或者表达式。另外，也可以使用SELECT…INTO…查询语句将查询结果赋给变量，这要求查询结果必须只有一行，具体语法形式如下：SELECTcol_name[,……]INTOvar_name[,……]FROMtable_name;col_name为字段名，var_name为变量名。【实例13-5】执行SQL语句，定义变量并为其赋值。首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc1()->BEGIN->DECLAREvar1,var2,var3,g_idINT;->SETvar1=1,var2=2;->SETvar3=var1+var2;->SELECTidintog_idFROMgoodsWHEREid=1;->END$$QueryOK,0rowsaffected(0.10sec)mysql>DELIMITER;13.3.2定义条件和处理程序条件和处理程序是MySQL提供的一种异常处理机制，定义条件是事先定义程序执行过程中可能会遇到的问题；定义处理程序是定义在遇到问题时执行的相应处理方法，并且保证存储过程和函数在遇到问题时不终止。1．定义条件在MySQL中定义条件使用DECLARE…CONDITION语句，其语法形式如下：DECLAREcondition_nameCONDITIONFOR[condition_type];上述语句中，condition_name表示条件名。condition_type表示条件的类型，其可取值及其意义如下：mysql_error_code：表示数值类型错误代码。sqlstate_value：表示长度为5的字符串类型错误代码。下面以名为“not_found_database”的条件“ERROR1049(42000)”的定义为例，来看看这两种形式的区别。使用mysql_error_code形式：DECLAREnot_found_databaseCONDITIONFOR1049;使用sqlstate_value形式：DECLAREnot_found_databaseCONDITIONFORSQLSTATE'42000';【提示】数值类型的错误代码不要使用0，因为0表示成功而不是错误；字符串类型的错误代码不要使用'00'，因为'00'表示成功而不是错误。2．定义处理程序在定义条件之后，可以使用DECLARE…HANDLER语句定义处理程序，语法形式如下：DECLAREhandler_typeHANDLERFORcondition_value[...]statement下面简单介绍上述语句中各组成部分及其意义。（1）handler_type为异常处理方式，可取值及其意义如下：CONTINUE：表示遇到错误不处理，程序继续执行。EXIT：表示遇到错误立即退出程序。UNDO：表示遇到错误后撤回之前的操作，目前MySQL暂不支持该操作。（2）condition_value表示错误值，可取值及其意义如下：mysql_error_code：表示数值类型的错误代码。sqlstate_value：表示包含5个字符的字符串错误值。condition_name：表示使用DECLARE…CONDITION语句定义的条件名。SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。NOTFOUND：匹配所有以02开头的SQLSTATE错误代码。SQLEXCEPTION：匹配所有未被SQLWARNING和NOTFOUND捕获的SQLSTATE错误代码。（3）statement为程序语句段，表示在遇到定义的异常条件时，需要执行的存储过程或函数。定义处理程序有以下6种方法：方法一：捕获sqlstate_valueDECLARECONTINUEHANDLERFORSQLSTATE'42s02'SET@info='cannotfind';意义为，如果遇到sqlstate_value值为'42s02'的异常，执行CONTINUE操作，并输出“cannotfind”信息。方法二：捕获mysql_error_codeDECLARECONTINUEHANDLERFOR1146SET@info='cannotfind';意义为，如果遇到mysql_error_code值为1146的异常，执行CONTINUE操作，并输出“cannotfind”信息。方法三：先定义条件，然后再调用条件DECLAREcan_not_findCONDITIONFOR1146;DECLARECONTINUEHANDLERFORcan_not_findSET@info='cannotfind';意义为，先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。方法四：使用SQLWARNINGDECLAREEXITHANDLERFORSQLWARNINGSET@info='error';意义为，捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并输出“error”信息。方法五：使用NOTFOUNDDECLAREEXITHANDLERFORNOTFOUNDSET@info='cannotfind';意义为，捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并输出“cannotfind”信息。方法六：使用SQLEXCEPTIONDECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='error';意义为，捕获所有没有被SQLWARNING和NOTFOUND捕获的sqlstate_value值，然后执行EXIT操作，并输出“error”信息。【实例13-6】执行SQL语句，在存储过程中定义条件和处理程序，体验异常处理机制在存储过程中的作用。步骤1登录MySQL，并选择任一数据库，之后执行以下SQL语句，创建t_handler表。CREATETABLEt_handler(s1INT,PRIMARYKEY(s1));步骤2在存储过程和函数中定义条件和处理程序，SQL语句及其执行结果如下。mysql>DELIMITER$$mysql>CREATEPROCEDUREhandlerdemo()->BEGIN->DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;->SET@x=1;->INSERTINTOt_handlerVALUES(1);->SET@x=2;->INSERTINTOt_handlerVALUES(1);->SET@x=3;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;步骤3调用存储过程，查看变量x的值……（详见教材）13.3.3游标的使用在存储过程和函数中，当查询语句返回多条记录时，可以使用游标对结果集进行逐条读取。本节介绍定义、打开、使用和关闭游标的方法。1．定义游标在MySQL中，使用DECLARE关键字来定义游标，其语法形式如下：DECLAREcursor_nameCURSORFORselect_statement;上述语句中，cursor_name表示游标名，select_statement表示SELECT语句，返回一个用于创建游标的结果集。2．打开游标打开游标的关键字为OPEN，其语法形式如下：OPENcursor_name;【提示】在打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。3．使用游标使用游标的关键字是FETCH，其语法形式如下：FETCHcursor_nameINTOvar_name[,var_name]…上述语句的作用是将定义游标cursor_name时查询出的数据赋予变量var_name。4．关闭游标关闭游标的关键字为CLOSE，其语法形式如下：CLOSEcursor_name;【实例13-7】创建存储过程，并在存储过程中使用游标。在创建存储过程前首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc9(INflagVARCHAR(10),OUTgnameVARCHAR(30),OUTgpriceDECIMAL(7,2))->BEGIN->DECLAREg_idINT;->DECLAREg_nameVARCHAR(30);->DECLAREg_priceDECIMAL(7,2);->DECLAREg_cursorCURSORFORSELECTid,name,priceFROMdb_shop.goods;->DECLAREEXITHANDLERFORNOTFOUNDCLOSEg_cursor;->OPENg_cursor;->REPEAT->FETCHg_cursorINTOg_id,g_name,g_price;->IFg_id=flagTHEN->SELECTg_name,g_priceINTOgname,gprice;->ENDIF;->UNTILg_id=flag->ENDREPEAT;->CLOSEg_cursor;->END$$QueryOK,0rowsaffected(0.12sec)mysql>DELIMITER;调用存储过程，查看结果：mysql>CALLproc9(5,@gname,@gprice);QueryOK,1rowaffected(0.06sec)mysql>SELECT@gname,@gprice;+++|@gname|@gprice|+++|果汁|2.50|+++1rowinset(0.01sec)13.3.4流程控制的使用✈【教师】组织学生扫码观看“流程控制的使用”视频（详见教材），让学生对相关知识有一个大致了解流程控制语句是指可以控制程序运行顺序的指令，程序运行顺序主要包括顺序执行、条件执行和循环执行。MySQL支持的流程控制语句包括IF语句、CASE语句、LOOP语句、REPEAT语句、WHILE语句、LEAVE语句、ITERATE语句和RETURN语句，下面逐一介绍。1．IF语句IF实现条件判断，语句中可以包含多个判断条件，系统会根据条件的结果是否为TRUE执行相应的操作，语法形式如下：IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF上述语句中，search_condition为判断条件，statement_list为相应操作，如果所有判断条件均不为TRUE，则执行ELSE子句中的操作。2．CASE语句CASE语句可以实现比IF语句更复杂的条件构造，该语句有两种使用形式。第1种语法形式如下：CASEcase_exprWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASE上述语句中，case_expr表示判断条件的表达式，将此表达式与每个WHEN子句中的when_value值进行比较，直到与其中一个相等，此时，执行相应THEN子句中的statement_list。如果表达式与所有when_value值都不相等，则执行ELSE子句中的statement_list。CASE语句的第2种语法形式如下：CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE上述语句中，系统会对每个WHEN子句中的search_condition表达式进行判断，直到某个search_condition表达式为TRUE，此时将执行其对应的THEN子句中的statement_list。如果所有search_condition表达式的值都不为TRUE，则执行ELSE子句中的statement_list。3．LOOP语句和LEAVE语句LOOP语句可以实现简单的循环，使得系统能够重复执行循环结构内的语句列表。该语句列表由一条或多条语句组成，每条语句使用分号（;）隔开。语法形式如下：[loop_label:]LOOPstatement_listENDLOOP[end_label]上述语句中，loop_label表示LOOP语句的标注名称（可以省略），statement_list表示需要循环执行的SQL语句。如果不在statement_list中增加退出循环的语句，LOOP语句可以实现简单的死循环。使用LEAVE语句可以退出循环。语法形式如下：LEAVElabel;其中，label参数表示循环的标注名。4．REPEAT语句REPEAT语句可以实现一个带条件判断的循环结构。语法形式如下：[repeat_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[repeat_label]repeat_label表示REPEAT语句的标注名称（可以省略），每次SQL语句statement_list执行完毕后，会对条件search_condition进行判断，如果结果为TRUE，循环终止，否则继续执行循环中的语句。5．WHILE语句WHILE语句同样可以实现一个带条件判断的循环结构，但与REPEAT语句不同的是，WHILE语句会先对条件进行判断，如果为TRUE，才会执行需要循环的操作，否则终止循环，语法形式如下：[while_label:]WHILEsearch_conditionDOstatement_listENDWHILE[while_label]上述语句中，while_label为WHILE语句的标注名称，search_condition为判断条件，statement_list为需要循环的操作。6．ITERATE