mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

14.3 存储过程入门

存储过程是数据库中一个非常强大的功能，它允许你将一系列 SQL 语句封装成一个可重复调用的模块。如果说视图是“保存的查询”，那么存储过程就是“保存的程序”。通过存储过程，你可以实现复杂的业务逻辑，提高代码复用性和执行效率。

一、什么是存储过程？

存储过程（Stored Procedure）是一组预编译的 SQL 语句，它们被存储在数据库服务器中，并可以通过名称被调用执行。存储过程可以接受参数、声明变量、进行条件判断和循环控制，甚至可以返回结果集。

为什么使用存储过程？

优点

说明

性能提升

存储过程在首次执行时被编译并优化，后续调用直接执行，减少网络流量和解析开销。

代码复用

将常用的业务逻辑封装在存储过程中，多个应用程序可以重复调用。

简化复杂操作

可以将多条 SQL 语句和逻辑封装在一起，简化应用程序代码。

增强安全性

通过限制对基础表的直接访问，只给用户执行存储过程的权限，可以保护数据。

减少网络流量

应用程序只需发送存储过程名和参数，而不是多条 SQL 语句。

二、存储过程的基本语法

创建存储过程

DELIMITER //

CREATE PROCEDURE 过程名(参数列表)
BEGIN
    -- SQL 语句体
END //

DELIMITER ;

DELIMITER：MySQL 默认以分号 ; 作为语句结束符，但在存储过程体内会包含多条 SQL 语句，所以需要临时改变分隔符，以便 MySQL 将整个存储过程视为一个完整的语句。
参数列表：可以包含零个或多个参数，每个参数由模式（IN/OUT/INOUT）、参数名和类型组成。
BEGIN ... END：包裹存储过程的语句体，可以包含多个 SQL 语句和逻辑控制。

调用存储过程

CALL 过程名(参数值列表);

删除存储过程

DROP PROCEDURE [IF EXISTS] 过程名;

查看存储过程

SHOW PROCEDURE STATUS WHERE db = '数据库名';
SHOW CREATE PROCEDURE 过程名;

三、参数类型

存储过程的参数有三种模式：

模式

含义

说明

输入参数

调用者传入值，过程内部不能修改并返回。默认模式。

OUT

输出参数

过程内部赋值，返回给调用者。

INOUT

输入输出参数

调用者传入值，过程内部可以修改并返回。

示例

-- 创建带 IN 参数的存储过程
DELIMITER //
CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

-- 调用
CALL GetEmployeeById(1);

-- 创建带 OUT 参数的存储过程
DELIMITER //
CREATE PROCEDURE CountEmployees(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END //
DELIMITER ;

-- 调用
CALL CountEmployees(@emp_count);
SELECT @emp_count;  -- 查看输出变量的值

四、变量声明与使用

在存储过程中，可以使用 DECLARE 语句声明局部变量，也可以使用用户定义的变量（以 @ 开头）。

局部变量

作用域在 BEGIN ... END 块内。
必须在语句体的开头声明。
使用 SET 或 SELECT ... INTO 赋值。

DELIMITER //
CREATE PROCEDURE CalculateBonus()
BEGIN
    DECLARE avg_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);
    
    SELECT AVG(salary) INTO avg_salary FROM employees;
    SET bonus = avg_salary * 0.1;
    
    SELECT avg_salary, bonus;
END //
DELIMITER ;

五、流程控制

存储过程支持常见的流程控制语句，如 IF、CASE、循环等。

IF 语句

IF 条件 THEN
    语句;
ELSEIF 条件 THEN
    语句;
ELSE
    语句;
END IF;

示例：

DELIMITER //
CREATE PROCEDURE CheckSalary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    
    SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
    
    IF emp_salary > 10000 THEN
        SELECT '高薪' AS level;
    ELSEIF emp_salary > 5000 THEN
        SELECT '中薪' AS level;
    ELSE
        SELECT '低薪' AS level;
    END IF;
END //
DELIMITER ;

CASE 语句

CASE 表达式
    WHEN 值1 THEN 语句;
    WHEN 值2 THEN 语句;
    ELSE 语句;
END CASE;

循环

MySQL 支持 WHILE、REPEAT 和 LOOP 循环。

WHILE 循环：

WHILE 条件 DO
    语句;
END WHILE;

REPEAT 循环：

REPEAT
    语句;
UNTIL 条件 END REPEAT;

LOOP 循环（需配合 LEAVE 退出）：

label: LOOP
    语句;
    IF 条件 THEN
        LEAVE label;
    END IF;
END LOOP label;

示例：生成连续数字

DELIMITER //
CREATE PROCEDURE GenerateNumbers(IN max_num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= max_num DO
        SELECT i AS number;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

六、完整示例：员工加薪

假设我们需要给指定部门的员工统一加薪，并返回加薪后的平均工资。

DELIMITER //

CREATE PROCEDURE RaiseSalary(
    IN dept_name VARCHAR(50),
    IN percent DECIMAL(5,2),
    OUT new_avg DECIMAL(10,2)
)
BEGIN
    -- 更新员工工资
    UPDATE employees 
    SET salary = salary * (1 + percent/100)
    WHERE department = dept_name;
    
    -- 计算新的平均工资
    SELECT AVG(salary) INTO new_avg
    FROM employees
    WHERE department = dept_name;
END //

DELIMITER ;

调用：

CALL RaiseSalary('技术部', 10, @avg);
SELECT @avg;

七、注意事项

分隔符问题：创建存储过程时务必使用 DELIMITER 临时更改结束符，创建完后再改回 ;。
权限：创建存储过程需要 CREATE ROUTINE 权限，执行需要 EXECUTE 权限。
调试困难：存储过程的调试相对复杂，建议先测试好 SQL 语句再封装。
不要过度使用：存储过程会将业务逻辑分散在数据库和应用层，可能影响代码的可移植性和维护性。适当使用即可。
MySQL 版本差异：不同 MySQL 版本对存储过程的支持可能略有差异，建议使用较新版本（5.5+ 基本稳定）。

八、小结

存储过程是数据库中的可编程模块，封装了 SQL 语句和逻辑。
使用 CREATE PROCEDURE 创建，CALL 调用，DROP PROCEDURE 删除。
支持 IN、OUT、INOUT 参数，以及局部变量和流程控制。
可以提高性能、复用性和安全性，但也需注意维护成本。