oracle 存储 日志,oracle存储过程实现记录日志

oracle存储过程实现记录日志

create table

-- Create table

create table t_cem_proc_logs

(

id varchar2(32),

proc_mc varchar2(100),

title varchar2(4000),

content clob,

log_type varchar2(10),

log_time date default sysdate

)

;

-- Add comments to the table

comment on table t_cem_proc_logs

is '日志';

-- Add comments to the columns

comment on column t_cem_proc_logs.id

is 'id';

comment on column t_cem_proc_logs.proc_mc

is '过程名';

comment on column t_cem_proc_logs.title

is '标题';

comment on column t_cem_proc_logs.content

is '内容';

comment on column t_cem_proc_logs.log_type

is '日志级别：info,error,debug';

comment on column t_cem_proc_logs.log_time

is '时间';

-- Create/Recreate primary, unique and foreign key constraints

alter table t_cem_proc_logs

add constraint pri_proc_logs primary key (ID);

-- Create table

create table T_CEM_TASK_LOG

(

id VARCHAR2(32) not null,

task_id VARCHAR2(100),

task_code VARCHAR2(100),

start_date DATE,

end_date DATE,

log_status VARCHAR2(2),

log_message VARCHAR2(4000),

createdate DATE,

requestdata CLOB,

responsedata CLOB,

modifytime DATE,

corpid VARCHAR2(32)

);

-- Add comments to the table

comment on table T_CEM_TASK_LOG

is '调度日志';

-- Add comments to the columns

comment on column T_CEM_TASK_LOG.id

is 'ID';

comment on column T_CEM_TASK_LOG.task_id

is '调度标识';

comment on column T_CEM_TASK_LOG.task_code

is '调度任务代码';

comment on column T_CEM_TASK_LOG.start_date

is '开始执行时间';

comment on column T_CEM_TASK_LOG.end_date

is '执行结束时间';

comment on column T_CEM_TASK_LOG.log_status

is '状态 0：执行中 1：执行完成 2：执行错误';

comment on column T_CEM_TASK_LOG.log_message

is '日志信息';

comment on column T_CEM_TASK_LOG.createdate

is '创建日期';

comment on column T_CEM_TASK_LOG.requestdata

is '请求报文';

comment on column T_CEM_TASK_LOG.responsedata

is '返回报文';

comment on column T_CEM_TASK_LOG.modifytime

is '修改时间';

comment on column T_CEM_TASK_LOG.corpid

is '企业id';

-- Create/Recreate indexes

create index T_CEM_TASK_LOG_N1 on T_CEM_TASK_LOG (TASK_ID);

-- Create/Recreate primary, unique and foreign key constraints

alter table T_CEM_TASK_LOG

add constraint T_CEM_TASK_LOG_PK primary key (ID);

Create sequence

-- Create sequence

create sequence seq_log

minvalue 1

maxvalue 99999999

start with 1

increment by 1

cache 20

cycle;

package

CREATE OR REPLACE PACKAGE LOGUTILS IS

-- Author : DYB

-- Created : 2017/3/28 10:34:51

-- Purpose : 日志记录工具类

--日志

PROCEDURE LOG(P_TASK_ID VARCHAR2,

P_TASK_CODE VARCHAR2,

P_START_DATE DATE DEFAULT NULL,

P_END_DATE DATE DEFAULT NULL,

P_LOG_STATUS VARCHAR2 DEFAULT NULL,

P_LOG_MESSAGE VARCHAR2 DEFAULT NULL);

PROCEDURE INFO(p_proc_name varchar2, title varchar2, content clob);

PROCEDURE DEBUG(p_proc_name varchar2, title varchar2, content clob);

PROCEDURE ERROR(p_proc_name varchar2, title varchar2, content clob);

END LOGUTILS;

body

CREATE OR REPLACE PACKAGE BODY LOGUTILS IS

--日志

PROCEDURE LOG(P_TASK_ID VARCHAR2,

P_TASK_CODE VARCHAR2,

P_START_DATE DATE DEFAULT NULL,

P_END_DATE DATE DEFAULT NULL,

P_LOG_STATUS VARCHAR2 DEFAULT NULL,

P_LOG_MESSAGE VARCHAR2 DEFAULT NULL) IS

PRAGMA AUTONOMOUS_TRANSACTION;

V_COUNT NUMBER;

BEGIN

SELECT COUNT(1)

INTO V_COUNT

FROM T_CEM_TASK_LOG T

WHERE T.TASK_ID = P_TASK_ID

AND T.TASK_CODE = P_TASK_CODE;

IF V_COUNT = 0 THEN

INSERT INTO T_CEM_TASK_LOG

(ID,

TASK_ID,

TASK_CODE,

START_DATE,

END_DATE,

LOG_STATUS,

LOG_MESSAGE,

CREATEDATE)

VALUES

(CEM_TASK_LOG_S.NEXTVAL,

P_TASK_ID,

P_TASK_CODE,

P_START_DATE,

P_END_DATE,

P_LOG_STATUS,

P_LOG_MESSAGE,

SYSDATE);

ELSE

UPDATE T_CEM_TASK_LOG

SET END_DATE = P_END_DATE,

LOG_STATUS = P_LOG_STATUS,

LOG_MESSAGE = P_LOG_MESSAGE

WHERE TASK_ID = P_TASK_ID

AND TASK_CODE = P_TASK_CODE;

END IF;

COMMIT;

END LOG;

/*记录消息*/

PROCEDURE INFO(p_proc_name varchar2, title varchar2, content clob) is

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO t_cem_proc_logs(id,proc_mc,title,content, log_type,log_time)

VALUES ('LOG'||to_char(sysdate,'YYYYMMDD')||lpad(SEQ_log.NEXTVAL,8,'0'), upper(p_proc_name), substr(title,1,4000), content, 'INFO',sysdate);

COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;

ROLLBACK;

END;

/*记录测试*/

PROCEDURE DEBUG(p_proc_name varchar2, title varchar2, content clob) is

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO t_cem_proc_logs(id,proc_mc,title,content, log_type,log_time)

VALUES ('LOG'||to_char(sysdate,'YYYYMMDD')||lpad(SEQ_log.NEXTVAL,8,'0'), upper(p_proc_name), substr(title,1,4000), content, 'DEBUG',sysdate);

COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;

ROLLBACK;

END;

/*记录错误*/

PROCEDURE ERROR(p_proc_name varchar2, title varchar2, content clob) is

PRAGMA AUTONOMOUS_TRANSACTION;

begin

INSERT INTO t_cem_proc_logs(id,proc_mc,title,content, log_type,log_time)

VALUES ('LOG'||to_char(sysdate,'YYYYMMDD')||lpad(SEQ_log.NEXTVAL,8,'0'), upper(p_proc_name), substr(title,1,4000), content, 'ERROR',sysdate);

COMMIT;

EXCEPTION WHEN OTHERS THEN NULL;

ROLLBACK;

END;

END LOGUTILS;

原文：https://www.cnblogs.com/zhijiancanxue/p/12507915.html