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

PAGE6PAGE6PAGE5PAGE5

课题存储过程和函数（二）课时2课时（90min）教学目标知识技能目标：（1）掌握查看存储过程和函数的方法（2）掌握修改及删除存储过程和函数的方法素质目标：（1）培养探究意识（2）了解数据库的发展及主流国产数据库的应用，增强民族自豪感教学重难点教学重点：掌握查看存储过程和函数的方法教学难点：掌握修改及删除存储过程和函数的方法教学方法案例分析法、问答法、讨论法、讲授法、实践法教学用具电脑、投影仪、多媒体课件、教材教学设计第1节课：→→→传授新知（38min）第2节课：→传授新知（20min）→课堂实训（15min）→课堂小结（3min）→作业布置（2min）教学过程主要教学内容及步骤设计意图第一节课课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，预习本节课要学习的知识【学生】完成课前任务通过课前任务，使学生预习本节课要学习的知识，增加学生的学习兴趣考勤（2min）【教师】使用文旌课堂APP进行签到【学生】班干部报请假人员及原因培养学生的组织纪律性，掌握学生的出勤情况问题导入（5min）【教师】提出以下问题：创建完存储过程和函数后该怎么查看呢？·通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（38min）【教师】通过学生的回答引入要讲的知识，讲解查看存储过程和函数的相关知识13.4查看存储过程和函数✈【教师】组织学生扫码观看“查看与编辑存储过程和函数”视频（详见教材），让学生对相关知识有一个大致了解创建完存储过程和函数后，MySQL存储了其状态信息和定义语句，用户可以分别使用SHOWSTATUS和SHOWCREATE语句进行查看，也可以在系统数据库information_schema中查看。13.4.1查看存储过程和函数的状态使用SHOWSTATUS语句可以查看存储过程和函数的状态。基本语法形式如下：SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pf_name'];PROCEDURE或FUNCTION指定查看的是存储过程还是函数，LIKE语句指定存储过程和函数的名称。【实例13-9】执行SQL语句，查看存储过程proc的基本信息。首先登录MySQL，然后执行SHOWSTATUS语句，执行结果如下：mysql>SHOWPROCEDURESTATUSLIKE'proc'\G***************************1.row***************************Db:db_shopName:procType:PROCEDUREDefiner:root@localhostModified:2018-06-0915:06:48Created:2018-06-0915:06:48Security_type:DEFINERComment:character_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.02sec)其中的主要参数及其意义如下：Db：表示存储过程或函数所属数据库。Name：表示存储过程或函数名。Type：表示是存储过程还是函数。Definer：表示创建存储过程或函数的用户。Modified：表示最后修改日期。Created：表示创建日期。Security_type：表示MySQL在执行存储过程和函数的时候，是以创建用户的权限来执行，还是以调用者的权限来执行。13.4.2查看存储过程和函数的定义使用SHOWCREATE语句可以查看存储过程和函数的定义语句，语法形式如下：SHOWCREATE{PROCEDURE|FUNCTION}pf_name;PROCEDURE或FUNCTION指定查看的是存储过程还是函数，pf_name指定存储过程或函数名。【实例13-10】执行SQL语句，查看存储过程proc的定义语句。首先登录MySQL，然后执行SHOWCREATE语句，执行结果如下：mysql>SHOWCREATEPROCEDUREdb_c\G***************************1.row***************************Procedure:procsql_mode:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONCreateProcedure:CREATEDEFINER=`root`@`localhost`PROCEDURE`proc`(OUTnumINT)BEGINSELECTCOUNT(*)INTOnumFROMgoods;ENDcharacter_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.00sec)其中的主要参数及其意义如下：Procedure：表示存储过程名。sql_mode：表示SQL语句的模式。CreateProcedure：表示存储过程的定义语句。13.4.3查看存储过程和函数的信息在MySQL中，存储过程和函数的信息存储在系统数据库information_schema中的routines表中，查看存储过程和函数详细信息的语法形式如下：SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAME='pf_name';上述语句中，ROUTINE_NAME指定存储过程或函数名，如果有存储过程和存储函数名称相同，还可以使用ROUTINE_TYPE指定类型。【实例13-11】执行SQL语句，查看存储过程proc的详细信息。登录MySQL，并执行SQL语句，执行结果如下：mysql>SELECT*FROMinformation_schema.routinesWHEREROUTINE_NAME='proc'ANDROUTINE_TYPE='PROCEDURE'\G***************************1.row***************************SPECIFIC_NAME:procROUTINE_CATALOG:defROUTINE_SCHEMA:db_shopROUTINE_NAME:procROUTINE_TYPE:PROCEDUREDATA_TYPE:CHARACTER_MAXIMUM_LENGTH:NULLCHARACTER_OCTET_LENGTH:NULLNUMERIC_PRECISION:NULLNUMERIC_SCALE:NULLDATETIME_PRECISION:NULLCHARACTER_SET_NAME:NULLCOLLATION_NAME:NULLDTD_IDENTIFIER:NULLROUTINE_BODY:SQLROUTINE_DEFINITION:BEGINSELECTCOUNT(*)INTOnumFROMgoods;ENDEXTERNAL_NAME:NULLEXTERNAL_LANGUAGE:NULLPARAMETER_STYLE:SQLIS_DETERMINISTIC:NOSQL_DATA_ACCESS:CONTAINSSQLSQL_PATH:NULLSECURITY_TYPE:DEFINERCREATED:2018-06-0915:06:48LAST_ALTERED:2018-06-0915:06:48SQL_MODE:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONROUTINE_COMMENT:DEFINER:root@localhostCHARACTER_SET_CLIENT:gbkCOLLATION_CONNECTION:gbk_chinese_ciDATABASE_COLLATION:utf8_general_ci1rowinset(0.05sec)其中的主要参数及其意义如下：ROUTINE_CATALOG：表示存储过程或函数的目录。ROUTINE_SCHEMA：表示存储过程或函数所属数据库。ROUTINE_NAME：表示存储过程或函数名。ROUTINE_TYPE：表示是存储过程还是存储函数。ROUTINE_DEFINITION：表示BEGIN…END语句。SECURITY_TYPE：表示MySQL在执行存储过程和函数的时候，是以创建用户的权限来执行，还是以调用者的权限来执行。【学生】聆听、思考、记录通过教师的讲解和演示，使学生了解查看存储过程和函数的相关知识第二节课问题导入（5min）【教师】提出以下问题：存储过程和函数创建完毕后发现出错该怎么进行修改或删除呢？【学生】思考、举手回答通过问题导入，引导学生主动思考，激发学生的学习兴趣传授新知（20min）【教师】通过学生的回答引入新知，介绍修改和删除存储过程和函数的相关知识13.5修改和删除存储过程和函数13.5.1修改存储过程和函数在MySQL中，使用ALTER关键字可以修改存储过程和函数，基本语法形式如下：ALTER{PROCEDURE|FUNCTION}pf_name[characteristic…];上述语句中，pf_name表示存储过程或函数名。characteristic表示存储过程和函数的特性，其可取值有CONTAINSSQL，NOSQL，READSSQLDATA，MODIFIESSQLDATA，SQLSECURITY{DEFINER|INVOKER}，各值的意义与创建存储过程和函数时相同。【提示】修改存储过程使用ALTERPROCEDURE语句，修改存储函数使用ALTERFUNCTION语句，这两个语句结构相同，参数也一样。并且它们与创建存储过程和函数的语句中的参数也基本一样。【实例13-12】执行SQL语句，修改存储过程proc的读写权限和安全类型。首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：mysql>ALTERPROCEDUREprocMODIFIESSQLDATASQLSECURITYINVOKER;QueryOK,0rowsaffected(0.09sec)查看存储过程修改后信息，SQL语句及其执行结果如下：mysql>SELECTSPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE->FROMinformation_schema.routines->WHEREROUTINE_NAME='proc'ANDROUTINE_TYPE='PROCEDURE';++++|SPECIFIC_NAME|SQL_DATA_ACCESS|SECURITY_TYPE|++++|proc|MODIFIESSQLDATA|INVOKER|++++1rowinset(0.12sec)由查询结果可以看出，存储过程修改成功。【提示】不能使用关键字ALTER更改存储过程的参数或子程序，如果需要修改，必须删除存储过程后再重新创建。13.5.2删除存储过程和函数在MySQL中，删除存储过程和函数可以使用DROP语句，语法形式如下：DROP{PROCEDURE|FUNCTION}[IFEXISTS]pf_name;pf_name为要删除的存储过程或函数名。使用IFEXISTS可以在执行删除操作时，先判断存储过程和函数是否存在，避免系统报错。【实例13-13】执行SQL语句，删除存储过程proc。首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：mysql>DROPPROCEDUREIFEXISTSproc;QueryOK,0rowsaffected(0.03sec)13.5.3使用图形化工具查看和编辑存储过程和函数使用NavicatforMySQL可以非常方便地查看、修改和删除存储过程和函数。具体操作如下：步骤

1使用NavicatforMySQL连接MySQL后，双击需要操作的数据库，此处为“db_shop”。步骤

2单击“函数”按钮，然后右击需要查看信息的存储过程或函数，选择“对象信息”项步骤3“常规”选项卡内显示存储过程和函数相关的参数信息，“DDL”选项卡内显示存储过程和函数的创建语句……（详见教材）【学生】聆听、理解、记录通过教师的讲解和演示，使学生了解修改和删除存储过程和函数的相关知识课堂实训（15min）【教师】组织学生以小组为单位，根据步骤提示完成以下实训内容前面介绍了存储过程和函数的相关概念，以及创建、查看、修改和删除存储过程和函数的方法。下面通过创建并应用存储过程和函数，让读者重温本章