概述

本章，您将学习 MySQL 当中的存储过程与存储函数。

函数：为了要实现某个功能，用代码块封装成对外暴露名称且可以被调用的一个基本构造单元。当使用者需要使用函数时，直接调用函数名称并传递参数即可，不用关心具体的代码实现。

存储例程（Stored Routine）：MySQL 官方文档中，将存储过程和存储函数统一称为存储例程。

管理存储过程

存储过程（Stored Procedure）：MySQL 中预编译并存储的 SQL 语句集合，其作用类似于编程语言当中的函数，都是用于完成特定功能‌。

创建存储过程

创建存储过程的基本语法：

create procedure 存储过程名称(参数列表)
begin
    一组或多组 SQL 语句
end 结束标记符

有四种不同的传递参数模式：

• 空参模式 - 存储过程不需要任何输入参数
• in 模式 - 默认模式，参数作为输入值传递给存储过程，存储过程可以读取该输入值但不能改变该输入值。
• out 模式 - 参数用于从存储过程返回值，调用者可以读取存储过程执行之后的输出值。
• inout 模式 - 参数同时作为输入值和返回值，调用者可以向存储过程传递输入值，存储过程可以修改该值并返回输出值。

注意点：

• create procedure 等同于 create or replace procedure，表示创建新的存储过程或更新已有的存储过程

• 调用存储过程的语法为 —— call 存储过程名称(实参列表);

• 存储过程中的每条 SQL 语句必须以 ; 结尾

• 在定义存储过程之前，必须自定义标记符（分隔符），因为存储过程中的每条 SQL 语句都是以 ; 结尾，回车之后的每条 SQL 语句都会被执行，但是存储过程需要执行一组 SQL 语句，所以在定义存储过程之前，必须先自定义标记符（分隔符）。一个完整存储过程的语法如下所示：

  delimiter //
  
  create procedure ...(...)
  begin
      ...;
      ...;
      ...;
  end //
  
  delimiter ;
  
  call ...(...);

• 某些 MySQL 关键字在存储过程的 begin ... end 代码块中不可用

• 若需要使用局部变量，可在 begin ... end 代码块中的第一行使用 declare 关键字对局部变量进行声明并在第二行使用 set 关键字对局部变量赋值

删除存储过程

删除存储过程的语法：

drop procedure if exists 存储过程名称;

查看存储过程

查看存储过程：

# 查看特定存储过程的创建语法
show create procedure 存储过程名称;

# 查看当前 MySQL 实例中所有的存储过程以及相关的信息
show procedure status;

# 使用 like 关键字进行筛选
show procedure status like '%wallet%';

使用存储过程

空参模式

在视图（View）文档中，作者在 home 数据库中创建了两个表—— wallettype 主表以及 homewallet 从表。

空参模式的示例如下：

use home;

show tables from home;
+----------------+
| Tables_in_home |
+----------------+
| homewallet     |
| wallettype     |
+----------------+
2 rows in set (0.00 sec)

select * from wallettype;
+------+----------------+
| code | name           |
+------+----------------+
| bw   | bank wallet    |
| cw   | cash wallet    |
| vw   | virtual wallet |
+------+----------------+
3 rows in set (0.01 sec)

# 创建一个空参模式的存储过程
delimiter //
create procedure homewallet_insert_procedure()
begin
    insert into home.homewallet values('bw', 'jason', 500),('vw', 'emily', 2000),('cw', 'david', 200.5);
end //

delimiter ;

# 调用存储过程
call homewallet_insert_procedure();
Query OK, 3 rows affected (0.01 sec)

select * from homewallet;
+------------+---------+----------+
| walletcode | ownname | amount   |
+------------+---------+----------+
| bw         | jack    | 1000.000 |
| bw         | clinton |    0.000 |
| bw         | jason   |  500.000 |
| vw         | emily   | 2000.000 |
| cw         | david   |  200.500 |
+------------+---------+----------+
5 rows in set (0.00 sec)

由于一开始使用了 ues 关键字，因此该存储过程保存在 home 这个数据库中。

show procedure status like '%wallet%';
+------+-----------------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name                        | Type      | Language | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-----------------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| home | homewallet_insert_procedure | PROCEDURE | SQL      | root@localhost | 2025-10-11 10:54:53 | 2025-10-11 10:54:53 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------+-----------------------------+-----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

in 模式

在创建单个个存储过程时，可以定义多个 in 模式，每个 in 模式用逗号隔开，每个 in 模式的形式如下所示：

IN parameter_name data_type 

一个简单 in 模式的存储过程如下所示：

use home;

delimiter //
create procedure if not exists home_select_in(in home_ownname varchar(40))
begin
    select * from home.homewallet where ownname=home_ownname;
end //

delimiter ;

# 往 home_ownname 参数传递值
call home_select_in('david');

out 模式

在创建单个个存储过程时，可以定义多个 out 模式，每个 out 模式用逗号隔开，每个 out 模式的形式如下所示：

OUT parameter_name data_type 

out 模式的简单示例如下：

use home;

delimiter //
create procedure if not exists sum_and_avg(in a int, in b int, out sum int, out avg decimal(10,2))
begin
    set sum:=a+b;
    set avg:=sum/2;
end //

delimiter ;

call sum_and_avg(5,10,@sum_return,@avg_return);

select @sum_return,@avg_return;
+-------------+-------------+
| @sum_return | @avg_return |
+-------------+-------------+
|          15 |        7.50 |
+-------------+-------------+
1 row in set (0.00 sec)

sum 和 avg 都是 out 模式的参数名，在使用相应的存储过程时，需要使用用户变量来接收 out 模式的输出值。

创建一个存储过程，要求是 —— 需要用户输入大洲名称，并用用户变量接收相应大洲下的国家数量的返回值

use world;

delimiter //
create procedure if not exists continent_country_num(in con_name enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America'), out num int)
begin
    select count(*) into num from country where continent=con_name;
end //

delimiter ;

call continent_country_num('asia',@num_return);

select @num_return;
+-------------+
| @num_return |
+-------------+
|          51 |
+-------------+
1 row in set (0.00 sec)

select count(*) into num from country where continent=con_name; 这条 SQL 语句中，使用 select ... into 语法将 count(*) 查询出来的结果赋值给 num 这个参数。当调用该存储过程时，使用 @num_return 用户变量接收 num 参数的输出值。

inout 模式

在创建单个个存储过程时，可以定义多个 inout 模式，每个 inout 模式用逗号隔开，每个 inout 模式的形式如下所示：

INOUT parameter_name data_type 

简单示例如下：

use home;

delimiter //
create procedure if not exists inout_num(inout a1 int, inout a2 int)
begin
    set a1:=a1*10;
    set a2:=a1+a2;
end //

delimiter ;

# 定义用户变量
set @a1_inout=10, @a2_inout=200;

# 调用存储过程，向参数传递变量值
call inout_num(@a1_inout,@a2_inout);
Query OK, 0 rows affected (0.00 sec)

# 查询输出的值
select @a1_inout,@a2_inout;
+-----------+-----------+
| @a1_inout | @a2_inout |
+-----------+-----------+
|       100 |       300 |
+-----------+-----------+
1 row in set (0.00 sec)

四种模式的使用场景

管理存储函数

创建存储过程

基本语法为：

delimiter //

create function  函数名(参数列表) returns 返回类型
[deterministic | not deterministic]
begin
    函数体/语句块
    return 返回值;
end
//

delimiter ;

select 函数名(参数值);

注意点：

• 必须通过 returns 关键字确定返回值的类型

• 必须在 begin ... end 代码块中使用 return 关键字确定返回值，通常都会将返回值赋予给局部变量

• 存储函数有两种：无参有返回值的存储函数，有参有返回值的存储函数

• deterministic 或 not deterministic 为可选的关键字，用于声明子程序的行为特征（确定性或不确定性）。当未显式声明时，默认为 not deterministic 行为特征

• MySQL 8.x 版本中，为了安全性考虑，系统变量 log_bin_trust_function_creators 的默认值为 0 ，该系统变量主要用于主从复制环境中是否信任用户创建的存储例程。若遇到 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable 错误，请执行以下操作：

  set global log_bin_trust_function_creators=1;
  
  # 退出当前的连接会话
  quit;
  
  # 重新连接
  Shell > /usr/local/mysql8/bin/mysql -u root --password="MyNewPass4!"

• 允许没有参数，也允许一个或多个参数，若有多个参数，请在定义时用逗号隔开，每个参数的形式如下：

  parameter_name data_type

删除存储函数

语法为：

drop function if exists 存储函数名;

查看存储函数

# 查看存储函数的创建语法
show create function 存储函数名;

# 查看所有的存储函数
select * from information_schema.parameters where routine_type='FUNCTION';

使用存储函数

无参有返回值

一个用来统计全世界国家数量的存储函数：

use world;

delimiter //
create function count_function() returns int
begin
    declare countnum int default 0;
    select count(*) into countnum from country;
    return countnum;
end //

delimiter ;

select count_function();
+------------------+
| count_function() |
+------------------+
|              239 |
+------------------+
1 row in set (0.03 sec)

有参有返回值

根据用户输入的大洲名称，返回当前大洲下所有国家 GNP 的平均值：

use world;

delimiter //
create function avg_gnp(continent_name enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')) returns decimal(15,6)
begin
    set @n:=1;
    select avg(gnp) into @n from country where continent=continent_name;
    return @n;
end //

delimiter ;

select avg_gnp('North America');
+--------------------------+
| avg_gnp('North America') |
+--------------------------+
|            261854.789189 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

存储过程与存储函数的对比

Q：存储例程的所有例子都没有逻辑判断，是不是缺少了一些内容？

是的，因为还没有学习有关流程控制的内容。

赞赏