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

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

需求产生:

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

技术选择:

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

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

代码结构:

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

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

包:主要是3个存储过程,

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

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

代码:

日志表和序列:

  1. create table T_CSLOG 
  2.   n_id     NUMBER not null
  3.   tblname  VARCHAR2(30) not null
  4.   sj1      DATE
  5.   sj2      DATE
  6.   i_hs     NUMBER, 
  7.   u_hs     NUMBER, 
  8.   d_hs     NUMBER, 
  9.   portcode CLOB, 
  10.   startrq  DATE
  11.   endrq    DATE
  12.   bz       VARCHAR2(100), 
  13.   n        NUMBER 
  14. create index IDX_T_CSLOG1 on T_CSLOG (TBLNAME, SJ1, SJ2) 
  15. alter table T_CSLOG  add constraint PRIKEY_T_CSLOG primary key (N_ID) 
  16.  
  17.     
  18. create sequence SEQ_T_CSLOG 
  19. minvalue 1 
  20. maxvalue 99999999999 
  21. start with 1 
  22. increment by 1 
  23. cache 20 
  24. cycle;  

 

包代码:

  1. --包头 
  2. create or replace package pck_cslog is 
  3.   --声明一个关联数组类型,它就是日志表的关联数组 
  4.   type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type; 
  5.   --声明这个关联数组的变量。 
  6.   cslog_tbl cslog_type; 
  7.   --语句开始。   
  8.   procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2); 
  9.   --行操作 
  10.   procedure oneachrow_cs(v_tblname t_cslog.tblname%type, 
  11.                          v_type    varchar2, 
  12.                          v_code    varchar2 := ''
  13.                          v_rq      date := ''); 
  14.   --语句结束,写到日志表中。 
  15.   procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2); 
  16. end pck_cslog; 
  17.  
  18. --包体 
  19. create or replace package body pck_cslog is 
  20.   --私有方法,把关联数组中的一条记录写入库里 
  21.   procedure write_cslog(v_tblname t_cslog.tblname%type) is 
  22.   begin 
  23.     if cslog_tbl.exists(v_tblname) then 
  24.       insert into t_cslog values cslog_tbl (v_tblname); 
  25.     end if; 
  26.   end
  27.   --私有方法,清除关联数组中的一条记录 
  28.   procedure clear_cslog(v_tblname t_cslog.tblname%type) is 
  29.   begin 
  30.     if cslog_tbl.exists(v_tblname) then 
  31.       cslog_tbl.delete(v_tblname); 
  32.     end if; 
  33.   end
  34.   --某个SQL语句执行开始。 v_type:语句类型,insert时为 i, update时为u ,delete时为 d 
  35.   procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is 
  36.   begin 
  37.      --如果关联数组中不存在,初始赋值。 否则表示,同时有insert,delete语句对目标表操作。 
  38.     if not cslog_tbl.exists(v_tblname) then 
  39.       cslog_tbl(v_tblname).n_id := seq_t_cslog.nextval; 
  40.       cslog_tbl(v_tblname).tblname := v_tblname; 
  41.       cslog_tbl(v_tblname).sj1 := sysdate; 
  42.       cslog_tbl(v_tblname).sj2 := null
  43.       cslog_tbl(v_tblname).i_hs := 0; 
  44.       cslog_tbl(v_tblname).u_hs := 0; 
  45.       cslog_tbl(v_tblname).d_hs := 0; 
  46.       cslog_tbl(v_tblname).portcode := ' '--初始给一个空格 
  47.       cslog_tbl(v_tblname).startrq := to_date('9999''yyyy'); 
  48.       cslog_tbl(v_tblname).endrq := to_date('1900''yyyy'); 
  49.       cslog_tbl(v_tblname).n := 0; 
  50.     end if; 
  51.     cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname).bz || v_type || ','
  52.     ----***个语句进入,显示1,如果以后并行,则该值递增。 
  53.     cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n + 1;   
  54.   end
  55.   --每行操作。 
  56.   procedure oneachrow_cs(v_tblname t_cslog.tblname%type, 
  57.                          v_type    varchar2, 
  58.                          v_code    varchar2 := ''
  59.                          v_rq      date := ''is 
  60.   begin 
  61.     if cslog_tbl.exists(v_tblname) then 
  62.       --行数,代码,起、止时间 
  63.       if v_type = 'i' then 
  64.         cslog_tbl(v_tblname).i_hs := cslog_tbl(v_tblname).i_hs + 1; 
  65.       elsif v_type = 'u' then 
  66.         cslog_tbl(v_tblname).u_hs := cslog_tbl(v_tblname).u_hs + 1; 
  67.       elsif v_type = 'd' then 
  68.         cslog_tbl(v_tblname).d_hs := cslog_tbl(v_tblname).d_hs + 1; 
  69.       end if; 
  70.        
  71.       if v_code is not null and 
  72.          instr(cslog_tbl(v_tblname).portcode, v_code) = 0 then 
  73.         cslog_tbl(v_tblname).portcode := cslog_tbl(v_tblname).portcode || ',' || v_code; 
  74.       end if; 
  75.      
  76.       if v_rq is not null then 
  77.         if v_rq > cslog_tbl(v_tblname).endrq then 
  78.           cslog_tbl(v_tblname).endrq := v_rq; 
  79.         end if; 
  80.         if v_rq < cslog_tbl(v_tblname).startrq then 
  81.           cslog_tbl(v_tblname).startrq := v_rq; 
  82.         end if; 
  83.       end if; 
  84.     end if; 
  85.   end
  86.   --语句结束。  
  87.   procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is 
  88.   begin 
  89.     if cslog_tbl.exists(v_tblname) then 
  90.       cslog_tbl(v_tblname).bz := cslog_tbl(v_tblname) 
  91.                                  .bz || '-' || v_type || ','
  92.       --语句退出,将并行标志位减一。 当它为0时,就可以写表了 
  93.       cslog_tbl(v_tblname).n := cslog_tbl(v_tblname).n - 1; 
  94.       if cslog_tbl(v_tblname).n = 0 then 
  95.         cslog_tbl(v_tblname).sj2 := sysdate; 
  96.         write_cslog(v_tblname); 
  97.         clear_cslog(v_tblname); 
  98.       end if; 
  99.     end if; 
  100.   end
  101.  
  102. begin 
  103.   null
  104. end pck_cslog;  

绑定触发器:

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

 

三个触发器:

  1. --语句开始前 
  2. create or replace trigger tri_onb_t_a 
  3.   before insert or delete or update on t_a 
  4. declare 
  5.   v_type varchar2(1); 
  6. begin 
  7.   if inserting then    v_type := 'i';  elsif updating then    v_type := 'u';  elsif deleting then    v_type := 'd';  end if; 
  8.   pck_cslog.onbegin_cs('t_a', v_type); 
  9. end
  10.  
  11. --语句结束后 
  12. create or replace trigger tri_one_t_a 
  13.   after insert or delete or update on t_a 
  14. declare 
  15.   v_type varchar2(1); 
  16. begin 
  17.   if inserting then    v_type := 'i';  elsif updating then    v_type := 'u';  elsif deleting then    v_type := 'd';  end if; 
  18.   pck_cslog.onend_cs('t_a', v_type); 
  19. end
  20.  
  21. --行级触发器 
  22. create or replace trigger tri_onr_t_a 
  23.   after insert or delete or update on t_a 
  24.   for each row 
  25. declare 
  26.   v_type varchar2(1); 
  27. begin 
  28.   if inserting then    v_type := 'i';  elsif updating then    v_type := 'u';  elsif deleting then    v_type := 'd';  end if; 
  29.   if v_type = 'i' or v_type = 'u' then 
  30.     pck_cslog.oneachrow_cs('t_a', v_type, :new.name);  --此处是把监控的行的某一列的值传入包体,这样***会记录到日志表 
  31.   elsif v_type = 'd' then 
  32.     pck_cslog.oneachrow_cs('t_a', v_type, :old.name); 
  33.   end if; 
  34. end 

测试成果:

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

  1. declare 
  2.   i number; 
  3. begin 
  4.   for i in 1 .. 100 loop 
  5.     insert into t_a values (i, i || 'shenjunjian'); 
  6.   end loop; 
  7.   commit
  8.    
  9.   delete from t_a   where id > 79; 
  10.   delete from t_a   where id < 40; 
  11.   commit
  12. end

 

clob列,还可以显示监控删除的行:

 

并行时,在bz列中,可能会有类似信息:

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

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

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

责任编辑:庞桂玉 来源: noonoo的博客
相关推荐

2011-05-20 14:06:25

Oracle触发器

2011-05-19 14:29:49

Oracle触发器语法

2011-04-14 13:54:22

Oracle触发器

2009-11-18 13:15:06

Oracle触发器

2010-09-01 16:40:00

SQL删除触发器

2010-04-23 12:50:46

Oracle触发器

2010-04-15 15:32:59

Oracle操作日志

2010-04-09 13:17:32

2010-10-20 14:34:48

SQL Server触

2010-10-25 14:09:01

Oracle触发器

2010-04-09 09:07:43

Oracle游标触发器

2010-05-04 09:44:12

Oracle Trig

2011-04-19 10:48:05

Oracle触发器

2011-03-03 14:04:48

Oracle数据库触发器

2010-04-26 14:12:23

Oracle使用游标触

2011-03-03 09:30:24

downmoonsql登录触发器

2010-04-29 10:48:10

Oracle序列

2010-04-26 14:03:02

Oracle使用

2011-03-28 10:05:57

sql触发器代码

2009-09-18 14:31:33

CLR触发器
点赞
收藏

51CTO技术栈公众号