詳解oracle中通過觸發(fā)器記錄每個語句影響總行數(shù)
需求產(chǎn)生：
業(yè)務系統(tǒng)中，有一步“抽數(shù)”流程，就是把一些數(shù)據(jù)從其它服務器同步到本庫的目標表。這個過程有可能 多人同時抽數(shù)，互相影響。有測試人員反應，原來抽過的數(shù)，偶爾就無緣無故的找不到了，有時又會出來重復行。這個問題產(chǎn)生肯定是抽數(shù)邏輯問題以及并行的問題了！但他們提了一個簡單的需求：想知道什么時候數(shù)據(jù)被刪除了，什么時候插入了，我需要監(jiān)控“表的每一次變更”！
技術選擇：
第一就想到觸發(fā)器，這樣能在不涉及業(yè)務系統(tǒng)的代碼情況下，實現(xiàn)監(jiān)控。觸發(fā)器分為“語句級觸發(fā)器”和“行級觸發(fā)器”。語句級是每一個語句執(zhí)行前后觸發(fā)一次操作，如果我在每一個SQL語句執(zhí)行后，把表名，時間，影響行寫到記錄表里就行了。
但問題來了，在語句觸發(fā)器中，無法得到該語句的行數(shù)，sql%rowcount 在觸發(fā)器里報錯。只能用行級觸發(fā)器去統(tǒng)計行數(shù)！
代碼結構：
整個監(jiān)控數(shù)據(jù)行的功能包含： 一個日志表，包，序列。
日志表：記錄目標表名，SQL執(zhí)行開始、結束時間，影響行數(shù)，監(jiān)控數(shù)據(jù)行上的某些列信息。
包：主要是3個存儲過程，
- 語句開始存儲過程：用關聯(lián)數(shù)組來記錄目標表名和開始時間，把其它值清0.
- 行操作存儲過程：把關聯(lián)數(shù)組目標表所對應的記錄數(shù)加1。
- 語句結束存儲過程：把關聯(lián)數(shù)組目標表中統(tǒng)計的信息寫到日志表。
序列： 用于生成日志表的主鍵
代碼：
日志表和序列：
create table T_CSLOG
(
n_id NUMBER not null,
tblname VARCHAR2(30) not null,
sj1 DATE,
sj2 DATE,
i_hs NUMBER,
u_hs NUMBER,
d_hs NUMBER,
portcode CLOB,
startrq DATE,
endrq DATE,
bz VARCHAR2(100),
n NUMBER
)
create index IDX_T_CSLOG1 on T_CSLOG (TBLNAME, SJ1, SJ2)
alter table T_CSLOG add constraint PRIKEY_T_CSLOG primary key (N_ID)
create sequence SEQ_T_CSLOG
minvalue 1
maxvalue 99999999999
start with 1
increment by 1
cache 20
cycle;
包代碼：
-包頭
create or replace package pck_cslog is
--聲明一個關聯(lián)數(shù)組類型，它就是日志表的關聯(lián)數(shù)組
type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type;
--聲明這個關聯(lián)數(shù)組的變量。
cslog_tbl cslog_type;
--語句開始。
procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2);
--行操作
procedure oneachrow_cs(v_tblname t_cslog.tblname%type,
v_type varchar2,
v_code varchar2 := '',
v_rq date := '');
--語句結束，寫到日志表中。
procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2);
end pck_cslog;
--包體
create or replace package body pck_cslog is
--私有方法，把關聯(lián)數(shù)組中的一條記錄寫入庫里
procedure write_cslog(v_tblname t_cslog.tblname%type) is
begin
if cslog_tbl.exists(v_tblname) then
insert into t_cslog values cslog_tbl (v_tblname);
end if;
end;
--私有方法，清除關聯(lián)數(shù)組中的一條記錄
procedure clear_cslog(v_tblname t_cslog.tblname%type) is
begin
if cslog_tbl.exists(v_tblname) then
cslog_tbl.delete(v_tblname);
end if;
end;
--某個SQL語句執(zhí)行開始。 v_type：語句類型，insert時為 i, update時為u ，delete時為 d
procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is
begin
--如果關聯(lián)數(shù)組中不存在，初始賦值。 否則表示，同時有insert，delete語句對目標表操作。
if not cslog_tbl.exists(v_tblname) then
cslog_tbl(v_tblname).n_id := seq_t_cslog.nextval;
cslog_tbl(v_tblname).tblname := v_tblname;
cslog_tbl(v_tblname).sj1 := sysdate;
cslog_tbl(v_tblname).sj2 := null;
cslog_tbl(v_tblname).i_hs := 0;
cslog_tbl(v_tblname).u_hs := 0;
cslog_tbl(v_tblname).d_hs := 0;
cslog_tbl(v_tblname).portcode := ' '; --初始給一個空格
cslog_tbl(v_tblname).startrq := to_date('9999', 'yyyy');
cslog_tbl(v_tblname).endrq := to_date('1900', 'yyyy');
cslog_tbl(v_tblname).n := 0;
end if;
cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname).bz || v_type || ',';
----第一個語句進入，顯示1，如果以后并行，則該值遞增。
cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n + 1;
end;
--每行操作。
procedure oneachrow_cs(v_tblname t_cslog.tblname%type,
v_type varchar2,
v_code varchar2 := '',
v_rq date := '') is
begin
if cslog_tbl.exists(v_tblname) then
--行數(shù)，代碼，起、止時間
if v_type = 'i' then
cslog_tbl(v_tblname).i_hs := cslog_tbl(v_tblname).i_hs + 1;
elsif v_type = 'u' then
cslog_tbl(v_tblname).u_hs := cslog_tbl(v_tblname).u_hs + 1;
elsif v_type = 'd' then
cslog_tbl(v_tblname).d_hs := cslog_tbl(v_tblname).d_hs + 1;
end if;
if v_code is not null and
instr(cslog_tbl(v_tblname).portcode, v_code) = 0 then
cslog_tbl(v_tblname).portcode := cslog_tbl(v_tblname).portcode || ',' || v_code;
end if;
if v_rq is not null then
if v_rq > cslog_tbl(v_tblname).endrq then
cslog_tbl(v_tblname).endrq := v_rq;
end if;
if v_rq cslog_tbl(v_tblname).startrq then
cslog_tbl(v_tblname).startrq := v_rq;
end if;
end if;
end if;
end;
--語句結束。
procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is
begin
if cslog_tbl.exists(v_tblname) then
cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname)
.bz || '-' || v_type || ',';
--語句退出，將并行標志位減一。 當它為0時，就可以寫表了
cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n - 1;
if cslog_tbl(v_tblname).n = 0 then
cslog_tbl(v_tblname).sj2 := sysdate;
write_cslog(v_tblname);
clear_cslog(v_tblname);
end if;
end if;
end;
begin
null;
end pck_cslog;
綁定觸發(fā)器：
有了以上代碼后，想要監(jiān)控的一個目標表，只需要給它添加三個觸發(fā)器，調(diào)用包里對應的存儲過程即可。 假定我要監(jiān)控 T_A 的表：
三個觸發(fā)器：
--語句開始前
create or replace trigger tri_onb_t_a
before insert or delete or update on t_a
declare
v_type varchar2(1);
begin
if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if;
pck_cslog.onbegin_cs('t_a', v_type);
end;
--語句結束后
create or replace trigger tri_one_t_a
after insert or delete or update on t_a
declare
v_type varchar2(1);
begin
if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if;
pck_cslog.onend_cs('t_a', v_type);
end;
--行級觸發(fā)器
create or replace trigger tri_onr_t_a
after insert or delete or update on t_a
for each row
declare
v_type varchar2(1);
begin
if inserting then v_type := 'i'; elsif updating then v_type := 'u'; elsif deleting then v_type := 'd'; end if;
if v_type = 'i' or v_type = 'u' then
pck_cslog.oneachrow_cs('t_a', v_type, :new.name); --此處是把監(jiān)控的行的某一列的值傳入包體，這樣最后會記錄到日志表
elsif v_type = 'd' then
pck_cslog.oneachrow_cs('t_a', v_type, :old.name);
end if;
end;
測試成果：
觸發(fā)器建好了，可以測試插入刪除了。先插入100行，再隨便刪除一些行。
declare
i number;
begin
for i in 1 .. 100 loop
insert into t_a values (i, i || 'shenjunjian');
end loop;
commit;
delete from t_a where id > 79;
delete from t_a where id 40;
commit;
end;
clob列，還可以顯示監(jiān)控刪除的行：
并行時，在bz列中，可能會有類似信息：
i,i,-i,-i ,這表示同一時間有2個語句在插入目標表。
i,d,-d,-i 表示在插入時，有一個刪除語句也在執(zhí)行。
當平臺多人在用時，避免不了有同時操作同一張表的情況，通過這個列的值，可以觀察到數(shù)據(jù)庫的執(zhí)行情況！
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
您可能感興趣的文章:- Oracle中觸發(fā)器示例詳解
- Oracle觸發(fā)器trigger詳解
- Oracle觸發(fā)器用法實例詳解
- oracle監(jiān)控某表變動觸發(fā)器例子(監(jiān)控增,刪,改)
- Oracle創(chuàng)建主鍵自增表(sql語句實現(xiàn))及觸發(fā)器應用
- Oracle中游標Cursor基本用法詳解
- Oracle存儲過程游標用法分析
- Oracle顯示游標的使用及游標for循環(huán)
- 快速學習Oracle觸發(fā)器和游標
