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
 --聲明一個(gè)關(guān)聯(lián)數(shù)組類型，它就是日志表的關(guān)聯(lián)數(shù)組
 type cslog_type is table of t_cslog%rowtype index by t_cslog.tblname%type;
 --聲明這個(gè)關(guān)聯(lián)數(shù)組的變量。
 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 := '');
 --語句結(jié)束，寫到日志表中。
 procedure onend_cs(v_tblname t_cslog.tblname%type, v_type varchar2);
end pck_cslog;

--包體
create or replace package body pck_cslog is
 --私有方法，把關(guān)聯(lián)數(shù)組中的一條記錄寫入庫里
 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;
 --私有方法，清除關(guān)聯(lián)數(shù)組中的一條記錄
 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;
 --某個(gè)SQL語句執(zhí)行開始。 v_type：語句類型，insert時(shí)為 i, update時(shí)為u ，delete時(shí)為 d
 procedure onbegin_cs(v_tblname t_cslog.tblname%type, v_type varchar2) is
 begin
   --如果關(guān)聯(lián)數(shù)組中不存在，初始賦值。 否則表示，同時(shí)有insert，delete語句對(duì)目標(biāo)表操作。
  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 := ' '; --初始給一個(gè)空格
   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 || ',';
  ----第一個(gè)語句進(jìn)入，顯示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
   --行數(shù)，代碼，起、止時(shí)間
   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;
 --語句結(jié)束。 
 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 || ',';
   --語句退出，將并行標(biāo)志位減一。 當(dāng)它為0時(shí)，就可以寫表了
   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;

--語句開始前
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;

--語句結(jié)束后
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;

--行級(jí)觸發(fā)器
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); --此處是把監(jiān)控的行的某一列的值傳入包體，這樣最后會(huì)記錄到日志表
 elsif v_type = 'd' then
  pck_cslog.oneachrow_cs('t_a', v_type, :old.name);
 end if;
end;

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;