Oracle中怎樣通過觸發器記錄每個語句影響總行數

Oracle中怎樣通過觸發器記錄每個語句影響總行數，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

需求產生：

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

技術選擇：

觸發器分為“語句級觸發器”和“行級觸發器”。語句級是每一個語句執行前后觸發一次操作，如果我在每一個SQL語句執行后，把表名，時間，影響行寫到記錄表里就行了。

但問題來了，在語句觸發器中，無法得到該語句的行數，sql%rowcount 在觸發器里報錯。只能用行級觸發器去統計行數!

代碼結構：

整個監控數據行的功能包含： 一個日志表，包，序列。

日志表：記錄目標表名，SQL執行開始、結束時間，影響行數，監控數據行上的某些列信息。

包：主要是3個存儲過程，

1. 語句開始存儲過程：用關聯數組來記錄目標表名和開始時間，把其它值清0.

2. 行操作存儲過程：把關聯數組目標表所對應的記錄數加1。

3. 語句結束存儲過程：把關聯數組目標表中統計的信息寫到日志表。

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

代碼：

日志表和序列：

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   --聲明一個關聯數組類型，它就是日志表的關聯數組   type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type;   --聲明這個關聯數組的變量。   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   --私有方法，把關聯數組中的一條記錄寫入庫里   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;   --私有方法，清除關聯數組中的一條記錄   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語句執行開始。 v_type：語句類型，insert時為 i, update時為u ，delete時為 d   procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is   begin      --如果關聯數組中不存在，初始賦值。 否則表示，同時有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       --行數，代碼，起、止時間       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;

綁定觸發器：

有了以上代碼后，想要監控的一個目標表，只需要給它添加三個觸發器，調用包里對應的存儲過程即可。 假定我要監控 T_A 的表：

三個觸發器：

--語句開始前 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;  --行級觸發器 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);  --此處是把監控的行的某一列的值傳入包體，這樣***會記錄到日志表   elsif v_type = 'd' then     pck_cslog.oneachrow_cs('t_a', v_type, :old.name);   end if; end;

測試成果：

觸發器建好了，可以測試插入刪除了。先插入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列，還可以顯示監控刪除的行：

并行時，在bz列中，可能會有類似信息：

i,i,-i,-i ,這表示同一時間有2個語句在插入目標表。

i,d,-d,-i 表示在插入時，有一個刪除語句也在執行。

當平臺多人在用時，避免不了有同時操作同一張表的情況，通過這個列的值，可以觀察到數據庫的執行情況!

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。