转载别人的，在这里记录一下
原文链接

第一步、创建日志表，用来存放oracle存储过程的日志

create table TBL_WLF_SYS_LOG
(
      S_TIME            VARCHAR2(32) not null,
    S_LEVEL            VARCHAR2(32),
    S_PROCNAME        VARCHAR2(64),
    S_MSG            VARCHAR2(4000),
    S_ADVICE        VARCHAR2(1024)
)
tablespace TBS_WLF_DAT;
-- Add comments to the table 
comment on table TBL_WLF_SYS_LOG
  is '存储过程日志表';
-- Add comments to the columns 
comment on column TBL_WLF_SYS_LOG.S_TIME
  is '操作时间';
-- Add comments to the columns 
comment on column TBL_WLF_SYS_LOG.S_LEVEL
  is '操作级别';
-- Add comments to the columns 
comment on column TBL_WLF_SYS_LOG.S_PROCNAME
  is '执行存储过程名称';
-- Add comments to the columns 
comment on column TBL_WLF_SYS_LOG.S_MSG
  is '错误信息';
-- Add comments to the columns 
comment on column TBL_WLF_SYS_LOG.S_ADVICE
  is '建议信息';

第二步、建立日志存储过程

CREATE OR REPLACE PROCEDURE prc_wlf_sys_writelog(
  i_flag       INTEGER,
  i_id         INTEGER,
  str_procname varchar2,
  str_msg      varchar2,
  str_advice   varchar2
) IS
  -- 操作时间
  str_time   varchar2(32);
  -- 操作级别
  str_level  varchar2(32);
  -- 执行存储过程名称
  p_procname varchar2(1024);
  -- 错误信息，或者记录信息
  p_msg      varchar2(1024);
  -- 建议信息
  p_advice   varchar2(1024);

BEGIN
  IF (i_flag = 2 AND i_id >= 1 AND i_id <= 4) THEN
    CASE
      WHEN i_id = 1 THEN
        str_level := 'log';
      WHEN i_id = 2 THEN
        str_level := 'debug';
      WHEN i_id = 3 THEN
        str_level := 'alarm';
      ELSE
        str_level := 'error';
    END CASE;
    p_procname := str_procname;
    p_msg      := str_msg;
    p_advice   := str_advice;
  ELSE
    str_level  := 'error';
    p_procname := 'p_public_writelog';
    p_msg      := 'writelog_error';
    p_advice   := '';
  END IF;

  str_time := to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');

  INSERT INTO tbl_wlf_sys_log
    (s_time, s_level, s_procname, s_msg, s_advice)
  VALUES
    (str_time, str_level, p_procname, p_msg, p_advice);
  COMMIT;
END prc_wlf_sys_writelog;
/

第三步、在我们自己的存储过程中，调用日志存储过程（第二步中的）
加粗的是要求加上的

CREATE OR REPLACE PROCEDURE A_select1(
                                                        --传入实参
                                                        p_orgIds IN VARCHAR2,
                                                        p_period IN VARCHAR2,
                                                        p_userId IN VARCHAR2,
                                                        p_result OUT VARCHAR2)

 IS

  v_orgId            VARCHAR2(32);
  v_qty              INTEGER;
  v_finish_qty       INTEGER;
  v_status           INTEGER;
  v_month_report_num INTEGER;
  v_date             VARCHAR2(32);
  v_date2            VARCHAR2(32);
  v_date3            VARCHAR2(32);
  
  v_date4            VARCHAR2(32);
  v_date5            VARCHAR2(32);
  
   -- debug信息
    v_debugmsg      varchar2(1024);
    -- 错误信息
    v_errmsg      varchar2(1024);
    
  CURSOR c_orgIds IS
    select a.ORG_ID org_id
      from base_hs_health_org a
      left join (select distinct u.ref_id as org_id
                   from com_user u
                   left join com_user_role ur
                     on u.id = ur.user_id
                   left join com_role r
                     on r.id = ur.role_id
                  where r.id = '099d0eae68464cd59d83caed570a823f') H
        on a.org_id = H.ORG_ID
       where H.ORG_ID is not null
     start with a.org_id = p_orgIds
    connect by prior a.org_id = parent_id;

begin
**-- 存储过程开始日志
v_debugmsg := 'A_select1 begin  log- ';
prc_wlf_sys_writelog(2, 2, 'A_select1', v_debugmsg, '');**



  p_result := 0;
  ---遍历所有上报机构
  OPEN c_orgIds;
LOOP
    FETCH c_orgIds
      INTO v_orgId;
    EXIT WHEN c_orgIds%NOTFOUND;
    BEGIN
       -- 表示输出buffer不受限制。
      DBMS_OUTPUT.ENABLE(buffer_size => null);
      -- DBMS_OUTPUT.PUT_LINE(v_orgId);
      --- 清除原有数据
     --- delete from rhsa_hs4_import_detail
     ---  where period = p_period
     ---    and org_id = v_orgId;

v_date4:=dbms_utility.get_time;

 ---填报数量
      select count(*)
        into v_qty
        from RHSA_HS4_1_2013_TEMP
      where org_id = v_orgId
      and period in (select * from table(splitstr(p_period,',')));


-- 计算时间差
v_date5:=dbms_utility.get_time;
dbms_output.put_line('时间差: '||(v_date5-v_date4)|| ' 毫秒  '   ||' 机构id: '||v_orgId|| ' v_qty: '||v_qty);


       
    END;
  END LOOP;
  CLOSE c_orgIds;
  p_result := 1;
  
   **-- 存储过程开始日志
  v_debugmsg := 'A_select1 end  log- ';
  prc_wlf_sys_writelog(2, 2, 'A_select1', v_debugmsg, '');**
  
  
EXCEPTION
  WHEN OTHERS THEN
    p_result := 0;
    
    **begin
      rollback;
      v_errmsg := 'sqlexception~~sqlcode:' || to_char(sqlcode) ||
                  ' sqlstate:' || substr(sqlerrm, 1, 512);
      prc_wlf_sys_writelog(2, 4, 'A_select1', v_errmsg, '');
    end;**
    
    
    
end;

本文主要介绍如何记录存储过程执行详细日志,统计存储过程执行时间,实现逻辑如下:

• 创建一个序列,用于标识[存储过程,函数,触发器]执行先后顺序

create sequence SEQ_PROGRAM_EXECUTE_LOG
minvalue 1
maxvalue 1000000000000
start with 1
increment by 1
cache 100
cycle
order;

• 创建日志表,用于存储[存储过程,函数,触发器]执行日志

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'
);
-- Add comments to the table 
comment on table PROGRAM_EXECUTE_LOG
  is '过程、函数、触发器执行性能日志';
-- Add comments to the columns 
comment on column PROGRAM_EXECUTE_LOG.program_name
  is '程序名称';
comment on column PROGRAM_EXECUTE_LOG.is_succeed
  is '执行结果(成功Y,失败N)';
comment on column PROGRAM_EXECUTE_LOG.log_date
  is '日志时间';
comment on column PROGRAM_EXECUTE_LOG.execute_msg
  is '执行详细信息';
comment on column PROGRAM_EXECUTE_LOG.time_consuming
  is '耗时';
comment on column PROGRAM_EXECUTE_LOG.execute_order
  is '流水号-SEQ_PROGRAM_EXECUTE_LOG';
comment on column PROGRAM_EXECUTE_LOG.execute_begintime
  is '执行开始时间';
comment on column PROGRAM_EXECUTE_LOG.execute_endtime
  is '执行结束时间';
comment on column PROGRAM_EXECUTE_LOG.process_flag
  is '处理标识(已处理Y,未处理N)';
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX1 ON PROGRAM_EXECUTE_LOG (EXECUTE_ORDER);
CREATE INDEX PROGRAM_EXECUTE_LOG_IDX2 ON PROGRAM_EXECUTE_LOG (TIME_CONSUMING);

• 创建计算耗时的函数

CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF(ENDTIME   IN TIMESTAMP,
                                            STARTTIME IN TIMESTAMP)
  RETURN INTEGER AS
  STR      VARCHAR2(50);
  MISECOND INTEGER;
  SECONDS  INTEGER;
  MINUTES  INTEGER;
  HOURS    INTEGER;
  DAYS     INTEGER;
BEGIN
  STR      := TO_CHAR(ENDTIME - STARTTIME);
  MISECOND := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 10, 3));
  SECONDS  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 7, 2));
  MINUTES  := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 4, 2));
  HOURS    := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 1, 2));
  DAYS     := TO_NUMBER(SUBSTR(STR, 1, INSTR(STR, ' ')));
  RETURN DAYS * 24 * 60 * 60 * 1000 + HOURS * 60 * 60 * 1000 + MINUTES * 60 * 1000 + SECONDS * 1000 + MISECOND;
END;

• 创建日志记录过程

CREATE OR REPLACE PROCEDURE SYS_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 OR REPLACE PROCEDURE SYS_PROCESS_ERRMSG(PROC_NAME  VARCHAR2, --过程信息
                                                EXE_MSG    CLOB, --执行信息
                                                ERR_MSG    VARCHAR2, --错误信息
                                                BEGIN_TIME TIMESTAMP, --过程开始执行TIMESTAMP
                                                RESULT     OUT VARCHAR2) AS
  /*faith@2016.11.24用于处理异常信息,方便后续扩展
  1.执行信息中添加ERR_MSG
  2.执行记录日志过程
  3.错误信息赋值给返回结果RESULT
  调用方式：
  SYS_PROCESS_ERRMSG(V_PROC_NAME, V_EXE_MSG,V_ERR_MSG,V_BEGIN_TIME,RESULT);
  */

  V_EXE_MSG CLOB := EXE_MSG;
BEGIN
  RESULT := 'OK';
  --如果错误信息长度为0,返回OK
  IF LENGTH(ERR_MSG) = 0 OR ERR_MSG IS NULL THEN
    RETURN;
  END IF;
  DBMS_LOB.APPEND(V_EXE_MSG, ERR_MSG || CHR(10));
  SYS_SAVELOG(PROC_NAME, 'I', V_EXE_MSG, BEGIN_TIME);
  ROLLBACK;
  RESULT := ERR_MSG;
EXCEPTION
  WHEN OTHERS THEN
    RESULT := '处理异常信息发生错误!';
END;

• 使用样例

  PROCEDURE SYS_SAVELOG_EXAMPLE(PLAN_DATE IN DATE,
                                CONFIRM   IN VARCHAR2,
                                RESULT    OUT VARCHAR2) AS
    V_PLAN_DATE  DATE := PLAN_DATE;
    V_CONFIRM    VARCHAR2(10) := CONFIRM; --CONFIRM标识, Y:报工确认  N：报工解锁
    V_ERR_MSG    VARCHAR2(1000);
    V_BEGIN_TIME TIMESTAMP := SYSTIMESTAMP;
    V_PROC_NAME  VARCHAR2(1000) := 'SYS_SAVELOG_EXAMPLE/' ||
                                   TO_CHAR(V_PLAN_DATE,
                                           'YYYY-MM-DD HH24:MI:SS') || '/' ||
                                   V_CONFIRM;
    V_EXE_MSG    CLOB;
  
  BEGIN
  
    DBMS_LOB.CREATETEMPORARY(V_EXE_MSG, TRUE); --初始化CLOB
    RESULT := 'OK';
  
    IF V_CONFIRM = 'Y' THEN
      V_ERR_MSG := '不允许确认今天或者今天之后的数据!'; --今天或者
      GOTO PROCESS_ERRMSG;
    END IF;
  
    COMMIT;
    DBMS_LOB.APPEND(V_EXE_MSG, '执行成功!');
  
    SYS_SAVELOG(V_PROC_NAME, 'Y', V_EXE_MSG, V_BEGIN_TIME);
    --异常处理
    RETURN; --若为顺序执行到此位置，则直接返回
    <<PROCESS_ERRMSG>>
    SYS_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);
      SYS_SAVELOG(V_PROC_NAME, 'N', V_EXE_MSG, V_BEGIN_TIME);
      RESULT := V_ERR_MSG; --错误号对应的信息
  END;

• PLSQL 中文乱码解决

select userenv('language') from dual; 
-- 配置环境变量
-- NLS_LANG=sql查询出来的值

   在写存储过程中一般需要书写过程运行日志，但是工作这么长时间自己从没认真研究过调用日志存储过程的问题。现因项目整合需要做日志监控，才发现日志的重要性：

create or replace procedure bo_dw.sp_sys_log 
(
    oi_task_id         in out integer ,
    iv_task_name       in     varchar2,
    iv_table_name      in     varchar2,
    iv_task_sign       in     varchar2,
    ii_task_status     in     integer ,
    iv_task_log        in     varchar2,
    iv_task_pos        in     varchar2,
    iv_task_step_stime in     varchar2,
    ii_rowcount        in     integer default null
)
/** HEAD
  * @name sp_sys_log
  * @caption 日志记录
  * @type 日志
  * @parameter oi_task_id     inout integer   任务ID，任务状态为1时做输出参数，任务状态为0和其它时做输入参?
  * @parameter iv_task_name   in    varchar2  任务名称
  * @parameter iv_table_name  in    varchar2  目标表名
  * @parameter iv_task_sign   in    varchar2  任务标志，任务的统计时间
  * @parameter ii_task_status in    integer   任务状态，0 程序运行完成，1 程序运行中，其它 出错代码
  * @parameter iv_task_log    in    varchar2  任务日志，正常时为程序运行中或程序运行完成，出错时为出错信息
  * @parameter iv_task_pos    in    varchar2  任务位置，程序中的出错位?
  * @parameter ii_rowcount    int   integer   数据量，可选参数, 缺省值为NULL
  * @description 日志记录
  * @target vgopsys#tb_sys_log 单条日志表
  */
is
  PRAGMA AUTONOMOUS_TRANSACTION;
  vv_task_memo varchar2(2000) ;
begin
    /**  --表结构
        create table bo_dw.tb_sys_log
        (
          task_id     number,             -- 任务ID
          task_name   varchar2(30),       -- 程序名称
          table_name  varchar2(30),       -- 目标表名
          task_sign   varchar2(20),       -- 任务标志
          start_time  date,               -- 程序开始时间
          end_time    date,               -- 程序结束时间或出错时?
          task_status number,             -- 任务状态 1开始 0完成 其他失败
          task_log    varchar2(200),      -- 任务日志:正常时为程序运行中或程序运行完成，出错时为出错信息
          task_pos    varchar2(50),       -- 任务位置
          task_remark varchar2(2000)      -- 任务备注：  任务位置：开始时间yyyymmdd ：数据量 （分隔符chr(10)）
        );

        序号
        -- Create sequence
        create sequence seq_sys_proc_log
        maxvalue 9999999999999999999999
        start with 1
        increment by 1;
      */

    /** @description 对不同的任务状态，进行不同的日志处理 */

    vv_task_memo :='起止时间：'||iv_task_step_stime||' - '||to_char(sysdate,'dd hh24:mi:ss')||'|'|| '位置：'||iv_task_pos||'|'||'sql记录数：'||ii_rowcount;

    if ii_task_status = 1 then
        select bo_dw.seq_sys_proc_log.nextval into oi_task_id from dual;
        insert into bo_dw.tb_sys_log
        (
            task_id,            -- 任务ID
            task_name,          -- 程序名称
            table_name,         -- 目标表名
            task_sign,          -- 任务标志
            start_time,         -- 程序开始时间
            end_time,           -- 程序结束时间或出错时间
            task_status,        -- 任务状态
            task_log,           -- 任务日志
            task_pos,           -- 任务位置
            task_memo
        )
        values
        (
            oi_task_id     ,
            iv_task_name   ,
            iv_table_name  ,
            iv_task_sign   ,
            sysdate        ,
            null           ,
            1              ,
            '程序运行中'    ,
            iv_task_pos    ,
            vv_task_memo
        );
    elsif ii_task_status = 2 then
        update bo_dw.tb_sys_log
           set task_pos = iv_task_pos,
               task_memo= task_memo || chr(10) ||  vv_task_memo
         where task_id = oi_task_id;
    elsif ii_task_status = 0 then
        update bo_dw.tb_sys_log
           set end_time = sysdate,
               task_status = 0,
               task_log = '程序运行完成',
               task_pos = iv_task_pos,
               task_memo= task_memo || chr(10) ||  vv_task_memo
         where task_id = oi_task_id;
    else
        update bo_dw.tb_sys_log
           set end_time = sysdate,
               task_status = ii_task_status,
               task_log = substr(iv_task_log, 1, 200),
               task_pos = iv_task_pos /*,
               task_memo= task_memo || chr(10) ||  vv_task_memo*/
         where task_id = oi_task_id;
    end if;
    commit;
end;

  这是一个例子，认真琢磨一下还是能明白的。这里在procedure定义中有个PRAGMA AUTONOMOUS_TRANSACTION，百度解释为 自治事务 。对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序。

自治事务的特点:

第一,这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。

第二,在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。

关于自治事务解释来源链接：点击打开链接