MySQL存储过程详解

以下是关于 MySQL 存储过程的详细教程，包含基本概念、语法、示例及注意事项：

---

MySQL 存储过程教程

1. 存储过程是什么？

• 定义：存储过程（Stored Procedure）是一组预先编译并存储在数据库中的 SQL 语句集合，可通过名称调用。
• 核心功能：
  • 封装复杂业务逻辑。
  • 减少网络传输（只需传递过程名和参数）。
  • 提高代码复用性和执行效率。

---

2. 存储过程基本语法

2.1 创建存储过程

DELIMITER //

CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    -- SQL 逻辑
END //

DELIMITER ;

• DELIMITER：修改分隔符（默认 ;），避免与 SQL 语句冲突。
• 参数格式：[IN | OUT | INOUT] 参数名 数据类型
  • IN：输入参数（默认）。
  • OUT：输出参数。
  • INOUT：输入输出参数。

示例：创建简单存储过程

DELIMITER //

CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) FROM users;
END //

DELIMITER ;

---

2.2 调用存储过程

CALL 存储过程名([参数]);

示例：

CALL GetUserCount();

---

2.3 删除存储过程

DROP PROCEDURE IF EXISTS 存储过程名;

---

3. 存储过程参数

3.1 输入参数（IN）

DELIMITER //

CREATE PROCEDURE GetUserByName(IN userName VARCHAR(50))
BEGIN
    SELECT * FROM users WHERE name = userName;
END //

DELIMITER ;

调用：

CALL GetUserByName('Alice');

---

3.2 输出参数（OUT）

DELIMITER //

CREATE PROCEDURE GetMaxAge(OUT maxAge INT)
BEGIN
    SELECT MAX(age) INTO maxAge FROM users;
END //

DELIMITER ;

调用：

CALL GetMaxAge(@maxAge);
SELECT @maxAge;

---

3.3 输入输出参数（INOUT）

DELIMITER //

CREATE PROCEDURE DoubleValue(INOUT num INT)
BEGIN
    SET num = num * 2;
END //

DELIMITER ;

调用：

SET @value = 10;
CALL DoubleValue(@value);
SELECT @value; -- 输出 20

---

4. 变量与流程控制

4.1 变量声明

DECLARE 变量名 数据类型 [DEFAULT 默认值];

示例：

CREATE PROCEDURE CalculateTotal()
BEGIN
    DECLARE total INT DEFAULT 0;
    SET total = (SELECT SUM(price) FROM orders);
    SELECT total;
END

---

4.2 条件判断（IF）

IF 条件 THEN
    -- 逻辑
ELSEIF 条件 THEN
    -- 逻辑
ELSE
    -- 逻辑
END IF;

示例：

CREATE PROCEDURE CheckUserStatus(IN userId INT)
BEGIN
    DECLARE status VARCHAR(20);
    SELECT user_status INTO status FROM users WHERE id = userId;
    
    IF status = 'active' THEN
        SELECT '用户活跃';
    ELSE
        SELECT '用户未活跃';
    END IF;
END

---

4.3 循环（WHILE）

WHILE 条件 DO
    -- 逻辑
END WHILE;

示例：插入 10 条测试数据

CREATE PROCEDURE InsertTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        INSERT INTO test_table (value) VALUES (i);
        SET i = i + 1;
    END WHILE;
END

---

5. 异常处理

MySQL 存储过程支持简单的错误处理：

DECLARE 处理动作 HANDLER FOR 错误类型
BEGIN
    -- 错误处理逻辑
END;

示例：

CREATE PROCEDURE SafeInsert()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT '发生错误，操作回滚';
        ROLLBACK;
    END;
    
    START TRANSACTION;
    INSERT INTO users (name) VALUES ('Alice');
    INSERT INTO users (name) VALUES (NULL); -- 触发错误（假设 name 非空）
    COMMIT;
END

---

6. 存储过程优缺点

优点

• 性能优化：减少网络传输，预编译执行。
• 代码复用：统一业务逻辑。
• 安全控制：通过权限管理限制数据访问。

缺点

• 调试困难：数据库端调试工具有限。
• 维护成本：业务逻辑分散在应用层和数据库层。
• 移植性差：不同数据库语法差异大。

---

7. 适用场景

• 复杂查询：多表关联计算或聚合。
• 批量操作：数据迁移或定时任务。
• 权限隔离：通过存储过程限制直接表访问。

---

8. 示例：完整存储过程

示例：用户注册逻辑

DELIMITER //

CREATE PROCEDURE RegisterUser(
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    OUT p_message VARCHAR(100)
BEGIN
    DECLARE userCount INT;
    
    -- 检查用户名是否存在
    SELECT COUNT(*) INTO userCount FROM users WHERE username = p_username;
    IF userCount > 0 THEN
        SET p_message = '用户名已存在';
    ELSE
        -- 插入新用户
        INSERT INTO users (username, email) VALUES (p_username, p_email);
        SET p_message = '注册成功';
    END IF;
END //

DELIMITER ;

调用：

CALL RegisterUser('alice', 'alice@example.com', @message);
SELECT @message;

---

9. 总结

• 核心操作：创建、调用、删除存储过程。
• 参数类型：IN、OUT、INOUT。
• 流程控制：变量、条件、循环、异常处理。
• 适用场景：优先用于复杂、高频的数据库操作。

通过存储过程，可以将业务逻辑封装在数据库中，但需权衡维护成本和性能收益。