Oracle存储过程实现记录日志

230次阅读

共计 4497 个字符，预计需要花费 12 分钟才能阅读完成。

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;