作者选择了 多样性在技术基金作为 写给捐款计划的一部分接受捐款。
介绍
通常情况下,当您使用关系数据库时,您会发出单独的 Structured Query Language (SQL) 查询以获取或操纵数据,例如 SELECT, INSERT, UPDATE或 DELETE,直接从应用程序代码中。
MySQL 和许多其他关系数据库管理系统一样,支持使用存储程序. 存储程序有助于将一个或多个 SQL 陈述在一个共同的名称下重复使用,封装了数据库内部的共同业务逻辑。
使用存储程序,您可以创建可重复使用的流程,用于用于多个应用程序的常见任务,提供数据验证,或通过限制数据库用户直接访问基础表并发行任意查询来提供额外的数据访问安全层。
在本教程中,您将学习存储程序是什么,以及如何创建返回数据并使用输入和输出参数的基本存储程序。
前提条件
要遵循本指南,您需要运行基于 SQL 的关系数据库管理系统(RDBMS)的计算机。
- 运行 Ubuntu 20.04 的服务器,具有非 root 用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 所描述的。
- MySQL 安装并保护在服务器上,如在 [Ubuntu 20.04 上如何安装 MySQL] 所描述的。
<$>[注] 注: 请注意,许多 RDBMS 使用自己的 SQL 独特实现,而存储程序语法并非官方 SQL 标准的一部分。
您还需要一个空的数据库,在其中您将能够创建表,展示存储程序的使用. 我们鼓励您通过以下 连接到MySQL和设置示例数据库部分,了解有关连接到MySQL服务器的详细信息,并创建本指南中示例中使用的测试数据库。
连接到MySQL并设置样本数据库
在本节中,您将连接到MySQL服务器并创建一个样本数据库,以便您可以按照本指南中的示例。
您将存储有关目前所拥有的汽车的详细信息,包括其制造、模型、建造年份和价值。
如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:
1[environment local]
2ssh sammy@your_server_ip
然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:
1mysql -u sammy -p
创建一个名为程序的数据库:
1CREATE DATABASE procedures;
如果数据库创建成功,您将收到这样的输出:
1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)
若要选择程序数据库,请运行以下使用语句:
1USE procedures;
您将获得以下输出:
1[secondary_label Output]
2Database changed
选择数据库后,您可以在数据库中创建示例表. 表汽车将包含数据库中关于汽车的简化数据。
make:此列包含每个拥有汽车的 make,用最多100个字符的varchar数据类型表示。model:此列包含使用最多100个字符的varchar数据类型表示的汽车模型名称。year:此列存储汽车的构建年与int数据类型以保持数值。
使用以下命令创建示例表:
1CREATE TABLE cars (
2 make varchar(100),
3 model varchar(100),
4 year int,
5 value decimal(10, 2)
6);
如果下列输出打印,则已创建表:
1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)
接下来,通过运行以下INSERT INTO操作来加载汽车表中的一些样本数据:
1INSERT INTO cars
2VALUES
3('Porsche', '911 GT3', 2020, 169700),
4('Porsche', 'Cayman GT4', 2018, 118000),
5('Porsche', 'Panamera', 2022, 113200),
6('Porsche', 'Macan', 2019, 27400),
7('Porsche', '718 Boxster', 2017, 48880),
8('Ferrari', '488 GTB', 2015, 254750),
9('Ferrari', 'F8 Tributo', 2019, 375000),
10('Ferrari', 'SF90 Stradale', 2020, 627000),
11('Ferrari', '812 Superfast', 2017, 335300),
12('Ferrari', 'GTC4Lusso', 2016, 268000);
INSERT INTO操作将添加十辆运动车样本,其中包括五款保时捷和五款法拉利车型。
1[secondary_label Output]
2Query OK, 10 rows affected (0.00 sec)
3Records: 10 Duplicates: 0 Warnings: 0
有了它,您可以随时遵循其他指南,并开始使用 SQL 中的存储程序。
存储程序的介绍
在MySQL和许多其他关系数据库系统中,存储程序被称为包含一个或多个命令的对象,然后在呼叫时由数据库以序列执行。在最基本的例子中,存储程序可以在可重复使用的程序下保存一个常见的陈述,例如从数据库中获取常用的过滤器的数据。
存储程序中的命令集可能包括常见的 SQL 陈述,例如 SELECT 或 INSERT 查询,这些查询返回或操纵数据。
- 通过存储程序传输或返回的参数.
- 声明变量直接在程序代码中处理获取的数据。
- 条件声明,允许根据某些条件执行存储程序代码的部分,如
IF或CASE指令。 - 循环,如
WHILE,LOOP和REPEAT,允许执行代码的部分多次,例如在获取数据集中的每个行。
<$>[注] 注: MySQL 支持的广泛语法允许编写可靠的程序,并通过存储程序解决复杂问题。本指南仅涵盖了存储程序的基本用途,其中包含存储程序体、输入和输出参数的 SQL 陈述。执行条件代码、使用变量、循环和自定义错误处理不属于本指南的范围。我们鼓励您在官方 [MySQL 文档] 中了解更多有关存储程序的信息。
当程序被命名时,数据库引擎会按照定义执行,命令按命令执行。
数据库用户必须具有适当的权限来执行该程序. 此权限要求提供了一层安全性,不允许直接访问数据库,同时允许用户访问可保证安全执行的个别程序。
存储的程序直接在数据库服务器上执行,在本地执行所有计算,并在完成时才返回给呼叫用户的结果。
如果您想要更改程序行为,您可以更新数据库中的程序,并且正在使用的应用程序将自动接收新版本. 所有用户将立即开始使用新程序代码,而无需调整他们的应用程序。
以下是用于创建存储程序的 SQL 代码的总体结构:
1DELIMITER //
2CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n)
3BEGIN
4 instruction_1;
5 instruction_2;
6 . . .
7 instruction_n;
8END //
9DELIMITER ;
这个代码片段中的第一个和最后一个指令是DELIMITER和DELIMITER。 通常,MySQL 使用半色符号(;)来划分陈述并指明它们何时开始和结束。 如果你在MySQL 控制台中执行半色符号分开的多个陈述,它们将被视为单独的命令,并独立执行,一个接一个。 但是,存储的程序可以包含多个命令,当它被调用时会连续执行。 这在试图告诉MySQL创建一个新程序时会带来困难。 数据库引擎会在存储的程序中遇到半色符号,并认为它应该停止执行陈述。 在这种情况下,预期的陈述是整个程序创建代码,而不是单个指令在程序内部,所以MySQL
要绕过这个限制,你可以使用DELIMITER命令暂时更改CREATE PROCEDURE呼叫的持续时间内从;到//的界限器。
创建新程序的代码的核心是CREATE PROCEDURE呼叫,然后是程序的名称:procedure_name在示例中。 程序名称随后是程序将接受的可选参数列表。 最后一部分是程序体,包含在BEGIN和END陈述中。
END命令以//结束,即临时分界器,而不是典型的半色。
在下一节中,您将创建一个基本的存储程序,没有包含单个查询的参数。
创建没有参数的存储程序
在本节中,您将创建您的第一个存储程序,将一个单一的 SQL SELECT 语句封装,以返回由其制造和值以下降顺序排序的所有汽车列表。
首先,执行您要使用的SELECT语句:
1SELECT * FROM cars ORDER BY make, value DESC;
数据库将返回从汽车表中列出的汽车列表,首先按 make 排序,然后在单个 make 中按下列顺序排序:
1[secondary_label Output]
2+---------+---------------+------+-----------+
3| make | model | year | value |
4+---------+---------------+------+-----------+
5| Ferrari | SF90 Stradale | 2020 | 627000.00 |
6| Ferrari | F8 Tributo | 2019 | 375000.00 |
7| Ferrari | 812 Superfast | 2017 | 335300.00 |
8| Ferrari | GTC4Lusso | 2016 | 268000.00 |
9| Ferrari | 488 GTB | 2015 | 254750.00 |
10| Porsche | 911 GT3 | 2020 | 169700.00 |
11| Porsche | Cayman GT4 | 2018 | 118000.00 |
12| Porsche | Panamera | 2022 | 113200.00 |
13| Porsche | 718 Boxster | 2017 | 48880.00 |
14| Porsche | Macan | 2019 | 27400.00 |
15+---------+---------------+------+-----------+
1610 rows in set (0.00 sec)
最有价值的法拉利排在榜单的顶部,最不有价值的保时捷排在榜单的底部。
假设此查询将在多个应用程序或多个用户中经常使用,并假设您希望确保每个人都使用相同的结果排序方式。
若要创建此存储程序,请执行以下代码片段:
1DELIMITER //
2CREATE PROCEDURE get_all_cars()
3BEGIN
4 SELECT * FROM cars ORDER BY make, value DESC;
5END //
6DELIMITER ;
正如上一节所描述的那样,第一个和最后一个命令(‘DELIMITER //’和‘DELIMITER ;’)告诉MySQL停止对待半栏字符作为程序创建的持续时间的语句划界符。
CREATE PROCEDURE SQL 命令随后使用程序名为 get_all_cars,您可以定义该命令以最好地描述该程序的功能。 程序名之后,您可以添加参数的对数为 ()。 在此示例中,该程序不使用参数,因此这些参数是空的。
注意:根据您的MySQL用户权限,您在执行CREATE PROCEDURE命令时可能会收到错误:ERROR 1044 (42000):用户sammy@localhost被拒绝访问数据库程序`。
1GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE on *.* TO 'sammy'@'localhost';
2FLUSH PRIVILEGES;
更新用户权限后,以 root 登录,以用户身份重新登录,然后重启CREATE PROCEDURE语句。
您可以在 Stored Routines and MySQL Privileges 文档中了解有关存储程序的用户的权限的应用。
数据库将以成功消息回复:
1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)
现在将get_all_cars程序保存到数据库中,当被调用时,它将执行已保存的语句。
要执行保存的存储程序,您可以使用CALL SQL 命令,然后是程序名称。
1CALL get_all_cars;
程序名称, get_all_cars,是您使用程序所需的全部。您不再需要手动键入您以前使用的 SELECT 语句的任何部分。
1[secondary_label Output]
2+---------+---------------+------+-----------+
3| make | model | year | value |
4+---------+---------------+------+-----------+
5| Ferrari | SF90 Stradale | 2020 | 627000.00 |
6| Ferrari | F8 Tributo | 2019 | 375000.00 |
7| Ferrari | 812 Superfast | 2017 | 335300.00 |
8| Ferrari | GTC4Lusso | 2016 | 268000.00 |
9| Ferrari | 488 GTB | 2015 | 254750.00 |
10| Porsche | 911 GT3 | 2020 | 169700.00 |
11| Porsche | Cayman GT4 | 2018 | 118000.00 |
12| Porsche | Panamera | 2022 | 113200.00 |
13| Porsche | 718 Boxster | 2017 | 48880.00 |
14| Porsche | Macan | 2019 | 27400.00 |
15+---------+---------------+------+-----------+
1610 rows in set (0.00 sec)
17
18Query OK, 0 rows affected (0.00 sec)
您现在已经成功创建了一个存储的程序,没有任何参数,返回了汽车表的所有车辆以特定的方式订购。
在下一节中,您将创建一个接受参数的程序,以根据用户输入更改程序行为。
使用输入参数创建存储程序
在本节中,您将包含存储程序定义的输入参数,以允许执行程序的用户将数据传输给该程序,例如,用户可以提供查询过滤器。
以前创建的存储程序 get_all_cars 随时从 cars 表中检索所有汽车. 让我们创建另一个程序来查找特定生产年份的汽车. 为了允许这一点,您将在程序定义中定义一个命名参数。
运行以下代码:
1DELIMITER //
2CREATE PROCEDURE get_cars_by_year(
3 IN year_filter int
4)
5BEGIN
6 SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC;
7END //
8DELIMITER ;
从上一节中对程序创建代码有几个更改。
首先,它的名字是get_cars_by_year,它描述了程序:根据生产年份检索汽车。
此前空白的序列现在包含一个单一的参数定义: IN year_filter int. IN 关键字告诉数据库,参数将由呼叫用户 to 通过程序。 year_filter 是参数的任意名称。 您将使用它来参考程序中的参数。 最后, int 是数据类型。 在这种情况下,生产年份被表达为数值。
程序名称后定义的年_过滤器参数出现在WHERE year = year_filter条款中的SELECT声明中,将汽车表对其生产年进行过滤。
数据库将再次响应成功消息:
1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)
尝试执行程序,而不将任何参数传递给它,就像你以前一样:
1CALL get_cars_by_year;
MySQL 数据库将返回一个错误消息:
1[secondary_label Error message]
2ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0
此时,存储程序预计将提供一个参数,但没有给出任何参数。 要调用具有参数的存储程序,您可以按程序预期的顺序提供参数值。
1CALL get_cars_by_year(2017);
现在,所谓的程序将正确执行,并返回该年度的汽车列表:
1[secondary_label Output]
2+---------+---------------+------+-----------+
3| make | model | year | value |
4+---------+---------------+------+-----------+
5| Ferrari | 812 Superfast | 2017 | 335300.00 |
6| Porsche | 718 Boxster | 2017 | 48880.00 |
7+---------+---------------+------+-----------+
82 rows in set (0.00 sec)
9
10Query OK, 0 rows affected (0.00 sec)
在此示例中,您了解如何将输入参数传输到存储的程序中,并在程序中的查询中使用它们以提供过滤选项。
在下一节中,您将使用输出参数创建以一次运行返回多个不同值的程序。
创建具有输入和输出参数的存储程序
在前两个示例中,您创建的存储程序被称为SELECT语句以获取结果集,但在某些情况下,您可能需要一个存储程序,该程序将返回多个不同的值,而不是单个查询的单一结果集。
假设您想要创建一个程序,该程序将提供有关特定年份的汽车的摘要信息,包括收集中的汽车数量及其市场价值(最低、最大和平均)。
要做到这一点,您可以在创建一个新的存储程序时使用OUT参数。类似于IN参数,OUT参数具有与它们相关的名称和数据类型。
创建一个get_car_stats_by_year程序,该程序将使用输出参数返回特定生产年对汽车的汇总数据:
1DELIMITER //
2CREATE PROCEDURE get_car_stats_by_year(
3 IN year_filter int,
4 OUT cars_number int,
5 OUT min_value decimal(10, 2),
6 OUT avg_value decimal(10, 2),
7 OUT max_value decimal(10, 2)
8)
9BEGIN
10 SELECT COUNT(*), MIN(value), AVG(value), MAX(value)
11 INTO cars_number, min_value, avg_value, max_value
12 FROM cars
13 WHERE year = year_filter ORDER BY make, value DESC;
14END //
15DELIMITER ;
这一次,除了IN参数year_filter来过滤汽车的生产年份外,四个OUT参数被定义在窗格中。cars_number参数被表示为int数据类型,将用于返回收藏中的汽车数量。min_value、avg_value和max_value参数代表市场价值,并被定义为十分数(10,2)类型(类似于cars表中的value列)。
SELECT 语句使用 SQL 数学函数查询汽车表中的四个值: COUNT 以获取汽车总数,以及 MIN, AVG 和 MAX 以获取值列的最低、平均和最大值。
<$>[注] 注: 有关 SQL 中的数学函数使用的更多信息,您可以遵循 如何在 SQL 中使用数学表达式和汇总函数教程。
为了告诉数据库,该查询的结果应该存储在存储程序的输出参数中,引入一个新的关键字,即INTO。在INTO关键字之后,列出与获取数据相应的四个程序参数的名称。
数据库将确认成功的程序创建:
1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)
现在,运行新的程序,执行:
1CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
四个新参数以@符号开始. 这些是MySQL控制台中的本地变量名称,您可以使用它们暂时存储数据。
数据库将响应:
1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)
这与以前的行为不同,结果立即显示在屏幕上. 这是因为存储程序的结果已被保存到输出参数,而不是作为查询结果返回。
1SELECT @number, @min, @avg, @max;
使用此查询,您正在从本地变量中选择值,而不是再次调用程序。
<$>[注] 注: 若要了解更多关于在 MySQL 中使用用户定义变量,请参阅文档中的 用户定义变量部分。在应用程序开发中使用时,从存储程序返回的数据的方式在不同的编程语言和框架中会有所不同。
输出将显示所查询变量的值:
1[secondary_label Output]
2+---------+----------+-----------+-----------+
3| @number | @min | @avg | @max |
4+---------+----------+-----------+-----------+
5| 2 | 48880.00 | 192090.00 | 335300.00 |
6+---------+----------+-----------+-----------+
71 row in set (0.00 sec)
这些数字是指2017年生产的汽车数量,以及今年生产的汽车的最低、平均和最高市场价值。
在本示例中,您了解如何使用输出参数返回存储过程中的多个不同值,以便在以后使用。
删除存储程序
在本节中,您将删除数据库中存在的存储程序。
有时,您创建的程序可能不再需要。在其他情况下,您可能想要更改程序的工作方式。MySQL不允许在创建后更改程序定义,所以唯一的方法是先删除程序并重新创建它以所需的更改。
让我们删除最后一个程序, get_car_stats_by_year. 要做到这一点,您可以使用‘DROP PROCEDURE’ 语句:
1DROP PROCEDURE get_car_stats_by_year;
数据库将通过成功消息确认成功的程序删除:
1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)
您可以通过尝试调用它来验证该程序被删除:
1CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);
此时,您将看到一个错误消息,称该程序不在数据库中:
1[secondary_label Error message]
2ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist
在本节中,您将了解如何删除数据库中存储的现有程序。
结论
通过遵循本指南,您了解了存储程序是什么,以及如何在MySQL中使用它们来将可重复使用的陈述保存为命名程序,并在以后执行它们。
您可以使用存储程序创建可重复使用的程序和统一方法来访问多个应用程序中的数据,以及执行复杂的行为,超出单个 SQL 查询所提供的可能性。
如果您想了解更多有关 SQL 语言的不同概念并使用它的工作,我们鼓励您查看 如何使用 SQL 系列中的其他指南。