oracle中开发存储过程收集log日志记载程序运行情况

1.创建日志表

create table PAREBIDATA.LOG_RUN_CONFIG
(
  log_id       NUMBER not null,
  program_name VARCHAR2(200),
  step_no      VARCHAR2(200),
  begin_time   DATE not null,
  end_time     DATE,
  state_flag   VARCHAR2(20) not null,
  run_info     VARCHAR2(200),
  user_id      VARCHAR2(50),
  created_by   VARCHAR2(50) default USER not null,
  created_time DATE default SYSDATE not null,
  updated_by   VARCHAR2(50) default USER not null,
  updated_time DATE default SYSDATE not null,
  sqldess      VARCHAR2(4000),
  parameter    VARCHAR2(4000)
);
-- Add comments to the table 
comment on table PAREBIDATA.LOG_RUN_CONFIG
  is '程序运行情况记录表';
-- Add comments to the columns 
comment on column PAREBIDATA.LOG_RUN_CONFIG.log_id
  is 'ID';
comment on column PAREBIDATA.LOG_RUN_CONFIG.program_name
  is '运行程序名';
comment on column PAREBIDATA.LOG_RUN_CONFIG.begin_time
  is '运行开始时间';
comment on column PAREBIDATA.LOG_RUN_CONFIG.end_time
  is '运行结束时间';
comment on column PAREBIDATA.LOG_RUN_CONFIG.state_flag
  is '程序运行状态';
comment on column PAREBIDATA.LOG_RUN_CONFIG.run_info
  is '程序运行信息';
comment on column PAREBIDATA.LOG_RUN_CONFIG.step_no
  is '程序运行到具体哪一步';
comment on column PAREBIDATA.LOG_RUN_CONFIG.user_id
  is '数据库用户';
comment on column PAREBIDATA.LOG_RUN_CONFIG.sqldess
  is 'SQL代码号';
comment on column PAREBIDATA.LOG_RUN_CONFIG.parameter
  is '运行参数';
  
  
-- Create sequence 
create sequence PAREBIDATA.PRO_RUN_LOG_SEQUENCE PAREBIDATA
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;


-- Grant/Revoke object privileges 
grant select, insert, update, delete on PAREBIDATA.LOG_RUN_CONFIG to parebidata,parebiopr,r_parebidata_dml;

-- Grant/Revoke object privileges 
grant select, ALTER on PAREBIDATA.PRO_RUN_LOG_SEQUENCE to parebidata,parebiopr,r_parebidata_dml;
grant select on PAREBIDATA.PRO_RUN_LOG_SEQUENCE to r_parebidata_qry,r_parebidata_dev_qry;

--CREATE PUBLIC SYNONYM
CREATE PUBLIC SYNONYM LOG_RUN_CONFIG FOR PAREBIDATA.LOG_RUN_CONFIG;

CREATE PUBLIC SYNONYM PRO_RUN_LOG_SEQUENCE FOR PAREBIDATA.PRO_RUN_LOG_SEQUENCE;

2开发存储过程

CREATE OR REPLACE PACKAGE MIS_LOG_PACKAGE IS
  


--////////////////////////////////////////////////////////////////////////////


///////
  /*
  


******************************************************************************


*****
  *
  * --包    名 ： MIS_PUB_PACKAGE
  * --创 建 人 ： xxxx
  * --创建日期 ： 2016-10-20
  * --程序说明 ： 通过日记记录package运行过程
  * --修 改 人 ：
  * --修改日期 ： 2016-10-20




  


******************************************************************************


*****
  */
  


--////////////////////////////////////////////////////////////////////////////


///////


  -- 该过程用于生成日志信息，并将该信息插入到日志表p中
  PROCEDURE RUN_BEGIN_PROCEDURE(P_SP_NAME IN VARCHAR2,
                                P_STEP    IN VARCHAR2,
                                P_MESSAGE  IN VARCHAR2,
                                P_PARAMETER IN VARCHAR2);


  --该过程用于在正常情况下从日志表中提取对应日志信息，并进行更新操作
  PROCEDURE RUN_END_PROCEDURE(P_SP_NAME IN VARCHAR2,
                              P_STEP    IN VARCHAR2,
                              P_MESSAGE  IN VARCHAR2 );


  --该过程用于在异常情况下从日志表中提取对应日志信息，并进行更新操作
  PROCEDURE RUN_EXCEPT_PROCEDURE(P_SP_NAME IN VARCHAR2,
                                 P_STEP    IN VARCHAR2,
                                 P_MESSAGE  IN VARCHAR2,
                                 P_SQLCODE IN VARCHAR2);




END MIS_LOG_PACKAGE;
/


GRANT EXECUTE ON MIS_LOG_PACKAGE TO parebidata,parebiopr,r_parebidata_exec;




CREATE OR REPLACE PACKAGE BODY MIS_LOG_PACKAGE IS
  


--////////////////////////////////////////////////////////////////////////////


///////
  /*
  


******************************************************************************


*****
  * --包    名 ： MIS_PUB_PACKAGE
  * --创 建 人 ： xxxxxx
  * --创建日期 ： 2016-10-20
  * --程序说明 ： 通过日记记录package运行过程
  * --修 改 人 ：
  * --修改日期 ：


  


******************************************************************************


*****
  */
  


--////////////////////////////////////////////////////////////////////////////


///////


  ------------------------------------------------------
  /************************************************
  * 过程名称：run_begin_procedure
  * 过程说明：该过程用于生成一条日志信息，并插入日志表中
  ************************************************/
  -----------------------------------------------------


  PROCEDURE RUN_BEGIN_PROCEDURE(P_SP_NAME IN VARCHAR2, --传入存储过程名字
                                P_STEP    IN VARCHAR2, --传入存储过程中具体步骤
                                P_MESSAGE IN VARCHAR2, --传入运行说明
                                P_PARAMETER IN VARCHAR2 --传入运行参数
                                ) IS
    V_SP_NAME VARCHAR2(200);
    V_STEP    VARCHAR2(200);
    V_MESSAGE VARCHAR2(200);
    V_PARAMETER VARCHAR2(200);


  BEGIN
    -- 变量赋值
    V_SP_NAME := P_SP_NAME;
    V_STEP    := P_STEP;
    V_MESSAGE := P_MESSAGE;
    V_PARAMETER :=P_PARAMETER;


    --代码段开始 记录运行日志
    DELETE from LOG_RUN_CONFIG WHERE begin_time <trunc(sysdate)-7;--保留一周日志
    INSERT INTO LOG_RUN_CONFIG
      (LOG_ID, --主键
       PROGRAM_NAME, --被监控过程名称
       BEGIN_TIME,   --运行开始时间
       STATE_FLAG,   --运行状态标识
       USER_ID,      --用户ID
       RUN_INFO,     --运行信息
       STEP_NO,      --运行到第几步
       PARAMETER     --运行的参数
       ) 
    VALUES
      (PAREBIDATA.PRO_RUN_LOG_SEQUENCE.NEXTVAL, --sequence生成主键
       V_SP_NAME, --传入的过程名参数
       SYSDATE,   --开始运行时间
       'RUNNING', --RUNNING正常运行中
       USER,      --用户
       V_MESSAGE, --运行信息
       V_STEP,    --传入的运行到第几步
       V_PARAMETER);


    COMMIT;


  END RUN_BEGIN_PROCEDURE;
  -------------------------------------------------------


  /************************************************
  * 过程名称：run_result_end_procedure


  * 过程说明：该过程在正常情况下从日志表中提取
  *           对应日志信息，并进行更新操作
  ************************************************/
  -------------------------------------------------------


  PROCEDURE RUN_END_PROCEDURE(P_SP_NAME IN VARCHAR2, --传入存储过程名字
                              P_STEP    IN VARCHAR2, --传入存储过程中具体步骤
                              P_MESSAGE IN VARCHAR2 --传入运行说明
                              ) IS


    V_SP_NAME VARCHAR2(200);
    V_STEP    VARCHAR2(200);
    V_MESSAGE VARCHAR2(200);
    V_PK      LOG_RUN_CONFIG.LOG_ID%TYPE; --变量v_pk:用于保存主键


  BEGIN


    V_SP_NAME := P_SP_NAME;
    V_STEP    := P_STEP;
    V_MESSAGE := P_MESSAGE;


    --选出被监控过程生成的日志信息的主键
    SELECT LOG_ID
      INTO V_PK
      FROM (SELECT LOG_ID
              FROM LOG_RUN_CONFIG
             WHERE PROGRAM_NAME = V_SP_NAME
               AND  STEP_NO = V_STEP
               AND END_TIME IS NULL
               AND STATE_FLAG = 'RUNNING' --正在运行
             ORDER BY BEGIN_TIME DESC)
     WHERE ROWNUM = 1;


    --更新该日志信息
    UPDATE LOG_RUN_CONFIG
       SET END_TIME   = SYSDATE, --插入结束时间
           STATE_FLAG = 'SUCCESS', --变动状态标识（SUCCESS)
           RUN_INFO   = V_MESSAGE --传入运行日志信息参数
     WHERE LOG_ID = V_PK; --确保更新的日志信息为被监控过程的


    COMMIT;


  END RUN_END_PROCEDURE;


  -----------------------------------------------------


  /************************************************
  * 过程名称：run_except_procedure


  * 过程说明：该过程在异常情况下从日志表中提取
  *           对应日志信息，并进行更新操作
  ************************************************/


  ---------------------------------------------------
  PROCEDURE RUN_EXCEPT_PROCEDURE(P_SP_NAME IN VARCHAR2, --传入存储过程名字
                                 P_STEP    IN VARCHAR2, --传入存储过程中具体步骤
                                 P_MESSAGE IN VARCHAR2, --传入运行情况说明
                                 P_SQLCODE IN VARCHAR2  --传入SQL异常代码
                                 ) IS


    V_SP_NAME VARCHAR2(200);
    V_STEP    VARCHAR2(200);
    V_MESSAGE VARCHAR2(4000);
    V_SQLCODE VARCHAR2(4000);
    V_PK      LOG_RUN_CONFIG.LOG_ID%TYPE; --变量v_pk:用于保存主键


  BEGIN
    V_SP_NAME := P_SP_NAME;
    V_STEP    := P_STEP;
    V_MESSAGE := P_MESSAGE;
    V_SQLCODE := P_SQLCODE;


    --选出被监控过程生成的日志信息的主键
    SELECT LOG_ID
      INTO V_PK
      FROM (SELECT LOG_ID
              FROM LOG_RUN_CONFIG
             WHERE PROGRAM_NAME = V_SP_NAME
               AND STEP_NO=V_STEP
               AND END_TIME IS NULL
               AND STATE_FLAG = 'RUNNING' --正在运行
             ORDER BY BEGIN_TIME DESC)
     WHERE ROWNUM = 1;


    --更新该日志信息
    UPDATE LOG_RUN_CONFIG
       SET END_TIME   = SYSDATE, --异常跳出时间
           STATE_FLAG = 'ERROR', --变更标识为异常(ERROR)
           RUN_INFO   = SUBSTR(V_MESSAGE,1,2000), --传入运行信息参数
           SQLDESS    = SUBSTR(V_SQLCODE,1,2000)  --
     WHERE LOG_ID = V_PK; --确保更新的日志信息为被监控过程的


    COMMIT;


  END RUN_EXCEPT_PROCEDURE;
END MIS_LOG_PACKAGE;