MySQL存储过程与函数实战指南

在MySQL数据库开发中，存储过程和函数是提高数据库性能、封装业务逻辑的重要工具。本文将通过实战案例详细介绍MySQL存储过程与函数的使用技巧和最佳实践。

存储过程基础

1. 存储过程的创建和调用

-- 创建简单的存储过程
DELIMITER //
CREATE PROCEDURE GetUserInfo(IN user_id INT)
BEGIN
    SELECT 
        id,
        username,
        email,
        created_at
    FROM users 
    WHERE id = user_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserInfo(1001);

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

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

2. 带参数的存储过程

-- 创建带输入输出参数的存储过程
DELIMITER //
CREATE PROCEDURE UserStatistics(
    IN start_date DATE,
    IN end_date DATE,
    OUT total_users INT,
    OUT active_users INT,
    OUT new_users INT
)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE temp_count INT DEFAULT 0;
    
    -- 总用户数
    SELECT COUNT(*) INTO total_users 
    FROM users 
    WHERE created_at <= end_date;
    
    -- 活跃用户数（最近登录）
    SELECT COUNT(*) INTO active_users 
    FROM users 
    WHERE last_login_at BETWEEN start_date AND end_date;
    
    -- 新注册用户数
    SELECT COUNT(*) INTO new_users 
    FROM users 
    WHERE DATE(created_at) BETWEEN start_date AND end_date;
    
    -- 输出统计信息
    SELECT 
        total_users as '总用户数',
        active_users as '活跃用户数',
        new_users as '新用户数',
        ROUND(active_users / total_users * 100, 2) as '活跃率%';
        
END //
DELIMITER ;

-- 调用带输出参数的存储过程
CALL UserStatistics('2023-08-01', '2023-08-31', @total, @active, @new);
SELECT @total, @active, @new;

3. 复杂业务逻辑存储过程

-- 订单处理存储过程
DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN p_user_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_result_code INT,
    OUT p_result_message VARCHAR(255)
)
BEGIN
    -- 声明变量
    DECLARE v_stock_quantity INT DEFAULT 0;
    DECLARE v_product_price DECIMAL(10,2) DEFAULT 0;
    DECLARE v_user_balance DECIMAL(10,2) DEFAULT 0;
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_error_count INT DEFAULT 0;
    
    -- 声明异常处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_code = -1;
        SET p_result_message = '订单处理失败：数据库异常';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 1. 检查商品库存
    SELECT stock_quantity, price 
    INTO v_stock_quantity, v_product_price
    FROM products 
    WHERE id = p_product_id AND status = 1
    FOR UPDATE;
    
    -- 检查库存是否充足
    IF v_stock_quantity < p_quantity THEN
        SET p_result_code = 1001;
        SET p_result_message = CONCAT('库存不足，当前库存：', v_stock_quantity);
        ROLLBACK;
    ELSE
        -- 2. 检查用户余额
        SELECT balance INTO v_user_balance
        FROM user_accounts 
        WHERE user_id = p_user_id
        FOR UPDATE;
        
        SET v_total_amount = v_product_price * p_quantity;
        
        IF v_user_balance < v_total_amount THEN
            SET p_result_code = 1002;
            SET p_result_message = CONCAT('余额不足，需要：', v_total_amount, '，当前余额：', v_user_balance);
            ROLLBACK;
        ELSE
            -- 3. 创建订单
            INSERT INTO orders (user_id, product_id, quantity, unit_price, total_amount, status, created_at)
            VALUES (p_user_id, p_product_id, p_quantity, v_product_price, v_total_amount, 1, NOW());
            
            SET p_order_id = LAST_INSERT_ID();
            
            -- 4. 更新库存
            UPDATE products 
            SET stock_quantity = stock_quantity - p_quantity,
                updated_at = NOW()
            WHERE id = p_product_id;
            
            -- 5. 扣除用户余额
            UPDATE user_accounts 
            SET balance = balance - v_total_amount,
                updated_at = NOW()
            WHERE user_id = p_user_id;
            
            -- 6. 记录账户变动
            INSERT INTO account_transactions (user_id, order_id, amount, type, description, created_at)
            VALUES (p_user_id, p_order_id, -v_total_amount, 'purchase', '购买商品', NOW());
            
            -- 提交事务
            COMMIT;
            
            SET p_result_code = 0;
            SET p_result_message = '订单处理成功';
        END IF;
    END IF;
    
END //
DELIMITER ;

-- 调用订单处理存储过程
CALL ProcessOrder(1001, 2001, 2, @order_id, @result_code, @result_msg);
SELECT @order_id as '订单ID', @result_code as '结果码', @result_msg as '结果信息';

存储函数实战

1. 简单计算函数

-- 创建计算年龄的函数
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE age INT;
    
    IF birth_date IS NULL THEN
        RETURN NULL;
    END IF;
    
    SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
    
    RETURN age;
END //
DELIMITER ;

-- 使用函数
SELECT 
    username,
    birthday,
    CalculateAge(birthday) as age
FROM user_profiles 
WHERE birthday IS NOT NULL;

-- 创建格式化金额的函数
DELIMITER //
CREATE FUNCTION FormatMoney(amount DECIMAL(10,2))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE formatted_amount VARCHAR(20);
    
    IF amount IS NULL THEN
        RETURN '0.00';
    END IF;
    
    IF amount >= 10000 THEN
        SET formatted_amount = CONCAT(ROUND(amount/10000, 2), '万');
    ELSEIF amount >= 1000 THEN
        SET formatted_amount = CONCAT(ROUND(amount/1000, 2), 'K');
    ELSE
        SET formatted_amount = CONCAT(amount, '');
    END IF;
    
    RETURN formatted_amount;
END //
DELIMITER ;

-- 使用格式化函数
SELECT 
    product_name,
    price,
    FormatMoney(price) as formatted_price
FROM products;

2. 字符串处理函数

-- 创建手机号脱敏函数
DELIMITER //
CREATE FUNCTION MaskPhone(phone VARCHAR(20))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE masked_phone VARCHAR(20);
    
    IF phone IS NULL OR LENGTH(phone) < 7 THEN
        RETURN phone;
    END IF;
    
    -- 中国手机号脱敏：138****1234
    IF LENGTH(phone) = 11 AND phone REGEXP '^1[3-9][0-9]{9}$' THEN
        SET masked_phone = CONCAT(
            LEFT(phone, 3),
            '****',
            RIGHT(phone, 4)
        );
    ELSE
        -- 其他号码脱敏：保留前3位和后2位
        SET masked_phone = CONCAT(
            LEFT(phone, 3),
            REPEAT('*', LENGTH(phone) - 5),
            RIGHT(phone, 2)
        );
    END IF;
    
    RETURN masked_phone;
END //
DELIMITER ;

-- 创建生成随机字符串函数
DELIMITER //
CREATE FUNCTION GenerateRandomString(length INT)
RETURNS VARCHAR(255)
READS SQL DATA
NOT DETERMINISTIC
BEGIN
    DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE result VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    DECLARE char_length INT DEFAULT 62;
    
    IF length <= 0 OR length > 255 THEN
        RETURN '';
    END IF;
    
    WHILE i < length DO
        SET result = CONCAT(result, SUBSTRING(chars, FLOOR(1 + RAND() * char_length), 1));
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

-- 使用字符串处理函数
SELECT 
    username,
    phone,
    MaskPhone(phone) as masked_phone,
    GenerateRandomString(8) as random_token
FROM users 
LIMIT 5;

3. 业务逻辑函数

-- 创建计算用户等级的函数
DELIMITER //
CREATE FUNCTION GetUserLevel(user_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total_orders INT DEFAULT 0;
    DECLARE total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE user_level VARCHAR(20) DEFAULT 'Bronze';
    
    -- 获取用户订单统计
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0)
    INTO total_orders, total_amount
    FROM orders 
    WHERE user_id = user_id AND status IN (2, 3); -- 已支付或已完成
    
    -- 根据订单数量和金额确定等级
    IF total_amount >= 50000 AND total_orders >= 50 THEN
        SET user_level = 'Diamond';
    ELSEIF total_amount >= 20000 AND total_orders >= 20 THEN
        SET user_level = 'Gold';
    ELSEIF total_amount >= 5000 AND total_orders >= 10 THEN
        SET user_level = 'Silver';
    ELSE
        SET user_level = 'Bronze';
    END IF;
    
    RETURN user_level;
END //
DELIMITER ;

-- 创建计算商品评分的函数
DELIMITER //
CREATE FUNCTION CalculateProductRating(product_id INT)
RETURNS DECIMAL(3,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE avg_rating DECIMAL(3,2) DEFAULT 0.00;
    DECLARE review_count INT DEFAULT 0;
    
    SELECT 
        COUNT(*),
        COALESCE(AVG(rating), 0)
    INTO review_count, avg_rating
    FROM product_reviews 
    WHERE product_id = product_id AND status = 1;
    
    -- 如果评论数少于5个，降低评分权重
    IF review_count < 5 THEN
        SET avg_rating = avg_rating * 0.8;
    END IF;
    
    -- 确保评分在1-5之间
    IF avg_rating < 1.00 THEN
        SET avg_rating = 1.00;
    ELSEIF avg_rating > 5.00 THEN
        SET avg_rating = 5.00;
    END IF;
    
    RETURN avg_rating;
END //
DELIMITER ;

-- 使用业务函数
SELECT 
    u.username,
    GetUserLevel(u.id) as user_level,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
LIMIT 10;

循环和条件控制

1. 循环结构

-- 使用WHILE循环批量插入测试数据
DELIMITER //
CREATE PROCEDURE GenerateTestData(IN record_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE random_name VARCHAR(50);
    DECLARE random_email VARCHAR(100);
    
    -- 清空测试表
    TRUNCATE TABLE test_users;
    
    WHILE i <= record_count DO
        SET random_name = CONCAT('User', i);
        SET random_email = CONCAT('user', i, '@test.com');
        
        INSERT INTO test_users (username, email, created_at)
        VALUES (random_name, random_email, NOW());
        
        SET i = i + 1;
    END WHILE;
    
    SELECT CONCAT('成功生成 ', record_count, ' 条测试数据') as result;
END //
DELIMITER ;

-- 使用REPEAT循环
DELIMITER //
CREATE PROCEDURE BatchUpdateUserLevel()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_user_id INT;
    DECLARE v_user_level VARCHAR(20);
    DECLARE user_cursor CURSOR FOR 
        SELECT id FROM users WHERE status = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN user_cursor;
    
    user_loop: REPEAT
        FETCH user_cursor INTO v_user_id;
        
        IF NOT done THEN
            SET v_user_level = GetUserLevel(v_user_id);
            
            UPDATE users 
            SET level = v_user_level,
                updated_at = NOW()
            WHERE id = v_user_id;
        END IF;
        
    UNTIL done END REPEAT;
    
    CLOSE user_cursor;
    
    SELECT '用户等级更新完成' as result;
END //
DELIMITER ;

2. 条件控制和异常处理

-- 复杂的条件控制存储过程
DELIMITER //
CREATE PROCEDURE ProcessRefund(
    IN p_order_id INT,
    IN p_refund_reason VARCHAR(255),
    OUT p_result_code INT,
    OUT p_result_message VARCHAR(255)
)
BEGIN
    DECLARE v_order_status INT DEFAULT 0;
    DECLARE v_user_id INT DEFAULT 0;
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_refund_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_days_diff INT DEFAULT 0;
    
    -- 异常处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_code = -1;
        SET p_result_message = '退款处理异常';
    END;
    
    -- 获取订单信息
    SELECT 
        user_id, 
        total_amount, 
        status,
        DATEDIFF(NOW(), created_at)
    INTO v_user_id, v_total_amount, v_order_status, v_days_diff
    FROM orders 
    WHERE id = p_order_id;
    
    -- 检查订单是否存在
    IF v_user_id IS NULL THEN
        SET p_result_code = 1001;
        SET p_result_message = '订单不存在';
    -- 检查订单状态
    ELSEIF v_order_status NOT IN (2, 3) THEN
        SET p_result_code = 1002;
        SET p_result_message = '订单状态不允许退款';
    -- 检查退款时限
    ELSEIF v_days_diff > 7 THEN
        SET p_result_code = 1003;
        SET p_result_message = '超过退款时限（7天）';
    ELSE
        -- 开始退款处理
        START TRANSACTION;
        
        -- 根据时间计算退款金额
        CASE 
            WHEN v_days_diff <= 1 THEN
                SET v_refund_amount = v_total_amount; -- 全额退款
            WHEN v_days_diff <= 3 THEN
                SET v_refund_amount = v_total_amount * 0.9; -- 90%退款
            WHEN v_days_diff <= 7 THEN
                SET v_refund_amount = v_total_amount * 0.8; -- 80%退款
            ELSE
                SET v_refund_amount = 0;
        END CASE;
        
        -- 更新订单状态
        UPDATE orders 
        SET status = 4, -- 已退款
            refund_amount = v_refund_amount,
            refund_reason = p_refund_reason,
            refunded_at = NOW()
        WHERE id = p_order_id;
        
        -- 退款到用户账户
        UPDATE user_accounts 
        SET balance = balance + v_refund_amount,
            updated_at = NOW()
        WHERE user_id = v_user_id;
        
        -- 记录退款交易
        INSERT INTO account_transactions (
            user_id, order_id, amount, type, description, created_at
        ) VALUES (
            v_user_id, p_order_id, v_refund_amount, 'refund', 
            CONCAT('订单退款：', p_refund_reason), NOW()
        );
        
        COMMIT;
        
        SET p_result_code = 0;
        SET p_result_message = CONCAT('退款成功，退款金额：', v_refund_amount);
    END IF;
    
END //
DELIMITER ;

-- 调用退款处理
CALL ProcessRefund(10001, '商品质量问题', @code, @msg);
SELECT @code, @msg;

游标使用

1. 基本游标操作

-- 使用游标处理批量数据
DELIMITER //
CREATE PROCEDURE UpdateProductRatings()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_rating DECIMAL(3,2);
    DECLARE v_count INT DEFAULT 0;
    
    -- 声明游标
    DECLARE product_cursor CURSOR FOR 
        SELECT id FROM products WHERE status = 1;
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN product_cursor;
    
    -- 循环处理
    product_loop: LOOP
        FETCH product_cursor INTO v_product_id;
        
        IF done THEN
            LEAVE product_loop;
        END IF;
        
        -- 计算商品评分
        SET v_rating = CalculateProductRating(v_product_id);
        
        -- 更新商品评分
        UPDATE products 
        SET rating = v_rating,
            updated_at = NOW()
        WHERE id = v_product_id;
        
        SET v_count = v_count + 1;
        
        -- 每处理100个商品输出一次进度
        IF v_count % 100 = 0 THEN
            SELECT CONCAT('已处理 ', v_count, ' 个商品') as progress;
        END IF;
        
    END LOOP;
    
    -- 关闭游标
    CLOSE product_cursor;
    
    SELECT CONCAT('评分更新完成，共处理 ', v_count, ' 个商品') as result;
END //
DELIMITER ;

2. 复杂游标应用

-- 数据迁移存储过程
DELIMITER //
CREATE PROCEDURE MigrateUserData()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_old_user_id INT;
    DECLARE v_username VARCHAR(50);
    DECLARE v_email VARCHAR(100);
    DECLARE v_created_at TIMESTAMP;
    DECLARE v_new_user_id INT;
    DECLARE v_success_count INT DEFAULT 0;
    DECLARE v_error_count INT DEFAULT 0;
    
    -- 声明游标
    DECLARE user_cursor CURSOR FOR 
        SELECT id, username, email, created_at 
        FROM old_users 
        WHERE migrated = 0
        ORDER BY id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET v_error_count = v_error_count + 1;
    END;
    
    OPEN user_cursor;
    
    migration_loop: LOOP
        FETCH user_cursor INTO v_old_user_id, v_username, v_email, v_created_at;
        
        IF done THEN
            LEAVE migration_loop;
        END IF;
        
        -- 检查新表中是否已存在
        SELECT id INTO v_new_user_id 
        FROM new_users 
        WHERE email = v_email 
        LIMIT 1;
        
        IF v_new_user_id IS NULL THEN
            -- 插入新用户
            INSERT INTO new_users (username, email, status, created_at)
            VALUES (v_username, v_email, 1, v_created_at);
            
            SET v_new_user_id = LAST_INSERT_ID();
            
            -- 迁移用户扩展信息
            INSERT INTO new_user_profiles (user_id, old_user_id, migrated_at)
            VALUES (v_new_user_id, v_old_user_id, NOW());
            
            -- 标记原数据已迁移
            UPDATE old_users 
            SET migrated = 1, 
                new_user_id = v_new_user_id,
                migrated_at = NOW()
            WHERE id = v_old_user_id;
            
            SET v_success_count = v_success_count + 1;
        END IF;
        
        -- 重置变量
        SET v_new_user_id = NULL;
        
    END LOOP;
    
    CLOSE user_cursor;
    
    SELECT 
        v_success_count as '成功迁移',
        v_error_count as '迁移失败',
        (v_success_count + v_error_count) as '总处理数';
        
END //
DELIMITER ;

性能优化技巧

1. 存储过程优化

-- 优化前：多次查询
DELIMITER //
CREATE PROCEDURE GetUserOrderSummary_Slow(IN user_id INT)
BEGIN
    DECLARE total_orders INT;
    DECLARE total_amount DECIMAL(10,2);
    DECLARE avg_amount DECIMAL(10,2);
    DECLARE last_order_date DATE;
    
    SELECT COUNT(*) INTO total_orders FROM orders WHERE user_id = user_id;
    SELECT SUM(total_amount) INTO total_amount FROM orders WHERE user_id = user_id;
    SELECT AVG(total_amount) INTO avg_amount FROM orders WHERE user_id = user_id;
    SELECT MAX(DATE(created_at)) INTO last_order_date FROM orders WHERE user_id = user_id;
    
    SELECT total_orders, total_amount, avg_amount, last_order_date;
END //
DELIMITER ;

-- 优化后：单次查询
DELIMITER //
CREATE PROCEDURE GetUserOrderSummary_Fast(IN user_id INT)
BEGIN
    SELECT 
        COUNT(*) as total_orders,
        COALESCE(SUM(total_amount), 0) as total_amount,
        COALESCE(AVG(total_amount), 0) as avg_amount,
        MAX(DATE(created_at)) as last_order_date
    FROM orders 
    WHERE user_id = user_id;
END //
DELIMITER ;

-- 性能测试对比
SET @start_time = NOW(6);
CALL GetUserOrderSummary_Slow(1001);
SET @slow_time = TIMESTAMPDIFF(MICROSECOND, @start_time, NOW(6));

SET @start_time = NOW(6);
CALL GetUserOrderSummary_Fast(1001);
SET @fast_time = TIMESTAMPDIFF(MICROSECOND, @start_time, NOW(6));

SELECT 
    @slow_time as '优化前耗时(微秒)',
    @fast_time as '优化后耗时(微秒)',
    ROUND(@slow_time / @fast_time, 2) as '性能提升倍数';

2. 批量处理优化

-- 批量更新优化
DELIMITER //
CREATE PROCEDURE BatchUpdateUserStatus()
BEGIN
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE total_updated INT DEFAULT 0;
    DECLARE current_batch INT DEFAULT 0;
    
    -- 使用临时表提高性能
    CREATE TEMPORARY TABLE temp_inactive_users AS
    SELECT id 
    FROM users 
    WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
        AND status = 1;
    
    -- 添加索引
    ALTER TABLE temp_inactive_users ADD INDEX idx_id (id);
    
    -- 批量更新
    REPEAT
        UPDATE users u
        INNER JOIN (
            SELECT id 
            FROM temp_inactive_users 
            LIMIT batch_size OFFSET (current_batch * batch_size)
        ) t ON u.id = t.id
        SET u.status = 0, u.updated_at = NOW();
        
        SET current_batch = current_batch + 1;
        SET total_updated = total_updated + ROW_COUNT();
        
    UNTIL ROW_COUNT() = 0 END REPEAT;
    
    DROP TEMPORARY TABLE temp_inactive_users;
    
    SELECT CONCAT('批量更新完成，共更新 ', total_updated, ' 个用户') as result;
END //
DELIMITER ;

调试和监控

1. 调试技巧

-- 带调试信息的存储过程
DELIMITER //
CREATE PROCEDURE DebugExample(IN debug_mode BOOLEAN)
BEGIN
    DECLARE v_step VARCHAR(50);
    DECLARE v_count INT;
    
    SET v_step = 'Step 1: 检查用户数量';
    IF debug_mode THEN
        SELECT v_step as debug_info, NOW() as timestamp;
    END IF;
    
    SELECT COUNT(*) INTO v_count FROM users;
    
    IF debug_mode THEN
        SELECT CONCAT('用户总数：', v_count) as debug_info;
    END IF;
    
    SET v_step = 'Step 2: 处理业务逻辑';
    IF debug_mode THEN
        SELECT v_step as debug_info, NOW() as timestamp;
    END IF;
    
    -- 业务逻辑处理...
    
    IF debug_mode THEN
        SELECT '处理完成' as debug_info, NOW() as timestamp;
    END IF;
    
END //
DELIMITER ;

-- 调用调试模式
CALL DebugExample(TRUE);

2. 性能监控

-- 存储过程性能监控
DELIMITER //
CREATE PROCEDURE MonitorProcedurePerformance()
BEGIN
    SELECT 
        ROUTINE_SCHEMA as '数据库',
        ROUTINE_NAME as '存储过程名',
        ROUTINE_TYPE as '类型',
        CREATED as '创建时间',
        LAST_ALTERED as '最后修改时间'
    FROM information_schema.ROUTINES 
    WHERE ROUTINE_SCHEMA = DATABASE()
    ORDER BY LAST_ALTERED DESC;
    
    -- 查看存储过程执行统计（MySQL 5.7+）
    SELECT 
        OBJECT_SCHEMA as '数据库',
        OBJECT_NAME as '存储过程名',
        COUNT_EXECUTE as '执行次数',
        SUM_TIMER_EXECUTE/1000000000 as '总执行时间(秒)',
        AVG_TIMER_EXECUTE/1000000000 as '平均执行时间(秒)',
        MAX_TIMER_EXECUTE/1000000000 as '最大执行时间(秒)'
    FROM performance_schema.events_statements_summary_by_program 
    WHERE OBJECT_TYPE = 'PROCEDURE'
        AND OBJECT_SCHEMA = DATABASE()
    ORDER BY AVG_TIMER_EXECUTE DESC;
END //
DELIMITER ;

CALL MonitorProcedurePerformance();

最佳实践总结

1. 设计原则

• 单一职责：每个存储过程只处理一个业务逻辑
• 参数验证：对输入参数进行有效性检查
• 异常处理：使用HANDLER处理异常情况
• 事务控制：合理使用事务确保数据一致性
• 性能优化：避免在循环中执行SQL查询

2. 命名规范

• 存储过程：使用动词开头，如CreateUser、UpdateOrder
• 函数：使用动词或形容词，如CalculateAge、IsValidEmail
• 参数：输入参数用p_前缀，输出参数用out_前缀
• 变量：局部变量用v_前缀

3. 安全考虑

• 权限控制：严格控制存储过程的执行权限
• SQL注入防护：使用参数化查询，避免动态SQL
• 敏感数据处理：对敏感数据进行加密或脱敏
• 审计日志：记录重要操作的执行日志

4. 维护建议

• 版本控制：将存储过程纳入版本控制系统
• 文档完善：为每个存储过程编写详细文档
• 定期优化：定期检查和优化存储过程性能
• 测试覆盖：编写完整的单元测试和集成测试

5. 常见问题解决

-- 问题1：存储过程中的字符集问题
-- 解决方案：明确指定字符集
DELIMITER //
CREATE PROCEDURE HandleCharset()
BEGIN
    -- 设置字符集
    SET NAMES utf8mb4;
    
    -- 处理中文数据
    SELECT '中文测试' as test_text;
END //
DELIMITER ;

-- 问题2：存储过程调试困难
-- 解决方案：添加日志表
CREATE TABLE procedure_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    procedure_name VARCHAR(100),
    log_level ENUM('INFO', 'WARN', 'ERROR'),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE PROCEDURE LogMessage(
    IN proc_name VARCHAR(100),
    IN level ENUM('INFO', 'WARN', 'ERROR'),
    IN message TEXT
)
BEGIN
    INSERT INTO procedure_logs (procedure_name, log_level, message)
    VALUES (proc_name, level, message);
END //
DELIMITER ;

-- 问题3：存储过程性能问题
-- 解决方案：使用EXPLAIN分析执行计划
DELIMITER //
CREATE PROCEDURE AnalyzeQuery(IN query_text TEXT)
BEGIN
    SET @sql = CONCAT('EXPLAIN ', query_text);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

实际应用案例

1. 电商系统订单处理

-- 完整的订单处理流程
DELIMITER //
CREATE PROCEDURE CompleteOrderProcess(
    IN p_user_id INT,
    IN p_products JSON,  -- [{"product_id": 1, "quantity": 2}, ...]
    IN p_coupon_code VARCHAR(50),
    OUT p_order_id INT,
    OUT p_result_code INT,
    OUT p_result_message VARCHAR(255)
)
BEGIN
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_discount_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_final_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE v_product_count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_code = -1;
        SET p_result_message = '订单处理失败';
    END;
    
    START TRANSACTION;
    
    -- 1. 验证用户状态
    IF NOT EXISTS(SELECT 1 FROM users WHERE id = p_user_id AND status = 1) THEN
        SET p_result_code = 1001;
        SET p_result_message = '用户不存在或已禁用';
        ROLLBACK;
    ELSE
        -- 2. 解析商品信息并计算总金额
        SET v_product_count = JSON_LENGTH(p_products);
        
        WHILE i < v_product_count DO
            SET @product_id = JSON_UNQUOTE(JSON_EXTRACT(p_products, CONCAT('$[', i, '].product_id')));
            SET @quantity = JSON_UNQUOTE(JSON_EXTRACT(p_products, CONCAT('$[', i, '].quantity')));
            
            -- 检查库存和价格
            SELECT price INTO @price FROM products 
            WHERE id = @product_id AND status = 1 AND stock_quantity >= @quantity;
            
            IF @price IS NULL THEN
                SET p_result_code = 1002;
                SET p_result_message = CONCAT('商品ID ', @product_id, ' 库存不足或不存在');
                ROLLBACK;
                LEAVE;
            END IF;
            
            SET v_total_amount = v_total_amount + (@price * @quantity);
            SET i = i + 1;
        END WHILE;
        
        -- 3. 处理优惠券
        IF p_coupon_code IS NOT NULL AND p_coupon_code != '' THEN
            SELECT discount_amount INTO v_discount_amount
            FROM coupons 
            WHERE code = p_coupon_code 
                AND status = 1 
                AND start_date <= NOW() 
                AND end_date >= NOW()
                AND min_amount <= v_total_amount;
                
            IF v_discount_amount IS NULL THEN
                SET v_discount_amount = 0;
            END IF;
        END IF;
        
        SET v_final_amount = v_total_amount - v_discount_amount;
        
        -- 4. 创建订单
        INSERT INTO orders (
            user_id, total_amount, discount_amount, final_amount, 
            coupon_code, status, created_at
        ) VALUES (
            p_user_id, v_total_amount, v_discount_amount, v_final_amount,
            p_coupon_code, 1, NOW()
        );
        
        SET p_order_id = LAST_INSERT_ID();
        
        -- 5. 创建订单详情并更新库存
        SET i = 0;
        WHILE i < v_product_count DO
            SET @product_id = JSON_UNQUOTE(JSON_EXTRACT(p_products, CONCAT('$[', i, '].product_id')));
            SET @quantity = JSON_UNQUOTE(JSON_EXTRACT(p_products, CONCAT('$[', i, '].quantity')));
            
            SELECT price INTO @price FROM products WHERE id = @product_id;
            
            INSERT INTO order_items (
                order_id, product_id, quantity, unit_price, total_price
            ) VALUES (
                p_order_id, @product_id, @quantity, @price, @price * @quantity
            );
            
            UPDATE products 
            SET stock_quantity = stock_quantity - @quantity
            WHERE id = @product_id;
            
            SET i = i + 1;
        END WHILE;
        
        -- 6. 使用优惠券
        IF v_discount_amount > 0 THEN
            UPDATE coupons 
            SET used_count = used_count + 1
            WHERE code = p_coupon_code;
        END IF;
        
        COMMIT;
        
        SET p_result_code = 0;
        SET p_result_message = '订单创建成功';
    END IF;
    
END //
DELIMITER ;

2. 数据统计报表

-- 销售数据统计存储过程
DELIMITER //
CREATE PROCEDURE GenerateSalesReport(
    IN p_start_date DATE,
    IN p_end_date DATE,
    IN p_group_by ENUM('day', 'week', 'month')
)
BEGIN
    DECLARE v_date_format VARCHAR(20);
    DECLARE v_sql TEXT;
    
    -- 根据分组类型设置日期格式
    CASE p_group_by
        WHEN 'day' THEN SET v_date_format = '%Y-%m-%d';
        WHEN 'week' THEN SET v_date_format = '%Y-%u';
        WHEN 'month' THEN SET v_date_format = '%Y-%m';
    END CASE;
    
    -- 动态构建SQL
    SET v_sql = CONCAT(
        'SELECT ',
        'DATE_FORMAT(created_at, ''', v_date_format, ''') as period, ',
        'COUNT(*) as order_count, ',
        'SUM(final_amount) as total_amount, ',
        'AVG(final_amount) as avg_amount, ',
        'COUNT(DISTINCT user_id) as unique_customers ',
        'FROM orders ',
        'WHERE created_at BETWEEN ''', p_start_date, ''' AND ''', p_end_date, ''' ',
        'AND status IN (2, 3) ',
        'GROUP BY DATE_FORMAT(created_at, ''', v_date_format, ''') ',
        'ORDER BY period'
    );
    
    -- 执行动态SQL
    SET @sql = v_sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
END //
DELIMITER ;

-- 调用报表生成
CALL GenerateSalesReport('2023-08-01', '2023-08-31', 'day');

通过以上实战案例和最佳实践，我们可以看到MySQL存储过程和函数在实际项目中的强大应用。合理使用存储过程不仅能提高数据库性能，还能更好地封装业务逻辑，提高代码的可维护性和安全性。

记住，存储过程虽然功能强大，但也要适度使用。过度依赖存储过程可能会导致业务逻辑过于集中在数据库层，影响系统的可扩展性和可移植性。在实际开发中，需要根据具体业务需求和团队技术栈来合理选择使用存储过程的场景。