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;