需求產(chǎn)生：

業(yè)務(wù)系統(tǒng)中，有一步“抽數(shù)”流程，就是把一些數(shù)據(jù)從其它服務(wù)器同步到本庫的目標(biāo)表。這個(gè)過程有可能 多人同時(shí)抽數(shù)，互相影響。有測(cè)試人員反應(yīng)，原來抽過的數(shù)，偶爾就無緣無故的找不到了，有時(shí)又會(huì)出來重復(fù)行。這個(gè)問題產(chǎn)生肯定是抽數(shù)邏輯問題以及并行的問題了!但他們提了一個(gè)簡單的需求：想知道什么時(shí)候數(shù)據(jù)被刪除了，什么時(shí)候插入了，我需要監(jiān)控“表的每一次變更”!

技術(shù)選擇：

***就想到觸發(fā)器，這樣能在不涉及業(yè)務(wù)系統(tǒng)的代碼情況下，實(shí)現(xiàn)監(jiān)控。觸發(fā)器分為“語句級(jí)觸發(fā)器”和“行級(jí)觸發(fā)器”。語句級(jí)是每一個(gè)語句執(zhí)行前后觸發(fā)一次操作，如果我在每一個(gè)SQL語句執(zhí)行后，把表名，時(shí)間，影響行寫到記錄表里就行了。

但問題來了，在語句觸發(fā)器中，無法得到該語句的行數(shù)，sql%rowcount 在觸發(fā)器里報(bào)錯(cuò)。只能用行級(jí)觸發(fā)器去統(tǒng)計(jì)行數(shù)!

代碼結(jié)構(gòu)：

整個(gè)監(jiān)控?cái)?shù)據(jù)行的功能包含： 一個(gè)日志表，包，序列。

日志表：記錄目標(biāo)表名，SQL執(zhí)行開始、結(jié)束時(shí)間，影響行數(shù)，監(jiān)控?cái)?shù)據(jù)行上的某些列信息。

包：主要是3個(gè)存儲(chǔ)過程，

1. 語句開始存儲(chǔ)過程：用關(guān)聯(lián)數(shù)組來記錄目標(biāo)表名和開始時(shí)間，把其它值清0.
2. 行操作存儲(chǔ)過程：把關(guān)聯(lián)數(shù)組目標(biāo)表所對(duì)應(yīng)的記錄數(shù)加1。
3. 語句結(jié)束存儲(chǔ)過程：把關(guān)聯(lián)數(shù)組目標(biāo)表中統(tǒng)計(jì)的信息寫到日志表。

序列： 用于生成日志表的主鍵

代碼：

日志表和序列：

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 
  --聲明一個(gè)關(guān)聯(lián)數(shù)組類型，它就是日志表的關(guān)聯(lián)數(shù)組 
  type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type; 
  --聲明這個(gè)關(guān)聯(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 := ''); 
  --語句結(jié)束，寫到日志表中。 
  procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2); 
end pck_cslog; 
 
--包體 
create or replace package body pck_cslog is 
  --私有方法，把關(guān)聯(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; 
  --私有方法，清除關(guān)聯(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; 
  --某個(gè)SQL語句執(zhí)行開始。 v_type：語句類型，insert時(shí)為 i, update時(shí)為u ，delete時(shí)為 d 
  procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is 
  begin 
     --如果關(guān)聯(lián)數(shù)組中不存在，初始賦值。 否則表示，同時(shí)有insert，delete語句對(duì)目標(biāo)表操作。 
    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 := ' '; --初始給一個(gè)空格 
      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 || ','; 
    ----***個(gè)語句進(jìn)入，顯示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ù)，代碼，起、止時(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; 
  --語句結(jié)束。  
  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 || ','; 
      --語句退出，將并行標(biāo)志位減一。 當(dāng)它為0時(shí)，就可以寫表了 
      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)控的一個(gè)目標(biāo)表，只需要給它添加三個(gè)觸發(fā)器，調(diào)用包里對(duì)應(yīng)的存儲(chǔ)過程即可。 假定我要監(jiān)控 T_A 的表：

三個(gè)觸發(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; 
 
--語句結(jié)束后 
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; 
 
--行級(jí)觸發(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)控的行的某一列的值傳入包體，這樣***會(huì)記錄到日志表 
  elsif v_type = 'd' then 
    pck_cslog.oneachrow_cs('t_a', v_type, :old.name); 
  end if; 
end;  

測(cè)試成果：

觸發(fā)器建好了，可以測(cè)試插入刪除了。先插入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)控刪除的行：

并行時(shí)，在bz列中，可能會(huì)有類似信息：

i,i,-i,-i ,這表示同一時(shí)間有2個(gè)語句在插入目標(biāo)表。

i,d,-d,-i 表示在插入時(shí)，有一個(gè)刪除語句也在執(zhí)行。

當(dāng)平臺(tái)多人在用時(shí)，避免不了有同時(shí)操作同一張表的情況，通過這個(gè)列的值，可以觀察到數(shù)據(jù)庫的執(zhí)行情況!