Блог

Главная - Вопросы по программированию - Поиск иерархического текста в базе данных Oracle

Поиск иерархического текста в базе данных Oracle

#java #oracle

#java #Oracle

Вопрос:

Таблица = БЛОК (имеет составной уникальный индекс для обоих столбцов)

 IP_ADDRESS   CIDR_SIZE
=========    ==========
10.10         16
15.0          16
67.7          16
18.0           8

Требования:

  • Дополнительный блок не разрешен. Например, для 67.7.1 и 24 не допускается, поскольку это дочерний элемент 67.7. Другими словами, если в базе данных есть какой-либо IP-адрес, который соответствует начальной части нового IP, то он должен завершиться ошибкой. Могу ли я сделать это с помощью SQL-запроса Oracle?

Я думал сделать это с помощью…

  1. Выберите все записи в памяти.

  2. Преобразуйте каждый IP-адрес в его двоичные биты

    10.10 = 00001010.00001010
    15.0 = 00001111.00000000
    67.7 = 01000011.00000111
    18.0 = 00010010.00000000

  3. Преобразуйте новый IP в двоичный бит. 67.7.1 = 01000011.00000111.00000001

  4. Проверьте, начинаются ли новые двоичные биты IP с существующих двоичных битов IP.
  5. Если true, то новая запись существует в базе данных. Например, новый двоичный бит 01000011.00000111.00000001 начинается с существующих двоичных битов ip (67.7) 01000011.00000111 . Остальные записи не совпадают.

Я ищу, есть ли запрос Oracle, который может сделать это для меня, то есть вернуть соответствующие IP-адреса из базы данных. Я проверил текстовый API Oracle, но пока ничего не нашел.

Ответ №1:

Есть ли причина, по которой вы не можете использовать функцию INSTR? http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm#i77598

Я бы сделал что-то вроде NOT EXISTS предложения, которое проверяет INSTR(b_outer.IP_ADDRESS,b_inner.IP_ADDRESS) <> 1

* редактировать: думая об этом, вам, вероятно, нужно будет проверить, равен ли результат 1 (что означает совпадение потенциальных IP-адресов, начинающихся с первого символа существующего IP-адреса), в отличие от общего поиска по подстроке, который у меня был изначально.

Комментарии:

1. Instr не будет работать, если количество битов не перекрывается с коэффициентом 8. например 10.224/11 , является родительским 10.240/12 , но не 10.176/12

2. Спасибо. Я не против добавить в таблицу еще один столбец, который сохраняет ip_address в качестве двоичного битового формата, если это поможет мне достичь этого, используя только запрос, возможно, что-то противоположное предложению LIKE .

Ответ №2:

Да, вы можете сделать это в SQL, преобразовав IP-адреса в числа, а затем убедившись, что это не запись меньшего cidr размера, которая дает тот же ipnum при использовании ее cidr размера.

 WITH ipv AS
(   SELECT  IP.*
        ,   NVL(REGEXP_SUBSTR( ip, 'd ', 1, 1 ),0) * 256 * 256 * 256  -- octet1
            NVL(REGEXP_SUBSTR( ip, 'd ', 1, 2 ),0) * 256 * 256        -- octet2
            NVL(REGEXP_SUBSTR( ip, 'd ', 1, 3 ),0) * 256              -- octet3
            NVL(REGEXP_SUBSTR( ip, 'd ', 1, 4 ),0)  AS ipnum          -- octet4
        ,   32-bits                 AS ignorebits
    FROM  ips IP
)
SELECT  IP1.ip, IP1.bits
FROM    ipv IP1
WHERE   NOT EXISTS
    (   SELECT  1
        FROM    ipv IP2
        WHERE   IP2.bits < IP1.bits
        AND     TRUNC( IP2.ipnum / POWER( 2, IP2.ignorebits ) )
              = TRUNC( IP1.ipnum / POWER( 2, IP2.ignorebits ) )
    )

Примечание: В моем примере используется таблица, эквивалентная вашей:

 SQL> desc ips
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IP                                        NOT NULL VARCHAR2(16)
 BITS                                      NOT NULL NUMBER