Shulou(Shulou.com)05/31 Report--

This article is about how each statement affects the total number of lines in oracle through triggers. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Demand generation:

In a business system, there is a "decimation" process, which is to synchronize some data from other servers to the target table of this library. In this process, it is possible for many people to draw numbers at the same time and influence each other. Some testers responded that the original number could not be found occasionally for no reason, and sometimes they would come out and repeat the line. This problem must be the problem of decimal logic and parallelism! But they made a simple requirement: to know when the data was deleted and when it was inserted, I needed to monitor "every change of the table"!

Technology selection:

The first is to think of triggers, so that monitoring can be achieved without involving the code of the business system. Triggers are divided into statement-level triggers and row-level triggers. Statement level is to trigger an operation before and after the execution of each statement. If I write the table name, time, and influence rows into the record table after each SQL statement is executed.

But the problem is that in a statement trigger, you can't get the number of rows of the statement, and sql%rowcount reports an error in the trigger. You can only use row-level triggers to count rows!

Code structure:

The functions of the entire monitoring data row include: a log table, package, sequence.

Log table: record the target table name, SQL execution start and end time, affect the number of rows, monitor some column information on the data row.

Package: mainly 3 stored procedures

Statement start stored procedure: use an associative array to record the target table name and start time, and clear the other values of 0. 0.

Row operation stored procedure: add 1 to the number of records corresponding to the target table of the associated array.

Statement end stored procedure: writes the statistical information in the target table of the associative array to the log table.

Sequence: primary key used to generate log tables

Code:

Log tables and sequences:

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, 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_CSLOGminvalue 1maxvalue 99999999999start with 1increment by 1cache 20cycle

Package code:

-Baotou create or replace package pck_cslog is-- declares an associative array type, which is the log table associative array type cslog_type is table of t_cslog%rowtype index by tmixcslog.tblName% type;-- declares the variable of this associative array. Cslog_tbl cslog_type;-- the statement begins. Procedure onbegin_cs (v_tblname t_cslog.tblname%type, v_type varchar2);-- Row operation procedure oneachrow_cs (v_tblname t_cslog.tblname%type, v_type varchar2, v_code varchar2: ='', v_rq date: ='');-- end the statement and write to the log table. Procedure onend_cs (v_tblname t_cslog.tblname%type, v_type varchar2); end pck_cslog;-- packet create or replace package body pck_cslog is-Private method that writes a record in the associative array to the library 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 Private method that clears a record in the associative array 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;-- the execution of a SQL statement begins. V_type: statement type, I when insert, u when update, and d procedure onbegin_cs (v_tblname t_cslog.tblname%type, v_type varchar2) is begin when delete-- initial assignment if it does not exist in the associative array. Otherwise, an insert,delete statement operates on the target table at the same time. If not cslog_tbl.exists (v_tblname) then cslog_tbl (v_tblname). N_id: = seq_t_cslog.nextval; cslog_tbl (v_tblname). 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: ='';-initially give a space cslog_tbl (v_tblname). Startrq: = to_date ('999999,' 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 | |',';-the first statement goes in and displays 1, which is incremented if it is parallelized later. Cslog_tbl (v_tblname) .n: = cslog_tbl (v_tblname) .n + 1; end;-per line operation. 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-number of lines, code, start and stop time 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 | |','| | end if; if v_rq is not null then if v_rq > cslog_tbl (v_tblname). Endrq then cslog_tbl (v_tblname). Endrq: = vroomrq; 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_adeclare 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_adeclare 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 rowdeclare 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 Testa where id

< 40; commit;end; clob列，还可以显示监控删除的行：

In parallel, there may be similar information in the bz column:

This means that two statements are being inserted into the target table at the same time.

A delete statement is also executed at the time of insertion.

When the platform is used by multiple people, it is inevitable to operate the same table at the same time. Through the value of this column, you can observe the execution of the database!

Thank you for reading! This is the end of the article on "how to record each statement in oracle to affect the total number of lines". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.