У меня вопрос не совсем про Oracle, скорее про запросы, но к разделам по разработке ИС или БД он подходит еще меньше.
Есть система, которая обрабатывает запросы и формирует документы во внешней ИС.
Во внешней ИС документы проходят несколько стадий, финальной стадией будет "выполнено" или "ошибка". Обработка документа занимает неизвестное время, но обычно не более 5 минут. Поэтому у меня есть еще дополнительный скрипт, который запускается каждые 5 минут, проверяет статус документов и для выполненных документов выполняет какие-то действия.
Общая схема взаимодействия выглядит так.
1. В БД есть таблица для регистрации запросов:
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE FISCAL_DATA
(
FISCAL_ID NUMBER NOT NULL, --внутренний идентификатор записи (счетчик)
UUID VARCHAR2(128 BYTE), --идентификатор документа во внешней ИС
STATUS NUMBER DEFAULT 0 NOT NULL, --текущий статус документа
...
UPDATE_RETRY NUMBER --количество обновлений статуса
);
2. Когда поступает запрос, в этой таблице фиксируется соответствующая запись:
1.
2.
3.
insert into FISCAL_DATA (...)
values (...)
returning FISCAL_ID into :rec
3. Затем во внешней ИС формируется документ и его идентификатор фиксируется в таблице:
1.
2.
3.
update FISCAL_DATA
set STATUS = 0, UUID = :uuid, UPDATE_RETRY = 0
where FISCAL_ID = :rec
4. Далее в дело вступает регулярно (каждые 5 минут) запускаемый скрипт. Он последовательно выполняет несколько запросов.
4.1. Удаляет устаревшие записи:
1.
2.
delete from FISCAL_DATA
where TIMESTAMP < (trunc(sysdate) - 1800)
4.2. Документам старше суток проставляет статус "ошибка" (чтобы более не опрашивать их статус).
1.
2.
3.
4.
update FISCAL_DATA
set STATUS = 2, ERROR_TEXT = 'timeout', UPDATE_TIMESTAMP = sysdate
where STATUS = 0
and TIMESTAMP < (trunc(sysdate) - 1)
4.3. Документам старше 5 минут, у которых статус не менялся после нескольких опросов подряд, проставляет статус "ошибка".
1.
2.
3.
4.
5.
6.
7.
8.
update FISCAL_DATA
set STATUS = 2
, ERROR_TEXT = 'unsuccess'
, UPDATE_SOURCE = 'schedule'
, UPDATE_TIMESTAMP = sysdate
where STATUS = 0
and TIMESTAMP < (sysdate - 5/24/60)
and UPDATE_RETRY > 5
4.4. Затем получает список текущих документов (в статусе ожидания и старше 5 минут):
1.
2.
3.
4.
select FISCAL_ID, EXTERNAL_ID, UUID
from FISCAL_DATA
where STATUS = 0
and TIMESTAMP < (sysdate - 5/24/60)
4.5. Для каждого из текущих документов во внешней ИС опрашивается статус и вносится в таблицу:
1.
2.
3.
4.
5.
update FISCAL_DATA
set STATUS = :status
...
, UPDATE_RETRY = nvl(UPDATE_RETRY,0)+1
where UUID = :id
В принципе сейчас все работает, как часы.
Но есть некоторые пожелания.
Обычно через 5 минут документ уже обработан и у большинства записей UPDATE_RETRY=1.
Если этого не произошло, то причин может быть три:
- внешняя ИС не успела обработать документ, нужно повторить еще через 5 минут
- перебои связи, могут быть до нескольких часов
- перманентная ошибка
Вот со вторым пунктом сложности.
Если скрипт выполняет пункт 4.5 каждые 5 минут, то 5 попыток наступят через 25 минут. То есть через полчаса пункт 4.3 проставит в таблице статус "Ошибка" и более опрашивать статус этого документа не будет. И если это был часовой перерыв связи, то документы в этом периоде нужно будет проверять вручную (или вручную задать им STATUS=0).
Более правильным являлось бы удвоение временного интервала между повторными проверками - например первая проверка через 5 минут, вторая через 10, третья через 20, четвертая через 40, пятая через полтора часа, далее перевод в статус "Ошибка".
Как бы это сделать правильно?
1.
2.
3.
...
and TIMESTAMP < (sysdate - (case when UPDATE_RETRY>1 then UPDATE_RETRY-1 else 1 end)*5/24/60)
and UPDATE_RETRY > 5
Так?
