如何在 MySQL 中使用存储过程

wufei123 2025-01-26 阅读:1 评论:0

通常，在使用关系数据库时，您可以直接从应用程序代码中发出单独的结构化查询语言 (SQL) 查询来检索或操作数据，例如 SELECT、INSERT、UPDATE 或 DELETE。这些语句直接作用于并操作底层数据库表。如果在访问同一数据库的多...

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

如何在 MySQL 中使用存储过程

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

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

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

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

在本节中，您将连接到 MySQL 服务器并创建一个示例数据库，以便您可以按照本指南中的示例进行操作。

在本指南中，您将使用一个虚构的汽车集合。您将存储有关当前拥有的汽车的详细信息，包括其品牌、型号、制造年份，

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

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示，将sammy替换为您的 MySQL 用户名account:

mysql -u sammy-p

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

CREATE DATABASEprocedures;

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

OutputQuery OK, 1 row affected (0.01 sec)

选择程序数据库，运行以下 USE 语句：

USEprocedures;

您将收到以下输出：

OutputDatabase changed

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

品牌：此列包含每辆拥有的汽车的品牌，使用最多 100 个字符的 varchar 数据类型表示。
型号：此列保存汽车型号名称，使用 varchar 数据类型表示，最多 100 个字符。
年份：此列存储汽车的构建年，采用 int 数据类型来保存数值。
value：该列使用十进制数据类型存储汽车的值，最多 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 carsVALUES('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);

插入该行动将在表中添加十辆样品跑车，其中五辆保时捷和五款法拉利车型。以下输出表明所有添加了五行：

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

至此，您就可以按照指南的其余部分进行操作并开始在 SQL 中使用存储过程。

存储过程简介

MySQL 和许多其他关系数据库中的存储过程系统是包含一个或多个布局指令的命名对象然后在调用时由数据库按顺序执行。在最基本的例子，存储过程可以保存一个通用的语句可重用的例程，例如从数据库中检索数据经常使用的过滤器。例如，您可以创建一个存储过程来检索在最后给定时间内下订单的在线商店客户月数。在最复杂的场景中，存储过程可以代表描述复杂业务逻辑的广泛程序健壮的应用程序。

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

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

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

数据库用户必须具有适当的执行权限给定的程序。此权限要求提供了一层安全性，禁止直接数据库访问，同时授予用户访问权限保证安全执行的各个过程。

存储过程直接在数据库服务器上执行，在本地执行所有计算并将结果返回给调用者仅当完成时才使用。

如果您想更改过程行为，您可以更新数据库中的过程，以及正在使用它的应用程序自动选择新版本。所有用户将立即开始使用新的程序代码而不需要调整它们

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

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

此代码片段中的第一个和最后一个指令是 DELIMITER // 和分隔符 ;.通常，MySQL使用分号符号（;）分隔语句并指示它们何时开始和结束。如果你在 MySQL 控制台中执行多个语句，用分隔分号，它们将被视为单独的命令并执行各自独立，一个接一个。然而，存储过程可以包含多个命令，这些命令将在执行时按顺序执行被叫。当试图告诉 MySQL 创建一个新的程序。数据库引擎会遇到分号在存储过程主体中并认为它应该停止执行陈述。在这种情况下，预期的陈述是整个过程创建代码，而不是过程中的单个指令

要解决此限制，您可以使用 DELIMITER 命令暂时将分隔符从 ; 更改为 ; 本身，因此 MySQL 会误解您的意图。 // 在 CREATE PROCEDURE 期间称呼。然后，存储过程主体内的所有分号都将是按原样传递到服务器。整个程序完成后，分隔符改回 ;最后一个 DELIMITER ;.

创建新过程的代码的核心是 CREATE PROCEDURE 调用，后跟过程名称： procedure_name 在示例中。过程名称后面跟着一个可选的列表过程将接受的参数。最后一部分是程序正文，包含在 BEGIN 和 END 语句中。里面是过程代码，其中可以包含单个 SQL 语句，例如 SELECT 查询或更复杂的代码。

END 命令以临时分隔符 // 结尾，而不是典型的分号。

在下一节中，您将创建一个不包含单个查询的参数的基本存储过程。

创建一个不带参数的存储过程参数

在本节中，您将创建第一个存储过程，该存储过程封装单个 SQL SELECT 语句，以返回按品牌和价值降序排列的自有汽车列表。

开始于执行您要使用的 SELECT 语句：

SELECT * FROM cars ORDER BY make, value DESC;

数据库将返回来自以下位置的汽车列表汽车表，首先按品牌排序，然后在单个品牌内按价值降序排列：

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)

最有价值的法拉利位于列表顶部，最不值钱的保时捷出现在

假设此查询将在多个应用程序中频繁使用或由多个用户使用，并假设您希望确保每个人都会使用对结果进行排序的方式完全相同。为此，您想要创建一个存储过程将该语句保存在可重用的名为 procedure.

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

DELIMITER //CREATE PROCEDUREget_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 语句。

数据库将返回一条成功消息：

OutputQuery OK, 0 rows affected (0.02 sec)

get_all_cars过程现在保存在数据库中，当调用时，它将执行保存的语句： is.

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

CALLget_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 表中以特定方式订购的所有汽车。您可以在多个应用程序中使用该过程。

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

以上就是如何在 MySQL 中使用存储过程的详细内容，更多请关注知识资源分享宝库其它相关文章！