信息更新时间数据订正.sql

--产仔
declare 
cursor r is
select p.animal_id as animal_id,max(p.produce_date)as produce_date from tbl_produce p join tbl_animal_info a 
on p.animal_id= a.id group by p.animal_id;
thedata r%rowtype;

begin
  open r;
        loop
             fetch r into thedata;
             exit when r%notfound;
             
              update tbl_animal_info o
                   set o.update_time = thedata.produce_date
                   where (o.update_time < thedata.produce_date or o.update_time is null) and  o.id=thedata.animal_id ;
                   commit;
                   
end loop;
end;

--如果更新时间为空
update tbl_animal_info a set a.update_time =a.create_time
where a.update_time is null or a.update_time<a.create_time;
commit;



--更新时间大于目前系统时间
update tbl_animal_info set update_time =sysdate where update_time>sysdate;
commit;