Jeskson-微服务分布式

存储过程与函数

# 存储过程与函数

深入理解存储过程和函数的设计、开发和优化，提升数据库编程能力

# 概述

存储过程（Stored Procedure）和函数（Function）是数据库中预编译的SQL语句集合，可以接受参数、执行复杂的业务逻辑，并返回结果。它们是数据库编程的重要组成部分。

# 存储过程 vs 函数

特性 存储过程 函数
返回值 可选，通过OUT参数 必须返回值
调用方式 CALL语句 可在SQL表达式中使用
事务控制 可以控制事务 不能控制事务
副作用 可以有副作用 应该无副作用
DML操作 可以执行 通常不建议

# MySQL存储过程

# 1. 基本语法

# 创建存储过程

DELIMITER //

CREATE PROCEDURE procedure_name(
    [IN | OUT | INOUT] parameter_name datatype,
    ...
)
BEGIN
    -- 存储过程体
    SQL statements;
END //

DELIMITER ;

# 简单示例

DELIMITER //

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

DELIMITER ;

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

# 2. 参数类型

# IN参数（输入参数）

DELIMITER //

CREATE PROCEDURE GetUsersByCity(
    IN city_name VARCHAR(50)
)
BEGIN
    SELECT user_id, name, email 
    FROM users 
    WHERE city = city_name;
END //

DELIMITER ;

-- 调用
CALL GetUsersByCity('Beijing');

# OUT参数（输出参数）

DELIMITER //

CREATE PROCEDURE GetOrderStats(
    IN user_id INT,
    OUT total_orders INT,
    OUT total_amount DECIMAL(10,2)
)
BEGIN
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0)
    INTO total_orders, total_amount
    FROM orders 
    WHERE orders.user_id = user_id;
END //

DELIMITER ;

-- 调用
CALL GetOrderStats(123, @order_count, @order_total);
SELECT @order_count, @order_total;

# INOUT参数（输入输出参数）

DELIMITER //

CREATE PROCEDURE UpdateCounter(
    INOUT counter INT
)
BEGIN
    SET counter = counter + 1;
END //

DELIMITER ;

-- 调用
SET @my_counter = 10;
CALL UpdateCounter(@my_counter);
SELECT @my_counter;  -- 结果：11

# 3. 控制结构

# 条件语句

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN order_id INT,
    OUT status_message VARCHAR(100)
)
BEGIN
    DECLARE order_status VARCHAR(20);
    DECLARE order_amount DECIMAL(10,2);
    
    -- 获取订单信息
    SELECT status, total_amount 
    INTO order_status, order_amount
    FROM orders 
    WHERE id = order_id;
    
    -- 条件判断
    IF order_status = 'pending' THEN
        IF order_amount > 1000 THEN
            UPDATE orders SET status = 'approved' WHERE id = order_id;
            SET status_message = 'Order approved - high value';
        ELSE
            UPDATE orders SET status = 'processing' WHERE id = order_id;
            SET status_message = 'Order processing - normal value';
        END IF;
    ELSEIF order_status = 'processing' THEN
        UPDATE orders SET status = 'shipped' WHERE id = order_id;
        SET status_message = 'Order shipped';
    ELSE
        SET status_message = CONCAT('Cannot process order with status: ', order_status);
    END IF;
END //

DELIMITER ;

# 循环语句

DELIMITER //

CREATE PROCEDURE GenerateSequence(
    IN max_num INT
)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    -- 创建临时表
    DROP TEMPORARY TABLE IF EXISTS temp_sequence;
    CREATE TEMPORARY TABLE temp_sequence (
        num INT,
        square INT,
        cube INT
    );
    
    -- WHILE循环
    WHILE counter <= max_num DO
        INSERT INTO temp_sequence VALUES (
            counter, 
            counter * counter, 
            counter * counter * counter
        );
        SET counter = counter + 1;
    END WHILE;
    
    -- 返回结果
    SELECT * FROM temp_sequence;
END //

DELIMITER ;

-- 调用
CALL GenerateSequence(5);

# 游标处理

DELIMITER //

CREATE PROCEDURE ProcessAllUsers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id INT;
    DECLARE user_name VARCHAR(100);
    DECLARE user_email VARCHAR(100);
    DECLARE total_processed INT DEFAULT 0;
    
    -- 声明游标
    DECLARE user_cursor CURSOR FOR 
        SELECT id, name, email FROM users WHERE status = 'active';
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN user_cursor;
    
    -- 循环处理
    read_loop: LOOP
        FETCH user_cursor INTO user_id, user_name, user_email;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理每个用户
        INSERT INTO user_log (user_id, action, created_at) 
        VALUES (user_id, 'processed', NOW());
        
        SET total_processed = total_processed + 1;
    END LOOP;
    
    -- 关闭游标
    CLOSE user_cursor;
    
    -- 返回处理结果
    SELECT CONCAT('Processed ', total_processed, ' users') AS result;
END //

DELIMITER ;

# 4. 异常处理

DELIMITER //

CREATE PROCEDURE SafeTransferMoney(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT result_message VARCHAR(200)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result_message = 'Transaction failed due to an error';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 检查余额
    SELECT balance INTO from_balance 
    FROM accounts 
    WHERE account_id = from_account;
    
    IF from_balance < amount THEN
        SET result_message = 'Insufficient balance';
        ROLLBACK;
    ELSE
        -- 执行转账
        UPDATE accounts 
        SET balance = balance - amount 
        WHERE account_id = from_account;
        
        UPDATE accounts 
        SET balance = balance + amount 
        WHERE account_id = to_account;
        
        -- 记录交易
        INSERT INTO transactions (from_account, to_account, amount, created_at)
        VALUES (from_account, to_account, amount, NOW());
        
        COMMIT;
        SET result_message = 'Transfer completed successfully';
    END IF;
END //

DELIMITER ;

# MySQL函数

# 1. 创建函数

DELIMITER //

CREATE FUNCTION function_name(
    parameter1 datatype,
    parameter2 datatype
) RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL | CONTAINS SQL]
BEGIN
    -- 函数体
    RETURN value;
END //

DELIMITER ;

# 2. 函数示例

# 简单计算函数

DELIMITER //

CREATE FUNCTION CalculateDiscount(
    original_price DECIMAL(10,2),
    discount_rate DECIMAL(5,2)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    SET discounted_price = original_price * (1 - discount_rate / 100);
    RETURN discounted_price;
END //

DELIMITER ;

-- 使用函数
SELECT 
    product_name,
    price,
    CalculateDiscount(price, 10) as discounted_price
FROM products;

# 字符串处理函数

DELIMITER //

CREATE FUNCTION FormatPhoneNumber(
    phone VARCHAR(20)
) RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE formatted_phone VARCHAR(20);
    
    -- 移除所有非数字字符
    SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');
    
    -- 格式化为 (XXX) XXX-XXXX
    IF LENGTH(phone) = 10 THEN
        SET formatted_phone = CONCAT(
            '(', SUBSTRING(phone, 1, 3), ') ',
            SUBSTRING(phone, 4, 3), '-',
            SUBSTRING(phone, 7, 4)
        );
    ELSE
        SET formatted_phone = phone;
    END IF;
    
    RETURN formatted_phone;
END //

DELIMITER ;

-- 使用函数
SELECT 
    name,
    phone,
    FormatPhoneNumber(phone) as formatted_phone
FROM users;

# 业务逻辑函数

DELIMITER //

CREATE FUNCTION GetCustomerLevel(
    customer_id INT
) RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total_spent DECIMAL(10,2);
    DECLARE customer_level VARCHAR(20);
    
    -- 计算客户总消费
    SELECT COALESCE(SUM(total_amount), 0)
    INTO total_spent
    FROM orders
    WHERE user_id = customer_id
      AND status = 'completed'
      AND order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR);
    
    -- 根据消费金额确定等级
    IF total_spent >= 10000 THEN
        SET customer_level = 'Platinum';
    ELSEIF total_spent >= 5000 THEN
        SET customer_level = 'Gold';
    ELSEIF total_spent >= 1000 THEN
        SET customer_level = 'Silver';
    ELSE
        SET customer_level = 'Bronze';
    END IF;
    
    RETURN customer_level;
END //

DELIMITER ;

-- 使用函数
SELECT 
    id,
    name,
    email,
    GetCustomerLevel(id) as customer_level
FROM users
ORDER BY customer_level, name;

# PostgreSQL存储过程和函数

# 1. 基本语法

-- PostgreSQL函数语法
CREATE OR REPLACE FUNCTION function_name(
    parameter1 datatype,
    parameter2 datatype
)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 函数体
    RETURN value;
END;
$$;

-- PostgreSQL存储过程语法（PostgreSQL 11+）
CREATE OR REPLACE PROCEDURE procedure_name(
    parameter1 datatype,
    parameter2 datatype
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 存储过程体
END;
$$;

# 2. PostgreSQL函数示例

# 简单函数

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    user_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
    RETURN user_count;
END;
$$;

-- 调用函数
SELECT get_user_count();

# 带参数的函数

CREATE OR REPLACE FUNCTION get_orders_by_status(
    order_status VARCHAR(20)
)
RETURNS TABLE(
    order_id INTEGER,
    user_id INTEGER,
    total_amount DECIMAL(10,2),
    order_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.id,
        o.user_id,
        o.total_amount,
        o.order_date
    FROM orders o
    WHERE o.status = order_status
    ORDER BY o.order_date DESC;
END;
$$;

-- 调用函数
SELECT * FROM get_orders_by_status('completed');

# 复杂业务逻辑函数

CREATE OR REPLACE FUNCTION process_monthly_report(
    report_month DATE
)
RETURNS JSON
LANGUAGE plpgsql
AS $$
DECLARE
    total_orders INTEGER;
    total_revenue DECIMAL(10,2);
    avg_order_value DECIMAL(10,2);
    top_customer_id INTEGER;
    top_customer_name VARCHAR(100);
    result JSON;
BEGIN
    -- 计算总订单数
    SELECT COUNT(*) INTO total_orders
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', report_month)
      AND status = 'completed';
    
    -- 计算总收入
    SELECT COALESCE(SUM(total_amount), 0) INTO total_revenue
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', report_month)
      AND status = 'completed';
    
    -- 计算平均订单价值
    IF total_orders > 0 THEN
        avg_order_value := total_revenue / total_orders;
    ELSE
        avg_order_value := 0;
    END IF;
    
    -- 找出消费最多的客户
    SELECT 
        o.user_id,
        u.name
    INTO top_customer_id, top_customer_name
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', report_month)
      AND o.status = 'completed'
    GROUP BY o.user_id, u.name
    ORDER BY SUM(o.total_amount) DESC
    LIMIT 1;
    
    -- 构建JSON结果
    result := json_build_object(
        'month', report_month,
        'total_orders', total_orders,
        'total_revenue', total_revenue,
        'avg_order_value', avg_order_value,
        'top_customer', json_build_object(
            'id', top_customer_id,
            'name', top_customer_name
        )
    );
    
    RETURN result;
END;
$$;

-- 调用函数
SELECT process_monthly_report('2024-01-01'::DATE);

# 3. PostgreSQL存储过程

CREATE OR REPLACE PROCEDURE update_user_statistics()
LANGUAGE plpgsql
AS $$
DECLARE
    user_record RECORD;
    order_count INTEGER;
    total_spent DECIMAL(10,2);
BEGIN
    -- 遍历所有用户
    FOR user_record IN SELECT id FROM users LOOP
        -- 计算用户订单统计
        SELECT 
            COUNT(*),
            COALESCE(SUM(total_amount), 0)
        INTO order_count, total_spent
        FROM orders
        WHERE user_id = user_record.id
          AND status = 'completed';
        
        -- 更新用户统计信息
        UPDATE users
        SET 
            order_count = order_count,
            total_spent = total_spent,
            last_updated = NOW()
        WHERE id = user_record.id;
    END LOOP;
    
    -- 记录更新日志
    INSERT INTO system_logs (action, message, created_at)
    VALUES ('update_statistics', 'User statistics updated', NOW());
    
    COMMIT;
END;
$$;

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

# 性能优化

# 1. 存储过程优化

# 避免游标，使用集合操作

-- 不好的写法：使用游标
DELIMITER //
CREATE PROCEDURE UpdatePricesBad()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE product_id INT;
    DECLARE current_price DECIMAL(10,2);
    
    DECLARE price_cursor CURSOR FOR 
        SELECT id, price FROM products;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN price_cursor;
    
    read_loop: LOOP
        FETCH price_cursor INTO product_id, current_price;
        IF done THEN LEAVE read_loop; END IF;
        
        UPDATE products 
        SET price = current_price * 1.1 
        WHERE id = product_id;
    END LOOP;
    
    CLOSE price_cursor;
END //
DELIMITER ;

-- 好的写法：使用集合操作
DELIMITER //
CREATE PROCEDURE UpdatePricesGood()
BEGIN
    UPDATE products 
    SET price = price * 1.1;
END //
DELIMITER ;

# 使用批量操作

DELIMITER //
CREATE PROCEDURE BatchInsertOrders(
    IN batch_size INT
)
BEGIN
    DECLARE counter INT DEFAULT 0;
    
    WHILE counter < batch_size DO
        INSERT INTO orders (user_id, product_id, quantity, total_amount, order_date)
        VALUES 
            (FLOOR(RAND() * 1000) + 1, FLOOR(RAND() * 100) + 1, FLOOR(RAND() * 5) + 1, RAND() * 1000, NOW()),
            (FLOOR(RAND() * 1000) + 1, FLOOR(RAND() * 100) + 1, FLOOR(RAND() * 5) + 1, RAND() * 1000, NOW()),
            (FLOOR(RAND() * 1000) + 1, FLOOR(RAND() * 100) + 1, FLOOR(RAND() * 5) + 1, RAND() * 1000, NOW()),
            (FLOOR(RAND() * 1000) + 1, FLOOR(RAND() * 100) + 1, FLOOR(RAND() * 5) + 1, RAND() * 1000, NOW()),
            (FLOOR(RAND() * 1000) + 1, FLOOR(RAND() * 100) + 1, FLOOR(RAND() * 5) + 1, RAND() * 1000, NOW());
        
        SET counter = counter + 5;
    END WHILE;
END //
DELIMITER ;

# 2. 函数优化

# 标记函数特性

-- 确定性函数
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- 相同输入总是产生相同输出
NO SQL        -- 不访问数据库
BEGIN
    RETURN amount * 0.08;
END;

-- 只读函数
CREATE FUNCTION GetUserName(user_id INT)
RETURNS VARCHAR(100)
READS SQL DATA  -- 只读取数据，不修改
BEGIN
    DECLARE user_name VARCHAR(100);
    SELECT name INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END;

# 缓存计算结果

DELIMITER //
CREATE FUNCTION GetExpensiveCalculation(
    input_value INT
)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
    DECLARE result DECIMAL(10,2);
    DECLARE cache_exists INT DEFAULT 0;
    
    -- 检查缓存
    SELECT COUNT(*) INTO cache_exists
    FROM calculation_cache
    WHERE input_val = input_value
      AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR);
    
    IF cache_exists > 0 THEN
        -- 从缓存获取结果
        SELECT result_val INTO result
        FROM calculation_cache
        WHERE input_val = input_value
          AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)
        LIMIT 1;
    ELSE
        -- 执行复杂计算
        SET result = input_value * input_value * 3.14159;
        
        -- 存储到缓存
        INSERT INTO calculation_cache (input_val, result_val, created_at)
        VALUES (input_value, result, NOW())
        ON DUPLICATE KEY UPDATE 
            result_val = result,
            created_at = NOW();
    END IF;
    
    RETURN result;
END //
DELIMITER ;

# 调试和测试

# 1. 调试技巧

# 使用临时表记录调试信息

DELIMITER //
CREATE PROCEDURE DebugExample(
    IN user_id INT
)
BEGIN
    DECLARE debug_mode BOOLEAN DEFAULT TRUE;
    
    -- 创建调试表
    IF debug_mode THEN
        DROP TEMPORARY TABLE IF EXISTS debug_log;
        CREATE TEMPORARY TABLE debug_log (
            step_num INT,
            step_name VARCHAR(100),
            step_value TEXT,
            step_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    END IF;
    
    -- 步骤1：验证用户
    IF debug_mode THEN
        INSERT INTO debug_log (step_num, step_name, step_value)
        VALUES (1, 'Input validation', CONCAT('user_id: ', user_id));
    END IF;
    
    -- 业务逻辑...
    
    -- 步骤2：处理订单
    IF debug_mode THEN
        INSERT INTO debug_log (step_num, step_name, step_value)
        VALUES (2, 'Order processing', 'Orders processed successfully');
    END IF;
    
    -- 返回调试信息
    IF debug_mode THEN
        SELECT * FROM debug_log ORDER BY step_num;
    END IF;
END //
DELIMITER ;

# 2. 单元测试

-- 创建测试框架
CREATE TABLE test_results (
    test_id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(100),
    expected_result TEXT,
    actual_result TEXT,
    test_status ENUM('PASS', 'FAIL'),
    test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE PROCEDURE RunTests()
BEGIN
    DECLARE test_name VARCHAR(100);
    DECLARE expected_val DECIMAL(10,2);
    DECLARE actual_val DECIMAL(10,2);
    DECLARE test_status VARCHAR(10);
    
    -- 清空测试结果
    DELETE FROM test_results;
    
    -- 测试1：CalculateDiscount函数
    SET test_name = 'CalculateDiscount_10_percent';
    SET expected_val = 90.00;
    SET actual_val = CalculateDiscount(100.00, 10.00);
    
    IF ABS(expected_val - actual_val) < 0.01 THEN
        SET test_status = 'PASS';
    ELSE
        SET test_status = 'FAIL';
    END IF;
    
    INSERT INTO test_results (test_name, expected_result, actual_result, test_status)
    VALUES (test_name, expected_val, actual_val, test_status);
    
    -- 测试2：GetCustomerLevel函数
    SET test_name = 'GetCustomerLevel_Gold';
    -- 这里需要准备测试数据
    
    -- 显示测试结果
    SELECT 
        test_name,
        test_status,
        CASE 
            WHEN test_status = 'PASS' THEN '✓'
            ELSE '✗'
        END as result_icon
    FROM test_results;
    
    -- 统计
    SELECT 
        COUNT(*) as total_tests,
        SUM(CASE WHEN test_status = 'PASS' THEN 1 ELSE 0 END) as passed,
        SUM(CASE WHEN test_status = 'FAIL' THEN 1 ELSE 0 END) as failed
    FROM test_results;
END //
DELIMITER ;

-- 运行测试
CALL RunTests();

# 管理和维护

# 1. 查看存储过程和函数

-- MySQL：查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- 查看所有函数
SHOW FUNCTION STATUS WHERE Db = 'your_database';

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

-- 查看函数定义
SHOW CREATE FUNCTION function_name;

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

-- PostgreSQL：查看函数
SELECT 
    proname as function_name,
    pg_get_function_result(oid) as return_type,
    pg_get_function_arguments(oid) as arguments
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

# 2. 删除存储过程和函数

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

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

-- PostgreSQL：删除函数（需要指定参数类型）
DROP FUNCTION IF EXISTS function_name(parameter_types);

# 3. 权限管理

-- 授予执行权限
GRANT EXECUTE ON PROCEDURE procedure_name TO 'username'@'host';
GRANT EXECUTE ON FUNCTION function_name TO 'username'@'host';

-- 撤销执行权限
REVOKE EXECUTE ON PROCEDURE procedure_name FROM 'username'@'host';
REVOKE EXECUTE ON FUNCTION function_name FROM 'username'@'host';

-- PostgreSQL权限管理
GRANT EXECUTE ON FUNCTION function_name TO username;
REVOKE EXECUTE ON FUNCTION function_name FROM username;

# 最佳实践

# 1. 设计原则

  1. 单一职责：每个存储过程/函数只做一件事
  2. 参数验证：始终验证输入参数
  3. 错误处理：实现完善的错误处理机制
  4. 事务管理：合理使用事务，确保数据一致性
  5. 性能考虑：避免不必要的循环和游标

# 2. 命名规范

-- 存储过程命名：动词开头
CREATE PROCEDURE sp_CreateUser(...)
CREATE PROCEDURE sp_UpdateOrderStatus(...)
CREATE PROCEDURE sp_DeleteExpiredSessions(...)

-- 函数命名：描述返回值
CREATE FUNCTION fn_GetUserLevel(...)
CREATE FUNCTION fn_CalculateDiscount(...)
CREATE FUNCTION fn_FormatPhoneNumber(...)

-- 参数命名：使用前缀
-- p_ 表示参数
-- v_ 表示变量
-- c_ 表示游标
CREATE PROCEDURE sp_ProcessOrder(
    IN p_order_id INT,
    OUT p_status_message VARCHAR(100)
)
BEGIN
    DECLARE v_order_status VARCHAR(20);
    DECLARE v_total_amount DECIMAL(10,2);
    -- ...
END;

# 3. 文档和注释

DELIMITER //

/*
 * 存储过程：处理订单状态更新
 * 作者：开发团队
 * 创建日期：2024-01-01
 * 最后修改：2024-01-15
 * 
 * 功能描述：
 * 根据订单ID和新状态更新订单，并记录状态变更历史
 * 
 * 参数说明：
 * @p_order_id: 订单ID（必填）
 * @p_new_status: 新状态（必填，可选值：pending, processing, shipped, delivered, cancelled）
 * @p_operator_id: 操作员ID（必填）
 * @p_result_code: 返回结果代码（0=成功，1=订单不存在，2=状态无效，3=其他错误）
 * @p_result_message: 返回结果消息
 * 
 * 使用示例：
 * CALL sp_UpdateOrderStatus(12345, 'shipped', 100, @code, @message);
 * SELECT @code, @message;
 */
CREATE PROCEDURE sp_UpdateOrderStatus(
    IN p_order_id INT,
    IN p_new_status VARCHAR(20),
    IN p_operator_id INT,
    OUT p_result_code INT,
    OUT p_result_message VARCHAR(200)
)
BEGIN
    -- 声明变量
    DECLARE v_current_status VARCHAR(20);
    DECLARE v_order_exists INT DEFAULT 0;
    
    -- 异常处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result_code = 3;
        SET p_result_message = 'Database error occurred';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 验证订单是否存在
    SELECT COUNT(*), status 
    INTO v_order_exists, v_current_status
    FROM orders 
    WHERE id = p_order_id;
    
    IF v_order_exists = 0 THEN
        SET p_result_code = 1;
        SET p_result_message = 'Order not found';
        ROLLBACK;
    ELSEIF p_new_status NOT IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled') THEN
        SET p_result_code = 2;
        SET p_result_message = 'Invalid status';
        ROLLBACK;
    ELSE
        -- 更新订单状态
        UPDATE orders 
        SET 
            status = p_new_status,
            updated_at = NOW(),
            updated_by = p_operator_id
        WHERE id = p_order_id;
        
        -- 记录状态变更历史
        INSERT INTO order_status_history (
            order_id,
            old_status,
            new_status,
            operator_id,
            created_at
        ) VALUES (
            p_order_id,
            v_current_status,
            p_new_status,
            p_operator_id,
            NOW()
        );
        
        -- 提交事务
        COMMIT;
        
        SET p_result_code = 0;
        SET p_result_message = 'Order status updated successfully';
    END IF;
END //

DELIMITER ;

# 4. 版本控制

-- 在存储过程中包含版本信息
DELIMITER //
CREATE PROCEDURE sp_GetVersion()
BEGIN
    SELECT 
        'sp_UpdateOrderStatus' as procedure_name,
        '1.2.0' as version,
        '2024-01-15' as last_modified,
        'Added operator tracking' as changes;
END //
DELIMITER ;

-- 使用版本化的部署脚本
-- deploy_v1.2.0.sql
-- 检查当前版本
-- 如果版本不匹配，则更新存储过程
-- 记录部署日志

通过合理使用存储过程和函数，可以将复杂的业务逻辑封装在数据库层面，提高代码复用性和执行效率，同时确保数据的一致性和安全性。

聚合函数