【MySQL】数据库的存储过程与存储函数通关教程（完整版）

【MySQL】数据库的存储过程与存储函数通关教程（完整版）
?? 个人主页：黄小黄的博客主页
支持我：?? 点赞 ?? 收藏 ??关注
?? 格言：一步一个脚印才能承接所谓的幸运

本文来自专栏：MySQL8.0学习笔记
本文参考视频：MySQL数据库全套教程
欢迎点击支持订阅专栏

 文章目录
1 存储过程概述

??简介：

从MySQL5.0版本就开始支持存储过程。简单来说，存储过程就是一组SQL语句集，功能强大，可以实现一些复杂的逻辑功能， 类似Java中的方法。存储过程是数据库SQL语言层面的代码封装与重用。

?? 特性：
- 有输入输出参数，可以声明变量，同样有循环、选择控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
- 具有函数的普遍特性：模块化、封装、代码复用；
- 速度快，只有首次执行的时候需要经过编译和优化步骤，后续被调用可以直接执行。
2 存储过程快速入门

??定义存储过程的语法格式如下：
```
delimiter 自定义结束符号
create procedure 储存名([in, out, inout] 参数名 数据类型...)
begin
 sql语句
end 自定义结束符号
delimiter;
1
2
3
4
5
6
```
在进行操作示例前，先进行数据准备，表的构建代码如下，结果如图：
```
create table student
(
    sid     int         null,
    name    varchar(20) null,
    gender  varchar(20) null,
    age     int         null,
    birth   date        null,
    address varchar(20) null,
    score   double      null
);
1
2
3
4
5
6
7
8
9
10
```
下述代码定义了一个存储过程，查询了分数高于90分（包含90分）的学生姓名及成绩，具体可见代码注释：
在定义存储过程时，自定义结束符号常常使用$$或者//
```
-- 定义存储过程
DELIMITER $$
CREATE PROCEDURE protest01()
BEGIN
    SELECT name, score FROM student WHERE score >= 90;
end $$
DELIMITER ;

-- 调用存储过程
CALL protest01();
1
2
3
4
5
6
7
8
9
10
```
结果如下：

3 变量

 3.1 局部变量

局部变量由用户自定义，只在begin/end块中有效， 其 语法格式如下：
```
declare var_name type [default var_value];
1
```
操作示例：
在下面的代码中，存储过程定义了变量name，并更改了其值，最后查询。
```
DELIMITER $$
CREATE PROCEDURE protest02()
BEGIN
    DECLARE name VARCHAR(20) DEFAULT 'nezuko';
    SET name = '黄小黄';
    SELECT name;
end $$
DELIMITER ;

CALL protest02();
1
2
3
4
5
6
7
8
9
10
```
结果如下：

3.2 用户变量

用户变量也由用户自定义，在当前连接（会话）中有效， 可以类比Java中的成员变量。定义用户变量的 语法格式如下：
```
@var_name  -- 使用即声明
1
```
操作示例：
在下述代码中定义了用户变量@student_name，并尝试在begin/end代码块以外使用。
```
DELIMITER $$
CREATE PROCEDURE protest03()
BEGIN
    SET @student_name = '黄小黄同学';  -- 定义用户变量
end $$
DELIMITER ;

SELECT @student_name;  -- 可以在begin/end外使用用户变量
1
2
3
4
5
6
7
8
```
结果如下：

3.3 系统变量

??系统变量介绍：
- 系统变量又分为全局变量与会话变量；
- 全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通关更改my.ini文件夹更改；
- 会话变量在每次建立一个新连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份，用来做会话变量；
- 如果建立会话变量后，没有手动修改会话变量与全局变量的值，那么这些变量的值一样；
- 全局变量与会话变量的区别在于，全局变量的修改会影响整个服务器，但是会话变量的修改，只会影响到当前的会话（当前数据库连接）；
- 有些系统变量的值是可以通关语句动态修改的，但是有些系统变量是只读的，对于可更改的系统变量使用SET语句进行更改。
3.3.1 全局变量

由系统提供，在整个数据库中有效， 其语法格式如下：
```
@@global.var_name
1
```
操作示例：

（1）查看全局变量：
```
SHOW GLOBAL variables;
1
```
（2）查看某全局变量：
```
SELECT @@global.auto_increment_increment;
1
```
（3）修改全局变量的值：
```
-- 方式1
SET GLOBAL variable_name = xxxxx;
-- 方式2
SET @@global.variable_name = xxxxx;
1
2
3
4
```
3.3.2 会话变量

由系统提供，当前会话有效。 其语法格式如下：
```
@@session.var_name;
1
```
操作示例：

与全局变量相似，只是有略微不同，这里直接上代码了。
```
-- 1.查看会话变量
SHOW SESSION variables;
-- 2.查看某会话变量
SELECT @@session.auto_increment_increment;
-- 3.修改会话变量的值
-- 方式1
SET SESSION variable_name = xxxxx;
-- 方式2
SET @@session.variable_name = xxxxx;
1
2
3
4
5
6
7
8
9
```
4 存储过程中的参数传递

 4.1 参数传递—in

in表示传入的参数，可以传入数值或者变量，即使传入变量，也不会更改变量的值。 可以在内部更改，但仅仅作用在函数内部。

?? 示例：
封装一个具有参数的存储过程param01，传入学生编号，查找学生信息，代码如下：
```
DELIMITER $$
CREATE PROCEDURE param01(IN student_no INT)
BEGIN
    SELECT * FROM student WHERE sid = student_no;
end $$
DELIMITER ;

CALL param01(3)
1
2
3
4
5
6
7
8
```
需要注意的是，参数名尽量不要和待查询的名保持一致，在存储过程中，保持就近原则，即若参数和属性重名，在存储过程内解释为参数！

4.2 参数传递—out

out 表示从存储过程内部传值给调用者。

?? 示例：
封装一个有参数的存储过程，传入学号返回学生姓名，示例代码如下：
```
DELIMITER $$
CREATE PROCEDURE param02(IN student_no INT, OUT out_name VARCHAR(20))
BEGIN
    SELECT name INTO out_name FROM student WHERE sid = student_no;
end $$
DELIMITER ;

CALL param02(2, @o_name);
SELECT @o_name;
1
2
3
4
5
6
7
8
9
```
4.3 参数传递—inout

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入表里的值，又可以修改变量的值（即使函数执行完毕）。

?? 示例：
在下面的代码中，参数num在存储过程中被修改为num+100：
```
DELIMITER $$
CREATE PROCEDURE param03(INOUT num INT)
BEGIN
    SET num = num+100;
end $$
DELIMITER ;

SET @num = 30;
CALL param03(@num);
SELECT @num;
1
2
3
4
5
6
7
8
9
10
```
5 流程控制

 5.1 流程控制—判断

IF语句包含多个判断条件，根据结果为TRUE与FALSE执行语句，语法格式如下：
```
IF search_condition_1 THEN statement_list_1
	[ELSEIF search_condition_2 THEN statement_list_2]
	[ELSE search_condition_n]
END IF;
1
2
3
4
```
?? 示例：
下面代码中的存储过程，模拟了成绩的判断等级：
```
DELIMITER $$
CREATE PROCEDURE test_if(IN score DOUBLE)
BEGIN
    IF score < 60 THEN
        SELECT '不及格';
    ELSEIF score < 75 THEN
        SELECT '及格';
    ELSEIF score < 85 THEN
        SELECT '良好';
    ELSE
        SELECT '优秀';
    END IF;
END $$
DELIMITER ;

CALL test_if(90);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
```
5.2 流程控制—case

CASE类似于编程语言中的switch语法。 语法格式如下：
```
-- 语法一
case case_value
	when when_value then statement_list;
	[when when_value then statement_list;]
	[else statement_list;]
end case;

-- 语法二
case
	when search_condition then statement_list;
	[when search_condition then statement_list;]
	[else statement_list;]
end case;
1
2
3
4
5
6
7
8
9
10
11
12
13
```
5.3 流程控制—循环

?? 概述：
- 循环是一段程序中只出现一次，但可能会连续运行多次的代码；
- 循环的代码会运行特定的次数，或者是运行到特定条件结束。
?? 分类：
- while
- repeat
- loop
?? 循环控制：
- leave类似break；
- iterate类似continue。
5.3.1 while

语法格式：
```
【标签:】while 循环条件 do
	循环体
end while【标签:】
1
2
3
```
操作示例：
创建一个操作表test，包含两个属性(学号、用户名)，定义存储过程为其添加n条数据，测试数据n=10。参考代码及结果：
```
-- 1 创建表
CREATE TABLE IF NOT EXISTS test(
    id INT,
    username VARCHAR(10)
);
-- 2 定义存储过程
DELIMITER $$
CREATE PROCEDURE myinsert(IN insert_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    lable:WHILE i <= insert_count DO
        INSERT INTO test VALUES (i, concat('user', i));
        SET i = i + 1;
    END WHILE lable;
end $$
DELIMITER ;
-- 3 调用存储过程
CALL myinsert(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
```
5.3.2 repeat

类似于do-while循环，其格式如下：
```
【标签:】repeat
	循环体;
until 条件表达式
end repeat【标签】;
1
2
3
4
```
5.3.3 loop

一般还是使用while循环，更加简洁。
语法格式：
```
【标签:】loop
	循环体;
	if 条件表达式 then
		leave 【标签】
	end if;
end loop;
1
2
3
4
5
6
```
6 游标

游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括声明、open、fetch、close。
语法格式：
```
-- 声明
declare cursor_name cursor for select_statement;

-- 打开
open cursor_name;

-- 取值
fetch cursor_name into var_name [, var_name]...;

-- 关闭
close curcor_name;
1
2
3
4
5
6
7
8
9
10
11
```
?? 演示游标的使用：
输入一个学号，查询学生姓名和地址信息。
```
DELIMITER $$
CREATE PROCEDURE search(IN student_no INT)
BEGIN
    DECLARE student_name VARCHAR(20);
    DECLARE student_address VARCHAR(20);

    -- 1 声明游标
    DECLARE my_cursor CURSOR FOR
        SELECT name, address FROM student WHERE sid = student_no;

    -- 2 打开游标
    OPEN my_cursor;

    -- 3 取值
    FETCH my_cursor INTO student_name, student_address;
    SELECT student_name 姓名, student_address 地址;

    -- 4 关闭
    CLOSE my_cursor;
END $$
DELIMITER ;

CALL search(1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
```
7 异常处理

在MySQL中异常处理通过定义HANDLER来完成异常声明的实现，语法格式如下：
```
DECLARE handler_action HANDLER
	FOR condition_value[, condition_value]...
	statement

handler_action:{
	CONTINUE
	|EXIT
	|UNION
}

condition_value:{
	mysql_orror_codo
	|condition_name
	|SQLWARNING
	|NOT FOUND
	|SQLEXCEPTION
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
异常处理详细内容可以查阅官方相关文档。

8 存储函数

格式：
```
create function func_name ([param_name type[, ...]])
returns type
[characteristic ...]
begin
	routine_body
end;
1
2
3
4
5
6
```
?? 参数说明：
1. func_name：存储函数的名称；
2. param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型必须是MySQL所支持的参数类型；
3. RETURNS type：指定返回值的类型；
4. characteristic：可选项，指定存储函数的特性；
5. routine_body：SQL代码内容。
存储函数的使用方式类似于存储过程，只不过函数本身可以有返回值，而存储过程需要使用参数进行值的返回。

需要注意的是，在创建存储函数时，必须允许创建函数的权限信任，否则不能创建

代码如下：
```
set global log_bin_trust_function_creators = TRUE;
1
```
写在最后

??以上便是本文的全部内容啦，后续内容将会持续免费更新，如果文章对你有所帮助，麻烦动动小手点个赞 + 关注，非常感谢 !
如果有问题，欢迎私信或者评论区！

共勉：“你间歇性的努力和蒙混过日子，都是对之前努力的清零。”

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦
相关阅读:
【Lodash】 Filter 与Map 的结合使用
 Linux 部署express项目，并使用pm2进程守护
 超实用！推荐5款办公黑科技软件，用了就离不开
 【LeetCode刷题笔记】动态规划 — 70.爬楼梯
 干货|工作中要使用Git，看这篇文章就够了
 打印请求头
 Python 图片处理笔记
 2.GET和POST的区别
 手把手教你使用Git管理你的软件代码
 前端需要了解的linux命令
原文地址：https://blog.csdn.net/m0_67402235/article/details/126080620

文章目录

1 存储过程概述

2 存储过程快速入门

3 变量

3.1 局部变量

3.2 用户变量

3.3 系统变量

3.3.1 全局变量

3.3.2 会话变量

4 存储过程中的参数传递

4.1 参数传递—in

4.2 参数传递—out

4.3 参数传递—inout

5 流程控制

5.1 流程控制—判断

5.2 流程控制—case

5.3 流程控制—循环

5.3.1 while

5.3.2 repeat

5.3.3 loop

6 游标

7 异常处理

8 存储函数

写在最后