《MySQL数据库应用》课件-项目七　存储过程与存储函数

项目七存储过程与存储函数262263任务1创建和调用存储过程任务2创建存储函数任务3使用变量及流程控制语句任务4修改和删除存储过程与存储函数创建和调用存储过程任务12641. 了解存储过程的概念。2. 能创建存储过程。3. 能调用存储过程。4. 能使用游标。265266存储过程是一组预定义的SQL语句与控制逻辑的集合，作为数据库服务器端程序运行，功能远超简单的批处理。对数据库进行复杂操作时，可将这些复杂操作用存储过程封装起来，与数据库提供的事务处理结合在一起使用。本任务要求通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，创建一个名为“everyone_grade”的存储过程，如图所示。该存储过程能实现查找所有学生的成绩，并通过调用语句执行该存储过程。267创建存储过程268一、存储过程的概念在大型数据库系统中，存储过程是一组为了完成特定功能的SQL语句集，并以特定的名字存储在数据库中。存储过程经过第一次编译后调用时不需要再次编译，用户可通过指定存储过程的名称和参数来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都会用到存储过程。二、存储过程的创建使用存储过程将简化操作，减少多余的操作步骤，同时，还可以减少操作过程中的失误，提高效率。在MySQL中，使用CREATE PROCEDURE语句创建存储过程，需配合DELIMITER修改语句结束符（避免分号冲突），基本的SQL语法格式如下。269三、存储过程的调用在MySQL中，可使用CALL语句调用已创建的存储过程，其基本SQL语法格式如下。调用存储过程时，若已创建的存储过程中没有设置参数，调用存储过程同样不需要参数。270四、游标的使用在MySQL中，查询语句常返回多行数据，在存储过程或函数中，可以使用游标来逐个读取查询结果集中的记录。通过控制游标的移动，用户可以逐行查看和操作这些数据，但不能跳过任何记录。1. 声明游标在MySQL中，使用DECLARE语句来声明游标，具体的SQL语法格式如下。2712. 打开游标可使用关键字OPEN打开已声明的游标，该语句会将游标与相应的查询结果集关联起来，以便对查询结果集中的每条记录进行处理。如果游标未被声明，则OPEN语句会报错。具体的SQL语法格式如下。3. 使用游标使用游标的具体SQL语法格式如下。2724. 关闭游标使用CLOSE语句可关闭已使用的游标，具体的SQL语法格式如下。如果游标未打开，则使用该语句会报错。需要注意的是，如果游标未被明确地关闭，程序执行到END语句时会自动关闭游标，释放占用的资源和内存。273创建存储函数任务22741. 了解存储函数的概念。2. 能创建存储函数。3. 能调用存储函数。275276存储函数是MySQL提供的一种机制，它可以用来存储并重用在SQL查询中使用的常用逻辑或计算。通过存储函数，能在内置函数的基础上，以更简洁的方式实现个性化的数据处理。本任务要求通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，创建一个名为“s_find”的存储函数，如图所示。通过该存储函数，可以在输入学生的学号后，在表“tb_student”中查询学生的姓名，若学生存在，则返回该学生的姓名；若学生不存在，则返回“查无此人”。277创建存储函数278一、存储函数的概念在MySQL中，存储函数是用户自定义的SQL代码块，用于扩展内置函数的功能。类似于存储过程，存储函数封装了一系列SQL语句，以执行特定的数据库操作或计算，两者的区别在于：存储函数必须使用RETURN子句返回单一值，而存储过程无须强制返回值，需使用CALL语句单独调用，不能在表达式中直接使用。二、存储函数的创建存储函数是用户自定义的函数，先定义后调用。在MySQL中，使用CREATE FUNCTION语句创建，其基本的SQL语法格式如下。279三、存储函数的调用在MySQL中，存储函数的使用方法与MySQL内置函数的使用方法是一样的，都需使用SELECT语句调用已创建的存储函数，其基本的SQL语法格式如下。280使用变量及流程控制语句任务32811. 掌握变量的使用方法。2. 了解流程控制语句的分类。3. 掌握流程控制语句的使用方法。4. 能使用流程控制语句对数据表进行复杂的查询操作。282在MySQL中，变量用于存储中间结果或临时数据，流程控制语句用于实现复杂逻辑。通过变量和流程控制语句，可增强存储过程的灵活性。283本任务要求通过Navicat，在教学管理系统数据库“schoolsys”

中使用CREATE PRO-CEDURE语句创建一个名为“s_n”的存储过程，使用关键字IN声明输入参数“n”（数据类型INT），使用关键字DECLARE声明变量“sum”和“num”，并使用关键字SET为变量赋值，使存储过程“s_n”能计算1至任意数的累加和，如图所示。284创建存储过程，求1至任意数的累加和一、变量的使用在MySQL中，除了支持标准的存储过程和存储函数外，还引入了表达式。MySQL中的表达式与其他高级语言的表达式一样，由操作数和运算符构成。变量是操作数的重要表示方式，可以用来临时存储数据。在存储过程和函数中都可以定义及使用变量。变量主要分为系统变量、用户变量和局部变量。2851. 系统变量系统变量由MySQL服务器维护，用于控制服务器的行为和配置，系统变量分为全局变量和会话变量，通常由数据库管理员设置，以优化性能或调整服务器行为，其基本SQL语法格式如下。2862. 用户变量用户变量是由用户自己定义和管理的变量，作用范围仅限于当前客户端连接（即会话），用户变量的名字以 @ 开头，允许在同一会话中的多个语句之间存储和传递值，用户变量不需要显式声明，可以直接赋值并使用，其基本SQL语法格式如下。2873. 局部变量局部变量是在存储过程或函数内部声明的变量，作用范围仅限于声明的过程或函数体内，局部变量需要显式声明，并且可以用于保存临时数据或控制流程逻辑，其基本SQL语法格式如下。288二、流程控制语句的概念流程控制语句用于控制存储过程中SQL语句的执行顺序，是完成复杂操作必不可少的一部分。MySQL中的流程控制语句可以分为三类，其中IF、CASE语句为条件判断语句，LOOP、REPEAT、WHILE语句为循环语句，LEAVE、ITERATE语句为跳转语句。每个流程控制语句中可能包含一个单独语句，也可以使用BEGIN...END语句组合的语句块。2891. IF语句IF语句用来进行条件判断，根据变量是否满足条件（可包含多个条件）来执行不同的语句，是流程控制语句中最常用的判断语句。ELSEIF语句用于对多个条件判断语句进行判断，产生不同分支，输出不同结果。2902. CASE语句CASE语句也用来进行条件判断，与IF语句不同的是，CASE语句提供了多个条件进行选择，可以实现比IF语句更复杂的条件判断。3. LOOP语句LOOP语句可以使某些特定的语句重复执行，是不含条件判断的简单循环。LOOP语句内的子句一直重复执行，直到循环被LEAVE语句退出，则跳出循环。2914. LEAVE语句LEAVE语句主要用于跳出循环控制，可以用在循环语句内，也可以用在以BEGIN...END语句包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。5. ITERATE语句ITERATE为“再次循环”的意思，只能用在循环语句（LOOP、REPEAT和WHILE）内，作用是跳过当前循环的剩余语句，直接回到循环开始处重新执行。2926. REPEAT语句REPEAT语句是有条件控制的循环语句，每次语句执行完毕，会对条件表达式进行判断，如果表达式返回值为TRUE，则循环结束，否则重复执行循环中的语句。7. WHILE语句WHILE语句也是有条件控制的循环语句。WHILE语句和REPEAT语句不同的是，WHILE语句是满足条件时执行循环内的语句，否则退出循环。293三、流程控制语句的使用1. IF语句IF语句可根据表达式的结果为TRUE或FALSE执行相应的语句，其具体的SQL语法格式如下。2942. CASE语句CASE语句是条件判断语句，可同时进行多个条件的判断。CASE语句有两种格式：第一种为固定值条件判断，其具体的SQL语法格式如下。295第二种为非固定值条件判断，其具体的SQL语法格式如下。2963. LOOP语句LOOP循环语句可用来重复执行某些语句，与IF语句和CASE语句相比，LOOP语句只是创建一个循环操作的过程，并不进行条件判断。LOOP语句内的子句会一直重复执行直到循环被退出，所以LOOP语句需要通过与IF语句或CASE语句、LEAVE语句或ITERATE语句结合，构成一个完整的循环。LEAVE语句用来退出任何被标注的流程控制结构，常用于结束循环。ITERATE语句只能出现在LOOP语句、REPEAT语句和WHILE语句内。LOOP语句的具体SQL语法格式如下。2972984. WHILE语句WHILE语句用于创建一个带条件判断的循环过程，与REPEAT语句不同，WHILE语句在执行循环语句前先对指定的条件表达式进行判断，如果结果为TRUE，就执行循环内的语句，否则退出循环。其具体的SQL语法格式如下。2995. REPEAT语句REPEAT语句用于创建一个带条件判断的循环过程，每次循环语句执行完毕会对条件表达式进行判断，如果结果为TRUE，则循环结束，否则重复执行循环中的语句。其具体的SQL语法格式如下。3006. LEAVE语句使用LEAVE语句，可以在循环过程中提前退出循环，它可以用在循环语句内，也可以用在以 BEGIN...END 语句包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。其具体的SQL语法格式如下。3017. ITERATE语句使用ITERATE语句，可以在循环语句中提前跳出接下来的循环语句，进行下次循环，它可以用在循环语句内，也可以用在以 BEGIN...END 语句包裹起来的程序体内，跳过当前循环的剩余语句，直接回到循环开始处重新执行。其具体的SQL语法格式如下。302修改和删除存储过程与存储函数任务43031. 了解修改存储过程与存储函数特性的方法。2. 掌握查看存储过程与存储函数的操作步骤。3. 能删除存储过程与存储函数。304修改存储过程与存储函数主要是指修改它们的特性，例如修改访问权限等。修改存储过程与修改存储函数、删除存储过程与删除存储函数，这两类操作方法高度相似，因此，本任务只介绍修改和删除存储过程的操作。305本任务通过Navicat，在教学管理系统数据库“schoolsys”中使用SQL语句，将存储过程“everyone_grade”的特性修改为“MODIFIES SQL DATA”，如图所示，随后使用关键字DROP删除该存储过程。306修改存储过程一、修改存储过程与存储函数的特性1. 存储过程与存储函数的特性在MySQL中，存储过程与存储函数的特性用于设置其访问权限和规定程序中对SQL语句的操作权限，存储过程与存储函数的特性及功能见下表。307308存储过程与存储函数的特性及功能2. 修改存储过程的特性使用ALTER语句可以修改存储过程的特性，其具体的SQL语法格式如下。3. 修改存储函数的特性和修改存储过程特性相同，使用ALTER语句可以修改存储函数的特性，其具体的SQL语法格式如下。309二、查看存储过程与存储函数1. 使用SHOW PROCEDURE|FUNCTION STATUS语句查看存储过程与存储函数（1）查看存储过程使用SHOW PROCEDURE STATUS语句可以查看存储过程的状态，该语句用于返回子程序的特征。使用SHOW PROCEDURE STATUS语句查看存储过程的SQL语法格式如下。310（2）查看存储函数查看存储函数状态的语法与查看存储过程状态的语法基本相同，使用SHOW FUNCTION STATUS语句。其具体的SQL语法格式如下。3112. 使用SHOW CREATE语句查看存储过程与存储函数（1）查看存储过程除了SHOWPROCEDURESTATUS