同步死亡表.sql

declare
  cursor r is

    select a.id as ANIMAL_ID,a.last_check_time as DEATH_REG_DATE from tbl_animal_info a 
    where a.state_flag = 2 
    and a.id not in (select d.ANIMAL_ID from tbl_death d);            

  thedata r%rowtype;
begin
--删除死亡表里重复的记录
delete tbl_death where id not in (select min(d.id ) from tbl_death d group by d.animal_id);
--删除死亡表中所有在母猪表中不是死亡状态的死亡记录
delete from tbl_death d where not exists (select 1 from tbl_animal_info a where d.animal_id=a.id and a.state_flag=2 );
commit;
  open r;
  loop
    fetch r
      into thedata;
    exit when r%notfound;    
    insert into tbl_death(id,animal_id,death_reg_date,death_reason)
    values
      (DEATH_SEQUENCE.NEXTVAL,
       thedata.ANIMAL_ID,
       thedata.DEATH_REG_DATE,
       '其他'
       );

    commit;
  end loop;
end;