【愚公系列】2023年05月 Java教学课程 104-MySQL数据库（存储过程和函数）

一、MySQL存储过程和函数

1.存储过程和函数的概念

存储过程是一组预定义的SQL语句集合，它们被存储在数据库中并可以被重复调用。存储过程可以接收参数、执行一系列SQL操作并返回结果。

函数是一个可重复使用的代码块，它接收输入参数并根据这些参数执行一系列操作，最后返回一个结果。函数可以是内置函数、自定义函数或用户定义函数。

存储过程和函数都是数据库中的可重复使用的代码块，它们可以提高数据库的性能和可维护性。存储过程通常用于执行复杂的数据操作，而函数则用于计算和返回值。

2.存储过程和函数的好处

• 提高数据库性能：存储过程和函数可以预编译，因此在执行时可以提高数据库的性能。

• 简化复杂操作：存储过程和函数可以将复杂的操作封装在一个单独的单元中，使得操作更加简单，易于维护。

• 保证数据的一致性：存储过程和函数可以在执行时对数据进行验证和处理，从而保证数据的一致性和完整性。

• 提高安全性：存储过程和函数可以限制用户的访问权限，从而提高数据库的安全性。

• 提高代码复用性：存储过程和函数可以被多个应用程序调用，从而提高了代码的复用性。

• 提高可维护性：存储过程和函数使得数据库的维护更加容易，因为它们将逻辑代码与数据库分离开来，使得修改和维护更加容易。

3.存储过程和函数的区别

存储过程和函数在数据库中都是用来封装一些 SQL 语句，以便在需要时可以被重复调用。但是，它们之间有一些明显的区别：

• 返回值：函数必须返回一个值，而存储过程不一定需要返回值。

• 参数传递：函数和存储过程都可以接受参数，但是存储过程可以使用 IN、OUT 和 INOUT 参数，而函数只能使用 IN 参数。

• 数据修改：存储过程可以修改数据库中的数据，而函数不允许修改数据。

• 调用方式：函数可以在 SQL 语句中直接调用，而存储过程需要使用 CALL 语句来调用。

• 返回结果集：存储过程可以返回多个结果集，而函数只能返回一个结果集。

• 执行权限：存储过程可以被授权给其他用户或角色，而函数只能被定义它的用户或角色使用。

总的来说，存储过程适合于处理复杂的业务逻辑，而函数则适合于处理单一的计算逻辑。

4.创建存储过程

• 小知识

/*
该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！
sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。
这个时候就可以使用DELIMITER来指定分隔符了！
*/
-- 标准语法
DELIMITER 分隔符

• 数据准备

-- 创建db8数据库
CREATE DATABASE db8;

-- 使用db8数据库
USE db8;

-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(5), -- 学生性别
score INT -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;

• 创建存储过程语法

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

• 创建存储过程

-- 修改分隔符为$
DELIMITER $

-- 创建存储过程，封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符为分号
DELIMITER ;

5.调用存储过程

• 调用存储过程语法

-- 标准语法
CALL 存储过程名称(实际参数);

-- 调用stu_group存储过程
CALL stu_group();

6.查看存储过程

• 查看存储过程语法

-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';

7.删除存储过程

• 删除存储过程语法

-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;

-- 删除stu_group存储过程
DROP PROCEDURE stu_group;

8.存储过程语法

8.1存储过程语法介绍

• 存储过程是可以进行编程的。意味着可以使用变量、表达式、条件控制语句、循环语句等，来完成比较复杂的功能！

8.2变量的使用

• 定义变量

-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
DECLARE num INT DEFAULT 10; -- 定义变量
SELECT num; -- 查询变量
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();

• 变量的赋值1

-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
DECLARE NAME VARCHAR(10); -- 定义变量
SET NAME = '存储过程'; -- 为变量赋值
SELECT NAME; -- 查询变量
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();

• 变量的赋值2

-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
DECLARE men,women INT; -- 定义变量
SELECT SUM(score) INTO men FROM student WHERE gender='男'; -- 计算男同学总分数赋值给men
SELECT SUM(score) INTO women FROM student WHERE gender='女'; -- 计算女同学总分数赋值给women
SELECT men,women; -- 查询变量
END$

DELIMITER ;

-- 调用pro_test3存储过程
CALL pro_test3();

8.3if语句的使用

• 标准语法

-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

• 案例演示

/*
定义一个int变量，用于存储班级总成绩
定义一个varchar变量，用于存储分数描述
根据总成绩判断：
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
-- 定义总分数变量
DECLARE total INT;
-- 定义分数描述变量
DECLARE description VARCHAR(10);
-- 为总分数变量赋值
SELECT SUM(score) INTO total FROM student;
-- 判断总分数
IF total >= 380 THEN
SET description = '学习优秀';
ELSEIF total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END IF;

-- 查询总成绩和描述信息
SELECT total,description;
END$

DELIMITER ;

-- 调用pro_test4存储过程
CALL pro_test4();

8.4参数的传递

• 参数传递的语法

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
执行的sql语句;
END$
/*
IN:代表输入参数，需要由调用者传递实际数据。默认的
OUT:代表输出参数，该参数可以作为返回值
INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;

• 输入参数

– 标准语法

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
BEGIN
执行的sql语句;
END$

DELIMITER ;

– 案例演示

/*
输入总成绩变量，代表学生总成绩
定义一个varchar变量，用于存储分数描述
根据总成绩判断：
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test5(IN total INT)
BEGIN
-- 定义分数描述变量
DECLARE description VARCHAR(10);
-- 判断总分数
IF total >= 380 THEN
SET description = '学习优秀';
ELSEIF total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END IF;

-- 查询总成绩和描述信息
SELECT total,description;
END$

DELIMITER ;

-- 调用pro_test5存储过程
CALL pro_test5(390);
CALL pro_test5((SELECT SUM(score) FROM student));

• 输出参数

– 标准语法

DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)
BEGIN
执行的sql语句;
END$

DELIMITER ;

– 案例演示

/*
输入总成绩变量，代表学生总成绩
输出分数描述变量，代表学生总成绩的描述
根据总成绩判断：
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
BEGIN
-- 判断总分数
IF total >= 380 THEN
SET description = '学习优秀';
ELSEIF total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END IF;
END$

DELIMITER ;

-- 调用pro_test6存储过程
CALL pro_test6(310,@description);

-- 查询总成绩描述
SELECT @description;

– 小知识

@变量名: 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量

8.5case语句的使用

• 标准语法1

-- 标准语法
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

• 标准语法2

-- 标准语法
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

• 案例演示

/*
输入总成绩变量，代表学生总成绩
定义一个varchar变量，用于存储分数描述
根据总成绩判断：
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
-- 定义变量
DECLARE description VARCHAR(10);
-- 使用case判断
CASE
WHEN total >= 380 THEN
SET description = '学习优秀';
WHEN total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END CASE;

-- 查询分数描述信息
SELECT description;
END$

DELIMITER ;

-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));

8.6while循环

• 标准语法

-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;

• 案例演示

/*
计算1~100之间的偶数和
*/
DELIMITER $

CREATE PROCEDURE pro_test8()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- while循环
WHILE num <= 100 DO
-- 偶数判断
IF num%2=0 THEN
SET result = result + num; -- 累加
END IF;

-- 让num+1
SET num = num + 1;
END WHILE;

-- 查询求和结果
SELECT result;
END$

DELIMITER ;

-- 调用pro_test8存储过程
CALL pro_test8();

8.7repeat循环

• 标准语法

-- 标准语法
初始化语句;
REPEAT
循环体语句;
条件控制语句;
UNTIL 条件判断语句
END REPEAT;

-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

• 案例演示

/*
计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test9()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- repeat循环
REPEAT
-- 累加
SET result = result + num;
-- 让num+1
SET num = num + 1;

-- 停止循环
UNTIL num>10
END REPEAT;

-- 查询求和结果
SELECT result;
END$

DELIMITER ;

-- 调用pro_test9存储过程
CALL pro_test9();

8.8loop循环

• 标准语法

-- 标准语法
初始化语句;
[循环名称:] LOOP
条件判断语句
[LEAVE 循环名称;]
循环体语句;
条件控制语句;
END LOOP 循环名称;

-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
-- 如果不加退出循环的语句，那么就变成了死循环。

• 案例演示

/*
计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test10()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- loop循环
l:LOOP
-- 条件成立，停止循环
IF num > 10 THEN
LEAVE l;
END IF;

-- 累加
SET result = result + num;
-- 让num+1
SET num = num + 1;
END LOOP l;

-- 查询求和结果
SELECT result;
END$

DELIMITER ;

-- 调用pro_test10存储过程
CALL pro_test10();

8.9游标

• 游标的概念

– 游标可以遍历返回的多行结果，每次拿到一整行数据

– 在存储过程和函数中可以使用游标对结果集进行循环的处理

– 简单来说游标就类似于集合的迭代器遍历

– MySQL中的游标只能用在存储过程和函数中

• 游标的语法

– 创建游标

-- 标准语法
DECLARE 游标名称 CURSOR FOR 查询sql语句;

– 打开游标

-- 标准语法
OPEN 游标名称;

– 使用游标获取数据

-- 标准语法
FETCH 游标名称 INTO 变量名1,变量名2,...;

– 关闭游标

-- 标准语法
CLOSE 游标名称;

• 游标的基本使用

-- 创建stu_score表
CREATE TABLE stu_score(
id INT PRIMARY KEY AUTO_INCREMENT,
score INT
);

/*
将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 创建游标,查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;

-- 开启游标
OPEN stu_result;

-- 使用游标，遍历结果,拿到第1行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第2行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第3行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第4行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 关闭游标
CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表
SELECT * FROM stu_score;


-- ===========================================================
/*
出现的问题：
student表中一共有4条数据，我们在游标遍历了4次，没有问题！
但是在游标中多遍历几次呢？就会出现问题
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 创建游标,查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;

-- 开启游标
OPEN stu_result;

-- 使用游标，遍历结果,拿到第1行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第2行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第3行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第4行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 使用游标，遍历结果,拿到第5行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);

-- 关闭游标
CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错
SELECT * FROM stu_score;

• 游标的优化使用(配合循环使用)

/*
当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
加标记思想：
1.定义一个变量，默认值为0(意味着有数据)
2.当游标结束后，将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*
将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 定义标记变量
DECLARE flag INT DEFAULT 0;
-- 创建游标，查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 游标结束后，将标记变量改为1
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

-- 开启游标
OPEN stu_result;

-- 循环使用游标
REPEAT
-- 使用游标，遍历结果,拿到数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
UNTIL flag=1
END REPEAT;

-- 关闭游标
CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test12存储过程
CALL pro_test12();

-- 查询stu_score表
SELECT * FROM stu_score;

9.存储过程的总结

• 存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理

• 说白了存储过程其实就是将sql语句封装为方法，然后可以调用方法执行sql语句而已

• 存储过程的好处

– 安全

– 高效

– 复用性强

10.存储函数

• 存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！

• 存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)

• 标准语法

– 创建存储函数

DELIMITER $

-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
执行的sql语句;
RETURN 结果;
END$

DELIMITER ;

– 调用存储函数

-- 标准语法
SELECT 函数名称(实际参数);

– 删除存储函数

-- 标准语法
DROP FUNCTION 函数名称;

• 案例演示

/*
定义存储函数，获取学生表中成绩大于95分的学生数量
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
-- 定义统计变量
DECLARE result INT;
-- 查询成绩大于95分的学生数量，给统计变量赋值
SELECT COUNT(*) INTO result FROM student WHERE score > 95;
-- 返回统计结果
RETURN result;
END$

DELIMITER ;

-- 调用fun_test1存储函数
SELECT fun_test1();