存储过程运行日志记录通用模块

目标 实现记录执行存储过程的开始时间，结束时间，运行状态，错误信息等，以函数封装日志记录的方式，存储调用函数 源码 存储过程模版 CREATE OR REPLACE PROCEDURE proc_xx IS --修改标志返回值 V_AFFECT_LINE NUMBER; PROID NUMBER;BEGIN --调用更改标志函

目标

源码

CREATE OR REPLACE PROCEDURE proc_xx

 IS

 --修改标志返回值
 V_AFFECT_LINE NUMBER;
 PROID NUMBER;
BEGIN

 --调用更改标志函数，将进程改为运行中'S'
 V_AFFECT_LINE := INSERT_LOG(PROID,'proc_xx',WIFI.GLOBAL_PARAM.STATUS_START);

 --逻辑处理函数调用
 V_AFFECT_LINE := WIFI.func_xx();

 --修改标志，成功置C
 V_AFFECT_LINE := WIFI.MODIFY_STATUS(PROID,WIFI.GLOBAL_PARAM.STATUS_CLOSE,'');


EXCEPTION
 WHEN OTHERS THEN
 --调用更改标志函数,将进程改为出错'F'
 V_AFFECT_LINE := MODIFY_STATUS(PROID,WIFI.GLOBAL_PARAM.STATUS_FAILED,WIFI.GLOBAL_PARAM.LOG_EXCEPTION);
END;

create or replace function func_xx return int is
 begin

.... 
 
return 1;

exception
 when others then
 set_error_log ();
 RETURN 0;
 end;

CREATE OR REPLACE FUNCTION INSERT_LOG (
 proid OUT NUMBER,
 program_name IN VARCHAR2,
 status IN VARCHAR2
)
 RETURN NUMBER
IS
 -------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
---
-- -功能描述: 初始化日志
----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
 INSERT INTO program_log
 VALUES (program_log_seq.NEXTVAL, TO_CHAR (SYSDATE, 'YYYYMMDD'),
 program_name, SYSDATE, NULL, status, '');

 SELECT program_log_seq.CURRVAL INTO proid FROM dual;
 COMMIT;
 RETURN 1;
EXCEPTION
 WHEN OTHERS
 THEN
 RETURN 0;
 END;
 
 
 CREATE OR REPLACE FUNCTION MODIFY_STATUS
 ( proId IN number,
 status IN varchar2,
 proDesc in varchar2)
 RETURN NUMBER IS

 thisDate date;

BEGIN
 -------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
---
-- -功能描述: 修改日志表存储过程运行状态,记录开始时间 结束时间 出错信息
----------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
 thisDate := sysdate;
 --更新状态 出错信息
 update program_log set program_status=status, memo=proDesc where id=proId;

 --更新日期：如果是运行中，更新起始运行日期；如果是运行成功或者运行失败，更新结束运行日期
 IF status='S' THEN
 update program_log set start_date=thisDate where id=proId;
 ELSIF status='C' THEN
 update program_log set end_date=thisDate where id=proId;
 ELSIF status='F' THEN
 update program_log set end_date=thisDate where id=proId;
 COMMIT;
 RAISE_APPLICATION_ERROR(-20040,'STATUS IS F');

 END IF;
 /* ELSIF status='C' OR status='F' THEN
 update program_log set end_date=thisDate where id=proId;
 END IF;*/
 commit;
 RETURN 1;
EXCEPTION
 WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR(-20041,'STATUS IS F');
 RETURN 0;
END;

CREATE OR REPLACE PROCEDURE SET_ERROR_LOG
 IS
BEGIN
 GLOBAL_PARAM.LOG_EXCEPTION := 'error desc---'||sqlerrm;
 commit;
 RAISE no_data_found;
END; -- Procedure;


CREATE OR REPLACE PACKAGE GLOBAL_PARAM IS
LOG_EXCEPTION VARCHAR2(2000):='';
STATUS_START VARCHAR2(10):='S';
STATUS_CLOSE VARCHAR2(10):='C';
STATUS_FAILED VARCHAR2(10):='F';

END; 

create table PROGRAM_LOG
(
 ID NUMBER not null,
 BATCH_NUMBER VARCHAR2(50),
 PROGRAM_NAME VARCHAR2(100),
 START_DATE DATE,
 END_DATE DATE,
 PROGRAM_STATUS VARCHAR2(20),
 MEMO VARCHAR2(2000)
)