MySQL 存储过程

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

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

当存储过程调用自身时，它被称为 递归存储过程 。大多数数据库系统都支持递归存储过程。但是，在MySQL中，它的支持不是很好。

存储过程特点

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

如何创建一个过程？

下面的语法用于在MySQL中创建一个存储过程。它可以通过参数返回一个或多个值，有时可能不返回任何值。默认情况下，过程与当前数据库关联。但我们也可以通过指定名称 database_name.procedure_name 将其创建到当前数据库之外的另一个数据库中。以下是完整的语法：

DELIMITER &&
CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter datatype]) ]  
BEGIN  
    Declaration_section  
    Executable_section  
END &&
DELIMITER ; 

参数解释

该过程语法具有以下参数：

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

IN参数

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

OUT参数

它用于将参数作为输出传递。其值可以在存储过程内部更改，并将更改（新的）值传递回调用程序。需要注意的是，存储过程在启动时无法访问OUT参数的初始值。

INOUT参数

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

如何调用存储过程？

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

CALL procedure_name ( parameter(s))

例子

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

mysql> USE database_name;

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

无参数的过程

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

DELIMITER &&
CREATE PROCEDURE get_merit_student ()
BEGIN
    SELECT * FROM student_info WHERE marks > 70;
    SELECT COUNT(stud_code) AS Total_Student FROM student_info;  
END &&
DELIMITER ;

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

让我们称之为验证输出的过程：

mysql> CALL get_merit_student();

它将输出如下：

具有IN参数的过程

在这个过程中，我们使用了IN参数作为整数类型的’var1’来接受用户输入的数字。其主体部分使用SELECT语句从表中获取记录，并返回仅由用户提供的行。它还返回指定表的总行数。请参见下面的过程代码：

DELIMITER &&
CREATE PROCEDURE get_student (IN var1 INT)
BEGIN
    SELECT * FROM student_info LIMIT var1;
    SELECT COUNT(stud_code) AS Total_Student FROM student_info;  
END &&
DELIMITER ;

执行成功后，我们可以按照以下方式调用该过程：

mysql> CALL get_student(4);

我们将会得到以下输出：

OUT参数的过程

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

DELIMITER &&
CREATE PROCEDURE display_max_mark (OUT highestmark INT)
BEGIN
    SELECT MAX(marks) INTO highestmark FROM student_info; 
END &&
DELIMITER ;

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

mysql> CALL display_max_mark(@M);
mysql> SELECT @M;

这是输出结果：

带有INOUT参数的过程

在这个过程中，我们使用了INOUT参数作为整型的变量 ‘var1’ 。该过程的代码首先从指定的表中获取分数，并将其存储到相同的变量var1中。var1首先作为IN参数，然后作为OUT参数。因此，我们可以称之为INOUT参数模式。请参考以下过程代码：

DELIMITER &&
CREATE PROCEDURE display_marks (INOUT var1 INT)
BEGIN
    SELECT marks INTO var1 FROM student_info WHERE stud_id = var1; 
END &&
DELIMITER ;

执行成功后，我们可以按照以下方式调用该过程：

mysql> SET @M = '3';
mysql> CALL display_marks(@M);
mysql> SELECT @M;

我们将获得以下输出:

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

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

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

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

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

mysql> SHOW PROCEDURE STATUS WHERE db = 'mystudentdb';

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

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

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

DROP PROCEDURE [ IF EXISTS ] procedure_name;  

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

mysql> DROP PROCEDURE display_marks;

我们可以通过使用 SHOW PROCEDURE STATUS 命令列出指定数据库中的过程来验证它。请参阅下面的输出：

如何在MySQL Workbench中创建存储过程?

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

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

2. 右键单击 Stored Procedure（存储过程） ，我们将获得默认的过程代码。请参考下面的屏幕截图：

3. 完成该流程代码并点击 应用按钮 。在下一个窗口中，我们将再次审查该流程代码，如果没有错误，则点击应用按钮。

4. 在点击”Apply”按钮后，点击 Finish 按钮完成。

5.我们可以再次导航到模式菜单来验证这个新创建的存储过程。也就是首先选择您的数据库并展开它以显示其子菜单。在 子菜单 中，展开存储过程选项将显示新创建的存储过程。请参考下面的图片：

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

如何在MySQL中修改存储过程的过程？

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

以下语句用于更改过程的特征而不是实际过程 ：

ALTER PROCEDURE procedure_name [characteristics ...]

characteristics: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

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

mysql> ALTER PROCEDURE get_merit_student
COMMENT 'It displays all records';

在执行此语句后，我们可以通过以下语句验证它：

mysql> SHOW CREATE PROCEDURE get_merit_student \G;

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

请注意， 我们可以使用工作台工具来更改MySQL存储过程的主体部分 。因此，打开此工具，导航到模式菜单，并展开包含存储过程的数据库。现在，选择您的过程，在其上单击鼠标右键，然后选择 ALTER STORED PROCEDURE 选项。请参阅下面的屏幕截图：

点击这个选项后，我们将得到一个包含过程代码的窗口。请参见下面包含过程代码的屏幕以显示所有员工：

现在，我们将修改这段代码。假设我们只想显示男性员工。要做到这一点，我们可以将这段代码改成下面的代码，并点击 应用按钮 ：

SELECT * FROM employee WHERE gender = 'M';

在这个窗口中，我们将再次审查过程代码，如果没有发现错误，请点击 应用->应用->完成 按钮来完成该过程。

使用存储过程的缺点

• 如果我们使用存储过程，使用这些存储过程的每个连接的内存使用量将大幅增加。另外，如果在存储过程中过度使用多个逻辑应用程序，CPU使用率将增加。这是因为数据库服务器对逻辑操作设计不当。
• 存储过程的结构不适合开发复杂和灵活的业务逻辑。
• 调试存储过程很困难。只有少数数据库管理系统允许我们调试存储过程。不幸的是，MySQL没有提供调试存储过程的功能。
• 开发和维护存储过程并不容易。开发和维护存储过程通常需要专门的技能，不是所有应用程序开发人员都具备这些技能。这可能导致应用程序开发和维护阶段出现问题。