管理重叠的日期
如何编写一个过程,以便在添加新插入时,适当地添加行?
假设我有一张桌子:
create table test_table
(
code varchar2(10) not null,
type varchar2(50) not null,
start_date date not null,
end_date date not null,
parameter number
);
1。第一个测试用例:
在表中我们有:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1);
[2021-01-01 - 2021-01-10] type = "a"parameter = 1
当我们想要插入时:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('20.01.2021', 'DD,MM,YYYY'), 1)
>[2021-01-11 - 2021-01-20] 类型 = "a" 参数 = 1
*Result should be:
2021-01-01 - 2021-01-20 type = "a" parameter = 1*
2.第二个测试用例:
在表中我们有:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1)
[2021-01-01 - 2021-01-10] type = "a"parameter = 1
当我们想要插入时:
insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'), to_date('20.01.2021', 'DD,MM,YYYY'), 2)
[2021-01-06 - 2021- 01-20] 类型 =“a”参数 = 2
*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-20] type = "a" parameter = 2*
3。第三个测试用例:
在表中我们有:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1)
[2021-01-01 - 2021-01-20] type = "a"parameter = 1
当我们想要插入时:
insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'), to_date('15.01.2021', 'DD,MM,YYYY'), 2)
[2021-01-06 - 2021-01-15]类型=“a”参数=2
*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-15] type = "a" parameter = 2
[2021-01-16 - 2021-01-20] type = "a" parameter = 1*
How to write a procedure so that when adding a new insert, rows are added appropriately?
Let's say i have a table:
create table test_table
(
code varchar2(10) not null,
type varchar2(50) not null,
start_date date not null,
end_date date not null,
parameter number
);
1. First test case:
In table we have:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1);
[2021-01-01 - 2021-01-10] type = "a" parameter = 1
and when we want to insert:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('20.01.2021', 'DD,MM,YYYY'), 1)
[2021-01-11 - 2021-01-20] type = "a" parameter = 1
*Result should be:
2021-01-01 - 2021-01-20 type = "a" parameter = 1*
2. Second test case:
In table we have:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1)
[2021-01-01 - 2021-01-10] type = "a" parameter = 1
and when we want to insert:
insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'), to_date('20.01.2021', 'DD,MM,YYYY'), 2)
[2021-01-06 - 2021-01-20] type = "a" parameter = 2
*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-20] type = "a" parameter = 2*
3. Third test case:
In table we have:
insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'), to_date('10.01.2021', 'DD,MM,YYYY'), 1)
[2021-01-01 - 2021-01-20] type = "a" parameter = 1
and when we want to insert:
insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'), to_date('15.01.2021', 'DD,MM,YYYY'), 2)
[2021-01-06 - 2021-01-15] type = "a" parameter = 2
*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-15] type = "a" parameter = 2
[2021-01-16 - 2021-01-20] type = "a" parameter = 1*
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
当您插入完全包含在现有日期范围中间的新日期范围时,您需要:
INSERT
新范围;UPDATE
将现有范围更新为新范围之前的部分;并INSERT
一个新范围,用于新范围之后的现有范围部分。所以总共需要 3 处更改。同样,当您插入完全包含现有范围的新日期范围时,您需要:
INSERT
新范围;并DELETE
现有范围(或执行单个UPDATE
语句)。您可以对所有这些操作使用单个 MERGE 语句:
db<>fiddle 此处
When you insert a new date range that is completely contained in the middle of an existing date range then you need to:
INSERT
of the new range;UPDATE
the existing range to the portion of that range before the new range; andINSERT
a new range for the portion of the existing range after the new range. So you need a total of 3 changes.Similarly, when you insert a new date range that completely contains an existing range then you need to:
INSERT
the new range; andDELETE
the existing range (or do a singleUPDATE
statement).You can use a single
MERGE
statement for all of these actions:db<>fiddle here