如何使用SQL系列 之 如何在MySQL中使用存储过程

引言

通常，当使用关系型数据库时，你直接在应用程序代码中发出单独的结构化查询语言(SQL)查询来检索或操作数据，如SELECT、INSERT、UPDATE或DELETE。这些语句直接作用于并操作底层数据库表。如果相同的语句或一组语句中使用多个应用程序访问同一个数据库,他们经常重复的在单独的应用程序。

与许多其他关系型数据库管理系统类似，MySQL支持使用存储过程。存储过程帮助组织一个或多个SQL语句重用在一个共同的名字,将常见的业务逻辑封装在数据库本身。这样的过程可以从访问数据库的应用程序中调用，以一致的方式检索或操作数据。

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

在本教程中,您将了解哪些存储过程以及如何创建基本的存储过程,返回数据,并使用输入和输出参数。

前期准备

遵循这个指南,你将需要一个计算机运行一个基于sql的关系数据库管理系统(RDBMS)。本指南中的说明和示例使用以下环境进行了验证：

• 基本熟悉执行SELECT查询以从数据库中检索数据，如如何从SQL指南中的表中选择行所述。

注意：许多RDBMS使用它们自己的SQL实现。虽然触发提到作为一个SQL标准的一部分,标准不严格执行他们的语法或实现它们的方法。因此，它们的实现在不同的数据库中是不同的。本教程中概述的命令使用MySQL数据库的语法，可能无法在其他数据库引擎上工作。

你还需要一个数据库，其中一些表加载了示例数据，这样你就可以练习使用函数。我们鼓励您通过以下连接到示例数据库MySQL和建立一个连接到MySQL服务器上部分细节，创建测试数据库在本指南中使用的例子。

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

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器：

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将sammy替换为你的MySQL用户账户的名称：

mysql -u sammy -p

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

CREATE DATABASE procedures;

如果数据库成功创建,您将收到这样的输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择procedures数据库，运行以下USE语句：

USE procedures;

OutputDatabase changed

选择数据库后，您可以在其中创建示例表。cars表将包含数据库中关于汽车的简化数据。它将保存以下列：

• make：这一列保存了每辆汽车的make值，使用最多100个字符的varchar数据类型表示。
• model：这一列保存了汽车模型名称，使用varchar数据类型表示，最多不超过100个字符。
• year：这一列存储汽车的制造年份，使用int数据类型来保存数值。
• value：该列使用decimal数据类型存储汽车的值，最多为10位，小数点后为2位。

使用下面的命令创建示例表：

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

如果输出结果如下所示，说明表已经创建：

OutputQuery 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);

INSERTINTO操作将向表格中添加10辆跑车样本，其中包括5辆保时捷和5辆法拉利。下面的输出表明已经添加了5行数据：

OutputQuery 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)
BEGINinstruction_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()
BEGINSELECT * FROM cars ORDER BY make, value DESC;
END //
DELIMITER ;

如上一节所述，第一个和最后一个命令(DELIMITER //和DELIMITER;)告诉MySQL在创建过程期间停止将分号作为语句分隔符。

CREATE PROCEDURE SQL命令后面跟着过程名称get_all_cars，您可以定义该名称以最好地描述过程的功能。在过程名之后，有一对括号()，您可以在其中添加参数。在这个例子中，过程不使用参数，因此括号为空。然后，在定义过程代码块开始和结束的BEGIN和END命令之间，之前使用的SELECT语句会一字不差地编写。
注意：取决于你的MySQL用户权限，在执行CREATE PROCEDURE命令时，你可能会收到一个错误:ERROR 1044 (42000): Access denied for user 'sammy'@'localhost' to database 'procedures'。要授予用户创建和执行存储过程的权限，请以root身份登录MySQL并执行以下命令，根据需要替换MySQL用户名和主机：

GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

更新用户权限后，以root身份注销，以该用户身份重新登录，并重新运行CREATE PROCEDURE语句。

您可以在Stored Routines and MySQL Privileges文档中了解有关为数据库用户应用存储过程权限的更多信息。

数据库会响应一个成功的信息：

OutputQuery 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表中检索所有汽车。让我们创建另一个程序找到汽车从一个给定的制造业。为此，需要在过程定义中定义一个命名参数。

运行下面的代码：

DELIMITER //
CREATE PROCEDURE get_cars_by_year(IN year_filter int
)
BEGINSELECT * 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参数出现在WHERE year =year_filter 子句中的SELECT语句中，根据生产年份过滤cars表。

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

OutputQuery OK, 0 rows affected (0.02 sec)

试试不传递任何参数就执行这个过程，就像你之前做的那样：

CALL get_cars_by_year;

MySQL数据库将返回一条错误消息：

Error messageERROR 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参数也有与其关联的名称和数据类型。但是，除了向存储过程传递数据之外，存储过程还可以向它们填充数据，以便向调用用户返回值。

创建一个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)
)
BEGINSELECT COUNT(*), MIN(value), AVG(value), MAX(value)INTO cars_number, min_value, avg_value, max_valueFROM carsWHERE year = year_filter ORDER BY make, value DESC;
END //
DELIMITER ;

这一次，除了用于按生产年份过滤汽车的IN参数year_filter外，括号块中还定义了四个OUT参数。cars_number参数是用int表示数据类型和将用于返回集合中的汽车的数量。min_value、avg_value和max_value参数表示市场值，并用decimal(10,2)类型定义(类似于cars表中的value列)。这些将用于返回集合中最便宜和最昂贵的汽车的信息，以及所有匹配汽车的平均价格。

SELECT语句使用SQL的数学函数从cars表中查询了四个值:COUNT来获得汽车的总数，MIN、AVG和MAX从value列中获得最小值、平均值和最大值。

注意：要了解更多关于在SQL中使用数学函数的信息，你可以参考SQL教程中如何使用数学表达式和聚集函数。

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

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

OutputQuery OK, 0 rows affected (0.02 sec)

现在通过执行以下命令来运行这个新过程：

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

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

数据库将响应：

OutputQuery OK, 1 row affected (0.00 sec)

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

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;

数据库将以成功消息确认过程删除成功：

OutputQuery OK, 0 rows affected (0.02 sec)

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

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

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

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

在本部分中,您已经了解了如何删除现有数据库中的存储过程。

总结

通过本指南，您了解了什么是存储过程，以及如何在MySQL中使用存储过程将可重用语句保存到命名过程中，然后再执行它们。您创建了不带参数的存储过程，以及使用输入和输出参数使其更加灵活的过程。

您可以使用存储过程来创建可重用的例程和跨多个应用程序访问数据的统一方法，以及实现超出单个SQL查询所提供的可能性的复杂行为。本教程只介绍了使用存储过程的基础知识。要了解更多信息，请参阅MySQL存储过程文档。