MySQL 存储过程

过程（通常称为存储过程）是存储在数据库中的预编译 SQL 语句的集合。它是常规计算语言中的子例程或子程序。过程始终包含名称、参数列表和 SQL 语句。我们可以通过触发器、其他过程以及如 Java、Python、PHP 等应用程序来调用过程。它最早在 MySQL 5.0 版本中引入。目前，几乎所有的关系型数据库系统都支持它。

如果我们考虑企业级应用，我们总是需要定期在数据库上执行特定任务，例如数据库清理、处理工资单等等。这些任务涉及多个 SQL 语句来执行每个任务。如果我们将这些任务分组为一个任务，这个过程可能会更容易。我们可以通过在数据库中创建存储过程来满足 MySQL 中的此要求。

当一个过程调用自身时，它被称为递归存储过程。大多数数据库系统都支持递归存储过程。但是，MySQL 对此支持不佳。

存储过程特性

• 存储过程提高了应用程序的性能。一旦创建了存储过程，它们就会被编译并存储在数据库中。
• 存储过程减少了应用程序和数据库服务器之间的流量。因为应用程序只需发送存储过程的名称和参数，而不是发送多个 SQL 语句。
• 存储过程是可重用的，并且对任何应用程序都是透明的。
• 过程始终是安全的。数据库管理员可以授予访问数据库中存储过程的应用程序权限，而无需授予对数据库表的任何权限。

如何创建过程？

以下语法用于在 MySQL 中创建存储过程。它可以返回一个或多个值，也可以根本不返回值。默认情况下，过程与我们当前的数据库相关联。但是，我们也可以通过指定名称为 database_name.procedure_name 来从当前数据库创建到另一个数据库。查看完整语法

参数解释

过程语法包含以下参数

MySQL 过程参数有三种模式之一

IN 参数

这是默认模式。它将参数作为输入，例如属性。当我们定义它时，调用程序必须向存储过程传递一个参数。此参数的值始终受到保护。

OUT 参数

它用于将参数作为输出传递。它的值可以在存储过程中更改，更改后的（新）值会传回调用程序。请注意，过程在开始时无法访问 OUT 参数的初始值。

INOUT 参数

它是 IN 和 OUT 参数的组合。这意味着调用程序可以传递参数，过程可以修改 INOUT 参数，然后将新值传回调用程序。

如何调用存储过程？

我们可以使用 CALL 语句来调用存储过程。该语句通过其参数（IN、OUT 或 INOUT）将值返回给其调用者。以下语法用于在 MySQL 中调用存储过程

示例

让我们通过一个例子来了解如何在 MySQL 中创建过程。首先，我们需要选择一个将存储新创建过程的数据库。我们可以使用以下语句选择数据库

假设此数据库有一个名为 student_info 的表，其中包含以下数据

无参数的过程

假设我们想显示分数大于 70 的所有表记录并计算所有表行数。以下代码创建了一个名为 get_merit_students 的过程

如果此代码成功执行，我们将获得以下输出

让我们调用该过程来验证输出

这将产生以下输出

带 IN 参数的过程

在此过程中，我们将 IN 参数用作整数类型的'var1'，用于接受用户输入的数字。其主体部分使用 SELECT 语句从表中获取记录，并仅返回用户提供的行。它还返回指定表的总行数。查看过程代码

成功执行后，我们可以如下调用该过程

我们将获得以下输出

带 OUT 参数的过程

在此过程中，我们将 OUT 参数用作整数类型的'highestmark'。其主体部分使用 MAX() 函数从表中获取最高分。查看过程代码

此过程的参数将从 student_info 表中获取最高分。当我们调用该过程时，OUT 参数会告知数据库系统其值将从过程中输出。现在，我们将在 CALL 语句中将其值传递给会话变量 @M，如下所示

这是输出

带 INOUT 参数的过程

在此过程中，我们将 INOUT 参数用作整数类型的'var1'。其主体部分首先使用指定的 id 从表中获取分数，然后将其存储到同一个变量 var1 中。var1 首先充当 IN 参数，然后充当 OUT 参数。因此，我们可以称之为 INOUT 参数模式。查看过程代码

成功执行后，我们可以如下调用该过程

我们将获得以下输出

如何在 MySQL 中显示或列出存储过程？

当 MySQL 服务器中有多个过程时，列出所有过程非常重要。这是因为有时许多数据库中的过程名称是相同的。在这种情况下，此查询非常有用。我们可以按以下方式列出当前 MySQL 服务器上存储的所有过程

此语句显示所有存储过程名称，包括它们的特征。如果我们想在特定数据库中显示过程，我们需要使用 WHERE 子句。如果我们想使用特定单词列出存储过程，我们需要使用 LIKE 子句。

我们可以使用以下语句列出 MySQL mystudentsb 数据库中的所有存储过程

这将产生以下输出，我们可以看到 mystudentdb 数据库包含四个存储过程

如何在 MySQL 中删除/丢弃存储过程？

MySQL 还允许使用命令来丢弃过程。当过程被丢弃时，它也会从数据库服务器中删除。以下语句用于在 MySQL 中丢弃存储过程

假设我们想从 mystudentdb 数据库中删除名为 display_marks 的过程。我们可以通过首先选择数据库，然后使用以下语法来删除该过程

我们可以使用 SHOW PROCEDURE STATUS 命令列出指定数据库中的过程来验证这一点。查看以下输出

如何在 MySQL Workbench 中创建过程？

我们首先启动工具并使用用户名和密码登录，以便在 MySQL Workbench 中创建过程。现在，我们需要执行以下步骤来创建存储过程

1. 转到导航选项卡，然后单击 Schema 菜单，其中包含所有先前创建的数据库。选择您想要的数据库（例如，employeedb）。它将弹出以下选项。

2. 右键单击Stored Procedure，我们将获得默认的过程代码。查看下面的屏幕

3. 完成过程代码并单击Apply 按钮。在下一个窗口中，我们将再次查看过程代码，如果没有发现错误，请单击 Apply 按钮。

4. 单击 Apply 按钮后，单击Finish 按钮完成。

5. 我们可以再次导航到 schema 菜单来验证这个新创建的过程。这意味着首先选择您的数据库并展开它以显示其子菜单。在子菜单中，展开 stored procedure 选项将显示新创建的过程。查看下面的图片

6. 我们可以通过点击红色矩形框或直接执行 CALL 语句来调用该过程。

如何在 MySQL 中修改过程？

MySQL 不允许任何命令在 MySQL 中修改过程。但是，它提供了一个命令，用于更改存储过程的特性。此命令可以对过程进行多项更改，但不会修改存储过程的参数或主体。如果我们想进行此类更改，我们必须使用 DROP PROCEDURE 和 CREATE PROCEDURE 语句丢弃并重新创建过程。

以下语句用于更改过程的特性，但不是实际过程:

假设我们想为现有过程添加注释。在这种情况下，我们可以使用 ALTER 语句来完成此任务

执行此语句后，我们可以使用以下语句进行验证

这将显示以下输出，我们可以看到注释已成功添加。

需要注意的是，我们可以在 MySQL 中使用 workbench 工具修改存储过程的主体。因此，打开此工具，导航到 schema 菜单，然后展开包含存储过程的数据库。现在，选择您的过程，右键单击它，然后选择ALTER STORED PROCEDURE 选项。查看下面的屏幕

单击此选项后，我们将看到一个包含过程代码的窗口。查看下面的屏幕，其中包含显示所有员工的过程代码

现在，我们将修改此代码。假设我们只想显示男性员工。为此，我们可以将此代码从以下代码更改为，然后单击Apply 按钮

在此窗口中，我们将再次查看过程代码，如果没有发现错误，请单击Apply -> Apply -> Finish 按钮以完成该过程。

使用存储过程的缺点

• 如果我们使用存储过程，使用这些存储过程的每个连接的内存使用量都会大大增加。此外，如果我们过度使用存储过程中的许多逻辑应用程序，CPU 使用率将增加。这是因为数据库服务器并未针对逻辑操作进行优化。
• 存储过程的构造并非用于开发复杂且灵活的业务逻辑。
• 调试存储过程很困难。只有少数数据库管理系统允许我们调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能。
• 开发和维护存储过程并不容易。开发和维护存储过程通常需要专门的技能集，并非所有应用程序开发人员都具备。这可能会在应用程序开发和维护阶段都带来问题。