oracle触发器查询统计本表

    以前发过一篇转载的，这次由于项目需要，实际动手演练了一把，达到了需要的效果，大致是插入数据后，统计表内和当前插入数据时间相同的个数。

采用行级触发器+语句级触发器。

--包

  CREATE OR REPLACE PACKAGE UPDATECASE AS
   TYPE T_DATE IS TABLE OF date INDEX BY BINARY_INTEGER;
   V_UPDATETIME T_DATE;
   V_ROW  BINARY_INTEGER := 0;
   end;

--行级触发器 

create or replace trigger Tri_student_row
  after insert or update or delete of createtime,type
   on student
   FOR EACH ROW
BEGIN
  updatecase.V_ROW  :=  updatecase.V_ROW + 1;
  if DELETING  then
     updatecase.V_UPDATETIME(updatecase.V_ROW) := :OLD.CREATETIME;
  else 
  updatecase.V_UPDATETIME(updatecase.V_ROW) := :NEW.CREATETIME;
  end if;
END;

--语句级触发器

create or replace trigger Tri_student
  after insert or update or delete on student
declare
  studentcount1 number(38,5);
  studentcount2 number(38,5);
  v_area1 number(38,5);
  v_area2 number(38,5);
  updatetime date;
BEGIN
  FOR V_LOOP IN 1 .. UPDATECASE.V_ROW LOOP
    updatetime :=  UPDATECASE.V_UPDATETIME(V_LOOP);
    select count(*),nvl(sum(area),0)
      into studentcount1,v_area1 from student t
     where t.createtime =updatetime and t.type=1;
    select count(*),nvl(sum(area),0)
      into studentcount2,v_area2 from student t
     where t.createtime =updatetime and t.type=2;

  
    MERGE INTO PERSONCOUNT t
　　USING (select count(*) co from PERSONCOUNT t1 where  t1.comedate=updatetime) b
　　ON (b.co<>0)
　　WHEN MATCHED THEN
　　UPDATE SET
　　t.count1=studentcount1,t.count2=studentcount2,t.area1=v_area1,t.area2=v_area2
      where t.comedate=updatetime
　　WHEN NOT MATCHED THEN
　　INSERT(comedate,count1,count2,area1,area2) VALUES(updatetime,studentcount1,studentcount2,v_area1,v_area2);
  end loop;
  UPDATECASE.V_ROW := 0;
END;