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

需求产生:

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

技术选择:

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

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

代码结构:

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

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

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

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

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

代码:

日志表和序列:

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 的表:

需要给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;

在行级触发器时里,调用了:pck_cslog.oneachrow_cs方法。 这个方法接受四个参数:

  procedure oneachrow_cs(v_tblname t_cslog.tblname%type,  --表名
                         v_type    varchar2,              --DML类型,i,u,d
                         v_code    varchar2 := '',        --监控数据行的某个主键列
                         v_rq      date := '');           --监控数据行的时间范围

后两个参数有什么用呢?

如果你不关心插入或删除行的信息的话,那你直接调用pck_cslog.oneachrow_cs('t_a', v_type);就可以了。

如果你监控这些数据行的一些列值,像我这里的业务系统,多数表都是一个主键+日期+业务数据这种结构。这里假设要监控T_TRADE表,监控影响的code和rq列,那么调用时按下面方法写,就能监控一条SQL调用影响的所有主键及日期范围了:

pck_cslog.oneachrow_cs('T_TRADE', v_type, :new.code,  :new.rq);

测试成果:

触发器建好了,可以测试插入删除了。先插入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;

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

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

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

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏源哥的专栏

由于查询语句中日期的格式引起的问题

我这边有一个系统,在一个环境下运行完全正常,但迁到另外一个环境后,其中一个查询功能就莫名其妙的出现了问题,我通过检查,发现有一个很复杂的查询语句,在一个数据库环...

7410
来自专栏xingoo, 一个梦想做发明家的程序员

程序猿的日常——Mybatis现学现卖

最近有一个小项目需求,需要用spring mvc + mybatis实现一个复杂的配置系统。其中遇到了很多不太常见的问题,在这里特意记录下: 主要涉及的内容有...

36670
来自专栏个人随笔

MySQL基础补充

  结构语言分类  DDL(数据定义语言)  create  drop  alter   创建删除以及修改数据库,表,存储过程,触发器,索引....  D...

308120
来自专栏PHP实战技术

Mysql存储过程从0开始(上)

1、首先你要明白,mysql也是一种语言,他也可以编写程序,也是支持逻辑判断,if,elseif,else,switch,while等等的判断 2、mysql赋...

29360
来自专栏精讲JAVA

Java面试之数据库面试题

触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可...

16320
来自专栏java技术学习之道

JAVA设计模式之单例模式

15330
来自专栏岑玉海

sqoop 常用命令整理(一)

  这些内容是从sqoop的官网整理出来的,是1.4.3版本的Document,如果有错误,希望大家指正。       1.使用sqoop导入数据   sqoo...

43060
来自专栏python成长之路

(解释文)My SQL中主键为0和主键自排约束的关系

20150
来自专栏PingCAP的专栏

TiDB 源码阅读系列文章(四)Insert 语句概览

本文为 TiDB 源码阅读系列文章的第四篇。上一篇文章简单介绍了整体流程,无论什么语句,大体上是在这个框架下运行,DDL 语句也不例外。

44950
来自专栏我爱编程

Day24访问数据库

使用SQLite SQLite是一种嵌入式数据库,它的数据库就是一个文件。由于SQLite本身是C写的,而且体积很小,所以,经常被集成到各种应用程序中,甚至在i...

38640

扫码关注云+社区

领取腾讯云代金券