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

💁 个人主页：黄小黄的博客主页
❤️ 支持我：👍 点赞 🌷 收藏 🤘关注
🎏 格言：一步一个脚印才能承接所谓的幸运

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

文章目录

1 存储过程概述

🆔简介：

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

🦁 特性：

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

2 存储过程快速入门

🐱定义存储过程的语法格式如下：

delimiter 自定义结束符号
createprocedure 储存名([in,out,inout] 参数名 数据类型...)beginsql语句
end 自定义结束符号
delimiter;

在进行操作示例前，先进行数据准备，表的构建代码如下，结果如图：

createtable student
(
    sid     intnull,
    name    varchar(20)null,
    gender  varchar(20)null,
    age     intnull,
    birth   datenull,
    address varchar(20)null,
    score   doublenull);

下述代码定义了一个存储过程，查询了分数高于90分（包含90分）的学生姓名及成绩，具体可见代码注释：
在定义存储过程时，自定义结束符号常常使用$$或者//

-- 定义存储过程DELIMITER $$
CREATEPROCEDURE protest01()BEGINSELECT name, score FROM student WHERE score >=90;end $$
DELIMITER;-- 调用存储过程CALL protest01();

结果如下：

3 变量

3.1 局部变量

局部变量由用户自定义，只在begin/end块中有效， 其 语法格式如下：

declare var_name type[default var_value];

⭕️操作示例：
在下面的代码中，存储过程定义了变量name，并更改了其值，最后查询。

DELIMITER $$
CREATEPROCEDURE protest02()BEGINDECLARE name VARCHAR(20)DEFAULT'nezuko';SET name ='黄小黄';SELECT name;end $$
DELIMITER;CALL protest02();

结果如下：

3.2 用户变量

用户变量也由用户自定义，在当前连接（会话）中有效， 可以类比Java中的成员变量。定义用户变量的 语法格式如下：

@var_name-- 使用即声明

⭕️操作示例：
在下述代码中定义了用户变量@student_name，并尝试在begin/end代码块以外使用。

DELIMITER $$
CREATEPROCEDURE protest03()BEGINSET@student_name='黄小黄同学';-- 定义用户变量end $$
DELIMITER;SELECT@student_name;-- 可以在begin/end外使用用户变量

结果如下：

3.3 系统变量

🆔系统变量介绍：

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

3.3.1 全局变量

由系统提供，在整个数据库中有效， 其语法格式如下：

@@global.var_name

⭕️操作示例：

（1） 查看全局变量：

SHOWGLOBAL variables;

（2）查看某全局变量：

SELECT @@global.auto_increment_increment;

（3）修改全局变量的值：

-- 方式1SETGLOBAL variable_name = xxxxx;-- 方式2SET @@global.variable_name= xxxxx;

3.3.2 会话变量

由系统提供，当前会话有效。 其语法格式如下：

@@session.var_name;

⭕️操作示例：

与全局变量相似，只是有略微不同，这里直接上代码了。

-- 1.查看会话变量SHOWSESSION variables;-- 2.查看某会话变量SELECT @@session.auto_increment_increment;-- 3.修改会话变量的值-- 方式1SETSESSION variable_name = xxxxx;-- 方式2SET @@session.variable_name= xxxxx;

4 存储过程中的参数传递

4.1 参数传递—in

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

🐰 示例：
封装一个具有参数的存储过程param01，传入学生编号，查找学生信息，代码如下：

DELIMITER $$
CREATEPROCEDURE param01(IN student_no INT)BEGINSELECT*FROM student WHERE sid = student_no;end $$
DELIMITER;CALL param01(3)

需要注意的是，参数名尽量不要和待查询的名保持一致，在存储过程中，保持就近原则，即若参数和属性重名，在存储过程内解释为参数！

4.2 参数传递—out

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

🐰 示例：
封装一个有参数的存储过程，传入学号返回学生姓名，示例代码如下：

DELIMITER $$
CREATEPROCEDURE param02(IN student_no INT,OUT out_name VARCHAR(20))BEGINSELECT name INTO out_name FROM student WHERE sid = student_no;end $$
DELIMITER;CALL param02(2,@o_name);SELECT@o_name;

4.3 参数传递—inout

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

🐰 示例：
在下面的代码中，参数num在存储过程中被修改为num+100：

DELIMITER $$
CREATEPROCEDURE param03(INOUT num INT)BEGINSET num = num+100;end $$
DELIMITER;SET@num=30;CALL param03(@num);SELECT@num;

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]ENDIF;

🐰 示例：
下面代码中的存储过程，模拟了成绩的判断等级：

DELIMITER $$
CREATEPROCEDURE test_if(IN score DOUBLE)BEGINIF score <60THENSELECT'不及格';ELSEIF score <75THENSELECT'及格';ELSEIF score <85THENSELECT'良好';ELSESELECT'优秀';ENDIF;END $$
DELIMITER;CALL test_if(90);

5.2 流程控制—case

CASE类似于编程语言中的switch语法。 语法格式如下：

-- 语法一case case_value
    when when_value then statement_list;[when when_value then statement_list;][else statement_list;]endcase;-- 语法二casewhen search_condition then statement_list;[when search_condition then statement_list;][else statement_list;]endcase;

5.3 流程控制—循环

🆔 概述：

• 循环是一段程序中只出现一次，但可能会连续运行多次的代码；
• 循环的代码会运行特定的次数，或者是运行到特定条件结束。

🅰️ 分类：

• while
• repeat
• loop

🅱️ 循环控制：

• leave类似break；
• iterate类似continue。

5.3.1 while

语法格式：

【标签:】while 循环条件 do
    循环体
endwhile【标签:】

⭕️操作示例：
创建一个操作表test，包含两个属性(学号、用户名)，定义存储过程为其添加n条数据，测试数据n=10。参考代码及结果：

-- 1 创建表CREATETABLEIFNOTEXISTS test(
    id INT,
    username VARCHAR(10));-- 2 定义存储过程DELIMITER $$
CREATEPROCEDURE myinsert(IN insert_count INT)BEGINDECLARE i INTDEFAULT1;
    lable:WHILE i <= insert_count DOINSERTINTO test VALUES(i, concat('user', i));SET i = i +1;ENDWHILE lable;end $$
DELIMITER;-- 3 调用存储过程CALL myinsert(10);

5.3.2 repeat

类似于do-while循环，其格式如下：

【标签:】repeat
    循环体;
until 条件表达式
endrepeat【标签】;

5.3.3 loop

一般还是使用while循环，更加简洁。
语法格式：

【标签:】loop
    循环体;if 条件表达式 thenleave 【标签】
    endif;endloop;

6 游标

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

-- 声明declare cursor_name cursorfor select_statement;-- 打开open cursor_name;-- 取值fetch cursor_name into var_name [, var_name]...;-- 关闭close curcor_name;

🐰 演示游标的使用：
输入一个学号，查询学生姓名和地址信息。

DELIMITER $$
CREATEPROCEDURE search(IN student_no INT)BEGINDECLARE student_name VARCHAR(20);DECLARE student_address VARCHAR(20);-- 1 声明游标DECLARE my_cursor CURSORFORSELECT 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);

7 异常处理

在MySQL中异常处理通过定义HANDLER来完成异常声明的实现，语法格式如下：

DECLARE handler_action HANDLERFOR condition_value[, condition_value]...
    statement

handler_action:{
    CONTINUE|EXIT|UNION
}

condition_value:{
    mysql_orror_codo
    |condition_name
    |SQLWARNING
    |NOT FOUND
    |SQLEXCEPTION
}

异常处理详细内容可以查阅官方相关文档。

8 存储函数

格式：

createfunction func_name ([param_name type[,...]])returnstype[characteristic ...]begin
    routine_body
end;

🐰 参数说明：

1. func_name：存储函数的名称；
2. param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型必须是MySQL所支持的参数类型；
3. RETURNS type：指定返回值的类型；
4. characteristic：可选项，指定存储函数的特性；
5. routine_body：SQL代码内容。

存储函数的使用方式类似于存储过程，只不过函数本身可以有返回值，而存储过程需要使用参数进行值的返回。

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

代码如下：

setglobal log_bin_trust_function_creators =TRUE;

写在最后

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

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