这个最好使用存储过程实现。 create or replace procedure aaa(startdate date, enddate date, price number) as begin for r in (select * from t where not (t.edate < startdate or t.sdate > enddate) ) loop --查询出有冲突的行,
if r.sdate >= startdate and r.edate <= enddate then -- 包含关系,直接delete delete from t where sdate = r.sdate; else -- 交叉关系,拆分处理 if r.sdate <= startdate then -- update t set edate = startdate - 1 where sdate = r.sdate; else update t set sdate = enddate + 1 where edate = r.edate; end if; end if; end loop; --插入新纪录 insert into t values (seq.netval, startdate, enddate, price);end;
procedure aaa(startdate date, enddate date, price number) as cursor A IS select * from T where t.edate >=startdate AND t.sdate <=enddate; R T%ROWTYPE; begin OPEN A; LOOP FETCH A INTO R; EXIT WHEN A%NOTFOUND; if r.sdate < startdate then update t set edate=startdate-1 where sdate=r.sdate; if r.edate >enddate then insert into t(ID,sdate,edate,price) values (seq.netval,r.edate+1,enddate,price); END IF; ELSE if r.edate <= enddate then -- 包含关系,直接delete delete from t where sdate = r.sdate; else -- 交叉关系,拆分处理 update t set sdate=enddate+1 where edate=r.edate; end if; END IF; END LOOP; --插入新纪录 insert into t(ID,sdate,edate,price) values (seq.netval, startdate, enddate, price); COMMIT; end;
create or replace procedure aaa(startdate date, enddate date, price number) as
begin
for r in (select * from t where not (t.edate < startdate or t.sdate > enddate) ) loop --查询出有冲突的行,
if r.sdate >= startdate and r.edate <= enddate then -- 包含关系,直接delete
delete from t where sdate = r.sdate;
else -- 交叉关系,拆分处理
if r.sdate <= startdate then --
update t
set edate = startdate - 1
where sdate = r.sdate;
else
update t
set sdate = enddate + 1
where edate = r.edate;
end if;
end if;
end loop; --插入新纪录
insert into t values (seq.netval, startdate, enddate, price);end;
cursor A IS select * from T where t.edate >=startdate AND t.sdate <=enddate;
R T%ROWTYPE;
begin
OPEN A;
LOOP
FETCH A INTO R;
EXIT WHEN A%NOTFOUND;
if r.sdate < startdate then
update t set edate=startdate-1 where sdate=r.sdate;
if r.edate >enddate then
insert into t(ID,sdate,edate,price) values (seq.netval,r.edate+1,enddate,price);
END IF;
ELSE
if r.edate <= enddate then -- 包含关系,直接delete
delete from t where sdate = r.sdate;
else -- 交叉关系,拆分处理
update t set sdate=enddate+1 where edate=r.edate;
end if;
END IF;
END LOOP;
--插入新纪录
insert into t(ID,sdate,edate,price) values (seq.netval, startdate, enddate, price);
COMMIT;
end;
这个主要是情况较多,想全点就可以了
本身难度不大,就是麻烦新段完全包含原段,就是第一个“包含关系,直接delete”那个