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

存储过程功能

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

创建过程

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

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

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

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

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

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

BEGIN

  declaration_section

  executable_section

END &&  
DELIMITER;

参数

OR REPLACEOptional. CREATE PROCEDURE defines a new procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing procedure.
procedure_nameRequired. Specify the name to assign to this procedure.
parameterOptional. Specify one or more parameters passed into the procedure. When creating a procedure, there are three types of parameters that can be declared:
TypeDescription
IN(Default) The parameter can be referenced by the procedure. The value of the parameter can not be overwritten by the procedure.
OUTThe parameter can not be referenced by the procedure, but the value of the parameter can be overwritten by the procedure.
IN OUTThe parameter can be referenced by the procedure and the value of the parameter can be overwritten by the procedure.
declaration_sectionIt represents the declarations of all variables.
executable_sectionIt represents the code for the procedure.

要调用存储过程，请使用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 ;

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

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 ;

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

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，如下所示:

CALL get_max_salary(@S);
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 ;

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

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

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

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

删除过程

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

语法

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

DROP PROCEDURE [IF EXISTS] procedure_name;

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

示例:

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

DROP PROCEDURE CalcMaxSalary;