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

需求产生：

站在用户的角度思考问题，与客户深入沟通，找到玉门网站设计与玉门网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：成都网站设计、网站制作、企业官网、英文网站、手机端网站、网站推广、域名注册、网络空间、企业邮箱。业务覆盖玉门地区。

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

技术选择：

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

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

代码结构：

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

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

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

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

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

代码：

日志表和序列：

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

包代码：

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

绑定触发器：

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

三个触发器：

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

测试成果：

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

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

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

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

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

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

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

当前文章：Oracle中，通过触发器，记录每个语句影响总行数
文章位置：http://www.csdahua.cn/qtweb/news45/136895.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 快上网

成都快上网为您推荐相关内容

用户体验知识

同城分类信息

买链接　　 　成都app制作　　 　国泰民安消防　　 　盐亭网站建设　　 　网站优化公司　　 　网站搜索引擎优化　　 　户外广告设计　　 　成都网站改版公司　　 　400电话办理　　 　LED显示屏　　 　联通机柜租用　　 　成都标识标牌设计制作　　 　南充网站制作　　 　成都网络营销公司　　 　定制网站设计　　 　绵竹网站建设　　 　成都企业网站维护　　 　微信公众号开发　　 　自适应网页设计　　 　研学实践