目标 实现记录执行存储过程的开始时间，结束时间，运行状态，错误信息等，以函数封装日志记录的方式，存储调用函数 源码 存储过程模版 create or replace procedure proc_xx is --修改标志返回值 v_affect_line number; proid number;begin --调用更改标志函
目标实现记录执行存储过程的开始时间，结束时间，运行状态，错误信息等，以函数封装日志记录的方式，存储调用函数源码存储过程模版create or replace procedure proc_xx is --修改标志返回值 v_affect_line number; proid number;begin --调用更改标志函数，将进程改为运行中's' v_affect_line := insert_log(proid,'proc_xx',wifi.global_param.status_start); --逻辑处理函数调用 v_affect_line := wifi.func_xx(); --修改标志，成功置c v_affect_line := wifi.modify_status(proid,wifi.global_param.status_close,'');exception when others then --调用更改标志函数,将进程改为出错'f' v_affect_line := modify_status(proid,wifi.global_param.status_failed,wifi.global_param.log_exception);end;
函数模版create or replace function func_xx return int is begin.... return 1;exception when others then set_error_log (); return 0; end;
相关日志记录函数create or replace function insert_log ( proid out number, program_name in varchar2, status in varchar2) return numberis ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -功能描述: 初始化日志----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------begin insert into program_log values (program_log_seq.nextval, to_char (sysdate, 'yyyymmdd'), program_name, sysdate, null, status, ''); select program_log_seq.currval into proid from dual; commit; return 1;exception when others then return 0; end; create or replace function modify_status ( proid in number, status in varchar2, prodesc in varchar2) return number is thisdate date;begin ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -功能描述: 修改日志表存储过程运行状态,记录开始时间 结束时间 出错信息---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thisdate := sysdate; --更新状态 出错信息 update program_log set program_status=status, memo=prodesc where id=proid; --更新日期：如果是运行中，更新起始运行日期；如果是运行成功或者运行失败，更新结束运行日期 if status='s' then update program_log set start_date=thisdate where id=proid; elsif status='c' then update program_log set end_date=thisdate where id=proid; elsif status='f' then update program_log set end_date=thisdate where id=proid; commit; raise_application_error(-20040,'status is f'); end if; /* elsif status='c' or status='f' then update program_log set end_date=thisdate where id=proid; end if;*/ commit; return 1;exception when others then raise_application_error(-20041,'status is f'); return 0;end;create or replace procedure set_error_log isbegin global_param.log_exception := 'error desc---'||sqlerrm; commit; raise no_data_found;end; -- procedure;create or replace package global_param islog_exception varchar2(2000):='';status_start varchar2(10):='s';status_close varchar2(10):='c';status_failed varchar2(10):='f';end;
创建日志表create table program_log( id number not null, batch_number varchar2(50), program_name varchar2(100), start_date date, end_date date, program_status varchar2(20), memo varchar2(2000))