oracle存储过程错误日志

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

第一步、创建日志表，用来存放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;