第8章 存储过程和存储函数

第8章

存储过程和存储函数1.存储过程和存储函数概述2.创建存储过程和存储函数3.调用存储过程和存储函数4.查看存储过程和存储函数5.修改存储过程和存储函数6.删除存储过程和存储函数本章内容1.存储过程和存储函数概述存储过程和存储函数的概念MySQL的存储过程和存储函数是一种保存在数据库服务器上、可以用来执行特定工作的一组SQL代码的程序段。存储过程和函数可以包含针对数据库操作的SQL语句，还可以在其内部进行流程控制，而且执行速度快，所以在数据库应用开发中广泛使用。1.存储过程和存储函数概述存储过程和存储函数的优点存储过程和存储函数允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。存储过程和存储函数是在MySQL服务器上执行的，执行速度快、网络通信流量小。存储过程和存储函数可以作为一种安全机制来利用，其权限可以与数据表的权限不同，保证数据的安全性。2.创建存储过程和存储函数（1）使用SQL语句创建存储过程创建存储过程使用CREATEPROCEDURE语句，其语法格式如下：CREATEPROCEDURE<存储过程名>([<参数>[,…n]])[characteristic…]<存储过程体>2.创建存储过程和存储函数（1）使用SQL语句创建存储过程示例8-1：创建一个不带有参数的存储过程up_studentInfo，查询出所有1994年9月之前出生的学生名单。示例8-2：创建一个带有输入参数的存储过程up_deptStudentInfo，通过一个给定的班级，查询出该班级的所有学生记录。示例8-3：创建一个带有输入和输出参数的存储过程up_scoreGradeInfo，通过一个给定的学号，查询出该学生选修课程的数量及平均分，并通过输出参数返回。2.创建存储过程和存储函数（2）使用SQL语句创建存储函数存储函数即用户自定义函数，创建存储函数使用CREATEFUNCTION语句，其语法格式如下：CREATEFUNCTION<函数名>([<参数>[,…n]])RETURNS<数据类型>[characteristic…]<函数体>2.创建存储过程和存储函数（2）使用SQL语句创建存储函数示例8-4：创建一个函数func_getStudentName，通过一个给定的学号，返回该学生的姓名。2.创建存储过程和存储函数（3）局部变量的使用定义变量DECLARE<变量名>[,…]<数据类型>[DEFAULT默认值]给变量赋值SET<变量名1>=<赋值表达式1>[,<变量名2>=<赋值表达式2>,…]SELECT<字段名>[,…]INTO<变量名>[,…] FROM<表名>WHERE<查询条件>2.创建存储过程和存储函数（4）定义条件和处理程序定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。2.创建存储过程和存储函数（5）流程控制语句的使用IF语句IF<条件表达式1>THEN<语句块1>[ELSEIF<条件表达式2>THEN<语句块2>]…[ELSE<语句块n>]ENDIF2.创建存储过程和存储函数（5）流程控制语句的使用示例8-5：创建一个存储过程up_scoreStateInfo，通过一个给定的学号和课程号，查询出该学生指定课程的成绩，如果成绩合格，则返回1；否则返回0。2.创建存储过程和存储函数（5）流程控制语句的使用CASE语句CASE<表达式名称>WHEN<表达式值1>THEN<结果1>[WHEN<表达式值2>THEN<结果2>]…[ELSE<结果n>]END[CASE]CASEWHEN<条件表达式1>THEN<结果1>[WHEN<条件表达式2>THEN<结果2>]…[ELSE<结果n>]END[CASE]2.创建存储过程和存储函数（5）流程控制语句的使用示例8-6：改写示例8-5中的存储过程up_scoreStateInfo，使用CASE语句来实现。示例8-7：查询成绩表（score），输出学号、课程编号、成绩以及成绩等级。示例8-8：查询学生表（student），输出学号、姓名、性别和班级，要求将性别“男”替换为“♂”、性别“女”替换为“♀”。2.创建存储过程和存储函数（5）流程控制语句的使用WHILE语句[label:]WHILE<条件表达式>DO <语句块>ENDWHILE[label]2.创建存储过程和存储函数（5）流程控制语句的使用示例8-9：创建一个函数func_sum，用来计算1+2+3+…+n的和。2.创建存储过程和存储函数（5）流程控制语句的使用REPEAT语句[label:]REPEAT <语句块> UNTIL<条件表达式>ENDREPEAT[label]2.创建存储过程和存储函数（5）流程控制语句的使用示例8-10：改写示例8-9中的函数func_sum，使用REPEAT语句来实现。2.创建存储过程和存储函数（5）流程控制语句的使用LOOP语句[label:]LOOP <语句块> [LEAVElabel]ENDLOOP[label]2.创建存储过程和存储函数（5）流程控制语句的使用示例8-11：改写示例8-9中的函数func_sum，使用LOOP和LEAVE语句来实现。2.创建存储过程和存储函数（5）流程控制语句的使用ITERATE语句ITERATElabel 2.创建存储过程和存储函数（5）流程控制语句的使用示例8-12：创建一个函数func_sum1，用来计算1+2+3+…+n的和，但不包括同时能被3和7整除的数。使用WHILE和ITERATE语句来实现。2.创建存储过程和存储函数（6）游标的使用游标是一种可以对查询结果集进行按行处理的数据结构。在存储过程中，可以把查询结果保存到游标中，并可对结果集中的数据逐行地进行处理。游标中的数据保存在内存中，从其中提取数据的速度要比从数据表中直接提取数据的速度快得多。游标的使用包括声明游标、打开游标、读取游标、关闭游标。2.创建存储过程和存储函数（6）游标的使用声明游标DECLARE<游标名称>CURSORFOR<select语句>2.创建存储过程和存储函数（6）游标的使用打开游标OPEN<游标名称>2.创建存储过程和存储函数（6）游标的使用读取游标FETCH<游标名称>INTO<变量名1>[,<变量名2>…]2.创建存储过程和存储函数（6）游标的使用关闭游标CLOSE<游标名称>2.创建存储过程和存储函数（6）游标的使用示例8-13：创建一个存储过程up_getStuAvgGrade，通过游标操作来计算某一学生的平均成绩。3.调用存储过程和存储函数（1）调用存储过程调用存储过程使用CALL语句。调用后，数据库系统将执行存储过程中的语句。其语法格式如下：CALL<存储过程名>([<实际参数值>[,….]])3.调用存储过程和存储函数（1）调用存储过程示例8-14：调用示例8-2中的存储过程up_deptStudentInfo。示例8-15：调用示例8-3中的存储过程up_scoreGradeInfo。示例8-16：调用示例8-6中的存储过程up_scoreStateInfo。示例8-17：调用示例8-13中的存储过程up_getStuAvgGrade。3.调用存储过程和存储函数（2）调用存储函数在MySQL中，调用存储函数与使用内部函数的方法是一样的。3.调用存储过程和存储函数（2）调用存储函数示例8-18：调用示例8-4中的存储函数func_getStudentName。示例8-19：调用示例8-11中的存储函数func_sum。示例8-20：调用示例8-12中的存储函数func_sum1。4.查看存储过程和存储函数使用SHOWSTATUS语句查看存储过程和存储函数的状态SHOWPROCEDURESTATUS[LIKE'存储过程名']SHOWFUNCTIONSTATUS[LIKE'存储函数名']4.查看存储过程和存储函数示例8-21：使用SHOWSTATUS语句查看存储过程up_scoreGradeInfo的状态。4.查看存储过程和存储函数使用SHOWCREATE语句查看存储过程和存储函数的定义SHOWCREATEPROCEDURE<存储过程名>SHOWCREATEFUNCTION<存储函数名>4.查看存储过程和存储函数示例8-22：使用SHOWCREATE语句查看存储过程up_scoreGradeInfo的定义。4.查看存储过程和存储函数在routines表中查看存储过程和存储函数详细信息SELECT*FROMinformation_schema.routines [WHEREROUTINE_NAME='存储过程名']SELECT*FROMinformation_schema.routines [WHEREROUTINE_NAME='存储函数名']4.查看存储过程和存储函数示例8-23：使用SELECT语句查询routines表中的信息。5.修改存储过程和存储函数（1）修改存储过程修改存储过程使用ALTERPROCEDURE语句，其语法格式如下：ALTERPROCEDURE<存储过程名>[characteristic…]5.修改存储过程和存储函数（1）修改存储过程示例8-24：修改示例8-2中存储过程up_deptStudentInfo的定义，将读写权限更改为MODIFIESSQLDATA。5.修改存储过程和存储函数（2）修改存储函数修改存储函数使用ALTERFUNCTION语句，其语法格式如下：ALTERFUNCTION<存储函数名>[characteristic…]5.修改存储过程和存储函数（2）修改存储函数示例8-25：修改示例8-4中存储函数func_getStudentName的定义，将读写权限更改为READSSQLDATA，并加上注释信息“根据学号查找学生姓名”。6.删除存储过程和存储函数（1）删除存储过程删除存储过程使用DROPPROCEDURE语句，其语法格式如下：DROPPROCEDURE<存储过程名>6.删除存储过程和存储函数（2）删除存储过程示例8-26：删除示例8-1中的存储过程up_studentInfo。6.删除存储过程和存储函数（2）删除存储函数删除存储函数使用DROPFUNCTION语句，其语法格式如下：DROPFUNCTION<存储函数名>6.删除存储过程和存储函数（2）删除存储函数示例8-27：删除示例8-12中的存储函数func_sum1。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/