第5章 存储函数与存储过程5.1常用的系统函数数据库原理与设计2ABS(x)：返回x的绝对值。PI()：返回圆周率π的值。SQRT()：返回
非负数的二次方根。MOD(m,n)：返回m被n除后的余数。ROUND(x,y)：把x四舍五入到y指定的精度返回。如果y为负数，则将
保留x值到小数点左边y位。【例5-1】示例5.1.1 数学函数SELECT SQRT(ROUND(ABS(-4.014.01
),0)), MOD(-10,3),MOD(10,-3)5.1.2 字符串函数1、计算字符串字符数的函数和字符串长
度的函数 CHAR_LENGTH(str)：返回字符串str所包含的字符个数。 LENGTH(str)：返回值为字符串的字节长
度。一个汉字是3个字节，一个数字或字母是1个字节。【例5-2】示例SELECT CHAR_LENGTH(''CHINA''),LEN
GTH(''CHINA'');SELECT CHAR_LENGTH(''中国'') 字符数, LENGTH(''中国'') 字
符串长度;2. 合并字符串函数 CONCAT(s1,s2,……)返回结果为s1,s2,……连接成的字符串，如果任何一个参数为NUL
L，则返回值为NULL。【例5-3】示例SELECT CONCAT(''MySQL版本：'',@@version) 版本信息1,
CONCAT_WS(''-'',''MySQL'',''8.0.27'') 版本信息2; CONCAT_WS(sep,s1,s2,
……)返回结果为s1,s2,……连接成的字符串，并用sep字符间隔。3. 字符串大小写转换函数 LOWER(str)或LCASE(
str)是将字符串str中的字母字符全部转换成小写字母。【例5-4】根据用户名查询customers表中指定用户的信息。要求：用户
在输入用户名时不做大小写字母的限制。SELECT FROM customers WHERE LOWER(c_name)=LCA
SE(''redhOOK'');UPPER(str)或UCASE(str)是将字符串str中的字母字符全部转换成大写字母。4. 删除空
格函数 LTRIM(str)返回删除前端空格的字符串str；【例5-5】根据用户名查询customers表中指定用户的信息。请考虑
到用户输入值时可能存在输入前端或尾部空格的情况。SELECT FROM customers WHERE UPPER(c_nam
e)=TRIM(UCASE('' redhOOK ''));TRIM(str)返回删除前端和尾部空格的字符串str。 RTRIM(st
r)返回删除尾部空格的字符串str；【注意】这3个函数只删除字符串前端和尾部空格，不删除字符串中间的空格。5. 取子串函数 LEF
T(str,length)返回字符串str最左侧长度为length的子串；【例5-6】返回fruits表中f_name值以''b''开
头的水果信息。SELECT FROM fruits WHERE SUBSTRING(f_name,1,1)=''b'';SUB
STRING(str,start,length)返回字符串str从start开始长度为length的子串。RIGHT(str,le
ngth)返回字符串str最右侧长度为length的子串；SELECT FROM fruits WHERE f_name L
IKE ''b%'';6. 其它字符串函数5.1.3 日期和时间函数1. 获取当前系统的日期及取日期的年、月、日函数 CURDAT
E()或CURRENT_DATE()返回当前系统日期， 格式为''YYYY-MM-DD''。 【例5-8】通过orders表和
customers表，查询2021年下订单的客户编号、姓名及所在的城市。SELECT distinct c.c_id,c_name
,c_city FROM customers c,orders o WHERE c.c_id=o.c_id AND YEAR(o
_date)=2021; YEAR(d)、MONTH(d)、DAY(d)分别返回日期或日期时间d的年、月、日的值。 2. 获取
当前系统日期时间函数NOW() SYSDATE()CURREN
T_TIMESTAMP()LOCALTIME()格式为''YYYY-MM-DD HH:MM:SS''。【例5-9】根据出生日期计算年龄
。SELECT SYSDATE(),YEAR(SYSDATE())-YEAR(''2003/05/23'') 年龄;3. 其它日期和时
间函数5.1.4 系统信息函数1. 获取MySQL版本号、用户名和数据库名函数USER()返回当前登录的用户名。 【例5-10
】显示当前MySQL版本号、登录的用户名和使用的数据库。SELECT VERSION() 版本号,USER() 登录名,
DATABASE() 数据库名; VERSION()返回MySQL服务器版本号。 DATABASE()返回当前使用数据库
名。2. 其它系统信息函数5.1.5 条件判断函数1. IF()函数IF(条件表达式,v1,v2)如果条件表达式是真则函数返回v
1值，否则返回v2的值。【例5-11】显示customers表的c_id、c_name和c_email，当c_email字段值为N
ULL时，显示值为none，否则显示当前字段的值。SELECT c_id,c_name, IF(c_email IS
NULL,''none'',c_email) c_email FROM customers;2. IFNULL()函数IFNULL(v
1,v2) 返回参数v1或v2的值。假如v1不为NULL，则返回值为v1，否则返回值为v2。【例5-12】显示customer
s表的c_id、c_name和c_email，当c_email字段值为NULL时，显示值为none，否则显示当前字段的值。SELE
CT c_id,c_name,IFNULL(c_email,''none'') c_email FROM customers;3. C
ASE函数（1）语法格式一 CASE 表达式 WHEN v1 THEN r1 WHEN v2 TH
EN r2 …… [ELSE rn] END【例5-13】对fruits表按f_name的升序排列，显示前
三条记录的水果中文名称。SELECT f_name, CASE f_name WHEN ''apple'' THEN ''苹果
'' WHEN ''apricot'' THEN ''杏'' WHEN ''banana'' THEN ''香蕉'' END 中文名称
FROM fruits ORDER BY f_name LIMIT 3;（2）语法格式二CASE WHEN 条件表达式 T
HEN r1 WHEN 条件表达式 THEN r2 …… [ELSE rn]END【例5-14】对fruits表
按f_name的升序排列，显示前三条记录的水果单价与平均单价的比较信息。SELECT f_name,f_price, CASE
WHEN f_price>(SELECT AVG(f_price) FROM fruits) THEN ''高于平均价格''
WHEN f_price<(SELECT AVG(f_price) FROM fruits) THEN ''低于平均价格'' E
LSE ''等于平均价格'' END 价格比较 FROM fruits ORDER BY f_name LIMIT 3;5.1.6
加密函数MD5(str)，计算字符串str的MD5 128比特校验和。SHA(str)，计算字符串str的SHA检验和。SHA
加密算法比MD5更加安全。SHA2(str,hash_length)，使用hash_length作为长度，加密str。Hash_l
ength支持224、256、384、512和0，其中0等同于256。【例5-15】创建用户user表，为每条记录的密码字段值进行
加密。CREATE TABLE user( u_id int NOT NULL AUTO_INCREMENT PRIMARY KE
Y, u_name varchar(20), u_pass varchar(256));INSERT INTO user(u_na
me,u_pass) VALUES(''mary'',SHA2(''123456'',0)) ,(''jack'',MD5(''jac
k12345'')); SELECT FROM user;5.2 存储函数数据库原理与设计221）字符串常量
字符串常量指用单引号或双引号括起来的字符序列。在MySQL中推荐使用单引号。如：''SCOTT'' 5.2.1 常量与变量1.
常量【例5-16】查询表fruits中f_name值为apple的供应商编号。SELECT s_id FROM fruits W
HERE f_name=''apple'';SELECT s_id FROM fruits WHERE ''f_name''=''appl
e'';是如何进行比较的？2）数值常量 数值常量可以分为整数常量和小数常量。【例5-17】统计orderitems表中订
单数量超过3单（包含3单）的订单编号o_num。SELECT o_num 订单编号,COUNT() 订单数量 FROM orde
ritems GROUP BY o_num HAVING 订单数量>=3;orderitems表3）日期和时间常量
日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。【例5-18】根据orders表和customers表，查询2021年
9月30日的订单编号、用户名称及地址信息。SELECT o_num,c_name,c_address FROM orders o,
customers c WHERE o.c_id=c.c_id and o_date=''2021/09/30'';orders表cu
stomers表4）布尔值常量 布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表fa
lse。【例5-19】查询fruits表中以’t’开头的水果编号的f_id、f_name及f_price是否大于水果平均单价的判断
结果。SELECT f_id,f_name,f_price>(SELECT AVG(f_price) FROM fruits)
单价比较结果 FROM fruits WHERE f_id LIKE ''t%'';5）NULL值 NULL值适用于各种
字段类型，通常表示“不确定的值”，NULL值参与的运算，结果仍为NULL值。【例5-20】在fruits表中插入一条记录，f_id
为t3,f_name为orange，s_id为101。INSERT INTO fruits(f_id,s_id,f_name)
VALUES(''t3'',101,''orange''); 将插入的f_id值为t3的记录的f_price字段值在原有价格的基
础增加1.5元。UPDATE fruits SET f_price=f_price+1.5 WHERE f_id=''t3'';DE
LETE FROM fruits WHERE f_price IS NULL;1）局部变量 2. 变量【例5-21】查询frui
ts表中最高单价值赋给变量max_price，并显示其值。SET @max_value=(SELECT MAX(f_price)
FROM fruits);SELECT @max_value 最高单价;（1）局部变量的定义与赋值 SET @局部变量
名=表达式1[,@局部变量名=表达式2,……]（2）局部变量的显示 SELECT @局部变量名[,@局部变量名,……]
【例5-22】查询suppliers表中供应商编号为101的供应商姓名和电话的值赋给变量name和phone，并显示两个变量的结果
。SELECT s_name,s_call INTO @name,@phone FROM suppliers WHERE s_id
=101;SELECT @name,@phone;【例5-23】根据name变量所给的值查询customers表中指定客户的信息。
SET @name=''RedHook'';SELECT FROM customers WHERE c_name=@name;2）
全局变量 【例5-24】通过全局变量查看MySQL的相关信息。SELECT @@version,@@basedir,@@lice
nse,@@port; 全局变量是MySQL系统提供并赋值的变量。用户不能定义全局变量，只能使用。1、语句块BEGIN
SQL语句 ｜ SQL语句块END注意：① BEGIN…END语句块包含了该程序块的所有处理操作，允许语句块嵌套。② 在MySQ
L中单独使用BEGIN…END语句块没有任何意义，只有将其封装在存储过程、存储函数等程序内部才有意义。5.2.2 语句块、注释和
重置语句结束标记2、注释1）单行注释 使用“#”符号作为单行语句的注释符，写在需要注释的行或语句后方。【例5-25】示例。
#求两个数的最小值SET @x=5,@y=6; #定义两个变量并赋值SELECT IF(@x<
@y,@x,@y) 最小值; #比较两个变量并输出最小值 2）多行注释 使用/和/括起来可以连续书写多行的注释语句
。【例5-26】示例。/在MySQL Workbench工具下，MySQL执行UPDATE或DELETE语句时，如果WHERE短
语中没有给出包含主键的条件，执行报错，WHERE短语中包含主键条件则执行正常。因为MySQL运行在safe-updates模式下，
该模式会导致非主键条件下无法执行UPDATE或DELETE语句，需要执行命令 SET SQL_SAFE_UPDATES = 0修改
数据库模式。/ SET SQL_SAFE_UPDATES=0; DELETE FROM fruits WHE
RE f_name=''xxtt'';3、重置命令结束标记1）为什么要重置命令结束标记 在MySQL中，服务器处理的语句是以分号为结
束标记的。但在创建存储函数、存储过程的时候，函数体或存储过程体中可以包含多个SQL语句，每个SQL语句都是以分号结尾，而服务器处理
程序时遇到第一个分号则结束程序执行，这时就需要使用DELIMITER语句将MySQL语句的结束标记修改为其它符号。 2）语法：
DELIMITER 符号 符号可以是一些特殊符号，如两个#、两个@、两个$、两个%等。【例5-27】示例
。DELIMITER @@SELECT FROM fruits@@DELIMITER ;SELECT FROM fruit
s;5.2.3 存储函数1、创建存储函数CREATE FUNCTION 函数名([参数名 参数数据类型[,…]]) RETURN
S 函数返回值的数据类型 BEGIN 函数体; RETURN 语句; END2、调用存储函数SELEC
T 函数名([参数值[,…]])【例5-28】创建存储函数sphone，根据所给的供应商编号s_id值，函数返回该供应商的电话s
_call。DELIMITER @@CREATE FUNCTION sphone(sid INT) RETURNS char(2
0) BEGIN RETURN (SELECT s_call FROM supperliers WHERE s_id=sid);
END@@（1）报错的原因：MySQL开启了bin-log日志，所以创建函数或者存储过程时，必须声明其为确定性的，或者声明为不修
改数据。（2）解决方法：设置log_bin_trust_routine_creators全局变量为1，信任存储程序的创建者。【例5
-28】创建存储函数sphone，根据所给的供应商编号s_id值，函数返回该供应商的电话s_call。SET GLOBAL log
_bin_trust_function_creators = 1;DELIMITER @@CREATE FUNCTION sph
one(sid INT) RETURNS char(20) BEGIN RETURN (SELECT s_call FROM s
upperliers WHERE s_id=sid); END@@DELIMITER ;SELECT sphone(101);3.
删除存储函数DROP FUNCTION 函数名; 【注意】函数名后没有括号【例5-29】删除例5-28创建的
sphone存储函数。DROP FUNCTION sphone;5.3 程序流程控制语句数据库原理与设计405.3.1 条件判断
语句1、程序中变量的使用1）声明变量 DECLARE 局部变量名[,……] 数据类型 [DEFAULT 默认值]; 【
说明】（1）DECLARE声明的局部变量，变量名前不能加@。（2）DEFUALT子句提供了一个默认值，如果没有给默认值，局部变量初
始值默认为NULL。 2）为变量赋值 SET 局部变量名=表达式1[,局部变量名=表达式2,……];【例5-30】创
建求任意两个数和的存储函数sum_fn()。DELIMITER @@ CREATE FUNCTI
ON sum_fn(a float,b float) RETURNS float BEGIN DECLARE x,y fl
oat; SET x=a,y=b; RETURN x+y; END@@SELECT sum
_fn(7,3);2、IF语句1）形式一IF 条件表达式 THEN SQL语句块1; [ELSE
SQL语句块2; ] END IF;【例5-31】创建函数max_int，判断整型变量a和b的大小。DELIMITER @
@CREATE FUNCTION max_fn(a int,b int) RETURNS INT BEGIN IF a>b T
HEN RETURN a; ELSE RETURN b; END IF; END@@DELIMITER ;SELE
CT max_fn(7,8) 最大值; 2）形式二IF 条件表达式1 THEN SQL语句块1;EL
SEIF 条件表达式2 THEN SQL语句块2; ……ELSE SQL语句块n;END IF;【例
5-32】创建判断某一年是否为闰年的函数leap_year()。闰年的判断条件为：年值能被4整除但不能被100整除，或者能被400
整除。3、CASE语句1）形式一CASE 表达式 WHEN 表达式值1 THEN SQL语句块1; WHEN
表达式值2 THEN SQL语句块2; …… WHEN 表达式值n THEN SQL语句块n; [
ELSE SQL语句块n+1; ]END;【例5-33】创建存储函数email，根据所给的客户编号c_id值，函数返回
该客户的邮箱c_email；然后判断显示orders表中的o_num、c_id和客户邮箱。2）条件判断CASE WHEN
条件1 THEN SQL语句块1; WHEN 条件2 THEN SQL语句块2; …… WHEN
条件n THEN SQL语句块n; [ELSE SQL语句块n+1; ]END;【例5-34】用第二种形式实
现例5-33。SELECT o_num,c_id,CASE WHEN c_id=10001 THEN email(10001)
WHEN c_id=10002 THEN email(10002) WHEN c_id=10003 THEN email(1
0003) WHEN c_id=10004 THEN email(10004) END 客户邮箱FROM orders;1、LO
OP循环[标签:] LOOP SQL语句块; IF 条件 THEN LEAVE 标签; END IF;EN
D LOOP; 5.3.2 循环语句【例5-35】创建sum_fn()存储函数，计算1~n的和。2、WHILE循环WHILE
条件 DO SQL语句块;END WHILE;【例5-36】计算1~n的和。3、REPEAT循环REPEAT
SQL语句块; UNTIL 条件END REPEAT;【例5-37】计算1~n中能被3和5整除的数的和。5.4 存储过
程数据库原理与设计545.4.1 存储过程概述 存储过程是指用于执行特定操作的SQL语句的集合，在需要时可以直接调用
，提高代码的重用性。 存储过程的主要优点：存储过程执行速度快。 存储过程在创建时被编译，在第一次执行之后，就驻留在内存中，
之后每次执行该存储过程均不需要再重新编译，所以使用存储过程可以提高数据库的执行速度。 存储过程的主要优点：存储过程可以减少网络通信
流量。 存储过程由多条SQL语句组成，但调用执行仅用一条语句，所以只有少量的SQL语句在网络线上传输，从而减少了网络流量并
降低了网络负载。 存储过程的主要优点：存储过程具有安全特性。 参数化的存储过程可以防止SQL注入式攻击，而且系统管理员可
以通过GRANT、REVOKE等命令实现对用户数据访问权限的控制，避免了非授权用户对数据的访问，保证了数据的安全。 存储过程的主要
优点：存储过程允许模块化编程。 创建一次存储过程，存储在数据库中后，就可以在程序中重复调用任意多次，减少了数据库开发人员
的工作量。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码没有丝毫影响。创建存储过程CREATE PROCEDUR
E 存储过程名()BEGIN 过程体;END5.4.2 创建存储过程CALL 存储过程名();5.4.3 调用存储过程【例5-
38】创建存储过程apple_proc，在fruits表中查询供应apple的供应商编号s_id。SET GLOBAL log_b
in_trust_function_creators = 1;DELIMITER @@CREATE PROCEDURE apple
_proc()BEGIN SELECT s_id FROM fruits WHERE f_name=''apple'';END@
@【例5-39】调用执行存储过程apple_proc。CALL apple_proc();CREATE PROCEDURE 存储过
程名( [ IN | OUT | INOUT] 参数1 数据类型, [ IN | OUT | INOUT] 参数2 数
据类型,……)BEGIN 过程体；END5.4.4 存储过程的参数 ※※※ 1、IN输入参数【例5-40】创建一个向supp
liers表中插入新记录的存储过程s_in_proc。SELECT FROM suppliers WHERE s_id=1
08;CALL s_in_proc(108,''XiaoTong'',''GuangZhou'',''510000'',''1367845123
'');2、OUT输出参数【例5-41】创建存储过程s_out_proc，根据提供的供应商编号，返回供应商的名称和电话。3、INOU
T输入输出参数【例5-42】使用INOUT参数实现两个数的交换。DROP PROCEDURE 存储过程名;5.4.5 删除存
储过程 【例5-43】删除已创建的存储过程swap。DROP PROCEDURE swap;5.4.6 存储过程和存储函数的区别
（1）一般存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强。（2）存储过程可以返回参数，而函数只能返回值。函数只能返回一
个变量，存储过程可以返回多个。存储过程的参数可以有IN、OUT、INOUT三种类型，而函数只有IN类型。存储过程声明时不需要返回类
型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。（3）函数可以嵌入在SQL语句中使用，可以在SEL
ECT语句中作为查询语句的一部分调用；而存储过程一般是作为一个独立的部分来执行的。5.5 游 标数据库原理与设计67
通过SELECT语句查询时，返回的结果是一个由多行记录组成的集合。而程序设计语言有时要处理以集合形式返回的数据集中的每一行数
据，为此，SQL提供了游标机制。 游标充当指针的作用，使应用程序设计语言一次只能处理查询结果中的一行。1、声明游标
DECLARE 游标名 CURSOR FOR SELECT语句;5.5.1 游标的使用【说明】（1）声
明游标的作用是得到一个SELECT查询结果集，该结果集中包含了应用程序中要处理的数据，从而为用户提供逐行处理的途径。（2）SELE
CT语句是对表或视图的查询语句。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。2、打开
游标 OPEN 游标名;游标必须先声明后打开。打开游标时，SELECT语句的查询结果就被传送到了游标工作区
，以便供用户读取。3、提取数据 FETCH 游标名 INTO 变量名1[，变量名2……];打开游标后，游标
指针指向结果集的第一行之前，而FETCH语句将使游标指针指向下一行。在循环中使用FETCH语句，每次循环都会从表中读取一行数据，然
后进行相同的逻辑处理。4、关闭游标 CLOSE 游标名; 游标一旦关闭，游标占用的资源就被释放，用户不
能再从结果集中检索数据，如果想重新检索，必须重新打开游标才能使用。【例5-44】用游标提取customers表中指定客户的姓名和联
系人。例：创建存储过程f_p1，使用游标提取fruits表中所有记录信息。分析： 需要使用循环逐行读取。要解决的问题：
循环的结束条件是什么？解决方法： 借助于异常处理来实现。5.5.2 异常处理语法：DECLARE 错误处理类型 HAN
DLER FOR 错误条件 错误处理程序;【说明】（1）异常处理语句必须放在所有变量及游标定义之后，所有MySQL表达式之前；
DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;（2）错误处理类型 错误处理类
型只有CONTINUE和EXIT两种。 CONTINUE表示错误发生后，MySQL立即执行自定义错误处理程序，然后忽略该错误
继续执行其它MySQL语句。 EXIT表示错误发生后，MySQL立即执行自定义错误处理程序，然后立刻停止其它MySQL语句
的执行。（3）错误条件 错误条件定义了自定义错误处理程序运行的时机。错误条件取值：① SQLSTATE ''ANSI标准错误
代码''：包含5个字符的字符串值 DECLARE EXIT HANDLER FOR SQLSTATE ''42S02''
SET @info=''NO_SUCH_TABLE''; DECLARE 错误处理类型 HANDLER
FOR 错误条件 错误处理程序;② MySQL错误代码：匹配数值类型的错误代码 DECLARE EXIT H
ANDLER FOR 1146 SET @info=''NO_SUCH_TABLE'';（3）错误条件 ③ SQLWARNING：匹配所有以01开头的SQLSTATE错误代码 DECLARE EXIT HANDLER FOR SQLWARNING SET @info=''ERROR''; DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;④ NOT FOUND：匹配所有以02开头的SQLSTATE错误代码 DECLARE EXIT HANDLER FOR NOT FOUND SET @info=''NO_SUCH_TABLE'';⑤ SQLEXCEPION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码 DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=''ERROR'';（4）错误处理程序 错误发生后，MySQL会立即执行自定义错误处理程序中的MySQL语句。 DECLARE EXIT HANDLER FOR NOT FOUND SET @info=''NO_SUCH_TABLE''; DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;【例5-46】创建存储函数f_in_fun，向fruits表插入一条记录，f_id、s_id和f_name字段的值为a1, 101和banana，已知a1水果编号已存在于fruits表中，违背了主键约束。下面存储函数的创建，加入了错误处理机制，数据库开发人员控制程序的运行流程，解决了MySQL自动终止存储程序执行的问题。【例5-48】创建存储过程price_up_proc，使用游标更新fruits表中的f_price的值，f_price值小于等于5元单价增加20%，大于5元小于等于10元单价增加10%，其它增加5%。Thank You ! |
|
