Oracle中，通过触发器，记录每个语句影响总行数

需求产生：

业务系统中，有一步“抽数”流程，就是把一些数据从其它服务器同步到本库的目标表。这个过程有可能多人同时抽数，互相影响。有测试人员反应，原来抽过的数，偶尔就无缘无故的找不到了，有时又会出来重复行。这个问题产生肯定是抽数逻辑问题以及并行的问题了!但他们提了一个简单的需求：想知道什么时候数据被删除了，什么时候插入了，我需要监控“表的每一次变更”!

技术选择：

***就想到触发器，这样能在不涉及业务系统的代码情况下，实现监控。触发器分为“语句级触发器”和“行级触发器”。语句级是每一个语句执行前后触发一次操作，如果我在每一个SQL语句执行后，把表名，时间，影响行写到记录表里就行了。

但问题来了，在语句触发器中，无法得到该语句的行数，sql%rowcount 在触发器里报错。只能用行级触发器去统计行数!

代码结构：

整个监控数据行的功能包含：一个日志表，包，序列。

日志表：记录目标表名，SQL执行开始、结束时间，影响行数，监控数据行上的某些列信息。

包：主要是3个存储过程，

语句开始存储过程：用关联数组来记录目标表名和开始时间，把其它值清0.
行操作存储过程：把关联数组目标表所对应的记录数加1。
语句结束存储过程：把关联数组目标表中统计的信息写到日志表。

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

代码：

日志表和序列：

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;