Есть таблица NUMS с диапазонами номеров, которая содержит поля:
NUM_FROM number(10,0) - номер с
NUM_TO number(10,0) - номер по
PREFIX varchar2(12) - префикс
POSTFIX varchar2(12) - постфикс
LENGTH number(2,0) - длина
Каждая запись в этой таблице - это диапазон номеров с NUM_FROM по NUM_TO, к каждому номеру добавляется префикс PREFIX и постфикс POSTFIX (если есть) и дополняется нулями слева до нужной длины LENGTH. Т.е. например запись вида:
NUM_FROM=1, NUM_TO=1000000, PREFIX=ТЕСТ/, POSTFIX=/2017, LENGTH=7
описывает номера:
ТЕСТ/0000001/2017
ТЕСТ/0000002/2017
ТЕСТ/0000003/2017
...
ТЕСТ/0999999/2017
ТЕСТ/1000000/2017
На всякий случай скажу, что диапазоны не пересекаются, т.е. нельзя создать другую запись, номера которой будут входить в уже ранее созданный диапазон, например, NUM_FROM=1, NUM_TO=2000000, PREFIX=ТЕСТ/, POSTFIX=/2017 создать нельзя.
Итого, есть таблица с диапазоном номеров и есть 5 других таблиц (T1, T2, T3, T4, T5), никак не связанных между собой, в каждой из которых есть поле NUM типа varchar2(50). Этот номер пользователь может как ввести руками, так и выбрать из любого диапазонов номеров, описанных выше. Если пользователь выбирает номер из диапазона, то ему нужно предложить первый (минимальный) свободный номер, т.е. номер из выбранного диапазона, которого нет ни в одной из 5 таблиц (T1, T2, T3, T4, T5).
Как это организовать наиболее оптимально? Попробовал написать что-то типа такого:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
with n as (
select --+ materialize
level num_,
case when length(level) < n.length then n.prefix||lpad(level,n.length,'0')||n.postfix
else n.prefix||level||n.postfix
end num
from (select n.* from nums n where n.id = :ID) n
connect by level between n.num_from and n.num_to
)
select min(c2.num_)
from (
select n.num_
from n
minus
select c1.num_
from (
--1
select n.num_
from t1,
n
where t1.num = n.num
union all
--2
select n.num_
from t2,
n
where t2.num = n.num
union all
--3
select n.num_
from t3,
n
where t3.num = n.num
union all
--4
select n.num_
from t4,
n
where t4.num = n.num
union all
--5
select n.num_
from t5,
n
where t5.num = n.num
)c1
)c2
Т.е. из всего массива номеров диапазона вычитаем занятые и находим минимальный из полученных номеров (после для полученного числа добавим префикс и постфикс). Работает, но не быстро. Мне кажется, можно все сделать как-то оптимальнее и элегантнее.
Есть идея сделать еще одну таблицу NUMS_CONT, где при создании нового диапазона будут создаваться все номера из этого диапазона, а на таблицы T1, T2, T3, T4, T5 повесить триггера, чтобы они при создании/обновлении/удалении данных помечали записи в таблице NUMS_CONT как занятые/свободные. Тогда нахождение свободного номера сведется к запросу из одной таблицы.
Или можно сделать как-то иначе (проще/оптимальнее/эффективнее)?
