Блог

Поэтому в данный момент я разрабатываю приложение, в котором мне нужно проверить результаты на основе диапазона чисел в зависимости от возраста человека. Если числа перекрываются, нужно выдать ошибку.
Редактировать: Обновление поста в соответствии с заданным вопросом @Littlefoot. В приведенном ниже примере строка № 1 существует в таблице и на основе этого определяются допустимые и недопустимые условия.
Ниже приведены действительные и недействительные условия:

  ---- ------ ---------- -------- 
| ID | NAME | FROM_AGE | TO_AGE |           
 ---- ------ ---------- -------- 
|  1 | Kim  |       26 |     28 | -- Valid   
|  2 | Kim  |       20 |     25 | -- Valid   
|  3 | Kim  |          |     16 | -- Valid   
|  4 | Kim  |       29 |        | -- Valid   
|  5 | Kim  |       19 |     22 | -- Invalid 
|  6 | Kim  |       27 |     28 | -- Invalid 
|  7 | Kim  |       18 |     30 | -- Invalid 
|  8 | Kim  |       26 |     32 | -- Invalid 
 ---- ------ ---------- -------- 
 

Действительные условия: Возраст человека — «От возраста» и «До возраста» не должны совпадать для одного и того же человека
Недопустимые условия: Возраст человека находится в пределах От и до
Приведенные выше данные являются образцом. В реальном времени там будут разные люди.

Разместили это на apex.oracle.com и все еще предоставляю SQL-скрипт ниже.
URL: https://apex.oracle.com/pls/apex/f?p=4550:1
Рабочее пространство: richaferna
имя пользователя: демо
пароль: demo1234

URL-АДРЕС интерфейса: https://apex.oracle.com/pls/apex/f?p=77049:2

Идентификатор приложения:
77049 Название приложения: ВОЗРАСТ ЧЕЛОВЕКА

Ниже приведен сценарий SQL:

 CREATE TABLE "PERSON_AGE" (
    "ID"         NUMBER(5,0),
    "NAME"       VARCHAR2(20),
    "FROM_AGE"   NUMBER(3,0),
    "TO_AGE"     NUMBER(3,0),
    CONSTRAINT  "PERSON_AGE_PK" PRIMARY KEY("ID")
)
/

CREATE SEQUENCE"PERSON_AGE_SEQ" 
/

CREATE TRIGGER "BI_PERSON_AGE"  
  BEFORE INSERT ON "PERSON_AGE"              
  FOR EACH ROW 
BEGIN  
  IF :NEW."ID" IS NULL THEN
    SELECT "PERSON_AGE_SEQ".nextval INTO :new."ID" FROM sys.dual;
  END IF;
END;
/   

INSERT INTO PERSON_AGE VALUES(1, 'Kim', 26, 28);

INSERT INTO PERSON_AGE VALUES(2, 'Kim', 20, 25);

INSERT INTO PERSON_AGE VALUES(3, 'Kim', 0, 26);

INSERT INTO PERSON_AGE VALUES(4, 'Kim', 29, 0);
 

Ниже приведена проверка отсутствия возвращенных строк, которую я пробовал, которая не позволяет сохранять даже действительные данные:

 SELECT COUNT(*) 
  FROM "PERSON_AGE" a, "PERSON_AGE" b
 WHERE a.name = b.name
   AND a."TO_AGE" >= b."FROM_AGE"
   AND b."TO_AGE" >= a."FROM_AGE"
 

Спасибо,
Рича

Чтобы обеспечить невозможность перекрытия, вам необходимо:

• Создайте fast refresh on commit материализованное представление, которое подсчитывает, сколько существует перекрытий
• Добавьте к этому контрольное ограничение, чтобы убедиться, что количество равно нулю

Вы должны сделать эту проверку таким образом. Если вы (только) выполняете внешнюю проверку, два сеанса, запущенные одновременно, добавляющие одинаковые/перекрывающиеся значения, не будут видеть изменения друг друга. Таким образом, вы можете пройти проверку, но все равно получите перекрытия.

Также обратите внимание, что проверка MV происходит при фиксации. Поэтому, если одна транзакция добавляет несколько строк с одним перекрытием, вы узнаете об этом только в конце.

 create sequence person_age_seq 
/

create table person_age (
  id         number(5,0)
    default on null person_age_seq.nextval,
  name       varchar2(20),
  from_age   number(3,0),
  to_age     number(3,0),
  constraint person_age_pk 
    primary key ( id ),
  constraint person_age_from_before_to
    check ( from_age < to_age )
)
/

create materialized view log on person_age
  with rowid, primary key ( name, from_age, to_age )
  including new values;
 
create materialized view age_count
refresh fast on commit
as
select count(*) c 
from   person_age a, person_age b
where  a.name = b.name
and   (
  ( a.from_age > b.from_age and a.from_age < b.to_age ) or
  ( a.to_age > b.from_age and a.to_age < b.to_age ) or
  ( a.to_age > b.to_age and a.from_age < b.from_age ) 
);

alter materialized view age_count
  add constraint zero_overlap
  check ( c = 0 );

insert into person_age(name, from_age, to_age)
values( 'Kim', 20, 25);
commit;
insert into person_age(name, from_age, to_age)
values( 'Kim', 26, 28);
commit;
insert into person_age(name, from_age, to_age)
values( 'Kim', 28, 30);
commit;
insert into person_age(name, from_age, to_age)
values( 'Kim', 29, 30);
commit;

ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (CHRIS.ZERO_OVERLAP) violated

insert into person_age( name, from_age, to_age)
values( 'Kim', 29, 0);

ORA-02290: check constraint (CHRIS.PERSON_AGE_FROM_BEFORE_TO) violated

insert into person_age(name, from_age, to_age)
values( 'Kim', 0, 26);
commit;

ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (CHRIS.ZERO_OVERLAP) violated

insert into person_age(name, from_age, to_age)
values( 'Kim', 19, 31);
commit;

ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (CHRIS.ZERO_OVERLAP) violated

insert into person_age(name, from_age, to_age)
values( 'Kim', 21, 24);
commit;

ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (CHRIS.ZERO_OVERLAP) violated

select *
from   person_age a
order by from_age, to_age;

ID    NAME   FROM_AGE    TO_AGE   
    1 Kim          20        25 
    2 Kim          26        28 
    3 Kim          28        30 
 

Я проигнорировал null это для простоты. Ваша примерная таблица предполагает, что это возможно, поэтому вам нужно будет изменить запрос, чтобы учесть это. Лично я бы сказал (null, 26) , что они пересекаются (20, 25) , хотя вы сказали, что оба они действительны.

PS — пожалуйста, избегайте использования триггеров для присвоения значений первичного ключа; используйте default (on null) вместо этого