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;