跳到主要内容

MySQL 存储过程和函数

存储过程和函数是预先编译并存储在数据库中的 SQL 语句集合。本章将详细介绍如何创建和使用存储过程与函数。

存储过程概述​

什么是存储过程？​

存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经过编译后存储在数据库中：

┌─────────────────────────────────────────────────────────────┐
│                    存储过程的优势                            │
├─────────────────────────────────────────────────────────────┤
│                                                            │
│   1. 性能优化                                              │
│      - 预编译执行，减少解析时间                            │
│      - 减少网络传输，一次调用执行多条语句                   │
│                                                            │
│   2. 安全性                                                │
│      - 控制数据访问权限                                    │
│      - 隐藏表结构细节                                      │
│                                                            │
│   3. 代码复用                                              │
│      - 封装业务逻辑                                        │
│      - 统一维护                                            │
│                                                            │
│   4. 减少网络流量                                          │
│      - 客户端只需调用存储过程名                            │
│      - 不需要传输大量 SQL 语句                             │
│                                                            │
└─────────────────────────────────────────────────────────────┘

存储过程 vs 函数​

特性存储过程函数
返回值可返回多个结果集必须返回单个值
参数类型IN、OUT、INOUT只有 IN
调用方式CALL 语句SELECT 或表达式中
DML 操作可以执行不允许
使用场景复杂业务逻辑计算、转换

创建存储过程​

基本语法​

CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    SQL 语句;
END;

第一个存储过程​

-- 创建简单的存储过程
DELIMITER //

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

DELIMITER ;

-- 调用存储过程
CALL get_all_users();

解释：

  • DELIMITER //：临时更改语句分隔符，避免存储过程中的分号被误解
  • BEGIN ... END：存储过程体
  • DELIMITER ;：恢复默认分隔符

带参数的存储过程​

DELIMITER //

-- IN 参数：输入参数
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

-- OUT 参数：输出参数
CREATE PROCEDURE get_user_count(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END //

-- INOUT 参数：输入输出参数
CREATE PROCEDURE double_value(INOUT num INT)
BEGIN
    SET num = num * 2;
END //

DELIMITER ;

-- 调用示例
CALL get_user_by_id(1);

-- 调用 OUT 参数
CALL get_user_count(@count);
SELECT @count;

-- 调用 INOUT 参数
SET @value = 10;
CALL double_value(@value);
SELECT @value;  -- 输出 20

参数类型说明​

类型说明使用场景
IN输入参数（默认）传入值给存储过程
OUT输出参数存储过程返回值给调用者
INOUT输入输出参数传入值并返回修改后的值

变量声明​

DELIMITER //

CREATE PROCEDURE variable_demo()
BEGIN
    -- 声明局部变量
    DECLARE v_name VARCHAR(50) DEFAULT '默认名称';
    DECLARE v_count INT;
    DECLARE v_total DECIMAL(10, 2);
    
    -- 使用 SET 赋值
    SET v_name = '张三';
    
    -- 使用 SELECT INTO 赋值
    SELECT COUNT(*) INTO v_count FROM users;
    
    -- 使用查询结果
    SELECT SUM(salary) INTO v_total FROM users WHERE status = 'active';
    
    -- 返回结果
    SELECT v_name AS name, v_count AS user_count, v_total AS total_salary;
END //

DELIMITER ;

CALL variable_demo();

条件判断​

DELIMITER //

CREATE PROCEDURE get_user_level(IN user_id INT)
BEGIN
    DECLARE v_salary DECIMAL(10, 2);
    DECLARE v_level VARCHAR(20);
    
    -- 获取用户薪资
    SELECT salary INTO v_salary FROM users WHERE id = user_id;
    
    -- 条件判断
    IF v_salary >= 20000 THEN
        SET v_level = '高级';
    ELSEIF v_salary >= 10000 THEN
        SET v_level = '中级';
    ELSE
        SET v_level = '初级';
    END IF;
    
    SELECT user_id, v_salary, v_level;
END //

DELIMITER ;

CALL get_user_level(1);

CASE 语句​

DELIMITER //

CREATE PROCEDURE get_status_name(IN status_code VARCHAR(20))
BEGIN
    DECLARE v_status_name VARCHAR(50);
    
    CASE status_code
        WHEN 'active' THEN SET v_status_name = '活跃用户';
        WHEN 'inactive' THEN SET v_status_name = '非活跃用户';
        WHEN 'banned' THEN SET v_status_name = '已封禁';
        ELSE SET v_status_name = '未知状态';
    END CASE;
    
    SELECT status_code, v_status_name;
END //

DELIMITER ;

CALL get_status_name('active');

循环语句​

WHILE 循环​

DELIMITER //

CREATE PROCEDURE while_loop_demo(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    WHILE i < max_count DO
        SET result = CONCAT(result, i, ', ');
        SET i = i + 1;
    END WHILE;
    
    SELECT result;
END //

DELIMITER ;

CALL while_loop_demo(5);

REPEAT 循环​

DELIMITER //

CREATE PROCEDURE repeat_loop_demo(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    REPEAT
        SET result = CONCAT(result, i, ', ');
        SET i = i + 1;
    UNTIL i >= max_count END REPEAT;
    
    SELECT result;
END //

DELIMITER ;

CALL repeat_loop_demo(5);

LOOP 循环​

DELIMITER //

CREATE PROCEDURE loop_demo(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    my_loop: LOOP
        SET i = i + 1;
        
        -- 条件退出
        IF i > max_count THEN
            LEAVE my_loop;
        END IF;
        
        SET result = CONCAT(result, i, ', ');
        
        -- 跳过偶数
        IF i % 2 = 0 THEN
            ITERATE my_loop;
        END IF;
    END LOOP;
    
    SELECT result;
END //

DELIMITER ;

CALL loop_demo(10);

循环控制语句：

  • LEAVE：退出循环（类似 break）
  • ITERATE：跳过本次循环（类似 continue）

游标​

游标用于逐行处理查询结果：

DELIMITER //

CREATE PROCEDURE cursor_demo()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_salary DECIMAL(10, 2);
    
    -- 声明游标
    DECLARE cur CURSOR FOR 
        SELECT id, name, salary FROM users WHERE salary > 5000;
    
    -- 声明结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    DROP TEMPORARY TABLE IF EXISTS temp_results;
    CREATE TEMPORARY TABLE temp_results (
        id INT,
        name VARCHAR(50),
        salary DECIMAL(10, 2),
        tax DECIMAL(10, 2)
    );
    
    -- 打开游标
    OPEN cur;
    
    -- 循环读取
    read_loop: LOOP
        FETCH cur INTO v_id, v_name, v_salary;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 计算税收
        INSERT INTO temp_results VALUES (
            v_id, v_name, v_salary, v_salary * 0.1
        );
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
    
    -- 返回结果
    SELECT * FROM temp_results;
END //

DELIMITER ;

CALL cursor_demo();

错误处理​

DELIMITER //

CREATE PROCEDURE error_handling_demo(IN user_id INT)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 发生错误时执行
        GET DIAGNOSTICS CONDITION 1
        @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, 
        @text = MESSAGE_TEXT;
        
        SELECT @errno AS error_code, @text AS error_message;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- 业务操作
    UPDATE accounts SET balance = balance - 100 WHERE id = user_id;
    
    -- 模拟错误
    -- SELECT 1/0;  -- 会触发错误处理
    
    UPDATE accounts SET balance = balance + 100 WHERE id = user_id + 1;
    
    COMMIT;
    
    SELECT '操作成功' AS result;
END //

DELIMITER ;

Handler 类型：

  • CONTINUE：继续执行
  • EXIT：退出存储过程
  • UNDO：撤销操作（MySQL 暂不支持）

错误类型：

  • SQLEXCEPTION：所有 SQL 异常
  • SQLWARNING：SQL 警告
  • NOT FOUND：没有数据

创建函数​

基本语法​

CREATE FUNCTION 函数名([参数列表])
RETURNS 返回类型
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    SQL 语句;
    RETURN 返回值;
END;

函数示例​

DELIMITER //

-- 计算税收
CREATE FUNCTION calculate_tax(salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE tax_rate DECIMAL(5, 2);
    
    IF salary <= 5000 THEN
        SET tax_rate = 0.03;
    ELSEIF salary <= 10000 THEN
        SET tax_rate = 0.10;
    ELSEIF salary <= 20000 THEN
        SET tax_rate = 0.20;
    ELSE
        SET tax_rate = 0.30;
    END IF;
    
    RETURN salary * tax_rate;
END //

-- 格式化手机号
CREATE FUNCTION format_phone(phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF LENGTH(phone) = 11 THEN
        RETURN CONCAT(
            LEFT(phone, 3), '-',
            SUBSTRING(phone, 4, 4), '-',
            RIGHT(phone, 4)
        );
    ELSE
        RETURN phone;
    END IF;
END //

DELIMITER ;

-- 使用函数
SELECT 
    id,
    name,
    salary,
    calculate_tax(salary) AS tax
FROM users;

SELECT 
    phone,
    format_phone(phone) AS formatted_phone
FROM users;

函数特性​

特性说明
DETERMINISTIC相同输入总是返回相同结果
NOT DETERMINISTIC相同输入可能返回不同结果（默认）
READS SQL DATA函数读取数据
MODIFIES SQL DATA函数修改数据

管理存储过程和函数​

查看存储过程​

-- 查看数据库中的存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_user_by_id;

-- 从 information_schema 查看
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database';

修改存储过程​

-- MySQL 不支持直接修改，需要先删除再创建
DROP PROCEDURE IF EXISTS get_user_by_id;

DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    -- 新的定义
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

删除存储过程​

-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_by_id;

-- 删除函数
DROP FUNCTION IF EXISTS calculate_tax;

实战案例​

转账存储过程​

DELIMITER //

CREATE PROCEDURE transfer(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(10, 2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE v_balance DECIMAL(10, 2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = '转账失败，系统错误';
    END;
    
    -- 检查转出账户余额
    SELECT balance INTO v_balance FROM accounts WHERE id = from_id;
    
    IF v_balance IS NULL THEN
        SET result = '转出账户不存在';
    ELSEIF v_balance < amount THEN
        SET result = '余额不足';
    ELSE
        START TRANSACTION;
        
        -- 扣款
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        
        -- 存款
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        
        -- 记录日志
        INSERT INTO transfer_logs (from_id, to_id, amount, created_at)
        VALUES (from_id, to_id, amount, NOW());
        
        COMMIT;
        SET result = '转账成功';
    END IF;
END //

DELIMITER ;

-- 调用
CALL transfer(1, 2, 100.00, @result);
SELECT @result;

分页查询存储过程​

DELIMITER //

CREATE PROCEDURE paginate_users(
    IN page_num INT,
    IN page_size INT
)
BEGIN
    DECLARE offset_val INT;
    SET offset_val = (page_num - 1) * page_size;
    
    SELECT 
        id,
        username,
        email,
        status,
        created_at
    FROM users
    ORDER BY id
    LIMIT offset_val, page_size;
    
    -- 返回总数
    SELECT COUNT(*) AS total_count FROM users;
END //

DELIMITER ;

-- 调用：获取第 2 页，每页 10 条
CALL paginate_users(2, 10);

批量处理存储过程​

DELIMITER //

CREATE PROCEDURE batch_update_status(
    IN batch_size INT
)
BEGIN
    DECLARE affected_rows INT DEFAULT 0;
    DECLARE total_affected INT DEFAULT 0;
    
    repeat_loop: REPEAT
        -- 批量更新
        UPDATE orders 
        SET status = 'expired' 
        WHERE status = 'pending' 
          AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
        LIMIT batch_size;
        
        SET affected_rows = ROW_COUNT();
        SET total_affected = total_affected + affected_rows;
        
        -- 提交当前批次
        COMMIT;
        
        -- 短暂暂停，减少锁竞争
        DO SLEEP(0.1);
        
    UNTIL affected_rows = 0 END REPEAT;
    
    SELECT total_affected AS total_updated;
END //

DELIMITER ;

-- 调用：每批处理 1000 条
CALL batch_update_status(1000);

最佳实践​

1. 命名规范​

-- 好的命名
CREATE PROCEDURE sp_get_user_by_id(...)   -- 存储过程
CREATE PROCEDURE sp_transfer_money(...)    -- 动词开头
CREATE FUNCTION fn_calculate_tax(...)      -- 函数

-- 不好的命名
CREATE PROCEDURE proc1(...)                -- 无意义
CREATE PROCEDURE get_data(...)             -- 不够具体

2. 错误处理​

DELIMITER //

CREATE PROCEDURE safe_operation(IN user_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        
        -- 记录错误日志
        INSERT INTO error_logs (error_code, error_message, created_at)
        VALUES (@errno, @text, NOW());
        
        ROLLBACK;
    END;
    
    START TRANSACTION;
    -- 业务操作
    COMMIT;
END //

DELIMITER ;

3. 避免动态 SQL​

-- 不好的做法：直接拼接 SQL
SET @sql = CONCAT('SELECT * FROM ', table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- 好的做法：使用参数化查询
PREPARE stmt FROM 'SELECT * FROM users WHERE status = ?';
SET @status = 'active';
EXECUTE stmt USING @status;
DEALLOCATE PREPARE stmt;

4. 性能优化​

-- 1. 使用索引列
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;  -- 使用主键索引
END;

-- 2. 减少事务范围
CREATE PROCEDURE update_user(IN user_id INT, IN new_name VARCHAR(50))
BEGIN
    -- 只在必要时开启事务
    UPDATE users SET name = new_name WHERE id = user_id;
END;

-- 3. 使用 LIMIT 限制结果集
CREATE PROCEDURE get_recent_orders(IN user_id INT)
BEGIN
    SELECT * FROM orders 
    WHERE user_id = user_id 
    ORDER BY created_at DESC 
    LIMIT 100;
END;

小结​

本章我们学习了：

  1. 存储过程基础：创建、调用、参数类型
  2. 变量和控制结构：IF、CASE、循环、游标
  3. 错误处理：Handler 机制
  4. 存储函数：创建和使用自定义函数
  5. 管理操作：查看、修改、删除
  6. 实战案例：转账、分页、批量处理
  7. 最佳实践：命名规范、错误处理、性能优化

练习​

  1. 创建一个存储过程，实现用户注册功能（检查用户名是否存在）
  2. 创建一个函数，计算两个日期之间的工作日天数
  3. 使用游标实现数据导出功能
  4. 创建带事务的存储过程，处理订单创建流程

参考资源​