捕捉存储过程错误

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();