MySQL 存储过程

一、MySQL 存储过程 介绍

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

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

一个过程在调用自身时称为递归存储过程。大多数数据库系统都支持递归存储过程。但是，它在 MySQL 中并没有得到很好的支持。

二、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 ;   

参数说明

参数名称	说明
procedure_name	它表示存储过程的名称。
parameter	它表示参数的数量。它可以是一个或多个。
Declaration_section	它代表所有变量的声明。
Executable_section	它代表函数执行的代码。

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 参数告诉数据库系统它的值从过程中输出。现在，我们将其值传递给 CALL 语句中的会话变量@M，如下所示：

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

输出结果为：

八、带有 INOUT参数的 存储过程

在此存储过程中，我们使用 INOUT 参数作为整数类型的“var1”。它的主体部分首先从具有指定id的表中获取标记，然后将其存储到同一个变量 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. 转到导航选项卡并单击Schema菜单，其中所有以前创建的数据库都可用。选择所需的数据库（例如，employeedb）。它将弹出以下选项。

2. 右键单击​​ Stored Procedure，我们会得到默认的存储过程代码。请参阅以下屏幕：

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

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 Workbench工具更改 MySQL 中存储过程的主体。所以打开这个工具，导航到Schema菜单，然后展开包含存储过程的数据库。现在，选择您的过程，右键单击它并选择ALTER STORED PROCEDURE选项。请参阅以下屏幕：

单击此选项后，我们将获得一个包含存储过程代码的窗口。请参阅下面的屏幕，其中包含显示所有员工的程序代码：

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

SELECT * FROM employee WHERE gender = 'M';  

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

十三、使用存储过程的缺点

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