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

目标 实现记录执行存储过程的开始时间，结束时间，运行状态，错误信息等，以函数封装日志记录的方式，存储调用函数 源码 存储过程模版 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

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

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

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


EXCEPTION
       WHEN OTHERS THEN
    --调用更改标志函数,将进程改为出错&＃39;F&＃39;
    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, &＃39;YYYYMMDD&＃39;),
         program_name, SYSDATE, NULL, status, &＃39;&＃39;);

   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=&＃39;S&＃39; THEN
        update program_log set start_date=thisDate where id=proId;
    ELSIF status=&＃39;C&＃39; THEN
        update program_log set end_date=thisDate where id=proId;
    ELSIF status=&＃39;F&＃39; THEN
        update program_log set end_date=thisDate where id=proId;
        COMMIT;
        RAISE_APPLICATION_ERROR(-20040,&＃39;STATUS IS F&＃39;);

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

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


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

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)
)