oracle日志表及日志记录设计

日志表表结构

日志表

-- CREATE TABLE
CREATE TABLE DW_LOG
(W_LOG_TIME  TIMESTAMP(6),
 OBJ_NAME    VARCHAR2(128) NOT NULL,
 LOG_TYPE    INTEGER NOT NULL,
 OBJ_STEPNUM INTEGER,
 LOOP_FLAG   VARCHAR2(128),
 START_TIME  DATE,
 END_TIME    DATE,
 RET_SQLCODE INTEGER,
 RET_SQLERRM VARCHAR2(2048),
 EXEC_SQL    CLOB
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE DW_LOG
  IS '数据库对象运行日志表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN DW_LOG.W_LOG_TIME
  IS '写日志的时间';
COMMENT ON COLUMN DW_LOG.OBJ_NAME
  IS '对象名称';
COMMENT ON COLUMN DW_LOG.LOG_TYPE
  IS '日志类型(0:正常日志,1:错误日志)';
COMMENT ON COLUMN DW_LOG.OBJ_STEPNUM
  IS '对象内部位置';
COMMENT ON COLUMN DW_LOG.LOOP_FLAG
  IS '循环内部标志';
COMMENT ON COLUMN DW_LOG.START_TIME
  IS '执行开始时间';
COMMENT ON COLUMN DW_LOG.END_TIME
  IS '执行结束时间';
COMMENT ON COLUMN DW_LOG.RET_SQLCODE
  IS '执行异常返回码';
COMMENT ON COLUMN DW_LOG.RET_SQLERRM
  IS '执行异常信息';
COMMENT ON COLUMN DW_LOG.EXEC_SQL
  IS '执行语句';

写日志

CREATE OR REPLACE PROCEDURE PROC_DW_LOG(I_OBJ_NAME    IN VARCHAR2, --对象名称
                                        I_LOG_TYPE    IN INTEGER, --日志类型(0:正常日志,1:错误日志)
                                        I_OBJ_STEPNUM IN INTEGER, --对象内部位置
                                        I_LOOP_FLAG   IN VARCHAR2, --循环内部标志
                                        I_START_TIME  IN DATE, --执行开始时间
                                        I_END_TIME    IN DATE, --执行结束时间
                                        I_RET_SQLCODE IN INTEGER, --执行异常返回码
                                        I_RET_SQLERRM IN VARCHAR2, --执行异常信息
                                        I_EXEC_SQL    IN CLOB) AS --执行语句(一般记录动态sql)
  /*******************************************************************
  名称：PROC_DW_LOG
  功能描述：
   提供给调用者记录运行日志(函数或存储过程)
  修订记录
  版本号   编辑时间      编辑人            修改描述
  V1.0    2021-10-25    ZhangJuChang       创建
  入出参数说明
  返回值描述
  *******************************************************************/

BEGIN

  INSERT INTO DW_LOG
    (W_LOG_TIME, --写日志的时间
     OBJ_NAME, --对象名称
     LOG_TYPE, --日志类型(0:正常日志,1:错误日志)
     OBJ_STEPNUM, --对象内部位置
     LOOP_FLAG, --循环内部标志
     START_TIME, --执行开始时间
     END_TIME, --执行结束时间
     RET_SQLCODE, --执行异常返回码
     RET_SQLERRM, --执行异常信息
     EXEC_SQL) --执行语句(一般记录动态sql)
  VALUES
    (SYSTIMESTAMP,
     I_OBJ_NAME,
     I_LOG_TYPE,
     I_OBJ_STEPNUM,
     I_LOOP_FLAG,
     I_START_TIME,
     I_END_TIME,
     I_RET_SQLCODE,
     I_RET_SQLERRM,
     I_EXEC_SQL);
  COMMIT;
END;

调用模板

CREATE OR REPLACE PROCEDURE PROC_ODH_XSJY_JOB_MODEL_D(I_WORK_DATE      IN VARCHAR,
                                                      O_RESULT_CODE    OUT VARCHAR,
                                                      O_RESULT_MESSAGE OUT VARCHAR) IS
  /*******************************************************************
  名称：PROC_ODH_XSJY_JOB_MODEL_D
  功能描述：
  版本号    编辑时间     编辑人        修改描述
  V1.0     2021-08-23   ZhangJuChang    创建
  入出参数说明
  *******************************************************************/
  GV_OBJ_NAME      VARCHAR2(128) := 'PROC_ODH_XSJY_JOB_MODEL_D';
  GV_OBJ_STEPNUM   INTEGER := 0; --对象内部位置
  GV_LOOP_FLAG     VARCHAR2(128); --循环内部标志
  GV_EXEC_SQL      CLOB; --执行的sql语句
  GV_START_TIME    DATE := SYSDATE;
  GV_END_TIME      DATE := SYSDATE;
  GV_LOG_TYPE      INTEGER := 0; --日志类型(0:正常日志,1:错误日志)
  GV_EXCEPTION     EXCEPTION; --自定义类型的错误
  GV_WORK_DATE_PRE CHAR(8) := TO_CHAR(TO_DATE(I_WORK_DATE, 'YYYYMMDD') - 1,
                                      'YYYYMMDD');

BEGIN

  O_RESULT_CODE    := -1;
  O_RESULT_MESSAGE := 'fail';

  GV_OBJ_STEPNUM := GV_OBJ_STEPNUM + 1; --对象内部位置
  GV_START_TIME  := SYSDATE;
  --清空临时表数据
  GV_EXEC_SQL := 'TRUNCATE TABLE TMP_ODH_XSJY_JOB_ARTICLE';
  EXECUTE IMMEDIATE GV_EXEC_SQL;
  --1.向临时表插入当日有效数据(新增及变化)
  GV_EXEC_SQL := 'INSERT /*+APPEND*/ INTO TMP_ODH_XSJY_JOB_ARTICLE NOLOGGING
                   SELECT ' || I_WORK_DATE || ',
                          ''99991231'',
                          A.ID,
                          UPDATE_TIME,
                          CREATE_YEAR,
                          IS_PUSH,
                          B.CONTENT_HTML
                  FROM ODS.ODS_XSJY_JOB_ARTICLE_D_' ||
                 I_WORK_DATE || ' A
                  LEFT JOIN ODS.ODS_XSJY_JOB_ARTICLE_CONTENT_D_' ||
                 I_WORK_DATE || ' B
                       ON A.ID = B.ID
                  WHERE TO_CHAR(CREATE_TIME,''YYYYMMDD'') = ' ||
                 I_WORK_DATE || '
                    OR  TO_CHAR(UPDATE_TIME,''YYYYMMDD'') = ' ||
                 I_WORK_DATE || '
                  ';
  EXECUTE IMMEDIATE GV_EXEC_SQL;
  COMMIT;
  GV_END_TIME := SYSDATE;

  --删除当日及之后数据(兼容重跑)
  GV_OBJ_STEPNUM := GV_OBJ_STEPNUM + 1; --对象内部位置
  GV_START_TIME  := SYSDATE;
  DELETE ODH_XSJY_JOB_ARTICLE WHERE START_DT >= I_WORK_DATE;
  COMMIT;
  GV_END_TIME := SYSDATE;

  --开链(兼容重跑)
  GV_OBJ_STEPNUM := GV_OBJ_STEPNUM + 1; --对象内部位置
  GV_START_TIME  := SYSDATE;
  UPDATE ODH_XSJY_JOB_ARTICLE
     SET END_DT = '99991231'
   WHERE END_DT >= GV_WORK_DATE_PRE;
  COMMIT;
  GV_END_TIME := SYSDATE;

  --加工ODH(ODS历史变化)
  --1.封链(变化和删除)
  GV_OBJ_STEPNUM := GV_OBJ_STEPNUM + 1; --对象内部位置
  GV_START_TIME  := SYSDATE;
  UPDATE ODH_XSJY_JOB_ARTICLE TAG
     SET TAG.END_DT = GV_WORK_DATE_PRE
   WHERE TAG.ID IN (SELECT T.ID
                      FROM ODH_XSJY_JOB_ARTICLE T
                     INNER JOIN TMP_ODH_XSJY_JOB_ARTICLE TMP
                        ON T.ID = TMP.ID);
  COMMIT;
  GV_END_TIME := SYSDATE;
  --2.插入新增及变化的数据
  GV_OBJ_STEPNUM := GV_OBJ_STEPNUM + 1; --对象内部位置
  GV_START_TIME  := SYSDATE;
  FOR I IN 1 .. 10 LOOP
    GV_LOOP_FLAG := I;
    INSERT /*+APPEND*/
    INTO ODH_XSJY_JOB_ARTICLE NOLOGGING
      (ID, TITLE)
      SELECT RES.ID, RES.IS_PUSH
        FROM TMP_ODH_XSJY_JOB_ARTICLE RES
       WHERE RES.START_DT = I_WORK_DATE;
    COMMIT;
  END LOOP;
  GV_END_TIME := SYSDATE;

  O_RESULT_CODE    := 0;
  O_RESULT_MESSAGE := 'successed';

EXCEPTION
  WHEN GV_EXCEPTION THEN
    GV_LOG_TYPE      := 1;
    O_RESULT_CODE    := SQLCODE;
    O_RESULT_MESSAGE := SQLERRM;
    PROC_DW_LOG(GV_OBJ_NAME,
                GV_LOG_TYPE,
                GV_OBJ_STEPNUM,
                GV_LOOP_FLAG,
                GV_START_TIME,
                GV_END_TIME,
                O_RESULT_CODE,
                O_RESULT_MESSAGE,
                GV_EXEC_SQL);
  WHEN OTHERS THEN
    GV_LOG_TYPE      := 1;
    O_RESULT_CODE    := SQLCODE;
    O_RESULT_MESSAGE := SQLERRM;
    PROC_DW_LOG(GV_OBJ_NAME,
                GV_LOG_TYPE,
                GV_OBJ_STEPNUM,
                GV_LOOP_FLAG,
                GV_START_TIME,
                GV_END_TIME,
                O_RESULT_CODE,
                O_RESULT_MESSAGE,
                GV_EXEC_SQL);
END;