MySQL: MySQL存储过程与函数详解
最后更新：2026-08-26
存储过程是预编译的 SQL 集合——一次编写，多次调用。
本课讲解存储过程和函数的创建与使用。
graph TB
A[CALL 存储过程] --> B[参数传递 IN/OUT/INOUT]
B --> C[DECLARE 声明变量]
C --> D{流程控制}
D --> E[IF / CASE 条件]
D --> F[WHILE / LOOP 循环]
D --> G[游标 CURSOR 遍历]
E --> H[执行SQL]
F --> H
G --> H
H --> I[OUT参数返回]
I --> J[结束]
1. 你将学到
- CREATE PROCEDURE 创建存储过程
- IN/OUT/INOUT 参数类型
- DECLARE 变量声明
- IF/CASE/WHILE 流程控制
- 游标 CURSOR
2. 真实场景
(1) 痛点：复杂逻辑重复写
每月统计各部门工资总额，SQL 很长且重复执行。
(2) 存储过程的解法
SQL
CREATE PROCEDURE sp_dept_salary_stats()
BEGIN
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
END;
-- 调用
CALL sp_dept_salary_stats();
3. 创建存储过程
▶ 示例：基本存储过程
SQL
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE sp_get_user(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
-- 调用
CALL sp_get_user(1);
▶ 示例：带 OUT 参数
SQL
DELIMITER //
CREATE PROCEDURE sp_count_users(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;
-- 调用
CALL sp_count_users(@total);
SELECT @total;
▶ 示例：带 INOUT 参数
SQL
DELIMITER //
CREATE PROCEDURE sp_double(INOUT num INT)
BEGIN
SET num = num * 2;
END //
DELIMITER ;
-- 调用
SET @num = 5;
CALL sp_double(@num);
SELECT @num; -- 10
4. 变量声明
▶ 示例：DECLARE 变量
SQL
DELIMITER //
CREATE PROCEDURE sp_variable_demo()
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE avg_salary DECIMAL(10,2);
DECLARE user_name VARCHAR(50);
-- 赋值
SELECT COUNT(*) INTO total FROM employees;
SELECT AVG(salary) INTO avg_salary FROM employees;
-- 使用变量
SELECT total, avg_salary;
END //
DELIMITER ;
5. 流程控制
▶ 示例：IF 语句
SQL
DELIMITER //
CREATE PROCEDURE sp_check_salary(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 'High salary' AS level;
ELSEIF emp_salary > 5000 THEN
SELECT 'Medium salary' AS level;
ELSE
SELECT 'Low salary' AS level;
END IF;
END //
DELIMITER ;
▶ 示例：CASE 语句
SQL
DELIMITER //
CREATE PROCEDURE sp_order_status(IN order_id INT)
BEGIN
DECLARE order_status VARCHAR(20);
SELECT status INTO order_status FROM orders WHERE id = order_id;
CASE order_status
WHEN 'pending' THEN SELECT 'Order is pending' AS message;
WHEN 'paid' THEN SELECT 'Order is paid' AS message;
WHEN 'shipped' THEN SELECT 'Order is shipped' AS message;
ELSE SELECT 'Unknown status' AS message;
END CASE;
END //
DELIMITER ;
▶ 示例：WHILE 循环
SQL
DELIMITER //
CREATE PROCEDURE sp_sum_1_to_n(IN n INT, OUT total INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET total = 0;
WHILE i <= n DO
SET total = total + i;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- 调用
CALL sp_sum_1_to_n(100, @total);
SELECT @total; -- 5050
6. 游标 CURSOR
▶ 示例：游标遍历
SQL
DELIMITER //
CREATE PROCEDURE sp_list_users()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE user_name VARCHAR(50);
DECLARE user_email VARCHAR(100);
-- 声明游标
DECLARE cur_users CURSOR FOR
SELECT username, email FROM users;
-- 声明结束处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_users;
-- 循环遍历
read_loop: LOOP
FETCH cur_users INTO user_name, user_email;
IF done THEN
LEAVE read_loop;
END IF;
SELECT user_name, user_email;
END LOOP;
-- 关闭游标
CLOSE cur_users;
END //
DELIMITER ;
7. 存储函数
▶ 示例：创建函数
SQL
DELIMITER //
CREATE FUNCTION fn_calc_bonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE bonus DECIMAL(10,2);
IF salary > 10000 THEN
SET bonus = salary * 0.2;
ELSEIF salary > 5000 THEN
SET bonus = salary * 0.1;
ELSE
SET bonus = salary * 0.05;
END IF;
RETURN bonus;
END //
DELIMITER ;
-- 使用函数
SELECT name, salary, fn_calc_bonus(salary) AS bonus FROM employees;
8. 管理存储过程
▶ 示例：查看和删除
SQL
-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
-- 查看创建语句
SHOW CREATE PROCEDURE sp_get_user;
-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_get_user;
-- 删除函数
DROP FUNCTION IF EXISTS fn_calc_bonus;
❓ 常见问题
Q 存储过程和函数有什么区别？
A 存储过程用 CALL 调用，不返回值；函数用 SELECT 调用，必须返回一个值。
Q DELIMITER 是什么？
A 修改语句结束符。因为存储过程中有
;，需要换成 // 避免提前结束。Q 存储过程能提高性能？
A 存储过程预编译，减少网络传输。但现代 MySQL 优化器已经很好，性能提升有限。
Q 存储过程和函数区别？
A 过程用 CALL 调用可有 OUT 参数不返回值，函数在 SELECT 中使用必须 RETURN 一个值。
Q 存储过程会被 SQL 注入吗？
A 用 PREPARE+CONCAT 拼接 SQL 会注入，参数传递方式安全。
📖 小节
- 存储过程用
CREATE PROCEDURE创建，CALL调用 - 参数类型：IN（输入）、OUT（输出）、INOUT（双向）
- DECLARE 声明变量，SET 赋值
- 流程控制：IF/CASE/WHILE/LOOP
- 游标用于逐行遍历结果集
- 存储函数必须返回一个值
📝 作业
-
基础题（难度⭐）：创建一个存储过程，输入用户 ID 返回用户信息。
-
进阶题（难度⭐⭐）：创建带 IF 判断的存储过程，根据分数返回等级。
-
挑战题（难度⭐⭐⭐）：用游标遍历用户表，计算并更新每个用户的积分。
