sql - 自动调整范围表

Question

我有一个包含开始日期范围、结束日期范围和一些其他附加列的表格。在输入新记录时，我想自动调整任何重叠的日期范围(缩小、拆分或删除它们以允许新输入——参见下面的算法)。我还想确保不会意外地将重叠记录插入到该表中。

我的应用程序代码使用 Oracle 和 Java。我应该如何强制防止日期范围重叠并允许自动调整重叠范围？我是否应该创建一个 AFTER INSERT 触发器，使用 dbms_lock 来序列化访问，以防止数据重叠。然后在Java中，应用逻辑来自动调整一切？或者该部分应该在存储过程调用中的 PL/SQL 中？这是我们需要用于其他几个表的东西，所以抽象起来会很好。

如果有人已经写过类似的东西，请分享:)

我确实找到了这个引用:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101

这是一个示例，说明应如何处理 4 种重叠情况中的每一种以进行插入调整:

= Example 1 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(20, 50, 'A')
Gives
(0, 10, 'X')
**(20, 50, 'A')
**(51, 100, 'Z')
(200, 500, 'Y')


= Example 2 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input 
(40, 80, 'A')
Gives
(0, 10, 'X')
**(30, 39, 'Z')
**(40, 80, 'A')
**(81, 100, 'Z')
(200, 500, 'Y')


= Example 3 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(50, 120, 'A')
Gives
(0, 10, 'X')
**(30, 49, 'Z')
**(50, 120, 'A')
(200, 500, 'Y')


= Example 4 =
In DB (Start, End, Value):
(0, 10, 'X')
**(30, 100, 'Z')
(200, 500, 'Y')

Input
(20, 120, 'A')
Gives
(0, 10, 'X')
**(20, 120, 'A')
(200, 500, 'Y')

算法如下:

given range = g; input range  = i; output range set = o

if i.start <= g.start
  if i.end >= g.end
    o_1 = i
  else
    o_1 = i
    o_2 = (i.end + 1, g.end)
else
  if i.end >= g.end
    o_1 = (g.start, i.start - 1)
    o_2 = i
  else
    o_1 = (g.start, i.start - 1)
    o_2 = i
    o_3 = (i.end + 1, g.end)

Answer 1

我通常看到这样的数据模型，其范围的起点是唯一被跟踪的点，而终点是隐含的。所以会是

CREATE TABLE MY_TABLE
(START_AT    NUMBER,
 VALUE       NUMBER,
 CONSTRAINT MY_TABLE_PK (START_AT)
);

如果您需要以现有格式呈现值，您可以使用分析和物化 View ，使用 LEAD(START_AT) OVER (ORDER BY START_AT)(我认为这是正确的，但未经测试)获得解释的最终值。