捕捉存储过程错误

古城微笑少年丶 2022-07-16 10:22 197阅读 0赞

# ORACLE的存储过程 #

CREATE OR REPLACE PROCEDURE P_PUB_ERROR_LOG( module_name varchar2, proc_name varchar2, LOG_DESC varchar2, data_date varchar2, v_SQLCODE varchar2, v_SQLERRM varchar2, v_ERRLINE varchar2) is /* ****************************************************************** -- PURPOSE :通用记录日志的存储过程 --------------------------------------------------------------- -- CURRENT VERSION : -- 输入参数： -- module_name ： 操作的表名 -- proc_name ： 当前存储过程名 -- LOG_DESC ： 当前步骤描述，即业务逻辑 -- data_date： 数据业务汇总时间，注意和存储过程时间的区别，比如4月汇总3月的生成数据，data_date=3月，默认当前时间 -- v_SQLCODE： 自定义错误代码 -- v_SQLERRM： 错误内容 -- v_ERRLINE: 出现错误的行 ****************************************************************** */ /*必须要使用自治事务，否则commit会影响调用程序事务*/ PRAGMA AUTONOMOUS_TRANSACTION;
    
    SQLCODE2 varchar2(50);
    SQLERRM2 varchar2(300);
    ERRLINE2 varchar2(50);
    
    BEGIN insert into pub_err_log ( module_name, proc_name, log_desc, data_date, err_time, sql_code, sql_errm, ERR_LINE) values ( module_name, --模块名称 proc_name, -- 存储过程名称 log_desc, --步骤描述 data_date, --业务汇总时间 sysdate, --报错时间 v_SQLCODE, --SQLCODE v_SQLERRM, --SQLERRM v_ERRLINE --出错的行 );
    commit;
    
    exception when others then
    SQLCODE2 := SQLCODE;
    SQLERRM2 := SQLERRM;
    ERRLINE2 := DBMS_UTILITY.format_error_backtrace();--出错的行
    insert into pub_err_log (module_name,proc_name,log_desc,data_date,err_time,sql_code,sql_errm,ERR_LINE) values (module_name,proc_name,log_desc,data_date,sysdate,SQLCODE2,SQLERRM2,ERRLINE2);
    commit;
    END P_PUB_ERROR_LOG;

# sybaseIQ的存储过程 #

CREATE OR REPLACE PROCEDURE P_PUB_ERROR_LOG(IN A_OBJECT VARCHAR2(30) --发生错误对象 ,IN A_STEP_LOGS VARCHAR2(200) --步骤描述 ,IN A_START_TIME TIMESTAMP --步骤开始时间 ,IN A_ROW_CNT NUMBER(13) --步骤影响记录数 ,IN A_USER_ERRCODE NUMBER(13) --自定义错误代码 ,IN A_ERROR_NUM NUMBER(13) --SQLCODE ,IN A_ERRTEXT VARCHAR2(1000) --ERRORMSG() ,IN A_SUM_DATE VARCHAR2(10) DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD HH24:MM:SS')) --业务汇总时间，默认为当前日期 ) /*存储过程记录错误*/ IS OUT_PUT VARCHAR2;
    BEGIN /*插入记录*/ INSERT INTO PUB_ERROR_LOG ( OBJECT_NAME ,STEP_LOGS ,START_TIME ,ERR_DATE ,USE_TIME ,ROW_CNT ,USER_ERR_CODE ,SYS_ERR_CODE ,ERR_TEXT ,SUM_DATE ) VALUES ( A_OBJECT ,A_STEP_LOGS ,A_START_TIME ,TO_CHAR(SYSDATE,'YYYYMMDD HH24:MM:SS') ,ceil((SYSDATE,A_START_TIME)* 24 * 60 * 60) ,SQL%ROWCOUNT ,A_USER_ERRCODE ,A_ERROR_NUM ,SUBSTR(A_ERRTEXT, 1, 1000) ,A_SUM_DATE );
      COMMIT;
    
      --输出日志到控制台
      OUT_PUT := TO_CHAR(SYSDATE,'YYYYMMDD HH24:MM:SS') || A_STEP_LOGS||' 已影响 '||SQL%ROWCOUNT||' 条'
      DBMS_OUTPUT.PUT_LINE(OUT_PUT);
    
      --有错误信息时，把错误信息打印到控制面板
      IF A_USER_ERRCODE = -1 THEN
      OUT_PUT := 'STEP_LOGS='||A_STEP_LOGS||',ERR_TEXT='||SUBSTR(A_ERRTEXT, 1, 200)
      DBMS_OUTPUT.PUT_LINE(OUT_PUT);
      END IF;
    
    EXCEPTION
      WHEN OTHERS THEN
        OUT_PUT := 'ERR_TEXT='|| SUBSTR(ERRORMSG(), 1, 200)
        DBMS_OUTPUT.PUT_LINE(OUT_PUT);
    
        ROLLBACK;
    END;

# MySQL的存储过程 #

其实MySQL也是有oracle的sqlcode和sqlerror的错误提示的，就是要捕捉不是那么方便而已。

-- 创建日志表
    drop table if exists sp_error_log;
    create table sp_error_log( sp_name varchar(50) -- 存储过程名 ,log_desc varchar(500) -- 日志内容 ,sqlcode varchar(50) -- 错误代码 ,sqlerr varchar(300) -- 错误内容 ,loaddate datetime -- 系统时间 ,sum_date varchar(20) -- 数据业务日期 ,rowcount int -- 影响的行数 ,usetime int -- 耗时 );
    commit;
    select * from sp_error_log;
    -- 创建存储过程
    -- 暂时不知道如何像oracle那样捕捉错误所在行号，因此建议每一小步业务处理之后都加入一个日志，这个在长存储过程中需要注意
    
    drop procedure if exists sp_test ;
    
    delimiter //
    create procedure sp_test(in sum_date varchar(20)) begin -- 定义变量 declare sp_name varchar(50) default 'sp_test';
    declare log_desc varchar(200) character set utf8;  # 如果不定义编码，中文报错
    declare sqlcode  varchar(10) default '1';
    declare sqlerr   varchar(200) default '';
    declare rowcount int default null;
    declare usetime  int default null;
    -- 下面这三个是重点，捕捉MySQL的错误代码和内容，并写入日志表
    -- -------------------------------------------------------------------
    declare exit handler for sqlwarning begin GET DIAGNOSTICS condition 1 sqlcode = RETURNED_SQLSTATE, sqlerr= MESSAGE_TEXT;
        insert into sp_error_log(sp_name,log_desc,sqlcode,sqlerr,loaddate,sum_date,rowcount,usetime) values (sp_name,log_desc,sqlcode,sqlerr,sysdate(),sum_date,rowcount,usetime);
        commit;
    end;
    declare exit handler for not found begin GET DIAGNOSTICS condition 1 sqlcode = RETURNED_SQLSTATE, sqlerr= MESSAGE_TEXT;
        insert into sp_error_log(sp_name,log_desc,sqlcode,sqlerr,loaddate,sum_date,rowcount,usetime) values (sp_name,log_desc,sqlcode,sqlerr,sysdate(),sum_date,rowcount,usetime);
        commit;
    end;
    
    declare exit handler for sqlexception begin GET DIAGNOSTICS condition 1 sqlcode = RETURNED_SQLSTATE, sqlerr= MESSAGE_TEXT;
        insert into sp_error_log(sp_name,log_desc,sqlcode,sqlerr,loaddate,sum_date,rowcount,usetime) values (sp_name,log_desc,sqlcode,sqlerr,sysdate(),sum_date,rowcount,usetime);
        commit;
    end;
    -- -------------------------------------------------------------
    -- 下面开始业务处理
    set log_desc='这是业务处理说明';
    select to_date(eqwe);  -- 捕捉这里的函数错误
    select 3;
    end // delimiter ;
    
    -- 测试
    call sp_test('20161014');
    select * from sp_error_log;
    select row_count();