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;