ORACLE日志记录工具

女爷i 2022-01-21 04:29 278阅读 0赞

--删除日志表
    begin
      execute immediate 'drop table COM_LOGGER';
    exception 
      when others then null;
    end;
    /
    --创建日志表
    create table COM_LOGGER
    (
      log_id    NUMBER not null,
      log_text  VARCHAR2(4000),
      log_key   VARCHAR2(1000),
      log_value VARCHAR2(1000),
      log_level VARCHAR2(20),
      log_date  DATE
    );
    alter table COM_LOGGER add constraint PK_COM_LOGGER primary key (log_id);
    --表注释
    comment on table COM_LOGGER
      is '系统日志表，记录相关的日志信息';
    --表字段注释
    comment on column COM_LOGGER.log_id
      is '日志序号，由序列控制';
    comment on column COM_LOGGER.log_text
      is '日志内容';
    comment on column COM_LOGGER.log_key
      is '日志来源名称：PROCESS_INST_ID,NODE_INST_ID等';
    comment on column COM_LOGGER.log_value
      is '日志来源内容：PROCESS_INST_ID,NODE_INST_ID等的值';
    comment on column COM_LOGGER.log_level
      is '日志等级：DEBUG,INFO,WARN,ERROR';
    comment on column COM_LOGGER.log_date
      is '日志产生时间';
    --创建日志序列
    begin
      execute immediate 'create sequence seq_com_logger';
    exception 
      when others then
        null;
    end;
    /
    
    create or replace package logger is
    
      -- Author  : ATTWICE
      -- Created : 2012/12/06 09:17:00
      -- Purpose : 日志记录
    
      -- Public constant declarations
      log_level_debug constant varchar2(10) := 'debug';
      log_level_info  constant varchar2(10) := 'info';
      log_level_warn  constant varchar2(10) := 'warn';
      log_level_error constant varchar2(10) := 'error';
    
      procedure debug(log_text  varchar2,
                      log_key   varchar2 default null,
                      log_value varchar2 default null);
    
      procedure info(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null);
    
      procedure warn(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null);
    
      procedure error(log_text  varchar2,
                      log_key   varchar2 default null,
                      log_value varchar2 default null);
    
      procedure log(log_text  varchar2,
                    log_key   varchar2 default null,
                    log_value varchar2 default null,
                    log_level varchar2 default 'debug');
    end logger;
    /
    create or replace package body logger is
    
      procedure debug(log_text  varchar2,
                      log_key   varchar2 default null,
                      log_value varchar2 default null) is
      begin
        log(log_text, log_key, log_value, log_level_debug);
      end;
    
      procedure info(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null) is
      begin
        log(log_text, log_key, log_value, log_level_info);
      end;
    
      procedure warn(log_text varchar2, log_key varchar2 default null, log_value varchar2 default null) is
      begin
        log(log_text, log_key, log_value, log_level_warn);
      end;
    
      procedure error(log_text  varchar2,
                      log_key   varchar2 default null,
                      log_value varchar2 default null) is
      begin
        log(log_text, log_key, log_value, log_level_error);
      end;
    
      procedure log(log_text  varchar2,
                    log_key   varchar2,
                    log_value varchar2,
                    log_level varchar2 default 'debug') is
        pragma autonomous_transaction;
      begin
        insert into com_logger
          (log_id, log_text, log_key, log_value, log_level, log_date)
        values
          (seq_com_logger.nextval, log_text, log_key, log_value, lower(log_level), sysdate);
        commit;
      exception
        when others then
          rollback;
          log('sqlcode=' || sqlcode || ',sqlerrm=' || sqlerrm,
              'function logger.log',
              log_key || '-' || log_value,
              'error');
      end;
    
    end logger;
    /