确保 Oracle 行代表唯一的时间跨度

Question

我必须在 Oracle/PLSQL 中创建一个过程。我必须验证我创建的新行中的 start_date 和 end_date 之间的时间间隔不得与其他行中的其他 start_dates 和 end_dates 相交。

现在我需要检查每一行是否存在该条件，如果不对应，则重复指令应停止，然后显示一条消息，例如“给定的时间间隔不正确”。

我不知道如何在 Oracle/PLSQL 中进行重复指令，如果您能帮助我，我将不胜感激。

我需要一个循环或类似的东西来验证表中的每一行，date_hour_i 和 date_hour_e 给出的时间间隔不与其余行给出的其他时间间隔相交。还有一个规范......每一行的日期对应于一个客户和一个在给定时间间隔内为客户理发的员工......并且我想以某种方式不让引入新行，如果同一客户（或其他客户）和员工，新的时间间隔与同一/其他客户和员工的其他时间间隔相交......我希望我说清楚......

Answer 1

两个链接供您阅读乐趣：-

没有重叠的时间间隔

和

避免重叠值...

Answer 2

为什么要检查每一行？只需查询开始和结束时间。如果结果> 0，输出错误信息，否则插入。

Answer 3

我假设这将是在 BEFORE INSERT OR UPDATE 触发器期间。

您将需要查询现有表中的日期重叠 - 但这会产生突变触发器错误。

您可以通过使用 PRAGMA AUTONOMOUS_TRANSACTION 生成新线程来解决此问题。

或者 - 您可以将每个日期范围保存在辅助表中，并使用它来查询每个插入...类似于以下内容（未编译）

CREATE OR REPLACE TRIGGER mytrigger
BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW
DECLARE 
    cnt number;
BEGIN 
    SELECT count(*) into cnt
    FROM reserved_date_range                
    WHERE :new.begin_date BETWEEN begin_dt and end_dt

    if ( cnt > 0 ) then
        raise_application_error(-20000,'Overlapping date ranges');
    else
        insert into reserved_date_range( begin_dt, end_dt ) 
        values ( :new.begin_date, :new.end_date );
    end if;
End;
/

Answer 4

假设您的表是 tab1，开始日期是 stdate，结束日期是 endate
还让新的开始日期和新的结束日期位于 PLSQL 变量 v_stdate 和 v_endate 中。

所以你的插入可以是这样的

insert into tab1 (stdate,endate)
select v_stdate,v_endate  from dual
where not exists(
select 'overlap' from tab1 t1
where v_stdate between(t1.stdate and nvl(t1.endate,v_endate) 
or   v_endate between(t1.stdate and nvl(t1.endate,v_endate)

)

由于并发问题，这个问题的解决方案有点复杂。在你的情况下，你正在安排一个事件（或资源）。所以我想你有一个保存资源的表（比如客户端）。在为客户添加另一个时间表（或事件）之前，您应该锁定特定的客户记录，例如。

select client_id from Clients where client_id=p_client_id for update;

然后你可以验证没有重叠并插入新的计划并提交。此时锁将被释放。任何不使用序列化对象的解决方案都必然会因并发问题而存在缺陷。你可以在你的PLSQL 或插入后触发器中。但绝对必须锁定实际资源记录。