监控存储过程的脚本

Question

谁可以帮忙写个监控存储过程的脚本，包括开始时间，结束时间，异常报错

Answer 1

CREATE OR REPLACE PROCEDURE PROC_SAVELOG(PROGRAM_NAME VARCHAR2,--过程名称
                                         IS_SUCCEED   VARCHAR2,--是否执行成功 Y|N
                                         EXECUTE_MSG  CLOB,--过程执行信息
                                         BEGIN_TIME   TIMESTAMP--过程开始执行TIMESTAMP
                                         ) AS
  V_PROGRAM_NAME VARCHAR2(1000) := SUBSTRB(PROGRAM_NAME, 1, 1000); --过程名称
  V_IS_SUCCEED   CHAR(1) := SUBSTR(IS_SUCCEED, 1, 1); --过程执行成功表示 Y N
  V_BEGIN_TIME   TIMESTAMP := BEGIN_TIME;
  PRAGMA AUTONOMOUS_TRANSACTION; --日志开启自治事务，不影响业务逻辑事务
  /*
  调用方式：
  1.正常日志：PROC_SAVELOG(V_PROC_NAME, 'Y', '执行成功',V_BEGIN_TIME);
  2.异常日志：PROC_SAVELOG(V_PROC_NAME, 'N', SQLERRM,V_BEGIN_TIME);
  */
BEGIN
  INSERT INTO PROGRAM_EXECUTE_LOG
    (PROGRAM_NAME,
     IS_SUCCEED,
     LOG_DATE,
     EXECUTE_MSG, --已改为CLOB
     TIME_CONSUMING,
     EXECUTE_ORDER,
     EXECUTE_BEGINTIME,
     EXECUTE_ENDTIME)
  VALUES
    (V_PROGRAM_NAME,
     V_IS_SUCCEED,
     SYSDATE,
     EXECUTE_MSG,
     F_TIMESTAMP_DIFF(SYSTIMESTAMP, V_BEGIN_TIME),
     PROGRAM_EXECUTE_LOG_SEQ.NEXTVAL,
     V_BEGIN_TIME,
     SYSTIMESTAMP);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;
-- Create table
create table PROGRAM_EXECUTE_LOG
(
  program_name      VARCHAR2(1000),
  is_succeed        CHAR(1),
  log_date          DATE,
  execute_msg       CLOB,
  time_consuming    NUMBER,
  execute_order     NUMBER,
  execute_begintime TIMESTAMP(6),
  execute_endtime   TIMESTAMP(6),
  process_flag      VARCHAR2(10) default 'N'
)
tablespace YZB_DATA01
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table PROGRAM_EXECUTE_LOG
  is '过程、函数、触发器执行性能日志';
-- Add comments to the columns 
comment on column PROGRAM_EXECUTE_LOG.execute_order
  is '流水号program_pfmc_log_seq';
comment on column PROGRAM_EXECUTE_LOG.process_flag
  is '处理标识(已处理Y,未处理N)';
CREATE OR REPLACE PROCEDURE PROC_BG_CLEAR(ORG_CODE IN VARCHAR2,
                                          TODAY    IN DATE,
                                          RESULT   OUT VARCHAR2) AS
  V_ORG_CODE       VARCHAR2(10) := ORG_CODE;
  V_TODAY          DATE := TODAY;
  V_SETP           INT := 1;
  V_CONF_NUM       INT;
  V_NOTCONF_NUM    INT;
  V_COUNT          INT;
  V_ERR_MSG        VARCHAR2(1000);
  V_BEGIN_TIME     TIMESTAMP := SYSTIMESTAMP;
  V_PROCESS_ERPS   VARCHAR2(10);
  V_CB_UPDATE_TO_0 VARCHAR2(10) := 'N';
  V_PROC_NAME      VARCHAR2(1000) := 'PROC_BG_CLEAR/' || V_ORG_CODE || '/' ||
                                     TO_CHAR(V_TODAY, 'YYYY-MM-DD HH24:MI:SS');
  V_EXE_MSG        CLOB;  /*清空报工数据[email protected]*/BEGIN
  RESULT:='OK';
  DBMS_LOB.CREATETEMPORARY(V_EXE_MSG, TRUE); --初始化CLOB  SELECT NVL(sum(decode(t.CONFIRM, 'Y', 1, 0)), 0),
         NVL(sum(decode(t.CONFIRM, 'Y', 0, 1)), 0),
         COUNT(1)
    INTO V_CONF_NUM, V_NOTCONF_NUM, V_COUNT
    FROM P_BG_WORK T
   where T.PRODUCT_DATE = V_TODAY
     AND T.ORG_CODE = V_ORG_CODE;
  IF V_COUNT = 0 THEN
    V_ERR_MSG := to_char(V_TODAY, 'yyyy-mm-dd') || '未找到可删除数据!';
    goto PROCESS_ERRMSG;
  elsif V_CONF_NUM > 0 then
    V_ERR_MSG := to_char(V_TODAY, 'yyyy-mm-dd') || '已经确认,无法删除!';
    goto PROCESS_ERRMSG;
  end if;  DELETE FROM P_BG_WORK T
   where T.PRODUCT_DATE = V_TODAY
     AND T.ORG_CODE = V_ORG_CODE
     AND T.CONFIRM = 'N';  --日志处理
  PROC_SAVELOG(V_PROC_NAME, 'Y', V_EXE_MSG, V_BEGIN_TIME);
  RETURN;
  --异常处理
  <<PROCESS_ERRMSG>>
  PROC_PROCESS_ERRMSG(V_PROC_NAME, V_EXE_MSG, V_ERR_MSG, V_BEGIN_TIME, RESULT);
EXCEPTION
  WHEN OTHERS THEN
    V_ERR_MSG := SUBSTR(SQLERRM, 1, 160);
    DBMS_LOB.APPEND(V_EXE_MSG, V_ERR_MSG);
    PROC_SAVELOG(V_PROC_NAME, 'N', V_EXE_MSG, V_BEGIN_TIME);
    RESULT := V_ERR_MSG; --错误号对应的信息
END;