如何实现存储过程异常自动写入文件？

为什么要记录异常到文件？

1. 问题追溯：文件日志提供完整的错误上下文，包括错误时间、代码位置、报错信息等
2. 系统监控：通过分析日志文件，可发现高频错误或潜在性能瓶颈
3. 合规要求：部分行业规范强制要求保留操作日志
4. 异步处理：避免因异常中断影响主业务流程

主流数据库实现方案

SQL Server 实现

CREATE PROCEDURE SampleProcedure
AS
BEGIN
    BEGIN TRY
        -- 业务逻辑代码
        SELECT 1/0 -- 模拟错误
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(4000) = 
            ERROR_MESSAGE() + ' | 错误行号: ' + CAST(ERROR_LINE() AS NVARCHAR)
        -- 写入错误日志表
        INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
        VALUES (@ErrorMsg, GETDATE())
        -- 写入文件
        EXEC xp_logevent 60000, @ErrorMsg, error
    END CATCH
END

补充配置：

• 启用xp_logevent需要sysadmin权限
• 日志默认路径：SQL Server 错误日志目录

MySQL 实现

DELIMITER $$
CREATE PROCEDURE SampleProcedure()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 获取异常信息
        GET DIAGNOSTICS CONDITION 1
            @err_no = MYSQL_ERRNO,
            @err_msg = MESSAGE_TEXT;
        -- 写入文件
        SET @log_query = CONCAT(
            "SELECT CONCAT('【", NOW(), "】', ' 错误码:', ", 
            @err_no, ", ' 信息:', '", @err_msg, 
            "') INTO OUTFILE '/var/log/mysql/error.log'"
        );
        PREPARE stmt FROM @log_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END;
    -- 业务逻辑
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '模拟错误';
END$$
DELIMITER ;

注意事项：

• 需配置secure_file_priv参数
• MySQL用户需有文件写入权限

Oracle 实现

CREATE OR REPLACE PROCEDURE SampleProcedure IS
    v_file UTL_FILE.FILE_TYPE;
BEGIN
    v_file := UTL_FILE.FOPEN('LOG_DIR', 'error.log', 'A');  -- A表示追加模式
    BEGIN
        -- 业务逻辑
        RAISE_APPLICATION_ERROR(-20001, '模拟错误');
    EXCEPTION
        WHEN OTHERS THEN
            UTL_FILE.PUT_LINE(v_file, 
                TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' | ' ||
                SQLERRM || ' | 错误代码:' || SQLCODE);
            UTL_FILE.FCLOSE(v_file);
            RAISE;
    END;
END;

前置配置：

1. 创建目录对象：

   CREATE DIRECTORY LOG_DIR AS '/oracle/logs';

2. 授权目录读写权限

安全与优化建议

1. 权限控制

   • 限制日志目录的访问权限（建议600）
   • 使用专用服务账号执行写操作

2. 日志规范

   • 包含时间戳、错误级别、会话ID等元数据
   • 示例格式：[ERROR][2025-08-20 14:30:00][SessionID:123] 主键冲突

3. 性能优化

   • 异步写入：通过消息队列解耦日志记录
   • 批量提交：积累多条记录后批量写入
   • 日志轮转：设置最大文件尺寸（如100MB）自动分割

4. 异常分类

   | 错误级别 | 处理方式                |
   |----------|-------------------------|
   | CRITICAL | 立即通知+停止服务       |
   | ERROR    | 记录日志+触发告警       |
   | WARNING  | 记录日志+继续执行       |

常见问题解答

Q：为何不直接使用数据库表存储日志？
A：当数据库不可用时，文件日志可作为最后保障机制

Q：如何避免日志文件过大？

• 方案1：使用logrotate工具定期压缩旧日志
• 方案2：在存储过程中加入文件大小检测逻辑

Q：敏感信息如何处理？

• 对身份证、手机号等字段进行掩码处理（如138****8888）
• 使用加密存储,通过AES_ENCRYPT等函数处理

扩展工具推荐

1. ELK Stack：实现日志的集中管理与可视化分析
2. Sentry：开源错误跟踪平台，支持自动告警
3. Prometheus+Grafana：构建实时监控仪表盘

本文参考了Microsoft SQL Server文档、MySQL 8.0 Reference Manual、Oracle Database PL/SQL Packages and Types Reference相关内容，具体实现请以实际环境为准。