--创建表

create table (start_Date date,end_Date date)

select * from gd_date_test order by start_date


--开始,这段代码是验证时间段是否重叠,重叠则更新end_date为比他大的start_date-1

declare
  type t_emp is table of gd_date_test%rowtype index by binary_integer;

  v_emp t_emp;
begin

  for i in (select gt.*,rownum from (select * from gd_date_test a order by a.start_date) gt) loop
    v_emp(i.rownum).start_Date := i.start_Date;
    v_emp(i.rownum).end_Date := i.end_Date;
    v_emp(i.rownum).pk_id := i.pk_id;
  end loop;

  for j in 1 .. v_emp.count loop
    if j > 1 then
      if v_emp(j).start_Date < v_emp(j - 1).end_Date then
        v_emp(j - 1).end_Date := v_emp(j).start_Date-1;
      end if;
   
    end if;
  end loop;

  for j in 1 .. v_emp.count loop
    update gd_date_test t
       set t.end_date = v_emp(j).end_Date
     where v_emp(j).pk_id = t.pk_id;
 
  end loop;

end;

 

posted on 2011-09-19 18:45  小波Ooo  阅读(167)  评论(0)    收藏  举报