交叉时间

最新推荐文章于 2020-11-26 19:46:27 发布

weixin_34242658

最新推荐文章于 2020-11-26 19:46:27 发布

阅读量160

点赞数

Code
1

--检查插入的时间是否会与已存在的时间交叉
2

select nvl(count(1),0)
3

into l_exist_time
4

from hek_om_pop_validity_all hpva
5

where hpva.inventory_item_id = :HEK_OM_POP_VALIDITY_V.INVENTORY_ITEM_ID
6

and ((:HEK_OM_POP_VALIDITY_V.START_DATE between hpva.start_date and nvl(hpva.end_date, to_date('9999-12-22','yyyy-mm-dd')))
7

or (:HEK_OM_POP_VALIDITY_V.END_DATE between hpva.start_date and nvl(hpva.end_date,to_date('9999-12-22','yyyy-mm-dd')))
8

or (hpva.start_date between :HEK_OM_POP_VALIDITY_V.START_DATE and nvl(:HEK_OM_POP_VALIDITY_V.END_DATE,to_date('9999-12-22','yyyy-mm-dd'))));

PS. 一行一行相比较。l_exist_time >= 1的时候存在时间交叉。

Code
1

--检查修改的时间是否会与已存在的时间交叉
2

select nvl(count(1),0)
3

into l_exist_time
4

from hek_om_pop_validity_all hpva
5

where hpva.inventory_item_id = :HEK_OM_POP_VALIDITY_V.INVENTORY_ITEM_ID
6

--不用与自己比较
7

and hpva.line_id <> :HEK_OM_POP_VALIDITY_V.LINE_ID
8

and ((:HEK_OM_POP_VALIDITY_V.START_DATE between hpva.start_date and nvl(hpva.end_date,to_date('9999-12-22','yyyy-mm-dd')))
9

or (:HEK_OM_POP_VALIDITY_V.END_DATE between hpva.start_date and nvl(hpva.end_date,to_date('9999-12-22','yyyy-mm-dd')))
10

or (hpva.start_date between :HEK_OM_POP_VALIDITY_V.START_DATE and nvl(:HEK_OM_POP_VALIDITY_V.END_DATE,to_date('9999-12-22','yyyy-mm-dd'))));
11

PS. 一行一行进行比较，但要排除本身这一行，然后保证修改的行的起始时间和结束时间都不在其他行。l_exist_time >= 1的时候存在时间交叉。

关注