plsql 处理异常并记录在表内

先创建日志表GATEWAY_DB_LOG

create table GATEWAY_DB_LOG
(
  GW_ID       NUMBER(38) not null,
  LOG_TIME    DATE not null,
  LOG_CONTENT VARCHAR2(500),
  LOG_TYPE    VARCHAR2(20),
  LOG_SRC     VARCHAR2(50)
)

 

在存储过程或触发器内最后加上：

 

EXCEPTION --异常处理
            WHEN OTHERS THEN
              BEGIN
                rollback;
                ERROR_MSG :=sqlerrm(sqlcode) || ' CODE:' || sqlcode;
                INSERT INTO GATEWAY_DB_LOG (LOG_TIME,LOG_CONTENT,LOG_TYPE,LOG_SRC)
                           VALUES (sysdate,ERROR_MSG,
                           'tmp_flight','BATCH_ADD_DYNAMIC_PLAN');
                commit ;          
              END;