Oracle中怎樣通過觸發(fā)器記錄每個(gè)語(yǔ)句影響總行數(shù),很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來學(xué)習(xí)下,希望你能有所收獲。
創(chuàng)新互聯(lián)公司專注于企業(yè)成都全網(wǎng)營(yíng)銷、網(wǎng)站重做改版、管城網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、html5、商城網(wǎng)站開發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù),價(jià)格優(yōu)惠性價(jià)比高,為管城等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
需求產(chǎn)生:
業(yè)務(wù)系統(tǒng)中,有一步“抽數(shù)”流程,就是把一些數(shù)據(jù)從其它服務(wù)器同步到本庫(kù)的目標(biāo)表。這個(gè)過程有可能 多人同時(shí)抽數(shù),互相影響。有測(cè)試人員反應(yīng),原來抽過的數(shù),偶爾就無緣無故的找不到了,有時(shí)又會(huì)出來重復(fù)行。這個(gè)問題產(chǎn)生肯定是抽數(shù)邏輯問題以及并行的問題了!但他們提了一個(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í)間,影響行寫到記錄表里就行了。
但問題來了,在語(yǔ)句觸發(fā)器中,無法得到該語(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í)行開始、結(jié)束時(shí)間,影響行數(shù),監(jiān)控?cái)?shù)據(jù)行上的某些列信息。
包:主要是3個(gè)存儲(chǔ)過程,
語(yǔ)句開始存儲(chǔ)過程:用關(guān)聯(lián)數(shù)組來記錄目標(biāo)表名和開始時(shí)間,把其它值清0.
行操作存儲(chǔ)過程:把關(guān)聯(lián)數(shù)組目標(biāo)表所對(duì)應(yīng)的記錄數(shù)加1。
語(yǔ)句結(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; --語(yǔ)句開始。 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é)束,寫到日志表中。 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ù)組中的一條記錄寫入庫(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í)行開始。 v_type:語(yǔ)句類型,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í),就可以寫表了 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ā)器:
--語(yǔ)句開始前 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ì)有類似信息:
i,i,-i,-i ,這表示同一時(shí)間有2個(gè)語(yǔ)句在插入目標(biāo)表。
i,d,-d,-i 表示在插入時(shí),有一個(gè)刪除語(yǔ)句也在執(zhí)行。
當(dāng)平臺(tái)多人在用時(shí),避免不了有同時(shí)操作同一張表的情況,通過這個(gè)列的值,可以觀察到數(shù)據(jù)庫(kù)的執(zhí)行情況!
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝您對(duì)創(chuàng)新互聯(lián)的支持。
網(wǎng)站標(biāo)題:Oracle中怎樣通過觸發(fā)器記錄每個(gè)語(yǔ)句影響總行數(shù)
本文URL:http://muchs.cn/article12/jchcgc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供虛擬主機(jī)、云服務(wù)器、、域名注冊(cè)、網(wǎng)站改版、外貿(mào)建站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)