ORACLE存储优雅的记录日志

​ 背景，由于lz的工作中有较多的业务逻辑是通过存储过程实现的，在存储过程中的具体执行过程没有log记录，出现问题后的排查照成了很大的困扰，经过请教前辈得到下来解决办法，特此记录下防止遗忘，同时也分享给有相同业务场景的伙伴相互学习。

创建操作日志表

--操作日志表
CREATE TABLE TB_HANDLE_LOG(
  SERIALKEY     NUMBER,
  OPER_ID       NVARCHAR2(50) DEFAULT SYS_GUID(),
  TITLE         NVARCHAR2(50),
  ACTION        NVARCHAR2(100),
  METHOD        NVARCHAR2(100),
  OPER_NAME     NVARCHAR2(50),
  OPER_PARAM    clob,
  STATUS        NVARCHAR2(2) DEFAULT 'S',
  ERROR_MSG     clob DEFAULT '',
  OPER_TIME     DATE DEFAULT sysdate,
  ADDDATE       DATE DEFAULT sysdate,
  ADDWHO        NVARCHAR2 (30) DEFAULT USER
);

COMMENT ON TABLE TB_HANDLE_LOG IS '操作日志表';
COMMENT ON COLUMN TB_HANDLE_LOG.oper_id IS '日志主键';
COMMENT ON COLUMN TB_HANDLE_LOG.title IS '模块标题';
COMMENT ON COLUMN TB_HANDLE_LOG.action IS '功能请求';
COMMENT ON COLUMN TB_HANDLE_LOG.method IS '请求方法';
COMMENT ON COLUMN TB_HANDLE_LOG.oper_name IS '操作人员';
COMMENT ON COLUMN TB_HANDLE_LOG.oper_param IS '请求参数';
COMMENT ON COLUMN TB_HANDLE_LOG.status IS '操作状态（S正常 E异常）';
COMMENT ON COLUMN TB_HANDLE_LOG.error_msg IS '错误消息';
COMMENT ON COLUMN TB_HANDLE_LOG.oper_time IS '操作时间';

--Create SEQUENCE
CREATE SEQUENCE SEQ_TB_HANDLE_LOG
MINVALUE 1
MAXVALUE 99999999999999
START WITH 1
INCREMENT BY 1;
/

--Create TRIGGER
CREATE OR REPLACE TRIGGER TRI_TB_HANDLE_LOG
  BEFORE INSERT ON TB_HANDLE_LOG
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.SERIALKEY IS NULL THEN
    SELECT SEQ_TB_HANDLE_LOG.NEXTVAL INTO :NEW.SERIALKEY FROM DUAL;
  END IF;
END;
/

创建PACKAGE

CREATE OR REPLACE PACKAGE HANDLE_LOG IS
  /*Created on 2020/01/17 by JackDong*/
  /*存储过程记录日志*/
  /*记录消息*/
  PROCEDURE INFO(title NVARCHAR2,action NVARCHAR2,method NVARCHAR2,oper_name NVARCHAR2,
    oper_param NVARCHAR2,error_msg NVARCHAR2);
  /*记录错误*/
  PROCEDURE ERROR(title NVARCHAR2,action NVARCHAR2,method NVARCHAR2,oper_name NVARCHAR2,
    oper_param NVARCHAR2,error_msg NVARCHAR2);
END HANDLE_LOG;
/

CREATE OR REPLACE PACKAGE BODY HANDLE_LOG IS
  /*Created on 2020/01/17 by JackDong*/
  /*存储过程记录日志*/
  /*记录消息*/
  PROCEDURE INFO(title NVARCHAR2,action NVARCHAR2,method NVARCHAR2,oper_name NVARCHAR2,
            oper_param NVARCHAR2,error_msg NVARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       INSERT INTO TB_HANDLE_LOG(TITLE,ACTION,METHOD,OPER_NAME,OPER_PARAM,STATUS,ERROR_MSG,ADDWHO)
              VALUES(title,action,method,oper_name,oper_param,'S',error_msg,oper_name);
       COMMIT;
    EXCEPTION WHEN OTHERS THEN
       NULL;
       ROLLBACK;
    END;
  /*记录错误*/
  PROCEDURE ERROR(title NVARCHAR2,action NVARCHAR2,method NVARCHAR2,oper_name NVARCHAR2,
            oper_param NVARCHAR2,error_msg NVARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       INSERT INTO TB_HANDLE_LOG(TITLE,ACTION,METHOD,OPER_NAME,OPER_PARAM,STATUS,ERROR_MSG,ADDWHO)
              VALUES(title,action,method,oper_name,oper_param,'E',error_msg,oper_name);
       COMMIT;
    EXCEPTION WHEN OTHERS THEN
       NULL;
       ROLLBACK;
    END;
END HANDLE_LOG;
/

定时清空删除日志

DECLARE
  JOB NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(JOB,
                      '/*作业任务，删除一年前的INFO和DEBUG类型的日志,防止表数据过大*/
DELETE TB_HANDLE_LOG WHERE ADDDATE < add_months(SYSDATE ,-12);
COMMIT;',
                      SYSDATE,
                      'ADD_MONTHS(trunc(sysdate,''yyyy''),12)+1/24');
  COMMIT;
END;
/

调用方式

-- Created on 2020/1/17 by JACK 
declare 
  -- Local variables here
  v_title         nvarchar2(10):='用户登陆';
  v_action        nvarchar2(100):='验证密码';
  v_method        nvarchar2(100):='XXXXX';
  v_oper_param    nvarchar2(255):='user:jack';
  v_who           nvarchar2(10):='jack';
begin
  -- Test statements here
  HANDLE_LOG.INFO(v_title,v_action,v_method,v_who,v_oper_param,'用户登陆成功！');
  HANDLE_LOG.ERROR(v_title,v_action,v_method,v_who,v_oper_param,'用户登陆失败！');
end;

日志结果