个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章详解oracle中通过触发器记录每个语句影响总行数由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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

需求产生:

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

技术选择:

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

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

代码结构:

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

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

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

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

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

代码:

日志表和序列:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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;

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

包代码:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-包头
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 的表:

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

三个触发器:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--语句开始前
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行，再随便删除一些行.

?
1
2
3
4
5
6
7
8
9
10
11
12
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 ;

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

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

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

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

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

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

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

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

原文链接：https://my.oschina.net/u/1540190/blog/1057785 。

最后此篇关于详解oracle中通过触发器记录每个语句影响总行数的文章就讲到这里了,如果你想了解更多关于详解oracle中通过触发器记录每个语句影响总行数的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

26 4 0
文章推荐： Oracle 获取表注释和列注释
文章推荐： Oracle的用户、角色及权限相关操作
文章推荐： oracle 的表空间实例详解
文章推荐： Oracle sqlldr导入一个日期列实例详解
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com