【MySQL】（基础篇十七） —— 存储过程

存储过程

本文将介绍什么是存储过程，为什么要使用存储过程以及如何使用存储过程，并且介绍创建和使用存储过程的基本语法。

MySQL的存储过程是预编译的SQL语句集合，它们作为一个可执行单元存储在数据库中。存储过程能够封装复杂的业务逻辑，接受输入参数，输出结果集，甚至返回值，从而提供了一种高效且模块化的方法来执行常见的或复杂的数据库操作。

创建存储过程的基本语法如下：

CREATE PROCEDURE procedure_name (IN|OUT|INOUT param1 datatype, ...)
BEGIN
   -- SQL语句集合
   ...
END;

• CREATE PROCEDURE 是用来创建存储过程的关键字。
• procedure_name 是存储过程的名称。
• (IN|OUT|INOUT param1 datatype, …)参数列表，其中：
  • IN 参数表示传入值到存储过程中。
  • OUT 参数表示从存储过程中传出值。
  • INOUT 参数既可传入也可传出。
  • param1 datatype 定义参数名及其数据类型。
• BEGIN...END; 包含了存储过程中的SQL语句。

检查存储过程使用SHOW CREATEP ROCEDURE语句

【示例】创建一个存储过程，用于计算员工的平均工资

CREATE PROCEDURE `avg_salary`()
BEGIN
  SELECT AVG(salary) as avgsalary
  FROM employees;
 END

和视图一样，存储过程在被创建之后也会被保存到本地，可以随时查看其sql语句

执行存储过程

调用存储过程使用 CALL 语句，因为存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要

【示例】执行上面例子中创建的存储过程

CALL avg_salary();

运行结果：

删除存储过程

删除存储过程语法如下：

DROP PROCEDURE 存储过程名

使用参数

变量（variable）内存中一个特定的位置，用来临时存储数据。

【示例】创建一个带参数的存储过程employeesalary，实现计算员工工资的最高值、最低值和平均值

CREATE PROCEDURE employeesalary(
  OUT sl DECIMAL(8,2),
  OUT sh DECIMAL(8,2),
  OUT sa DECIMAL(8,2)
)
BEGIN
  SELECT MIN(salary)
  INTO sl
  FROM employees;
  SELECT MAX(salary)
  INTO sh
  FROM employees;
  SELECT AVG(salary)
  INTO sa
  FROM employees;
END;

调用存储过程，为几个数据创建对应的变量

CALL employeesalary(
  @lowsalary,
  @highsalary,
  @avgsalay
);

查询变量中存储的数据：

SELECT @lowsalary

运行结果：

【示例】同时使用IN和OUT参数创建存储过程，查询不同manager管理的用户的数量

CREATE PROCEDURE managertotal(
  IN manager INT,
  OUT totalpeople INT
)
BEGIN
  SELECT COUNT(manager_id)
  FROM employees
  WHERE manager_id = manager;
END

调用存储过程：

CALL managertotal('100', @total);

运行结果：