如何在 MySQL 中使用存储过程

介绍

通常，在使用关系数据库时，您会在应用程序代码中直接发出结构化查询语言 (SQL) 语句来检索或操作数据，例如 SELECT、INSERT、UPDATE 或 DELETE。这些语句直接操作底层数据库表。如果多个应用程序访问同一个数据库时使用相同的语句或语句组，则这些语句通常会在各个应用程序中重复出现。.

与许多其他关系型数据库管理系统一样，MySQL 支持使用存储过程。存储过程可以将一个或多个 SQL 语句分组，以便使用通用名称进行重用，并将常见的业务逻辑封装在数据库内部。应用程序可以通过访问数据库来调用存储过程，从而以一致的方式检索或操作数据。.

使用存储过程，您可以创建可重用的例程来执行常见任务，以便在多个应用程序中使用，验证数据，或者通过限制数据库用户直接访问底层表和发出任意查询来提供额外的数据访问安全层。.

在本教程中，您将学习什么是存储过程，以及如何创建返回数据并使用输入和输出参数的基本存储过程。.

先决条件

要遵循本指南，您需要一台运行基于 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已在以下环境中验证：

服务器运行Ubuntu 20.04，有一个具有管理员权限的非root用户，并且配置了UFW防火墙。
服务器上已安装并配置了 MySQL 安全措施。
执行 SELECT 查询从数据库中检索数据的基本介绍

注意：许多关系数据库管理系统 (RDBMS) 使用各自独特的 SQL 实现，存储过程的语法并非 SQL 官方标准的一部分。虽然本教程中提到的命令可能在其他 RDBMS 中也能运行，但存储过程是数据库特有的，因此如果您在 MySQL 以外的系统上测试这些命令，其确切的语法或输出结果可能会有所不同。.

您还需要一个空数据库，以便使用存储过程创建表。我们建议您按照下文“连接到 MySQL 并设置示例数据库”部分中的说明，了解如何连接到 MySQL 服务器以及创建本指南示例中使用的测试数据库。.

连接到 MySQL 并设置示例数据库

在本节中，您将连接到 MySQL 服务器并创建一个示例数据库，以便您可以使用本指南中的示例。.

在本指南中，您将使用一个虚构的汽车收藏。您需要记录您目前拥有的汽车的详细信息，包括它们的品牌、型号、生产年份和价值。.

如果您的 SQL 数据库系统运行在远程服务器上，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示符，并将 Sami 替换为您的 MySQL 用户名：

mysql -u sammy -p

创建一个名为 processes 的数据库：

CREATE DATABASE procedures;

如果数据库创建成功，您将收到以下输出：

Output
Query OK, 1 row affected (0.01 sec)

要选择过程数据库，请运行以下 USE 语句：

USE procedures;

您将得到以下输出：

Output
Database changed

选择数据库后，即可在其中创建实例表。表 machines 将包含数据库中机器的简化数据。它包含以下列：

品牌：此列表示其拥有的每辆车，使用 varchar 数据类型表示，最多 100 个字符。.
型号：此列包含汽车型号名称，使用 varchar 数据类型表示，最多 100 个字符。.
年份：此列存储汽车的生产年份，数据类型为 int，用于保存数值。.
值：此列存储汽车的值，使用十进制数据类型，最多 10 位数字，小数点后保留 2 位。.

使用以下命令创建示例表：

CREATE TABLE cars (
make varchar(100),
model varchar(100),
year int,
value decimal(10, 2)
);

如果打印出以下输出，则表示表格已创建：

Output
Query OK, 0 rows affected (0.00 sec)

然后，通过执行以下 INSERT INTO 操作，将示例数据加载到 Cars 表中：

INSERT INTO cars
VALUES
('Porsche', '911 GT3', 2020, 169700),
('Porsche', 'Cayman GT4', 2018, 118000),
('Porsche', 'Panamera', 2022, 113200),
('Porsche', 'Macan', 2019, 27400),
('Porsche', '718 Boxster', 2017, 48880),
('Ferrari', '488 GTB', 2015, 254750),
('Ferrari', 'F8 Tributo', 2019, 375000),
('Ferrari', 'SF90 Stradale', 2020, 627000),
('Ferrari', '812 Superfast', 2017, 335300),
('Ferrari', 'GTC4Lusso', 2016, 268000);

INSERT INTO 操作向表中添加了十辆示例跑车，其中五辆是保时捷，五辆是法拉利。以下输出显示所有五行数据均已添加：

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

至此，您就可以继续阅读本指南的其余部分，开始在 SQL 中使用存储过程了。.

存储过程简介

在 MySQL 和许多其他关系型数据库系统中，存储过程被称为对象，它包含一条或多条指令，这些指令会在数据库调用时按顺序执行。最简单的例子是，存储过程可以将常用表达式存储在可重用的例程中，例如使用常用筛选条件从数据库中检索数据。例如，您可以创建一个存储过程来检索最近几个月在网店下过单的客户。在更复杂的场景中，存储过程可以代表大型程序，用于描述功能强大的应用程序的复杂业务逻辑。.

存储过程中的指令集可以包含常见的 SQL 语句，例如 SELECT 或 INSERT 查询，用于返回或操作数据。此外，存储过程还可以使用以下功能：

参数会传递给存储过程或从存储过程返回。.
在过程代码中直接声明用于处理检索到的数据的变量。.
条件语句允许根据某些条件执行存储过程代码的某些部分，例如 IF 或 CASE 语句。.
WHILE、LOOP 和 REPEAT 等循环允许多次执行代码段，例如对检索到的数据集中的每一行执行一次。.
错误处理指令，例如向有权访问该过程的数据库用户返回错误消息。.
调用数据库中的其他存储过程。.

注意：MySQL 支持的丰富语法使得编写功能强大的程序并使用存储过程解决复杂问题成为可能。本指南仅涵盖存储过程的基本用法，包括将 SQL 语句包含在存储过程体中以及输入和输出参数。条件代码执行、变量的使用、循环和自定义错误处理超出了本指南的范围。我们建议您阅读 MySQL 官方文档，了解更多关于存储过程的信息。.

当按名称调用该过程时，数据库引擎会按照定义逐条指令执行它。.

数据库用户必须拥有执行指定过程的相应权限。此权限要求提供了一层安全保障，既禁止直接访问数据库，又允许用户访问可以安全执行的单个过程。.

存储过程直接在数据库服务器上运行，所有计算都在本地执行，并且仅在完成后才将结果返回给调用用户。.

如果要更改某个过程的行为，只需更新数据库中的该过程，使用该过程的应用程序将自动接收新版本。所有用户无需调整应用程序即可立即开始使用新的过程代码。.

以下是用于创建存储过程的 SQL 代码的一般结构：

DELIMITER //
CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
BEGIN
instruction_1;
instruction_2;
. . .
instruction_n;
END //
DELIMITER ;

这段代码片段中的第一个和最后一个指令分别是 `DELIMITER //` 和 `DELIMITER ;`。MySQL 通常使用分号 (;) 来分隔语句，并指示语句的开始和结束。如果您在 MySQL 控制台中运行多个以分号分隔的命令，它们会被视为单独的命令并依次独立执行。但是，存储过程可以包含多个命令，这些命令在被调用时会按顺序执行。当您尝试指示 MySQL 创建一个新过程时，就会出现问题。数据库引擎会在存储过程的主体中遇到分号，并认为应该停止执行该命令。在这种情况下，有问题的命令是整个过程创建代码，而不是过程本身中的单个指令，因此 MySQL 会误解您的意图。.

为了克服这个限制，可以使用 DELIMITER 语句在 CREATE PROCEDURE 调用期间临时将分隔符从 ; 更改为 //。这样，存储过程主体内的所有分号都会原封不动地发送到服务器。整个过程完成后，分隔符会通过最后一个 DELIMITER ; 语句恢复为 ;。.

创建新存储过程的代码核心是 CREATE PROCEDURE 调用，后面跟着存储过程名称：例如，procedure_name。存储过程名称之后是可选的参数列表，这些参数是存储过程接受的。最后一部分是存储过程主体，包含在 BEGIN 和 END 语句中。存储过程代码内部可以包含 SQL 语句，例如 SELECT 查询，或者更复杂的代码。.

END 命令以临时分隔符 // 结尾，而不是以普通的分号结尾。.

下一节中，您将创建一个基本的无参数存储过程来封装查询。.

创建不带参数的存储过程

在本节中，您将创建第一个存储过程，其中包含一个 SQL SELECT 语句，用于返回按品牌和价值降序排列的车辆列表。.

首先执行您想要使用的 SELECT 语句：

SELECT * FROM cars ORDER BY make, value DESC;

数据库从 Cars 表中返回汽车列表，首先按品牌排序，然后在同一品牌内按价值降序排列：

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)

最值钱的法拉利排在榜首，最不值钱的保时捷排在榜末。.

假设此查询在多个应用程序或由多个用户重复使用，并且您希望确保每个人都使用相同的方法来对结果进行排序。为此，您需要创建一个存储过程，将该表达式存储在一个可重用的命名过程中。.

要创建此存储过程，请运行以下代码片段：

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
SELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

如上一节所述，第一个和最后一个语句（DELIMITER // 和 DELIMITER 😉）告诉 MySQL 在过程创建期间忽略分号字符作为语句分隔符。.

CREATE PROCEDURE SQL 语句之后是过程名称 get_all_cars，您可以自定义该名称以更好地描述过程的功能。过程名称之后是一对圆括号 ()，您可以在其中添加参数。在本例中，该过程未使用参数，因此圆括号为空。然后，在定义过程代码块开始和结束的 BEGIN 和 END 语句之间，原封不动地写入之前使用的 SELECT 语句。.

数据库将返回成功消息：

Output
Query OK, 0 rows affected (0.02 sec)

get_all_cars 过程现在存储在数据库中，调用时会按原样执行存储的语句。.

要执行存储过程，可以使用 CALL SQL 命令，后跟过程名称。尝试像这样执行新创建的过程：

CALL get_all_cars;

您只需要存储过程名称 `get_all_cars` 即可使用该存储过程。您不再需要手动输入之前使用的 SELECT 语句的任何部分。数据库会像执行 SELECT 语句的输出一样执行结果：

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | SF90 Stradale | 2020 | 627000.00 |
| Ferrari | F8 Tributo | 2019 | 375000.00 |
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Ferrari | GTC4Lusso | 2016 | 268000.00 |
| Ferrari | 488 GTB | 2015 | 254750.00 |
| Porsche | 911 GT3 | 2020 | 169700.00 |
| Porsche | Cayman GT4 | 2018 | 118000.00 |
| Porsche | Panamera | 2022 | 113200.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
| Porsche | Macan | 2019 | 27400.00 |
+---------+---------------+------+-----------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

您已成功创建了一个不带任何参数的存储过程，该过程会按特定顺序返回 Cars 表中的所有车辆。您可以在多个应用程序中使用此方法。.

在下一节中，您将创建一个接受参数的过程，以便根据用户输入改变过程的行为。.

创建带有输入参数的存储过程

在本节中，您可以向存储过程定义添加输入参数，以便运行该过程的用户能够向其传递数据。例如，用户可以提供查询筛选条件。.

之前的存储过程 `get_all_cars` 总是从 `cars` 表中检索所有车辆。现在我们创建另一个存储过程，用于查找指定年份生产的车辆。为此，您需要在存储过程定义中定义一个名为 `get_all_cars` 的参数。.

运行以下代码：

DELIMITER //
CREATE PROCEDURE get_cars_by_year(
IN year_filter int
)
BEGIN
SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

与上一节相比，程序创建代码有几处改动。.

首先，名称是 get_cars_by_year，它描述了该方法：根据汽车的生产年份检索汽车。.

前面的空括号现在包含一个参数定义：`IN year_filter int`。`IN` 关键字告诉数据库，该参数是由调用用户传递给存储过程的。`year_filter` 是参数的任意名称，您将在存储过程代码中使用它来引用该参数。最后，`int` 是数据类型。在本例中，生产年份以数值形式表示。.

在过程名称之后定义的 year_filter 参数出现在 SELECT 语句的 WHERE year = year_filter 子句中，并按汽车的生产年份筛选汽车表。.

数据库将再次返回成功消息：

Output
Query OK, 0 rows affected (0.02 sec)

尝试像之前一样，不传递任何参数运行该过程：

CALL get_cars_by_year;

MySQL数据库返回错误信息：

Error message
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

这次，存储过程需要一个参数，但没有提供任何参数。要调用带参数的存储过程，可以按照过程期望的顺序，将参数值放在括号内。要检索 2017 年生产的汽车，请运行以下命令：

CALL get_cars_by_year(2017);

现在被调用的过程能够正确执行，并返回该年份的汽车列表：

Output
+---------+---------------+------+-----------+
| make | model | year | value |
+---------+---------------+------+-----------+
| Ferrari | 812 Superfast | 2017 | 335300.00 |
| Porsche | 718 Boxster | 2017 | 48880.00 |
+---------+---------------+------+-----------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

在这个例子中，你学习了如何将输入参数传递给存储过程，并在过程内的查询中使用这些参数来提供筛选选项。.

在下一节中，您将使用输出参数来创建在一次执行中返回多个不同值的过程。.

创建带有输入和输出参数的存储过程

在前两个示例中，您创建的存储过程都调用了 SELECT 语句来返回一组结果。但在某些情况下，您可能需要一个存储过程，该存储过程可以一次性返回多个不同的值，而不是为查询返回单个结果集。.

假设你想创建一个程序，提供有关给定年份汽车的汇总信息，包括收藏中的汽车数量及其市场价值（最小值、最大值和平均值）。.

为此，您可以在创建新的存储过程时使用 OUT 参数。与 IN 参数类似，OUT 参数也具有名称和数据类型。但是，OUT 参数不是将数据传递给存储过程，而是可以从存储过程中获取数据，并将值返回给调用用户。.

创建一个名为 get_car_stats_by_year 的过程，该过程使用输出参数返回给定生产年份的汽车的汇总数据：

DELIMITER //
CREATE PROCEDURE get_car_stats_by_year(
IN year_filter int,
OUT cars_number int,
OUT min_value decimal(10, 2),
OUT avg_value decimal(10, 2),
OUT max_value decimal(10, 2)
)
BEGIN
SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
INTO cars_number, min_value, avg_value, max_value
FROM cars
WHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

这次，除了用于按生产年份筛选车辆的输入参数 year_filter 之外，括号内还定义了四个输出参数。cars_number 参数的数据类型为 int，用于返回集合中的车辆数量。min_value、avg_value 和 max_value 参数表示市场价值，数据类型为 decimal (10, 2)（类似于 cars 表中的 value 列）。这些参数用于返回集合中最便宜和最贵的车辆信息，以及所有匹配车辆的平均价格。.

SELECT 语句使用 SQL 数学函数从 cars 表中查找四个值：COUNT 函数用于获取汽车总数，MIN、AVG 和 MAX 函数用于从 value 列中获取最小值、平均值和最大值。.

为了告诉数据库该查询的结果应该存储在存储过程的输出参数中，引入了一个名为 INTO 的新关键字。在 INTO 关键字后面，列出了与检索到的数据相关的四个过程参数的名称。这将使 MySQL 将 COUNT(*) 的值存储在 cars_number 参数中，将 MIN(value) 的结果存储在 min_value 参数中，依此类推。.

数据库确认该过程已成功创建：

Output
Query OK, 0 rows affected (0.02 sec)

现在运行以下新程序：

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

这四个新参数以 @ 符号开头。它们是 MySQL 控制台中的局部变量名，可用于临时存储数据。当您将它们传递给创建的存储过程时，该过程会将值插入到这些变量中。.

数据库将返回以下内容：

Output
Query OK, 1 row affected (0.00 sec)

这与之前的行为不同，之前的结果会立即显示在屏幕上。这是因为存储过程的结果存储在输出参数中，而不是作为查询结果返回。要访问结果，您可以直接在 MySQL shell 中选择它们，如下所示：

SELECT @number, @min, @avg, @max;

通过此查询，您可以从局部变量中选择值，而无需再次调用存储过程。存储过程会将结果存储在这些变量中，并且数据在您退出 shell 之前一直可用。.

注意：有关在 MySQL 中使用用户自定义变量的更多信息，请参阅文档中的“用户自定义变量”部分。在应用程序开发中，访问存储过程返回数据的方法会因编程语言和框架的不同而有所差异。如有疑问，请参阅您所选语言和框架的文档。.

输出结果显示查询变量的值：

Output
+---------+----------+-----------+-----------+
| @number | @min | @avg | @max |
+---------+----------+-----------+-----------+
| 2 | 48880.00 | 192090.00 | 335300.00 |
+---------+----------+-----------+-----------+
1 row in set (0.00 sec)

这些数值对应于 2017 年生产的汽车数量，以及该生产年份汽车的最低、平均和最高市场价值。.

在本示例中，您学习了如何使用输出参数从存储过程中返回多个不同的值以供后续使用。下一节将介绍如何删除已创建的存储过程。.

删除存储过程

在本节中，您将删除数据库中的存储过程。.

有时，您创建的存储过程可能不再需要。在其他情况下，您可能需要更改存储过程的运行方式。MySQL 不允许您在创建存储过程后更改其定义，因此唯一的方法是先删除该存储过程，然后再使用所需的更改重新创建它。.

让我们删除最后一个过程 get_car_stats_by_year。为此，您可以使用 DROP PROCEDURE 语句：

DROP PROCEDURE get_car_stats_by_year;

数据库确认已成功删除该程序，并显示成功消息：

Output
Query OK, 0 rows affected (0.02 sec)

您可以通过尝试调用该过程来验证它是否已被删除。运行：

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

这次，你会看到一条错误消息，提示数据库中不存在该过程：

Error message
ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist

在本节中，您学习了如何从数据库中删除存储过程。.

结果

通过本指南，您学习了什么是存储过程以及如何在 MySQL 中使用存储过程来存储可重用的语句，并在之后执行它们。您创建了不带参数的存储过程，以及使用输入和输出参数的存储过程，从而使它们更加灵活。.

如何在 MySQL 中使用存储过程

介绍

先决条件

连接到 MySQL 并设置示例数据库

存储过程简介

创建不带参数的存储过程

创建带有输入参数的存储过程

创建带有输入和输出参数的存储过程

删除存储过程

结果

在本文中：

文章作者：哈迪·巴哈多里

发表回复

教育如何在 SQL 中使用主键

教育JavaScript 中的字符串对象

如何创建一个专用的 CS2（反恐精英2）服务器

《黑暗之魂》系列的故事

Wbuntu：外观类似 Windows 11 的 Ubuntu 操作系统

使用 Olama 和 Open WebUI 托管 AI 聊天机器人

如何使用 Netcat 创建和测试 TCP 和 UDP 连接

VirtualBox 安装教程

如何在 Ubuntu 20.04 中配置软件仓库

如何使用Python播放音频？

如何在 MySQL 中使用存储过程

介绍

先决条件

连接到 MySQL 并设置示例数据库

存储过程简介

创建不带参数的存储过程

创建带有输入参数的存储过程

创建带有输入和输出参数的存储过程

删除存储过程

结果

在本文中：

文章作者： 哈迪·巴哈多里

跟随

发表回复

您可能也喜欢

文章作者：哈迪·巴哈多里