目标
实现记录执行存储过程的开始时间，结束时间，运行状态，错误信息等，以函数封装日志记录的方式，存储调用函数
存储过程模版
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 NUMBER
IS
-------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------
---
-- -功能描述: 初始化日志
----------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
---------
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