2个字段指定一个范围如何控制不能有重复或交叉？

Question

本帖最后由 newjq 于 2013-07-25 05:46:49 编辑

Answer 1

楼主贴的变量名错误太多，只好假设编译正确。最好先确保 startid <= endidupdate试试where startid<=:new.endid and endid>=:new.startid
然后判断ll_have>=2

Answer 2

这个过程是我按照题目的例子改的，没完全改好，其中new_rowid := :old.rowid应该是old_rowid := :old.rowid;
这句话的意思就是将本次更新的当前记录的最新值与除当前记录外的记录进行比较是否有交叉。
但是执行 old_rowid := :old.rowid;
    new_startid := :new.startid;
    new_endid := :new.endid;
    select count(*)
      into ll_have
      from tab1
     where startid <= new_endid
       and endid >= new_startid
       and rowid <> old_rowid;--执行报错ora-04091

Answer 3

-- 得用带“自治事务”的触发器，代码如下：
create table tab1(r_id number, startid number, endid number);create or replace trigger t_tab1_idcheck before insert or update
on tab1 for each row
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  ll_have number;
begin
  if inserting then  
    select count(1)
      into ll_have
      from tab1
     where startid <= :new.endid
       and endid >= :new.startid; --执行成功
    if (ll_have > 0) then
      raise_application_error(-20010,'error');
    end if;
  end if;  if updating then
    select count(*)
      into ll_have
      from tab1
     where startid <= :new.endid
       and endid >= :new.startid
       and rowid <> :old.rowid;  --执行报错
    if (ll_have > 0) then
      raise_application_error(-20010, 'error');
    end if;
  end if;
end;
/insert into tab1(r_id,startid,endid)
values(1,1,1);
insert into tab1(r_id,startid,endid)
values(2,3,4);
insert into tab1(r_id,startid,endid)
values(3,5,8);
commit;insert into tab1(r_id,startid,endid)
values(4,2,2);
commit;insert into tab1(r_id,startid,endid)
values(5,9,12);commit;insert into tab1(r_id,startid,endid)
values(6,2,3);
commit;insert into tab1(r_id,startid,endid)
values(7,6,7);
commit;

Answer 4

当然，最好在endid和startid字段上创建复合索引。
或根据endid和startid字段范围分区，再创建局部分区索引!

Answer 5

最好先确保 startid <= endid
这个已经在CHECK里限制了update试试where startid<=:new.endid and endid>=:new.startid
然后判断ll_have>=2
这个count(*)>=2有问题，如果将第2行的值3,4改为3,5的确count=2，若改为1,2，count=1，不符合要求。

Answer 6

PRAGMA AUTONOMOUS_TRANSACTION; 
正解，之前也百度了ora-04091，没有认真的体会“自治事务”，现在大概了解了：
这是oracle的安全机制，在更新时确保新数据不被脏读或破坏，如果确定没有问题，
加一条PRAGMA AUTONOMOUS_TRANSACTION，告诉oracle解除该程序段的安全机制。谢谢luoyoumou，结贴！

Answer 7

逻辑上应该不会错，本身修改的这条肯定满足该条件，只要另外有一条交叉的，就count(*)>=2
after update触发时，记得是查到新数据。

Answer 8

逻辑上应该不会错，本身修改的这条肯定满足该条件，只要另外有一条交叉的，就count(*)>=2
after update触发时，记得是查到新数据。
谢谢！我需要在before update时触发，要不然业务处理起来比较麻烦