create or replace trigger LS_RY_JBXX_GB
before update on RY_JBXX_GB for each row
begin
insert
into TEMP_RY_JBXX_GB
(ID,RWSJ,RGRQ,ZYRQ,ZYLBDM,JYRQ,JYLBDM,JGWZLB,JXWJRQ,XZZWGZ,WZJGZ,ZYJSZWGZ,JXGZ,JXWJDM,ZWDJDM,ZWDJRQ,ZWPBDM,SFBZCY,JSZWDM,JSZWRQ,JZLBDM,JZZGDM,JSZGRQ,JSDJDM,JSDJRQ,ZBQKDM,SZLBDM,SJBB,CJSJ,BJSJ,YWXTBSID,JLZT,SJC,BZ,YWID,LS_DELETETIME,LS_SJC)
values (sys_guid(),:old.RWSJ,:old哮喘儿童父母必读.RGRQ,:old.ZYRQ,:old.ZYLBDM,:old.JYRQ,:old.JYLBDM,
:old.JGWZLB,:old.JXWJRQ,:old.XZZWGZ,:old.WZJGZ,:old.ZYJSZWGZ,:bmw族old.JXGZ,:old.JXWJDM,
:old.ZWDJDM,:old.ZWDJRQ,:old.ZWPBDM,:old.SFBZCY,:old.JSZWDM,:old.JSZWRQ,:old.JZLBDM,
:old.JZZGDM,:old.JSZGRQ,:old.JSDJDM,:old.JSDJRQ,:old.ZBQKDM,:old.SZLBDM,:old.SJBB,
:rca指数old.CJSJ,:old.BJSJ,:old三只小狼和一只大坏猪.YWXTBSID,:old.JLZT,:old.SJC,:old.BZ,
:old.YWID,sysdate,sysdate);
end;
2.创建存储过程,将历史数据处理后放回仓库的表中
create or replace procedure INSERT_RY_JBXX
is
--获取当前最新时间的所有数据的抽取数据列表;
cursor cur_delorupd is select f.ywid,max(f.sjc) from ry_jbxx f where f.sjc>=to_char(sysdate-7,'yyyy-mm-dd hh24:mi:ss') and f.sjc<to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
group by ywid;
v_del varchar2(2);
uid varchar2(32);
v_sj varchar2(32);
begin
open cur_delorupd;
fetch cur_delorupd into uid,v_sj;
while cur_delorupd%found loop
--在仓库表中根据YWID查记录状态,判断此条数据是被更新还是被删除
select jlzt into v_del from (select jlzt from ry_jbxx t where ywid = uid order by t.sjc desc) m where rownum<2;
--如果是被删除,则将临时表中的当前数据删除
if v_del='0' then
delete from temp_ry_jbxx where ywid = uid;
end if;
fetch cur_delorupd into uid,v_sj;
end loop;
--剩余数据则全是被更新的数据,将临时表的中更新前数据设为无效,然后在放回--仓库表中,进而将当前的临时表数据删除,提交后等待下一次操作。linpack
update temp_ry_jbxx set jlzt = '0';
insert into ry_jbxx select * from temp_ry_jbxx;
delete from temp_ry_jbxx;
commit;
end INSERT_RY_JBXX;
一域多层四四制