Oracle中怎样通过触发器记录每个语句影响总行数，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

需求产生：

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

技术选择：

触发器分为“语句级触发器”和“行级触发器”。语句级是每一个语句执行前后触发一次操作，如果我在每一个SQL语句执行后，把表名，时间，影响行写到记录表里就行了。

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

代码结构：

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

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

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

语句开始存储过程：用关联数组来记录目标表名和开始时间，把其它值清0.

行操作存储过程：把关联数组目标表所对应的记录数加1。

语句结束存储过程：把关联数组目标表中统计的信息写到日志表。

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

代码：

日志表和序列：

createtableT_CSLOG(n_idNUMBERnotnull,tblnameVARCHAR2(30)notnull,sj1DATE,sj2DATE,i_hsNUMBER,u_hsNUMBER,d_hsNUMBER,portcodeCLOB,startrqDATE,endrqDATE,bzVARCHAR2(100),nNUMBER)createindexIDX_T_CSLOG1onT_CSLOG(TBLNAME,SJ1,SJ2)altertableT_CSLOGaddconstraintPRIKEY_T_CSLOGprimarykey(N_ID)createsequenceSEQ_T_CSLOGminvalue1maxvalue99999999999startwith1incrementby1cache20cycle;

包代码：

--包头createorreplacepackagepck_cslogis--声明一个关联数组类型，它就是日志表的关联数组typecslog_typeistableoft_cslog%rowtypeindexbyt_cslog.tblname%type;--声明这个关联数组的变量。cslog_tblcslog_type;--语句开始。procedureonbegin_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2);--行操作procedureoneachrow_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2,v_codevarchar2:='',v_rqdate:='');--语句结束，写到日志表中。procedureonend_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2);endpck_cslog;--包体createorreplacepackagebodypck_cslogis--私有方法，把关联数组中的一条记录写入库里procedurewrite_cslog(v_tblnamet_cslog.tblname%type)isbeginifcslog_tbl.exists(v_tblname)theninsertintot_cslogvaluescslog_tbl(v_tblname);endif;end;--私有方法，清除关联数组中的一条记录procedureclear_cslog(v_tblnamet_cslog.tblname%type)isbeginifcslog_tbl.exists(v_tblname)thencslog_tbl.delete(v_tblname);endif;end;--某个SQL语句执行开始。v_type：语句类型，insert时为i,update时为u，delete时为dprocedureonbegin_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2)isbegin--如果关联数组中不存在，初始赋值。否则表示，同时有insert，delete语句对目标表操作。ifnotcslog_tbl.exists(v_tblname)thencslog_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;endif;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;--每行操作。procedureoneachrow_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2,v_codevarchar2:='',v_rqdate:='')isbeginifcslog_tbl.exists(v_tblname)then--行数，代码，起、止时间ifv_type='i'thencslog_tbl(v_tblname).i_hs:=cslog_tbl(v_tblname).i_hs+1;elsifv_type='u'thencslog_tbl(v_tblname).u_hs:=cslog_tbl(v_tblname).u_hs+1;elsifv_type='d'thencslog_tbl(v_tblname).d_hs:=cslog_tbl(v_tblname).d_hs+1;endif;ifv_codeisnotnullandinstr(cslog_tbl(v_tblname).portcode,v_code)=0thencslog_tbl(v_tblname).portcode:=cslog_tbl(v_tblname).portcode||','||v_code;endif;ifv_rqisnotnullthenifv_rq>cslog_tbl(v_tblname).endrqthencslog_tbl(v_tblname).endrq:=v_rq;endif;ifv_rq<cslog_tbl(v_tblname).startrqthencslog_tbl(v_tblname).startrq:=v_rq;endif;endif;endif;end;--语句结束。procedureonend_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2)isbeginifcslog_tbl.exists(v_tblname)thencslog_tbl(v_tblname).bz:=cslog_tbl(v_tblname).bz||'-'||v_type||',';--语句退出，将并行标志位减一。当它为0时，就可以写表了cslog_tbl(v_tblname).n:=cslog_tbl(v_tblname).n-1;ifcslog_tbl(v_tblname).n=0thencslog_tbl(v_tblname).sj2:=sysdate;write_cslog(v_tblname);clear_cslog(v_tblname);endif;endif;end;beginnull;endpck_cslog;

绑定触发器：

有了以上代码后，想要监控的一个目标表，只需要给它添加三个触发器，调用包里对应的存储过程即可。 假定我要监控 T_A 的表：

三个触发器：

--语句开始前createorreplacetriggertri_onb_t_abeforeinsertordeleteorupdateont_adeclarev_typevarchar2(1);beginifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;pck_cslog.onbegin_cs('t_a',v_type);end;--语句结束后createorreplacetriggertri_one_t_aafterinsertordeleteorupdateont_adeclarev_typevarchar2(1);beginifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;pck_cslog.onend_cs('t_a',v_type);end;--行级触发器createorreplacetriggertri_onr_t_aafterinsertordeleteorupdateont_aforeachrowdeclarev_typevarchar2(1);beginifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;ifv_type='i'orv_type='u'thenpck_cslog.oneachrow_cs('t_a',v_type,:new.name);--此处是把监控的行的某一列的值传入包体，这样***会记录到日志表elsifv_type='d'thenpck_cslog.oneachrow_cs('t_a',v_type,:old.name);endif;end;

测试成果：

触发器建好了，可以测试插入删除了。先插入100行，再随便删除一些行。

declareinumber;beginforiin1..100loopinsertintot_avalues(i,i||'shenjunjian');endloop;commit;deletefromt_awhereid>79;deletefromt_awhereid<40;commit;end;

clob列，还可以显示监控删除的行：

并行时，在bz列中，可能会有类似信息：

i,i,-i,-i ,这表示同一时间有2个语句在插入目标表。

i,d,-d,-i 表示在插入时，有一个删除语句也在执行。

当平台多人在用时，避免不了有同时操作同一张表的情况，通过这个列的值，可以观察到数据库的执行情况!

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。