详解oracle中通过触发器记录每个语句影响总行数
需求产生：
业务系统中，有一步[抽数]流程，就是把一些数据从其它服务器同步到本库的目标表。这个过程有可能 多人同时抽数，互相影响。有测试人员反应，原来抽过的数，偶尔就无缘无故的 找不到 了，有时又会出来 重复行 。这个问题产生肯定是抽数逻辑问题以及并行的问题了！但他们提了一个简单的需求：想知道什么时候数据被删除了，什么时候插入了，我需要监控[ 表的每一次变更 ]！
技术选择：
第一就想到触发器，这样能在不涉及业务系统的代码情况下，实现监控。触发器分为[语句级触发器]和[行级触发器]。语句级是每一个语句执行前后触发一次操作，如果我在每一个SQL语句执行后，把表名，时间，影响行写到记录表里就行了。
但问题来了，在语句触发器中，无法得到该语句的行数， sql%rowcount 在触发器里报错。只能用行级触发器去统计行数！
代码结构：
整个监控数据行的功能包含： 一个日志表，包，序列。
日志表：记录目标表名，SQL执行开始、结束时间，影响行数，监控数据行上的某些列信息。
包：主要是3个存储过程，
语句开始存储过程：用关联数组来记录目标表名和开始时间，把其它值清0. 行操作存储过程：把关联数组目标表所对应的记录数加1。 语句结束存储过程：把关联数组目标表中统计的信息写到日志表。序列： 用于生成日志表的主键
代码：
日志表和序列：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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; |
包代码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
-包头 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 的表：
三个触发器：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
--语句开始前 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行，再随便删除一些行。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 表示在插入时，有一个删除语句也在执行。
当平台多人在用时，避免不了有同时操作同一张表的情况，通过这个列的值，可以观察到数据库的执行情况！
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！
原文链接：https://my.oschina.net/u/1540190/blog/1057785
查看更多关于详解oracle中通过触发器记录每个语句影响总行数的详细内容...