--创建表
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;
浙公网安备 33010602011771号