『MySQL 存储过程详解』及『存储过程 vs 存储函数』

一、MySQL存储过程详解

1. 存储过程的定义

MySQL 存储过程（Stored Procedure）是一组预编译的SQL语句集合，存储在数据库中，通过名称调用。它类似于编程语言中的函数，支持参数传递、流程控制（条件判断、循环等）、变量声明和异常处理。

2. 使用存储过程的目的

• 代码重用：避免重复编写相同逻辑的SQL语句。
• 减少网络流量：客户端只需传递存储过程名称和参数，而非多行SQL。
• 提高性能：预编译执行，减少解析和优化时间。
• 数据封装与安全：隐藏业务逻辑细节，通过权限控制限制直接访问表。
• 事务管理：在存储过程中统一管理事务（如提交或回滚）。

3. 使用前提条件

1. 权限要求：用户需具备 `CREATE ROUTINE` 权限，执行时可能需要 `EXECUTE` 权限。
2. 分隔符调整：需使用 `DELIMITER` 修改默认分隔符（`;`）以定义存储过程。
3. 语法基础：熟悉SQL、变量声明（`DECLARE`）、流程控制（`IF`, `LOOP`, `CASE`）等。

4. 注意事项

• 调试困难：MySQL存储过程调试工具较弱，需依赖日志或打印变量。
• 维护成本：业务逻辑分散在应用层和数据库层，需同步维护。
• 迁移问题：不同数据库（如Oracle、PostgreSQL）的存储过程语法差异大。
• 性能陷阱：复杂逻辑可能降低性能（如滥用循环或临时表）。
• 版本控制：存储过程存储在数据库中，需额外工具管理版本变更。

5. 应用场景

场景1：电商下单操作需同时插入订单、扣减库存、更新用户积分

DELIMITER $$
CREATE PROCEDURE PlaceOrder(
  IN user_id INT,
  IN product_id INT,
  IN quantity INT
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SELECT 'Error: Transaction rolled back';
  END;
  
  START TRANSACTION;
  -- 插入订单
  INSERT INTO orders (user_id, product_id, quantity) 
  VALUES (user_id, product_id, quantity);
  -- 扣减库存
  UPDATE products 
  SET stock = stock - quantity 
  WHERE id = product_id AND stock >= quantity;
  -- 检查库存是否足够
  IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SELECT 'Error: Insufficient stock';
  ELSE
    COMMIT;
    SELECT 'Order placed successfully';
  END IF;
END$$
DELIMITER ;


--调用存储过程
CALL PlaceOrder(1, 100, 2);

场景2：定时任务（配合事件调度器）：每天凌晨清理过期日志
 

CREATE PROCEDURE CleanupLogs()
BEGIN
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;
END

-- 创建事件
CREATE EVENT DailyLogCleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO CALL CleanupLogs();

场景3：计算部门平均工资

DELIMITER $$
CREATE PROCEDURE CalculateDeptAvgSalary(
  IN dept_id INT,
  OUT avg_salary DECIMAL(10,2)
)
BEGIN
  SELECT AVG(salary) INTO avg_salary 
  FROM employees 
  WHERE department_id = dept_id;
END$$
DELIMITER ;

-- 调用
CALL CalculateDeptAvgSalary(5, @avg);
SELECT @avg;

6. 总结

• 优点：提升性能、减少网络开销、增强安全性。  
• 缺点：调试困难、耦合数据库、迁移成本高。  
• 适用项目：传统单体架构、OLTP系统、需高频执行复杂逻辑的场景。  
• 不适用场景：微服务架构（倾向于业务逻辑在应用层）、需要跨数据库兼容的系统。

二、MySQL 存储过程 vs 存储函数

1. 核心目的

存储过程	存储函数
用于执行一系列操作（如增删改查、事务控制）。	用于计算并返回一个值，类似于编程语言中的函数。
示例：批量插入订单、更新库存、记录日志。	示例：计算用户年龄、校验邮箱格式、生成订单号。

---

2. 返回值

存储过程	存储函数
可以没有返回值，或通过 OUT/INOUT 参数返回多个值。	必须返回一个确定的值，且类型在定义时指定。
支持通过 SELECT 返回结果集（多行数据）。	无法直接返回结果集，只能返回单个标量值（如整数、字符串）。

---

3. 调用方式

存储过程	存储函数
使用 CALL 关键字调用： CALL procedure_name(param1, param2);	在 SQL 语句中直接调用，类似内置函数： SELECT function_name(param1, param2);
可以独立执行。	通常嵌入在 SELECT、WHERE 等语句中使用。

---

4. 参数类型

存储过程	存储函数
支持 IN（输入）、OUT（输出）、INOUT（输入输出）参数。	仅支持 IN 参数，且必须显式声明返回类型。
示例： CREATE PROCEDURE proc(IN a INT, OUT b INT)	示例： CREATE FUNCTION func(a INT) RETURNS INT

---

5. SQL 语句中的使用

存储过程	存储函数
不能在 SELECT、WHERE 等语句中直接调用。	可直接嵌入 SQL 表达式，如： SELECT id, calculate_discount(price) FROM products;
需要通过 CALL 单独执行。	适用于需要动态计算的场景（如字段值转换）。

---

6. 事务控制

存储过程	存储函数
支持事务控制（如 START TRANSACTION、COMMIT、ROLLBACK）。	不允许使用事务（无法执行 COMMIT 或 ROLLBACK）。
适合需要原子性操作的场景（如转账、订单提交）。	仅用于计算，不涉及数据修改的事务性逻辑。

---

7. 使用场景对比

场景	存储过程	存储函数
数据修改	✅ 适合（如批量更新、插入）	❌ 通常只读，避免副作用
复杂业务逻辑	✅ 支持多步骤逻辑和流程控制	❌ 适合简单计算
返回结果集	✅ 可通过 SELECT 返回多行数据	❌ 只能返回单个值
嵌入 SQL 表达式	❌ 无法嵌入	✅ 如 WHERE salary > calc_bonus()

---

8. 权限要求

存储过程	存储函数
需要 CREATE ROUTINE 权限，执行时可能需要 EXECUTE 权限。	同存储过程，但若函数标记为 DETERMINISTIC（无副作用），可能需额外权限。

9. 关键差异总结

特性	存储过程	存储函数
返回值	可无返回值，或通过参数返回	必须返回单个值
SQL 嵌入性	不可嵌入 SQL 语句	可直接嵌入 SELECT/WHERE 等
事务支持	支持	不支持
典型用途	数据修改、复杂逻辑	计算、数据转换
参数类型	IN/OUT/INOUT	仅 IN
结果集	可返回多行结果	只能返回标量值

10.何时选择存储过程 vs 存储函数？

• 选存储过程：需修改数据、管理事务、返回多个结果或输出参数。

• 选存储函数：需在 SQL 查询中动态计算值，且逻辑简单无副作用。