主页 > 知识库 > 详解oracle中通过触发器记录每个语句影响总行数

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

热门标签:服务外包 网站排名优化 AI电销 百度竞价排名 地方门户网站 Linux服务器 铁路电话系统 呼叫中心市场需求

详解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;

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

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

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

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

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

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
您可能感兴趣的文章:
  • Oracle中触发器示例详解
  • Oracle触发器trigger详解
  • Oracle触发器用法实例详解
  • oracle监控某表变动触发器例子(监控增,删,改)
  • Oracle创建主键自增表(sql语句实现)及触发器应用
  • Oracle中游标Cursor基本用法详解
  • Oracle存储过程游标用法分析
  • Oracle显示游标的使用及游标for循环
  • 快速学习Oracle触发器和游标

标签:黄山 湖南 兰州 崇左 湘潭 衡水 仙桃 铜川

巨人网络通讯声明:本文标题《详解oracle中通过触发器记录每个语句影响总行数》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266