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

Oracle how to record each statement through the trigger to affect the total number of lines, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

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:

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

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 | |',';-* statements enter and display 1, which will be incremented if parallelism occurs 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 | |','| | vested code; 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 的表：

Three triggers:

-- before the start of the sentence, create or replace trigger tri_onb_t_a before insert or delete or update on tweea 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 (' v_type); end -- after the end of the sentence, create or replace trigger tri_one_t_a after insert or delete or update on tweea 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 (' v_type); end -- row-level trigger create or replace trigger tri_onr_t_a after insert or delete or update on Trapa 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 ('tcm, v_type,: new.name);-- here, the value of a column of the monitored row is passed into the packet, so that * will be recorded in the log table elsif v_type =' d' then pck_cslog.oneachrow_cs ('tcm, v_type,: old.name); end if; end

Test results:

The trigger is ready to test insert and delete. Insert 100 rows first, and then delete some rows casually.

Declare i number; begin for i in 1.. 100 loop insert into Testa values (I, I | | 'shenjunjian'); end loop; commit; delete from Testa where id > 79; delete from Testa where id < 40; commit; end

The clob column, which can also display rows deleted by monitoring:

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!

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.