oracle触发器case,Oracle触发器查询统计本表

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

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

studentcount number(38,5);

BEGIN

FOR V_LOOP IN 1 .. UPDATECASE.V_ROW LOOP

select count(*)

into studentcount from student t

where t.createtime = UPDATECASE.V_UPDATETIME(V_LOOP);

insert into personcount(comedate,count)values(UPDATECASE.V_UPDATETIME(V_LOOP),studentcount);

end loop;

UPDATECASE.V_ROW := 0;

END;