我們經(jīng)常會(huì)寫很多pkg，procedures等過(guò)程。這些過(guò)程不止在程序中用到，并且也會(huì)提供給運(yùn)營(yíng)人員手動(dòng)執(zhí)行。由于手動(dòng)執(zhí)行是不可預(yù)知的，所以我們需要記錄下誰(shuí)進(jìn)行了這些操作，以便有數(shù)據(jù)問(wèn)題出現(xiàn)時(shí)可以進(jìn)行跟蹤。

首先我們建立一張日志表，用于記錄pkg的執(zhí)行情況。

create table PKG_RUN_LOG
(
SEQNUMBER not null,
SESSION_USER VARCHAR2(100),
OS_USERVARCHAR2(100),
IP_ADDRESSVARCHAR2(20),
PKG_NAMEVARCHAR2(200) not null,
START_TIMEDATE not null,
END_TIMEDATE
)

然后建立一個(gè)SEQ，用于日志表的SEQ：

-- Create sequence
create sequence LOG_SEQ
minvalue 100000000000001
maxvalue 999999999999999
start with 100000000000021
increment by 1
cache 20
order;

然后建立我們所需的pkg：

create or replace package LOG_PKG is

procedure Write_pkg_start(v_pkg_name inpkg_run_log.pkg_name%type,
v_seq_no outpkg_run_log.seq%type);

procedure Write_pkg_end(v_seq_no inpkg_run_log.seq%type);

procedureGet_User(v_seq_noout pkg_run_log.seq%type,
v_session_user out pkg_run_log.session_user%type,
os_userout pkg_run_log.os_user%type,
v_ip_address outpkg_run_log.ip_address%type);

end LOG_PKG;

create or replace package body LOG_PKG is
procedure Write_pkg_start(v_pkg_namepkg_run_log.pkg_name%type,
v_seq_no outpkg_run_log.seq%type) as
pragmaautonomous_transaction;--自治事務(wù)
v_session_user pkg_run_log.session_user%type;
os_userpkg_run_log.os_user%type;
v_ip_addresspkg_run_log.ip_address%type;
begin
Get_User(v_seq_no=> v_seq_no,
v_session_user => v_session_user,
os_user=> os_user,
v_ip_address =>v_ip_address);

insert intopkg_run_log
(seq,
session_user,
os_user,
ip_address,
pkg_name,
start_time,
end_time)
values
(v_seq_no,
v_session_user,
os_user,
v_ip_address,
v_pkg_name,
sysdate,
null);
commit;

exception
when othersthen
rollback;
end Write_pkg_start;

procedure Write_pkg_end(v_seq_no inpkg_run_log.seq%type) as
pragmaautonomous_transaction;--自治事務(wù)
begin
updatepkg_run_log t set t.end_time = sysdate where t.seq =v_seq_no;
commit;

exception
when othersthen
rollback;
end Write_pkg_end;

procedureGet_User(v_seq_noout pkg_run_log.seq%type,
v_session_user out pkg_run_log.session_user%type,
os_userout pkg_run_log.os_user%type,
v_ip_address outpkg_run_log.ip_address%type) as
begin
v_session_user := sys_context('USERENV', 'SESSION_USER');
os_user:= sys_context('USERENV', 'OS_USER');
v_ip_address :=sys_context('USERENV', 'IP_ADDRESS');

selectlog_seq.nextval into v_seq_no from dual;
end Get_User;

end LOG_PKG;

注意這里需要把pkg的過(guò)程定義為“自治事務(wù)”。

最后，讓我們來(lái)測(cè)試一下：

declare
v_seq number := null;

begin

LOG_PKG.Write_pkg_start(v_pkg_name=> 'abcdefg',v_seq_no =>v_seq);
...執(zhí)行你的具體業(yè)務(wù)邏輯的過(guò)程、pkg
log_pkg.Write_pkg_end(v_seq);

end;

查詢表，可以看到如下記錄：

愛(ài)華網(wǎng)本文地址 » http://www.klfzs.com/a/25101014/200153.html