Oracle中怎樣通過(guò)觸發(fā)器記錄每個(gè)語(yǔ)句影響總行數(shù)

143次閱讀

共計(jì) 5449 個(gè)字符，預(yù)計(jì)需要花費(fèi) 14 分鐘才能閱讀完成。

Oracle 中怎樣通過(guò)觸發(fā)器記錄每個(gè)語(yǔ)句影響總行數(shù)，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

需求產(chǎn)生：

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

技術(shù)選擇：

觸發(fā)器分為“語(yǔ)句級(jí)觸發(fā)器”和“行級(jí)觸發(fā)器”。語(yǔ)句級(jí)是每一個(gè)語(yǔ)句執(zhí)行前后觸發(fā)一次操作，如果我在每一個(gè) SQL 語(yǔ)句執(zhí)行后，把表名，時(shí)間，影響行寫(xiě)到記錄表里就行了。

但問(wèn)題來(lái)了，在語(yǔ)句觸發(fā)器中，無(wú)法得到該語(yǔ)句的行數(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í)行開(kāi)始、結(jié)束時(shí)間，影響行數(shù)，監(jiān)控?cái)?shù)據(jù)行上的某些列信息。

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

語(yǔ)句開(kāi)始存儲(chǔ)過(guò)程：用關(guān)聯(lián)數(shù)組來(lái)記錄目標(biāo)表名和開(kāi)始時(shí)間，把其它值清 0.

行操作存儲(chǔ)過(guò)程：把關(guān)聯(lián)數(shù)組目標(biāo)表所對(duì)應(yīng)的記錄數(shù)加 1。

語(yǔ)句結(jié)束存儲(chǔ)過(guò)程：把關(guān)聯(lián)數(shù)組目標(biāo)表中統(tǒng)計(jì)的信息寫(xiě)到日志表。

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

代碼：

日志表和序列：

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ù)組類(lèi)型，它就是日志表的關(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; -- 語(yǔ)句開(kāi)始。 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 :=   -- 語(yǔ)句結(jié)束，寫(xiě)到日志表中。 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ù)組中的一條記錄寫(xiě)入庫(kù)里  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 語(yǔ)句執(zhí)行開(kāi)始。 v_type：語(yǔ)句類(lèi)型，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 語(yǔ)句對(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è)語(yǔ)句進(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; -- 語(yǔ)句結(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 ||  ,  -- 語(yǔ)句退出，將并行標(biāo)志位減一。  當(dāng)它為 0 時(shí)，就可以寫(xiě)表了  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ǔ)過(guò)程即可。假定我要監(jiān)控 T_A 的表：

三個(gè)觸發(fā)器：

-- 語(yǔ)句開(kāi)始前  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; -- 語(yǔ)句結(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ì)有類(lèi)似信息：

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

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

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

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對(duì)丸趣 TV 的支持。

正文完