MySQL 存储过程

过程（也称为存储过程）是包含在 CREATE PROCEDURE 语句中的 SQL 语句的集合。过程始终包含名称、参数列表和 SQL 语句。它还可能包含条件语句，如 IF 或 CASE 或循环。可以通过使用过程、其他过程和应用程序（例如 Java、Python、PHP 等）来调用过程。

存储过程功能

• 存储过程提高了应用程序。存储过程一旦创建，就会被编译并存储在数据库中。
• 存储过程减少了应用程序和数据库服务器之间的流量。因为应用程序只需发送存储过程的名称和参数，而不是发送多个 SQL 语句。
• 存储过程是可重用的。业务逻辑可以在存储过程中实现，该存储过程可以被应用程序多次使用，或者可以被应用程序的不同模块使用。这使得数据库更加一致。如果需要任何更改，只能在存储过程中进行。
• 存储过程比 AdHoc 查询更安全。可以授予用户执行存储过程的权限，而不授予存储过程中使用的表的权限。存储过程有助于防止数据库受到 SQL 注入。

CREATE PROCEDURE

CREATE PROCEDURE 语句用于创建存储过程。 CREATE PROCEDURE 语句需要 CREATE ROUTINE 权限。

默认情况下，存储例程与默认数据库关联。要将例程与给定数据库显式关联，请在创建时将名称指定为db_name.procedure_name。

括号内的参数列表必须始终存在。如果没有参数，则应使用 () 的空参数列表。参数名称不区分大小写。

存储过程还可以执行另一个存储过程或模块化代码的函数。

给出了在 MySQL 中使用 CREATE PROCEDURE 语句的语法如下:

DELIMITER &&  
CREATE PROCEDURE procedure_name ([[ IN | OUT | INOUT ] parameter datatype [,...] ])

BEGIN

  declaration_section

  executable_section

END &&  
DELIMITER ; 

参数

要调用存储过程，请使用CALL 语句。请参阅以下语法:

CALL procedure_name(parameter(s)); 

创建不带参数的过程

考虑一个名为 Employee 的数据库表，其中包含以下记录:

+-------+--------+-----------+-----+--------+
| EmpID | Name   | City      | Age | Salary |
+-------+--------+-----------+-----+--------+
| 1     | John   | London    | 25  | 3000   |
| 2     | Marry  | New York  | 24  | 2750   |
| 3     | Jo     | Paris     | 27  | 2800   |
| 4     | Kim    | Amsterdam | 30  | 3100   |
| 5     | Ramesh | New Delhi | 28  | 3000   |
| 6     | Huang  | Beijing   | 28  | 2800   |
+-------+--------+-----------+-----+--------+ 

要获取此表中 Salary 大于或等于 3000 的所有记录并计算所有表行数，可以使用以下代码创建一个名为 high_salary_employees 的过程:

DELIMITER &&  
CREATE PROCEDURE high_salary_employees()

BEGIN
  SELECT * FROM Employee WHERE Salary >= 3000;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;  
END &&  
DELIMITER ; 

成功执行后，可以按如下方式调用该过程:

mysql> CALL high_salary_employees(); 

这将产生如下所示的结果:

+-------+--------+-----------+-----+--------+
| EmpID | Name   | City      | Age | Salary |
+-------+--------+-----------+-----+--------+
| 1     | John   | London    | 25  | 3000   |
| 4     | Kim    | Amsterdam | 30  | 3100   |
| 5     | Ramesh | New Delhi | 28  | 3000   |
+-------+--------+-----------+-----+--------+
3 rows in set (0.00 sec)

+---------------+
| TotalEmployee |
+---------------+
| 6             |
+---------------+
1 row in set (0.02 sec) 

使用 IN 参数创建过程

在此过程中，IN 参数与名为"var1"的整数类型参数一起使用，以从用户处获取值。其主体部分使用 SELECT 语句从表中获取记录，并仅返回用户提供的那些行。它还返回指定表的总行数。

DELIMITER &&  
CREATE PROCEDURE get_employees(IN var1 INT)

BEGIN
  SELECT * FROM Employee LIMIT var1;  
  SELECT COUNT(EmpID) AS TotalEmployee FROM Employee;  
END &&  
DELIMITER ; 

成功执行后，可以按如下方式调用该过程:

mysql> CALL get_employees(3); 

这将产生如下所示的结果:

+-------+-------+----------+-----+--------+
| EmpID | Name  | City     | Age | Salary |
+-------+-------+----------+-----+--------+
| 1     | John  | London   | 25  | 3000   |
| 2     | Marry | New York | 24  | 2750   |
| 3     | Jo    | Paris    | 27  | 2800   |
+-------+-------+----------+-----+--------+
3 rows in set (0.00 sec)

+---------------+
| TotalEmployee |
+---------------+
| 6             |
+---------------+
1 row in set (0.02 sec) 

创建带有 OUT 参数的过程

在此过程中，OUT 参数与整数类型的名为"maxsalary"的参数一起使用。它的主体部分使用 MAX() 函数从表中获取最高工资。

DELIMITER &&  
CREATE PROCEDURE get_max_salary(OUT maxsalary INT)

BEGIN
  SELECT MAX(Salary) INTO maxsalary FROM Employee;  
END &&  
DELIMITER ; 

成功执行后，可以按如下所述调用该过程。 OUT 参数告诉数据库系统其值从过程中传出。该值被传递到 CALL 语句中的会话变量 @S，如下所示:

mysql> CALL get_max_salary(@S);
mysql> SELECT @S; 

这将产生如下所示的结果:

+------+
| @S   |
+------+
| 3100 |
+------+
1 row in set (0.00 sec) 

使用 INOUT 参数创建过程

在此过程中，INOUT 参数与名为"var1"的整数类型参数一起使用。其主体部分使用指定的 EmpID 从表中获取 Salary 并将其存储到同一变量 var1 中。 var1 首先充当 IN 参数，然后充当 OUT 参数。

DELIMITER &&  
CREATE PROCEDURE get_salary(INOUT var1 INT)

BEGIN
  SELECT Salary INTO var1 FROM Employee WHERE EmpID = var1;  
END &&  
DELIMITER ; 

成功执行后，可以按如下方式调用该过程:

mysql> SET @S = '5';  
mysql> CALL get_salary(@S);  
mysql> SELECT @S; 

这将产生如下所示的结果:

+------+
| @S   |
+------+
| 3000 |
+------+
1 row in set (0.00 sec) 

删除过程

一旦在 MySQL 中创建过程，就可以使用 DROP PROCEDURE 语句将其删除。 DROP PROCEDURE 语句需要 ALTER ROUTINE 权限。默认情况下，MySQL 自动向例程创建者授予 ALTER ROUTINE 和 EXECUTE 权限。

语法

在 MySQL 中删除过程的语法如下:

DROP PROCEDURE [IF EXISTS] procedure_name; 

IF EXISTS 是一个可选参数，只有当过程存在于数据库中时，它才会有条件地删除该过程。如果删除不存在的过程，则会引发错误。

示例:

要删除名为CalcMaxSalary的过程，可以使用以下语句:

DROP PROCEDURE CalcMaxSalary;