MySQL数据库教程（第2版）-课件 第8章数据库编程技术

第8章

数据库编程技术【学习目标】理解常量和变量的概念。掌握常用的系统内置函数和流程控制语句。能够编写简单的存储过程、存储函数和触发器。了解游标的使用和事件的创建方法。8.1编程基础知识

8.1.1常量和变量1．常量常量是指在程序中可以直接引用的量，其值在程序运行期间保持不变，它的表示形式决定了其数据类型。常量可分为数值常量、字符串常量、日期时间常量、布尔常量和NULL值常量等。（1）数值常量数值常量由数字组成，可以分成整数常量和实数常量。①

整数常量，如156、-100。②

实数常量，如3.14、-100.23（2）字符串常量字符串常量是用单引号或双引号括起来的字符序列，如'HELLO'、″数据库系统″。（3）日期和时间常量日期常量是用单引号或双引号括起来的，包括年、月、日，并按照“年-月-日”的顺序表示日期，如'2026-01-01'。时间常量包括时、分、秒，并按照“时:分:秒”的顺序构成，如'10:20:35'。（4）布尔常量布尔常量只有TURE和FALSE两个值。TURE对应的数值为“1”；FALSE对应的数值为“0”。（5）NULL值NULL值适用于各种类型，表示没有值或无数据，不等价于空字符串或数据0值。变量是指在程序运行期间取值可以变化的量，用于临时存储数据，变量中的数据随着程序的运行而变化。一个变量有2个基本要素：变量名和变量的数据类型。每个变量都用唯一的变量名来标识，用户可以通过变量名来访问内存中的数据，变量的数据类型决定了变量的值和对应的运算。MySQL变量可分为用户变量、系统变量和局部变量。变量不区分大小写，例如@name、@Name、@NAME表示同一个变量。2.变量（1）用户变量用户使用变量定义语句定义的变量称为用户变量。用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有用户变量将自动释放。①SET语句SET语句用于定义和初始化用户变量，其语法格式如下。SET@用户变量1=表达式1[,@用户变量2=表达式2…];说明：

在用户变量前添加“@”符号，便于区分变量名和字段名称。

用户变量名可以包含字母、数字和符号（“.”“_”“$”）。

定义多个用户变量时，每个用户变量之间用英文逗号分隔。

表达式是赋给用户变量的值，可以是常量、变量或计算表达式。【例8-1】使用用户变量查询王刚同学所在的院系名称。利用用户变量@deptno将值从一条语句传递到另一条语句。利用用户变量@deptno可以将值从一条语句传递到另一条语句。第一步：在学生表student中查询到王刚同学所在的院系代码deptno字段值，并存储在用户变量@deptno中。mysql>SET@deptno=(SELECTdeptnoFROMstudentWHEREstuname='王刚');第二步：在院系表department中查询等于用户变量@deptno的院系代码deptno字段和院系名称deptname字段。mysql>SELECTdeptno,deptnameFROMdepartmentWHEREdeptno=@deptno;②SELECT语句SELECT语句中必须用:=给用户变量赋值，其语法格式如下。SELECT@用户变量1:=表达式1[,@用户变量2:=表达式2…]FROM表名[WHERE条件表达式];【例8-2】统计学生表中入学总分的最高分和最低分，分别存入用户变量@max和@min。mysql>SELECT@max:=MAX(enterscore),@min:=MIN(enterscore)FROMstudent;【例8-3】查询学生表student中的学生信息行号（按照入学总分从高到低排序）、姓名和入学总分。mysql>SET@row_number=0;mysql>SELECT(@row_number:=@row_number+1)AS行号,stunameAS姓名,enterscoreAS入学总分

->FROMstudentORDERBYenterscoreDESC;③

SELECT…INTO语句SELECT…INTO语句将查询到的一行结果中的字段值赋给对应的用户变量。语法格式如下。SELECT字段名称1[,字段名称2…]INTO@用户变量1[,@用户变量2…]FROM表名WHERE条件表达式;【例8-4】将学号stuno为20241010105的学生的姓名stuname和入学总分enterscore分别存入用户变量@name和@score中。mysql>SELECTstuname,enterscoreINTO@name,@scoreFROMstudentWHEREstuno='20241010105';（2）系统变量系统变量是MySQL的一些特殊设置，当MySQL数据库服务器启动时，初始化这些变量为默认值。大多数系统变量名称前都需要加两个@，某些特定的系统变量不加这两个@，如CURRENT_DATE（当前系统日期）、CURRENT_TIME（当前系统时间）、CURRENT_USER（当前用户名称）等。【例8-5】查询当前系统日期和使用的MySQL的版本信息。mysql>SELECTCURRENT_DATEAS当前日期,@@VERSIONAS当前版本;（3）局部变量局部变量的作用范围是BEGIN

END语句块中，用来存放存储过程体中的临时结果。局部变量只能定义在存储过程、存储函数和触发器中。局部变量和用户变量的主要区别：作用范围不同，用户变量存在于整个会话中，局部变量只存在于BEGIN

END语句块中。用户变量前有@符号，局部变量前没有@符号。①

局部变量的声明。可以使用DECLARE语句声明局部变量，并给局部变量赋初值，其语法格式如下。DECLARE局部变量名1[,局部变量名2…]数据类型[DEFAULT默认值];例如，在存储过程中，声明局部变量v_enterscore和n，数据类型均为INT，默认值为0；声明局部变量str和v_stuname，数据类型均为CHAR(10)。DECLAREv_enterscore,nINTDEFAULT0;DECLAREstr,v_stunameCHAR(10);②

为局部变量赋值。使用SET语句或SELECT…INTO语句为局部变量赋值，语法格式类似于为用户变量赋值。例如，使用SET语句为局部变量n和vtitle赋值。SETn=86,vtitle='程序设计基础';例如，使用SELECT…INTO语句为局部变量v_stuname和v_enterscore赋值。SELECTstuname,enterscoreINTOv_stuname,v_enterscoreFROMstudentWHEREstuno='24211040101';8.1.2系统内置函数

在MySQL中提供了许多内置的标准函数，每个标准函数可以实现某个特定的功能，方便用户使用。函数的调用格式如下函数名([参数1[,参数2

]])说明：（1）参数可以是常量、变量或计算表达式。（2）函数可以没有参数，也可以有一个或多个参数，多个参数之间用逗号进行分隔。（3）调用函数后，得到一个函数的返回值。函数名和括号之间不能有空格；没有参数的函数也不能省略括号()。1．数学函数【例8-6】统计学生表student中不同性别学生的入学总分的平均值（保留两位小数），要求显示性别和平均值。mysql>SELECTgenderAS性别,ROUND(AVG(enterscore),2)AS平均值

->FROMstudentGROUPBYgender;函数函数功能示例返回结果ABS(n)返回数值n的绝对值ABS(-2.5)2.5ROUND(n,m)返回按照指定的小数位数m对数值n四舍五入的结果ROUND(12.38,1)12.4FLOOR(n)返回小于或等于给定数值n的最大整数，即向下取整FLOOR(3.9)FLOOR(-3.9)3-42．字符串函数函数函数功能示例返回结果CONCAT(c1,c2,…)将多个字符串连接成一个字符串CONCAT('AB','XYZ')'ABXYZ'LENGTH(c)求字符串c的长度LENGTH('ABCD')4LEFT(c,n)取字符串c从左端开始的n个字符LEFT('ABCD',3)'ABC'RIGHT(c,n)取字符串c从右端开始的n个字符RIGHT('ABCD',3)'BCD'SUBSTRING(c,n1[,n2])取子字符串。从字符串c的n1位置开始取n2个字符。省略n2时，从n1位置开始取到串尾SUBSTRING('ABCDE',2,3)'BCD'REPLACE(c,c1,c2)用字符串c2替换字符串c中的所有字符串c1REPLACE('ABCDABE','AB','2')'2CD2E'TRIM(c)去掉字符串c左右两端的空格TRIM('ABCD')'ABCD'LOWER(c)将字符串c中的大写字母转换为小写字母LOWER('AB')'ab'UPPER(c)将字符串c中的小写字母转换为大写字母UPPER('ab')'AB'【例8-7】查询学生表student中姓李的学生的学号stuno、姓名stuname、性别gender和入学总分enterscore。mysql>SELECTstuno,stuname,gender,enterscoreFROMstudentWHERELEFT(stuname,1)='李';【例8-8】将教师表teacher中的教师姓名tchname字段分成姓和名两列显示。mysql>SELECTLEFT(tchname,1)AS姓,SUBSTRING(tchname,2)AS名FROMteacher;3．日期和时间函数函数函数功能示例返回结果CURDATE()返回系统当前日期CURDATE()2026-02-10CURTIME()返回系统当前时间CURTIME()11:23:58NOW()返回系统当前日期和时间NOW()2026-02-1011:23:58YEAR(d)返回日期表达式d的年份YEAR('2026-02-10')2026MONTH(d）返回日期表达式d的月份MONTH('2026-02-10')2DAY(d)返回日期表达式d的天数DAY('2026-02-10')10DATEDIFF(d1,d2)返回两个日期之间的天数差值DATEDIFF('2026-02-16','2026-02-10')6【例8-9】查询学生表student中学生的学号、姓名和年龄。本例中的年龄通过表达式“FLOOR(DATEDIFF(CURDATE(),birthdate)/365)”获得，使用AS指定列标题为年龄。mysql>SELECTstunoAS学号,stunameAS姓名,FLOOR(DATEDIFF(CURDATE(),birthdate)/365)AS年龄

->FROMstudent;结果会根据运行时间变化控制流程函数函数函数功能示例返回结果IF(expr,v1,v2)判断条件表达式expr的值，如果为真则返回v1的值，否则返回v2的值IF(5>0,'是','否')是IFNULL(v1,v2)如果v1的值不为空则返回v1的值，否则返回v2的值IFNULL(5,2)5【例8-10】查询学生表student中学生的学号、姓名和入学成绩优秀评价（如果enterscore大于或等于560，则显示“入学成绩优秀”，否则显示“无”）。mysql>SELECTstunoAS学号,stunameAS姓名,IF(enterscore>=560,'入学成绩优秀','无')AS入学成绩优秀评价

->FROMstudent;【例8-11】查询学生表student中学生的学号、姓名和奖惩情况（如果awards字段值为NULL，则显示“无”）。mysql>SELECTstunoAS学号,stunameAS姓名,IFNULL(awards,'无')AS奖惩情况

->FROMstudent;5.窗口函数窗口函数用于对一组数据行（窗口）执行计算，并为每一个数据行返回一个值，方便用户实现排名、累计等功能。窗口函数的基本语法格式如下。函数名OVER([PARTITIONBY字段名称,...][ORDERBY字段名称[ASC|DESC],...])其中：（1）函数名：可以是AVG、RANK、SUM、MAX、MIN、FRIST、LAST等。（2）PARTITIONBY子句：按照指定的字段进行分组，类似于GROUPBY。（3）ORDERBY子句：按照指定的字段进行排序，ASC升序，DESC降序。【例8-12】查询学生表中每个学生的姓名、入学总分和入学总分的平均值。直接采用聚合函数可以求出入学总分的平均值，但是只能生成一个结果，如果要求在每一条记录中都显示入学总分的平均值，则需要使用窗口函数。mysql>SELECTstunameAS姓名,enterscoreAS入学总分,AVG(enterscore)OVER()AS平均值->FROMstudent;【例8-13】查询学生表中学生的姓名、性别、入学总分和排名（入学总分按照从高到低排序）。mysql>SELECTstunameAS姓名,genderAS性别,enterscoreAS入学总分,

RANK()OVER(ORDERBYenterscoreDESC)AS排名->FROMstudent;加入PARTITIONBYgender则可以实现按性别分组排名mysql>SELECTstunameAS姓名,genderAS性别,enterscoreAS入学总分,RANK()OVER(PARTITIONBYgenderORDERBYenterscoreDESC)AS排名->FROMstudent;8.1.3流程控制语句流程控制语句是用于控制程序执行顺序的语句。在MySQL中，流程控制语句和局部变量一样，只能出现在存储过程、存储函数和触发器中用来控制程序的执行流程。流程控制语句包括：顺序语句分支语句循环语句1．顺序语句

（1）BEGIN…END语句块BEGIN…END用于定义语句块，语句块中可以包含一个语句序列，语句之间可以嵌套。关键字BEGIN定义语句块的起始位置，END定义同一语句块的结束位置。其基本语法格式BEGIN

语句序列;END;BEGINSELECTstuno,stuname,genderFROMstudentWHEREgender='男';END;（2）DELIMITER语句MySQL默认以英文分号作为单条语句的结束标志，因此在创建程序的语句中，系统在遇到第一个英文分号时就会认为创建程序的语句结束。当BEGIN…END语句块中包含多条以英文分号作为结束标志的语句时，系统处理到第一条语句的英文分号就会停止，导致创建程序失败。这就需要使用DELIMITER语句将单条语句的结束标志修改为其他符号，这样才可以顺利执行BEGIN…END语句块中包含的多条语句

语法格式DELIMITER结束标志DELIMITER

$$BEGINBEGINDECLAREv_deptnoCHAR(3);SETv_deptno=(SELECTdeptnoFROMstudentWHEREstuname='李华');SELECTdeptno,deptnameFROMdepartmentWHEREdeptno=v_deptno;END$$DELIMITER

;将语句的结束标志变成“$$”恢复以英文分号作为结束标志2．分支语句

分支语句有两种，分别为IF语句和CASE语句。（1）IF语句IF语句可以根据不同的条件表达式执行不同的操作，其语法格式如下。IF表达式1THEN语句序列1[ELSEIF表达式2THEN语句序列2]

…[ELSE语句序列n]ENDIF;IF语句的执行流程先计算条件表达式1的值，如果表达式1的值为真（TRUE），则执行语句序列1；否则继续计算表达式2的值，以此类推；如果没有一个表达式的值为真，则执行ELSE中的语句序列n。【例8-14】查询课程表course中数据库原理及应用课程的学分credit，如果查询结果为空，则显示“无学分信息”，否则显示学分。DECLAREv_creditDECIMAL(3,1);SELECTcreditINTOv_creditFROMcourseWHEREcrsname='数据库原理及应用'IFv_creditISNULLTHENSELECT'无学分信息'AS学分;ELSESELECTv_creditAS学分;ENDIF;（2）CASE语句CASE是另一种分支语句，有以下两种语法格式。①CASE语法格式一如下。CASE表达式WHEN

值1THEN语句序列1[WHEN值2THEN语句序列2]

…[ELSE

语句序列n]END[CASE];CASE语法格式一的执行流程如下。先计算出表达式的值，然后与WHEN…THEN语句块中的值进行匹配，如果与某个值的匹配结果为真，则执行对应的语句序列；如果与每一个语句块中的值都不匹配，则执行ELSE中的语句序列n。②CASE语法格式二如下。CASEWHEN表达式1THEN语句序列1[WHEN表达式2THEN语句序列2]

…[ELSE语句序列n]END[CASE];CASE语法格式二的执行流程如下。CASE关键字后没有参数。在WHEN…THEN语句块中指定一个表达式，如果表达式的值为真，则执行对应的语句序列；如果每一个语句块中的表达式的值都不为真，则执行ELSE中的语句序列n。【例8-15】查询课程表course中数据库原理及应用课程的开课院系名称。课程编号crsno字段中的前3位表示的是开课院系代码。例如，10600611对应的院系代码是106，即信息工程学院。DECLAREv_deptnoCHAR(3);DECLAREv_deptnameVARCHAR(50);SELECTLEFT(crsno,3)INTOv_deptnoFROMcourseWHEREcrsname='数据库原理及应用';CASEv_deptnoWHEN'101'THENSETv_deptname='外国语学院'WHEN'102'THENSETv_deptname='新能源学院'WHEN'103'THENSETv_deptname='机械工程学院'WHEN'104'THENSETv_deptname='电气与自动化学院'WHEN'105'THENSETv_deptname='经济与管理学院'WHEN'106'THENSETv_deptname='信息工程学院'ENDCASE;CASE分支语句与SELECT中的CASE表达式有如下的区别。①CASE分支语句执行操作，不返回值；SELECT中的CASE表达式必须返回一个值。②CASE分支语句结束为ENDCASE；SELECT中的CASE表达式的结束为END。【例8-15】查询课程表course中数据库原理及应用课程的开课院系名称。课程编号crsno字段中的前3位表示的是开课院系代码。例如，10600611对应的院系代码是106，即信息工程学院。SELECTcrsnameAS课程名称,CASELEFT(crsno,3)WHEN'101'THEN'外国语学院'WHEN'102'THEN'新能源学院'WHEN'103'THEN'机械工程学院'WHEN'104'THEN'电气与自动化学院'WHEN'105'THEN'经济与管理学院'WHEN'106'THEN'信息工程学院'ENDAS开课院系名称FROMcourseWHEREcrsname='数据库原理及应用';3．循环语句循环语句有3种：WHILE语句REPEAT语句LOOP语句WHILE语句的执行流程：先判断条件是否成立，如果条件成立，则执行语句序列；再次判断条件是否成立，如果条件成立，则继续循环，否则结束循环。（1）WHILE语句WHILE语句的语法格式：WHILE条件DO

语句序列ENDWHILE;【例8-16】计算1+2+3+…+100。DECLAREnINTDEFAULT1;DECLAREtotalINTDEFAULT0;WHILEn<=100DOSETtotal=total+n;SETn=n+1;ENDWHILE;变量total必须使用DEFAULT赋初值0，否则其初值默认为NULLDECLAREnINTDEFAULT1;DECLAREtotalINT;WHILEn<=100DOSETtotal=total+n;SETn=n+1;ENDWHILE;total=5050total=NULL（2）REPEAT语句REPEAT语句的语法格式:REPEAT

语句序列

UNTIL条件ENDREPEAT;REPEAT语句的执行流程:先执行语句序列，然后判断条件是否成立，如果条件不成立，则继续循环，否则结束循环。REPEAT语句的特点是“先执行，后判断”，语句序列至少执行一次；WHILE语句的特点是“先判断，后执行”，语句序列可能一次也不执行。【例8-17】计算2+4+6+…+100。DECLAREnINTDEFAULT2;DECLAREtotalINTDEFAULT0;REPEATSETtotal=total+n;SETn=n+2;UNTILn>100ENDREPEAT;（3）LOOP语句LOOP语句的语法格式:[语句标号:]LOOP

语句序列ENDLOOP[语句标号];LOOP语句的执行流程:重复执行语句序列，语句序列中通常存在一个LEAVE语句，执行到该语句时退出循环。其中的语句标号是用户自定义的名称。退出循环的LEAVE语句的语法格式:LEAVE语句标号;【例8-18】计算5的阶乘。DECLAREn,factINTDEFAULT1;f:LOOPSETfact=fact*n;SETn=n+1;IFn>5THENLEAVEf;ENDIF;ENDLOOPf;8.2存储过程

存储过程是在数据操作语句的集合，经过编译后存储在数据库中，用户通过指定存储过程的名称并给出需要的参数来调用执行存储过程中的语句。用户可以将经常需要执行的特定操作写成存储过程，每次需要时调用该存储过程，可以实现一个存储过程的多次调用，实现了程序的模块化设计。因为存储过程是预编译的，可以加快执行速度。存储过程由声明式SQL语句（如SELECT、INSERT、UPDATE等语句）和过程式SQL语句（如IF-THEN-ELSE等流程控制语句）组成，完成对较为复杂问题的处理。8.2.1创建存储过程使用CREATEPROCEDURE语句可以创建存储过程语法格式：CREATEPROCEDURE存储过程名（[参数1[,参数2

]]）[特征][COMMENT'注释信息']存储过程体存储过程名：用户自定义的存储过程名称。参数：存储过程中的参数是形式参数，简称形参

调用存储过程使用的参数是实际参数，简称实参。

形式参数有输入参数IN、输出参数OUT、输入输出参数INOUT共3种参数。

形式为：[IN

OUT

INOUT]参数名类型。

①IN：将实参的值传递给形参，作为存储过程的输入值。

②OUT：是存储过程的输出值，结束时将形参的结果值传递给实参。

③INOUT：既是输入值也是输出值，调用存储过程时将实参传递给形参，存储过程结束后将形参传递给实参。存储过程可以有0个或多个参数，没有参数时存储过程名后的括号()必须保留；当有多个参数时，各个参数之间用逗号分隔。存储过程体：调用存储过程时将要执行的SQL语句，这部分总是以BEGIN开始，END为结束。当存储过程体中只有一条SQL语句时，可以省略BEGIN

END语句。

说明

8.2.2调用存储过程创建存储过程后，可以在程序、触发器、其他存储过程中使用CALL语句调用。其语法格式如下。CALL存储过程名([参数1[,参数2

]]);说明：（1）参数是调用存储过程使用的实在参数，简称实参。（2）实参的个数必须与存储过程定义的形参个数相同。【例8-20】创建带输入参数的存储过程sp_countgender，统计学生表student中指定性别的学生人数。CREATEPROCEDUREsp_countgender(INp_genderCHAR(1))SELECTCOUNT(*)AS人数FROMstudentWHEREgender=p_gender;调用存储过程mysql>CALLsp_countgender('女');注释是用来解释代码的文本，MySQL执行时会忽略注释内容。好的注释能让代码更易读、易维护。注释类型：单行注释：以“--”或“#”开始，“--”后面要加一个空格。多行注释：以“/*”开始，以“*/”结束。通常用于文件头说明。【例8-21】创建计算1+2+…+100的累加和的存储过程sp_total。/*存储过程名：sp_total

功能描述：计算1+2+...+100的累加和*/DELIMITER$$CREATEPROCEDUREsp_total(OUTp_resultINT)BEGINDECLAREnINTDEFAULT1; --声明循环计数器，初始值为1DECLAREtotalINTDEFAULT0; --初始化累加和为0--使用WHILE循环进行累加

WHILEn<=100DO --不超过100，则继续循环

SETtotal=total+n; --累加当前值

SETn=n+1; --计数器递增

ENDWHILE; --循环结束

SETp_result=total; --结果值

END$$DELIMITER;CALLsp_total(@total);【例8-22】创建带输入参数和输出参数的存储过程sp_avggrade，输入课程名称，计算该课程的平均分。如果该课程无人选修，则输出“该课程暂无成绩记录”；如果平均分大于等于80，则输出“该课程平均分达到80分及以上”；否则输出“该课程平均分没有达到80分”。DELIMITER$$CREATEPROCEDUREsp_avggrade(INp_crsnameVARCHAR(50),OUTp_evaluateVARCHAR(50))BEGINDECLAREv_avgFLOAT;--声明变量存储平均分

--计算指定课程的平均分

SELECTAVG(grade)INTOv_avgFROMcoursecJOINscorescONc.crsno=sc.crsnoJOINstudentsONs.stuno=sc.stunoWHEREcrsname=p_crsname;IFv_avgISNULLTHEN--如果没有成绩记录

SETp_evaluate='该课程暂无成绩记录';ELSEIFv_avg>=80THENSETp_evaluate='该课程平均分达到80分及以上';ELSESETp_evaluate='该课程成绩平均分没有达到80分';ENDIF;END$$DELIMITER;调用存储过程。mysql>CALLsp_avggrade('程序设计基础',@eval);8.2.3删除存储过程当不再需要某个存储过程时，为了释放其占用的存储空间，可以使用DROPPROCEDURE语句删除该存储过程。其语法格式如下。DROPPROCEDURE[IFEXISTS]存储过程名;说明如下。（1）存储过程名：需要删除的存储过程的名称。（2）IFEXISTS：可选项，检测指定的存储过程是否存在，仅在其存在时才执行删除操作。这样可以避免因存储过程不存在而引起的错误。【例8-23】删除存储过程sp_avggrade。mysql>DROPPROCEDUREsp_avggrade;8.2.4使用游标

游标主要包括游标结果集和游标位置两部分，游标结果集是由定义游标的SELECT语句返回行的集合，游标位置则是指向这个结果集中某一行的指针。游标不能单独在查询中使用，一定要在存储过程或存储函数中使用。使用游标的关键步骤：声明游标打开游标遍历游标关闭游标1．声明游标使用游标之前首先要声明游标，定义SELECT语句的结果集语法格式：DECLARE游标名CURSORFORSELECT语句;

说明

（1）使用SELECT语句查询出来的结果构成结果集。（2）游标声明时，定义的SELECT语句还没有执行，没有结果集。因此声明游标之后必须打开游标。打开游标即执行与之对应的SELECT语句，得到结果集语法格式：OPEN游标名;2．打开游标3.遍历游标遍历游标可以获取游标所指向结果集中的当前记录，并将各个字段值传送给一组变量，变量的个数必须与SELECT语句返回的字段个数一致，语法格式：FETCH游标名INTO变量列表;

说明

（1）FETCH每次执行时只能从游标的结果集中提取一条记录，并向前移动指针。（2）如果需要逐条提取结果集中的全部记录，必须将FETCH语句放置在循环语句中。（3）错误处理程序。使用FETCH语句提取到结果集中的最后一条记录后，再执行FETCH语句则游标溢出，会发生NOTFOUND错误，此时立即执行错误处理程序中的语句。例如，错误处理程序：当发生NOTFOUND错误时，设置错误标志变量done为1。DECLARECONTINUEHANDLEFORNOTFOUNDSETdone=1;4．关闭游标游标使用结束后需要及时关闭，这样可以释放游标所占用的内存空间语法格式：CLOSE游标名;例如，在下列给出的使用游标的框架结构中，声明一个错误标志变量done用来保存FETCH操作是否发生错误，如果其值为0，则表示FETCH取得了记录（没有发生错误），循环继续；当FETCH已经是最后一行，找不到下一行了（发生NOTFOUND错误），这时遍历结束，设置变量done为1。在循环结构中判断如果done为1，则结束循环。BEGINDECLAREdoneINTDEFAULT0; --游标结束标志（0:未结束,1:已结束）

DECLARE游标名CURSORFORSELECT语句; --声明游标

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; --声明错误处理程序

OPEN游标名; --打开游标

REPEATFETCH游标名INTO变量列表; --提取结果集中的一条记录

IFdone=0THEN --未发生错误则执行语句序列语句序列;ENDIF;UNTILdone=1 --发生错误则结束循环

ENDREPEAT; CLOSE游标名; --关闭游标END【例8-24】创建一个包含游标的存储过程sp_evaluate_score，输入课程名称，查询选修了该课程的学生的学号、姓名、成绩和成绩评价（优秀、良好、中等、及格、不及格）。DELIMITER$$CREATEPROCEDUREsp_evaluate_score(INp_crsnameVARCHAR(50))BEGINDECLAREdoneINTDEFAULT0; --游标结束标志（0:未结束,1:已结束）

DECLAREv_stunoCHAR(11); --声明变量存储学号

DECLAREv_stuname,v_evaluateVARCHAR(50); --声明变量存储姓名和评价

DECLAREv_gradeTINYINT; --声明变量存储成绩

--声明游标：根据课程名称获取选修了该课程所有学生的学号、姓名、成绩

DECLAREcur_gradeCURSORFORSELECTs.stuno,stuname,gradeFROMcoursecJOINscorescONc.crsno=sc.crsnoJOINstudentsONs.stuno=sc.stunoWHEREcrsname=p_crsname;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1; --声明错误处理程序

OPENcur_grade; --打开游标，执行SELECT语句获得结果集

REPEATFETCHcur_gradeINTOv_stuno,v_stuname,v_grade; --提取结果集中的一条记录

IFdone=0THENCASEWHENv_grade>=90THENSETv_evaluate='优秀';WHENv_grade>=80THENSETv_evaluate='良好';WHENv_grade>=70THENSETv_evaluate='中等';WHENv_grade>=60THENSETv_evaluate='及格';ELSESETv_evaluate='不及格';ENDCASE;SELECTv_stuno,v_stuname,v_grade,v_evaluate;ENDIF;UNTILdone=1 --发生错误则结束循环

ENDREPEAT; CLOSEcur_grade; --关闭游标END$$DELIMITER;调用存储过程mysql>CALLsp_evaluate_score('程序设计基础');执行过程中先打开游标，执行SELECT语句获得游标结果集。stunostunamegrade20241010103宋洪博8520241010230李媛媛10020241040102李华7020241040129侯明斌8120241060104王刚85第1次FETCH：指针移动到第1行，获取（20241010103，宋洪博，85）。第2次FETCH：指针移动到第2行，获取（20241010230，李媛媛，100）。第3次FETCH：指针移动到第3行，获取（20241040102，李华，70）。第4次FETCH：指针移动到第4行，获取（20241040129，侯明斌，81）。第5次FETCH：指针移动到第5行，获取（20241060104，王刚，85）。第6次FETCH：指针移动到第5行之后，触发NOTFOUND，赋值done为1，结束循环。8.3存储函数

MySQL的存储函数与存储过程的作用和格式有许多类似之处，都是由声明式SQL语句和过程式SQL语句组成的主要区别：（1）存储函数有返回值，所以没有输出参数。（2）存储函数必须包含一条RETURN语句。（3）存储函数不能使用CALL语句来调用，存储函数的调用类似于系统内置函数的调用方法，即在表达式、赋值语句中实现调用。8.3.1创建存储函数使用CREATEFUNCTION语句可以创建存储函数语法格式：CREATEFUNCTION存储函数名([参数1[，参数2

]])RETURNS类型DETERMINISTIC存储函数体

说明

（1）存储函数名：用户自定义的存储函数名称。（2）参数：用于指定存储函数的参数，参数只有参数名和参数类型。不使用IN、OUT或INOUT指定参数。（3）RETURNS子句：用于声明存储函数返回值的数据类型。（4）DETERMINISTIC：表示存储函数的结果是确定的，不同用户输入相同的数据会返回相同的结果。（5）存储函数体：存储函数体中必须包含一条RETURNvalue语句，value用于指定存储函数的返回值；在存储过程中使用的SQL语句在存储函数中也适用，包括流程控制语句、游标等，若包含多条语句需要存放在以BEGIN开始，END为结束的结构中。8.3.2调用存储函数调用存储函数的类似于调用系统函数，可以使用SELECT关键字。语法格式：SELECT

存储函数名([参数1[，参数2

]]);【例8-25】创建无参数的存储函数fn_total，计算2+4+…+100的累加和。DELIMITER$$CREATEFUNCTIONfn_total()RETURNSINTDETERMINISTICBEGINDECLAREnINTDEFAULT2;--偶数计数器，从2开始

DECLAREtotalINTDEFAULT0;--累加和，初始值为0--循环累加偶数

REPEATSETtotal=total+n;--累加当前偶数

SETn=n+2; --下一个偶数（每次+2）

UNTILn>100 --当n超过100时结束

ENDREPEAT;RETURNtotal;--返回累加结果值END$$DELIMITER;mysql>SELECTfn_total();【例8-26】创建带参数的存储函数fn_deptname，实现输入指定的学生姓名，返回该学生所在院系的院系名称。DELIMITER$$CREATEFUNCTIONfn_deptname(p_stunameCHAR(50))RETURNSVARCHAR(50)DETERMINISTICBEGIN--查询并返回该学生所在的院系名称

RETURN(SELECTdeptnameFROMdepartmentdJOINstudentsONd.deptno=s.deptnoWHEREstuname=p_stuname);END$$DELIMITER;调用存储函数。SELECTfn_deptname('李华');【例8-27】创建带参数的存储函数fn_subsidy，实现输入教师姓名，返回该教师的课时补助（课时补助=授课总学时×每学时补助）。假定每学分16个学时，每学时补助的标准为：讲师80元、副教授100元、教授120元。如果总额超过3万元，则按3万元计算。DELIMITER$$CREATEFUNCTIONfn_subsidy(p_tchnameCHAR(50))RETURNSINTDETERMINISTICBEGINDECLAREv_hours,v_subsidyINTDEFAULT0; --授课总学时数、补助金额

DECLAREv_titleCHAR(5); --教师职称

--根据教师姓名计算授课总学时数

SELECTSUM(credit*16)INTOv_hoursFROMteachertJOINteachingtgONt.tchno=tg.tchnoJOINcoursecONtg.crsno=c.crsnoWHEREtchname=p_tchname;--无授课记录则返回0IFv_hoursISNULLTHENRETURN0;ENDIF;--根据教师姓名获取教师职称

SELECTtitleINTOv_titleFROMteacherWHEREtchname=p_tchname;--根据职称计算补助

CASEv_titleWHEN'讲师'THENSETv_subsidy=v_hours*80;WHEN'副教授'THENSETv_subsidy=v_hours*100;WHEN'教授'THENSETv_subsidy=v_hours*120;ENDCASE;--封顶30000元

IFv_subsidy>30000THENSETv_subsidy=30000;ENDIF;RETURNv_subsidy; --返回补助金额END$$DELIMITER;8.3.3删除存储函数使用DROPFUNCTION语句删除存储函数，其语法格式:DROPFUNCTION[IFEXISTS]存储函数名;说明如下。（1）存储函数名：需要删除的存储函数的名称。（2）IFEXISTS：可选项，检测指定的存储函数名是否存在，其存在时才执行删除操作。这样可以避免因存储函数不存在而引起的错误。【例8-28】删除存储函数fn_total。mysql>DROPFUNCTIONIFEXISTSfn_total;8.4触发器

触发器是一种特殊的存储过程，通常用于保护表中的数据，它在特定的操作发生时会自动执行。可以理解为“当...的时候，自动做...”，它不需要使用CALL语句调用，也不需要直接写出触发器名来调用。当有操作（如插入、修改、删除等）影响到触发器保护的数据时，触发器会自动被激活并执行相关语句。8.4.1创建触发器使用CREATETRIGGER语句可以创建触发器语法格式：CREATETRIGGER触发器名触发时间触发事件ON表名FOREACHROW触发器动作;

说明

（1）触发器名：用户自定义的触发器名称。（2）触发时间：触发器触发的时刻，有AFTER和BEFORE两个选项，分别表示触发动作是在触发事件之前执行，还是在触发事件之后执行。如果想在触发事件之后执行更多的改变操作，通常使用AFTER；如果想在触发事件之前验证新数据是否满足使用的限制，则使用BEFORE。（3）触发事件：激活触发器程序的语句包括INSERT、UPDATE和DELETE，即当在表中插入、修改或删除记录时都会激活触发器。（4）表名：与触发器相关的表名，在该表上发生触发事件才会激活触发器。（5）FOREACHROW：用于指定每一行都可以激活触发器。（6）触发器动作：触发器的主体，包含触发器激活时将要执行的语句。如果要执行多条语句，则需要放置在以BEGIN开始，END为结束的结构中。触发器不能返回任何结果，也不能被调用。触发器的执行过程可以分为以下几个阶段。①

事件发生（执行INSERT、UPDATE或DELETE语句）。②

检查触发器是否存在。③

确定触发器类型(BEFORE或AFTER)。④

执行触发器动作。【例8-29】在课程表course中创建一个触发器tr_course_ins，每插入一条记录后，就将用户变量@str赋值为“添加一门课程”。①

创建触发器tr_course_ins。mysql>CREATETRIGGERtr_course_insAFTERINSERT

->ONcourseFOREACHROW

->SET@str='添加一门课程';②在课程表course中插入一条新记录。mysql>INSERTINTOcourseVALUES('12345678','操作系统',4);③查询用户变量@str，验证触发器的执行结果。mysql>SELECT@str;8.4.2使用触发器在使用触发器的SQL语句中可以引用表中的任何字段，为了避免系统混淆，不可以直接使用字段名称，而要使用“OLD.字段名称”或“NEW.字段名称”。OLD.字段名称：表示引用被修改或删除前的值NEW.字段名称：表示引用新插入或修改后的值对于INSERT语句，只有NEW是合法的对于DELETE语句，只有OLD是合法的而对于UPDATE语句，NEW和OLD都是合法的1．INSERT触发器【例8-30】在学生表student中创建一个触发器tr_student_ins，每插入一条记录后，就显示所插入记录的学生的姓名。①创建触发器tr_student_ins，将新插入的记录的stuname字段值赋给用户变量@str。mysql>CREATETRIGGERtr_student_insAFTERINSERT

->ONstudentFOREACHROW

->SET@str=NEW.stuname;②在student表中插入一条新记录。

mysql>INSERTINTOstudentVALUES('20241010123','赵红','女','2005-12-23',NULL,'101',580,NULL);③查询用户变量@str，验证触发器的执行结果。mysql>SELECT@str;2．UPDATE触发器【例8-31】在学生表student中创建一个触发器tr_stuname_upd，在修改一条学生记录的姓名前，要保证外籍学生的姓名stuname字段值是大写字母。①创建触发器tr_stuname_upd，修改student表中的一条记录的姓名前，将stuname字段值转换为大写字母。mysql>CREATETRIGGERtr_stuname_updBEFOREUPDATE

->ONstudentFOREACHROW

->SETNEW.stuname=UPPER(NEW.stuname);②将student表中学号为20241010123的记录的姓名修改为mary。mysql>UPDATEstudentSETstuname='mary'WHEREstuno='20241010123';③查询学生表student中该记录的修改情况，验证触发器。mysql>SELECTstuno,stuname,genderFROMstudentWHEREstuno='20241010123';3．DELETE触发器【例8-32】在学生表student中创建一个触发器tr_student_del，在删除一条学生记录前，将所删除的记录保存到数据表student_archive中。student_archive表的结构与student表基本相同，但增加了具有自增属性的字段id，且该字段是主键。①创建student_archive表。mysql>CREATETABLEstudent_archive

->(idINTPRIMARYKEYAUTO_INCREMENT,

->stunoCHAR(11),

->stunameVARCHAR(50),

->genderCHAR(1),

->birthdateDATE,

->classnoVARCHAR(20),

->deptnoCHAR(3),

->enterscoreINT,

->awardsTEXT);②创建触发器tr_student_del，在删除一条学生记录前，将该记录插入student_archive表中。mysql>CREATETRIGGERtr_student_delBEFOREDELETE

->ONstudentFOREACHROW

->INSERTINTOstudent_archive->VALUES(0,OLD.stuno,OLD.stuname,OLD.gender,OLD.birthdate,OLD.classno,OLD.deptno,OLD.enterscore,OLD.awards);③删除student表中学号stuno为20241010123的学生。mysql>DELETEFROMstudentWHEREstuno='20241010123';8.4.3查看触发器查看触发器是指查看数据库中已经存在的触发器，可以通过以下两种方法实现。（1）使用SHOWTRIGGERS语句查看触发器SHOWTRIGGERS语句用于查看当前数据库中的所有触发器，其语法格式如下。SHOWTRIGGERS;（2）在数据表triggers中查看触发器在MySQL中，所有触发器的定义都保存在数据库information_schema的triggers表中，可以使用SELECT语句查看触发器的详细信息。查看所有触发器的详细信息，语法格式如下。SELECT*FROMinformation_schema.triggers;查看指定触发器的详细信息，语法格式如下。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='触发器名称';例如，使用SELECT语句查看触发器t_bookadd的详细信息的语句如下。SELECT*FROMinformation_schema.triggersWHEREtrigger_name='tr_student_del';8.4.4删除触发器可以使用DROP语句删除触发器，其语法格式如下。DROPTRIGGER[IFEXISTS][数据库名.]触发器名;如果没有指定数据库的名称，则默认操作的是当前数据库。当删除一张表时，该表中的触发器同时被自动删除。【例8-33】删除触发器tr_student_del。mysql>DROPTRIGGERtr_student_del;任务8.5事件事件是在指定时刻才被执行的过程式数据库对象，也称为临时性触发器。一个事件可以在事件调度器的管理下周期性地启动。事件和触发器的区别在于触发器是被某个操作（如插入、修改、删除等）触发的，而事件是在特定的时间被触发的。事件调度器必须在开启状态下才可以使用。创建事件前可以用“SELECT@@EVENT_SCHEDULER;”命令语句查看事件调度器的状态，ON表示开启，OFF表示关闭。如果事件调度器处于关闭状态，可以使用“SETGLOBALEVENT_SCHEDULER=1;”命令语句将其开启。8.5.1创建事件CREATEEVENT语句创建事件的语法格式：CREATEEVENT事件名

ONSCHEDULE时间调度

DO触发事件;说明如下。（1）事件名：用户自定义的事件名称。（2）时间调度：指定事件在何时发生或每隔多久发生一次，有以下两种取值方式。①AT时间点[+INTERVAL时间间隔]：表示事件在指定的时间点发生，如果有时间间隔，则表示事件在指定间隔的时间后发生。②EVERY时间间隔[STARTS时间点[+INTERVAL时间间隔]][END时间点[+INTERVAL时间间隔]]：表示事件在指定的时间区间内，每间隔多长时间发生一次。其中，STARTS指定开始时间，END指定结束时间。（3）触发事件：包含事件被激活时要执行的语句。可以是一条SQL语句，也可以是一个BEGIN…END语句块或者一个存储过程。【例8-34】创建立即执行的事件e_createtb，完成创建数据表timetb的操作。字段名称数据类型是否允许空值键说明noBIGINT否主键（自增）序号timefdTIME是

时间mysql>CREATEEVENTe_createtb

->ONSCHEDULEATNOW()

->DO

->CREATETABLEtimetb

->(

->noBIGINTPRIMARYKEYAUTO_INCREMENT,

->timefdTIME

->);【例8-35】创建事件e_instb，每隔10s向数据表timetb中插入一条当前时间数据。mysql>CREATEEVENTe_instb

->ONSCHEDULEEVERY10SECOND

->DOINSERTINTOtimetb(timefd)VALUES(CURTIME());8.5.2管理事件1．修改事件使用ALTEREVENT语句可以修改事件的时间调度、事件名和触发事件，其语法格式如下。ALTEREVENT事件名[ONSCHEDULE时间调度][RENAMETO新事件名][DO触发事件];【例8-36】将事件e_instb的名称改为e_instimetb。mysql>ALTEREVENTe_instb

->RENAMETOe_instimetb;2．关闭事件可以使用ALTEREVENT语句临时关闭某个事件，其语法格式如下。ALTEREVENT事件名DISABLE;【例8-37】关闭事件e_instimetb。mysql>ALTEREVENTe_instimetbDISABLE;3．启动事件可以使用ALTEREVENT语句再次启动某个事件，其语法格式如下。ALTEREVENT事件名ENABLE;【例8-38】再次启动事件e_instimetb。mysql>ALTEREVENTe_instimetbENABLE;8.5.3删除事件DROPEVENT语句删除已经创建的事件，其语法格式如下。DROPEVENT[IFEXISTS]事件名;【例8-39】删除事件e_instimetb。mysql>DROPEVENTe_instimetb;课堂案例：学生成绩管理数据库的编程1．系统内置函数的应用（1）查询学生的学号、姓名和院系班级名称，院系班级名称的形式为“院系名称+班级+班”，如信息工程学院计算2401班。SELECTstunoAS学号,stunameAS姓名,CONCAT(deptname,classno,'班')AS院系班级名称FROMdepartmentdJOINstu