Разделить значения через запятую oracle
Я знаю, что на этот вопрос в какой-то степени ответили PHP и MYSQL, но мне было интересно, может ли кто-нибудь научить меня простейшему подходу к разделению строки (с разделителями-запятыми) на несколько строк в Oracle 10g (предпочтительно) и 11g.
Таблица выглядит следующим образом:
Я хочу создать следующее:
Я видел несколько потенциальных решений для стека, однако они учитывали только один столбец (являющийся строкой, разделенной запятыми). Любая помощь будет принята с благодарностью.
Для примеров использования REGEXP , XMLTABLE и MODEL п, см Split , разделенных запятыми строк в таблице с помощью Oracle SQL
Это может быть улучшенный способ (также с regexp и connect by):
РЕДАКТИРОВАТЬ : Вот простое (например, «не углубленное») объяснение запроса.
-
length (regexp_replace(t.error, '[^,]+')) + 1 используется regexp_replace для удаления всего, что не является разделителем (в данном случае запятой), и length +1 для получения количества элементов (ошибок).
- В cast(multiset(. )) as sys.OdciNumberList Преобразует несколько семейств (один сборник для каждой строки в исходном наборе данных) в единый набор чисел, OdciNumberList.
- table() Функция превращает коллекцию в результирующий.
FROM без соединения создает перекрестное соединение между вашим набором данных и мультимножеством. В результате строка в наборе данных с 4 совпадениями будет повторяться 4 раза (с увеличением числа в столбце с именем "column_value").
Некоторые ссылки на документы Oracle:
с 11g вы можете использовать regexp_count(t.error, ',') вместо length (regexp_replace(t.error, '[^,]+')) , что может принести еще одно улучшение производительности
485 секунд при "нормальном" CONNECT BY. Так 0,296 секунды. Ты жжешь! Теперь все, что мне нужно сделать, это понять, как это работает. :-)
@BobJarvis добавил правку, чтобы объяснить, что он делает. Правописание / грамматические исправления приветствуются.
«Принятый ответ плохо работает» - какой ответ принят в этой теме? Пожалуйста, используйте ссылки для ссылки на другой пост.
регулярные выражения - замечательная штука :)
привет, не могли бы вы объяснить мне, почему приведенный выше запрос дает повторяющиеся строки, если я не использовал отдельное ключевое слово в запросе
Причина медлительности в том, что каждая комбинация Name s связана, что можно увидеть, если удалить distinct . К сожалению добавление and Name = prior Name в connect by пункт причин ORA-01436: CONNECT BY loop in user data .
Вы можете избежать ORA-01436 ошибки, добавив AND name = PRIOR name (или любой другой первичный ключ) и AND PRIOR SYS_GUID() IS NOT NULL
Есть огромная разница между двумя нижеприведенными:
- разделение одной строки с разделителями
- разделение строк с разделителями на несколько строк в таблице.
Если вы не ограничиваете строки, то предложение CONNECT BY создаст несколько строк и не даст желаемого результата.
- Для одиночной строки с разделителями см. Разделить одну строку с разделителями-запятыми на строки
- Чтобы разделить строки с разделителями в таблице, см. Раздел Разделить строки с разделителями-запятыми в таблице.
Помимо регулярных выражений , есть еще несколько альтернатив:
Настроить
Использование XMLTABLE :
Использование предложения MODEL :
Решение XMLTABLE по какой-то причине постоянно не выводит последнюю запись для строк смешанной длины. Например. row1: 3 слова; row2: 2 слова, row3: 1 слово; row4: 2 слова, row5: 1 слово - последнее слово не выводится. Порядок строк не имеет значения.
Еще пара примеров того же:
Имейте в виду, что comma_to_table() работает только с токенами, которые соответствуют соглашениям об именах объектов базы данных Oracle. Он будет швырять веревку, '123,456,789' например.
Я хотел бы предложить другой подход с использованием табличной функции PIPELINED. Это несколько похоже на метод XMLTABLE, за исключением того, что вы предоставляете свою собственную функцию для разделения строки символов:
Проблема с этим типом подхода заключается в том, что оптимизатор часто не знает количество элементов табличной функции, и ему приходится делать предположения. Это может быть потенциально вредным для ваших планов выполнения, поэтому это решение можно расширить для предоставления статистике выполнения оптимизатору.
Вы можете увидеть эту оценку оптимизатора, запустив EXPLAIN PLAN для запроса выше:
Несмотря на то, что в коллекции всего 3 значения, оптимизатор оценил для нее 8168 строк (значение по умолчанию). Сначала это может показаться несущественным, но оптимизатору этого может быть достаточно, чтобы выбрать неоптимальный план.
Решение состоит в том, чтобы использовать расширения оптимизатора для предоставления статистики для коллекции:
Тестирование полученного плана выполнения:
Как видите, количество элементов в приведенном выше плане больше не является предполагаемым значением 8196. Это по-прежнему неправильно, потому что мы передаем функции столбец вместо строкового литерала.
Для более точной оценки в данном конкретном случае потребуется некоторая настройка кода функции, но я думаю, что здесь в значительной степени объясняется общая концепция.
Я работаю PL/SQL разработчиком. Есть задача собирать некоторые данные для метрик, чтобы отслеживать загрузку систем. Есть некоторая функция, которая вызывается с параметром, состоящим из списка ID.
Задача заключается в следующем. Нужно разбить такую строку на элементы и записать их в целочисленную коллекцию.
Приступим.
Для начала нужны данные для работы. Напишем функцию, которая генерирует строку с числами, разделенными запятой. В функцию будем передавать целочисленный аргумент N – количество чисел в последовательности.
Мудрить не будем, последовательность сделаем с типом VARCHAR2, а не CLOB. Далее объясню, почему именно VARCHAR2.
Код функции для генерации последовательности:
Вернёмся к нашей задаче.
Первое, что приходит на ум, это сделать цикл по строке, с уменьшением длинны строки на каждой итерации. Так как по условию задачи результат нужно поместить в коллекцию, то создадим соответствующую коллекцию.
Результат:
0
1
2
…
421
422
423
…
Функция createNumber() принимает аргумент v_N = 1000. В функции createNumber() можно видеть обработку переполнения переменной v_str. Нехитрым подсчётом можно выяснить, что 4000 байт хватит для 1021 чисел. Наша 1000 без проблем влезает в этот размер.
Как видно, результат тот, который нужен был. Строка разделена.
Пусть даже в Oracle нет встроенной функции split(), как например в Java или Python, но данный вариант меня не устраивает, так как я считаю, что слишком много кода написано для такой простой задачи как разбиение строки.
На данном этапе я задумался, а можно ли разбить строку только средствами SQL? Я имею ввиду не классический SQL, а тот SQL, который предлагает Oracle.
Я вспомнил про конструкцию для построения иерархических запросов CONNECT BY.
Необязательный оператор START WITH говорит Oracle с чего начинать цикл, т.е. какая строка будет корневой. Условие может быть практически любым. Условие после CONNECT BY нужно указать обязательно. Тут надо сказать Oracle, как долго продолжать цикл.
Видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное «нанизывается» по мере надобности.
Также у этой конструкции есть псевдостолбец level, который возвращает уровень вложенности на текущей итерации.
На первый взгляд может показаться, что данная конструкция для разбиения строки не подходит. Это не совсем так. Если правильно задать условие, то рекурсивный обход можно превратить в циклический, как в циклах while или for.
Прежде чем писать запрос, обдумаем алгоритм обхода строки. Нужно, начиная от начала строки, отрезать некоторое количество символов, до символа разделителя. Выше я писал про псевдостолбец level. Мы его будем использовать, как номер текущей итерации.
Получается что-то такое:
Но если присмотреться, то можно увидеть, что данный алгоритм не сработает на самой первой итерации, так как третий аргумент функции INSTR() не может равняться 0.
Поэтому добавим небольшое условие с помощью функции DECODE().
Теперь самая первая итерации будет отрабатывать корректно.
Пора бы применить конструкцию CONNECT BY. Плюс вынесем нашу строку наверх.
Я уже писал, что при правильном условии конструкция CONNECT BY сможет вести себя подобно циклу. Условие выполняется до тех пор, пока функция INSTR() может найти n-ую позицию символа разделителя, где n – это номер текущей итерации, а как мы помним за номер итерации отвечает псевдостолбец level.
Вроде бы задача решена? Нет.
Код может и работает, но его читаемость нулевая. Я уже думал вернуться к варианту с циклом, но придумал как улучшить вариант с CONNECT BY.
В Oracle есть такое мощное средство, как регулярные выражения. Конкретно функции regexp_instr() и regexp_substr().
regexp_instr(исходная_строка, шаблон[, начальная_позиция [, вхождение ] ]) — функция возвращает позицию символа, находящегося в начале или конце соответствия для шаблона, так же как и ее аналог INSTR().
regexp_substr(исходная_строка, шаблон[, позиция [, вхождение ]]) — функция возвращает подстроку, которая соответствует шаблону.
Перепишем запрос, используя регулярные выражения:
Код читается намного лучше, чем в предыдущем примере. Такой вариант меня устраивает.
В конце было бы логичным привести сравнения времени выполнения разбора строки для трёх вариантов. Выше я обещал объяснить, почему вместо типа CLOB будем использовать тип VARCHAR2. Это нужно как раз для сравнения времени выполнения. Так как Oracle обрабатывает тип CLOB по-другому, чем VARCHAR2, что может исказить результаты.
Сперва мы узнаем, кто был в мужьях у этих красоток. А потом с помощью незамысловатых спецэффектов я вам покажу, в каком порядке они друг с другом бракосочетались. Так что юным девам эта статья будет особенно интересна.
Создадим и заполним базовую таблицу
ID | Актриса | Мужья |
---|---|---|
1 | Анджелина Джоли | Джонни Ли Миллер, Билли Боб Торнтон, Брэд Питт |
2 | Шарлиз Терон | |
3 | Пенелопа Крус | Хавьер Бардем |
Из таблицы видно, что Анжелика была замужем трижды. Ее мужья перечислены в колонке через разделитель в порядке очередности их бракосочетания с актрисой. Условимся, что разделитель — это запятая, а пробел после нее — просто мусор.
Лиза Терон вообще ни разу не была замужем (гражданские браки не в счет), и она, по всей видимости, до сих пор ждет своего айтишника. Так что следует взять это на заметку и как следует поторопиться — даме уже, без малого, 40.
Ну и Пенелопа Крус — замужем всего один раз. Какая скука.
Но это все прелюдия, а на деле нужно получить следующий результат
Актриса | Муж | Номер мужа п/п |
---|---|---|
Анджелина Джоли | Джонни Ли Миллер | 1 |
Анджелина Джоли | Билли Боб Торнтон | 2 |
Анджелина Джоли | Брэд Питт | 3 |
Шарлиз Терон | ||
Пенелопа Крус | Хавьер Бардем | 1 |
По сути надо выполнить операцию, обратную группировке и агрегации функцией listagg.
Будем двигаться от простого к сложному. Для начала предлагаю рассмотреть похожую задачу — извлечение чисел из одиночной строки с разделителем в табличный набор.
- Генерируются новые строки с помощью connect by level.
- Через regexp_count вычисляется количество чисел в строке между разделителями – это количество определяет верхнюю границу для генератора строк.
- С помощью regexp_substr извлекаются числа из строки. Номер вхождения шаблона в строку (4-й аргумент регулярки) соответствует значению псевдостолбца rownum — номер п/п сгенерированной строки. Вместо rownum можно было использовать и level, результат был бы аналогичным.
В таблице HOLLYWOOD мы имеем дело не с последовательностью чисел, а с именами знатных мужей. Но их можно также подсчитать с помощью функции regexp_count и извлекать, используя функцию regexp_substr, согласно вышеописанной методе. Теперь нужно вспомнить рецепты маринования бананов и выбрать один из способов генерации строк, когда известно их будущее количество. Для примеров я воспользуюсь 3-м и 5-м способом. Но при выборе наиболее оптимального метода генерации нужно обратить внимание на коммент пользователя xtender.
Объединив подходы, получаем следующее.
Спецэффект № 1.
Всё ОК – девушки счастливы в браках. Все, кроме Лизы Терон. Для таких, как Лиза, в запросе я использовал nvl2.
Спецэффект № 2.
Это было решение через коллекции.
UPD: Нарисовался еще один замечательный Спецэффект № 3 для Oracle 12c
Очевидно, что вариантов решения задачи существует немало. Выбор того или иного варианта – дело вкуса и вопрос производительности. Если бы у меня был Оскар, я бы его не задумываясь вручил тому, кто предложит наиболее лаконичный с точки зрения кода и наиболее оптимальный с точки зрения производительности способ.
Чтобы понять рекурсию, сначала надо понять рекурсию. Возможно, поэтому рекурсивные запросы применяют так редко. Наверняка вы представляете что такое SQL-запрос, я расскажу, чем рекурсивные запросы отличаются от обычных. Тема получилась объемная, приготовьтесь к долгому чтению. В основном речь пойдет об Oracle, но упоминаются и другие СУБД.
Суть проблемы
Большинство современных СУБД (Система Управления Базами Данных) — реляционные, т.е. представляют данные в виде двумерной таблицы, в которой есть строки (записи) и столбцы (поля записей). Но на практике мы часто сталкиваемся с иной организацией данных, а именно иерархической.
Взгляните на список файлов на вашем компьютере: все они организованы в виде дерева. Аналогично можно представить книги в библиотеке: Библиотека->Зал->Шкаф->Полка->Книга. То же самое и статьи на сайте: Сайт->Раздел->Подраздел->Статья. Примеры можно приводить долго. Впрочем, тут еще можно разделить все на отдельные таблицы: таблица для хранения списка библиотек, другая таблица для списка залов, третья для шкафов и т.д. Но если заранее не известна глубина вложенности или эта вложенность может меняться, тут уж от иерархии никак не отмашешься.
Проблема в том, что данные, имеющие иерархическую структуру, очень плохо представляются в реляционной модели. В стандарте SQL-92 нет средств для их обработки.
Зато такие средства появились в стандарте SQL-1999. Правда к тому времени в Oracle уже был собственный оператор CONNECT BY. Несмотря на это, в SQL-1999 синтаксис рекурсивных запросов совершенно не похож на синтаксис CONNECT BY в Oracle и использует ключевое слово WITH. Реализация же рекурсивных запросов в других СУБД несколько запоздала, так в MS SQL Server она появилась лишь в версии 2005.
Так же как и в синтаксисе, есть отличия и в терминологии. В Oracle обычно обсуждаемые запросы называются “иерархические”, у всех остальных “рекурсивные”. Суть от этого не меняется, я буду использовать и то и другое.
От слов к делу!
Для демонстрации будем использовать структуру каталогов, нам потребуется тестовая таблица, состоящая из 3-х полей:
id – идентификатор,
pid – идентификатор родителя (ссылается на id другой записи в той же таблице),
title – название каталога (вместо него может быть что угодно, даже несколько полей или ссылок к другим таблицам).
Здесь я использовал синтаксис mySQL, в других СУБД он несколько отличается. Так, в Oracle используются другие типы данных: вместо int — number, а вместо varchar — varchar2.
Заполнить табличку тестовыми данными не составит труда. Предлагаю записать список серверов, расположенных на территории различных предприятий в разных городах. Таким образом, в единой таблице оказываются и страны, и города, и фирмы, и сервера.
Думаю, остальные строки заполнить не составит сложностей. Получается почти как на картинке, только не в том порядке. Я специально заполнял не по-порядку, чтобы простым SELECT * FROM test_table получалось не иерархическая структура:
Тестовые данные готовы. Приступим к выборкам.
mySQL
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).
Впрочем, есть иной подход, заключающийся в создании левой и правой границы для каждого узла, предложенный Джо Селко. Тогда можно будет обойтись обычными, не рекурсивными запросами.
Как-то я выводил дерево объектов в действующем проекте на php. База данных была на mySQL. Поплевавшись на отсутствие удобных операторов, я решил тогда не отображать все дерево целиком, а показать пользователю только первый уровень (схлопнутое дерево). При клике по плюсику в узле дерева отображались дочерние узлы для выбранного объекта, при этом они подгружались через AJAX. Выборка дочерних узлов по известному pid происходит быстро, поэтому интерфейс получился вполне шустрым. Возможно, это не лучшее решение, но оно имеет право на жизнь.
SQL-1999
В отличие от предыдущего стандарта SQL-92, в названии следующего решили отобразить номер тысячелетия, чтобы он не страдал от проблемы двухтысячного года. Помимо этого :-), появились новые типы данных (LOB), новые предикаты SIMILAR и DISTINCT, точки сохранения транзакций, объекты и их методы, и многое другое. Среди нововведений появились также рекурсивные запросы, о которых мы сейчас и поговорим.
Для получения иерархических данных используется временное представление, которое описывается оператором WITH. После этого из нее выбираются данные простым селектом. В общем виде синтаксис примерно такой:
В MS SQL нет ключевого слова RECURSIVE, его следует опустить. Но в остальном все то же самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL, начиная с версии 2005, и во всех базах данных, которые поддерживают стандарт SQL 1999.
Тогда для получения нашего дерева запрос получится такой:
WITH RECURSIVE
Rec (id, pid, title)
AS (
SELECT id, pid, title FROM test_table
UNION ALL
SELECT Rec.id, Rec.pid, Rec.title
FROM Rec, test_table
WHERE Rec.id = test_table.pid
)
SELECT * FROM Rec
WHERE pid is null ;
Здесь используется рекурсивная таблица Rec, которую мы сами придумали, построив ее по исходной таблице test_table. В описании Rec указано правило, каким образом соединять: WHERE Rec.id = test_table.pid. А в главном запросе отметили, что начинать надо с записи, у которой pid является пустым, т.е. с корневой записи.
Честно говоря, я никогда не работал в MS SQL Server 2005 или другой СУБД, использующей такой синтаксис. Поэтому написал этот запрос чисто из теоретических соображений. Для общности картины, чтобы было с чем сравнить.
В MS SQL 2008 можно применить более новое средство hierarchyid. Спасибо XaocCPS за его описание.
Oracle
Описание синтаксиса в документации напоминает бусы: на единую нить запроса нанизываются нужные операторы. Никому не приходило в голову сделать украшение для гички?
Тут видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное “нанизывается” по мере надобности.
Необязательный оператор START WITH говорит Ораклу с чего начинать цикл, т.е. какая строка (или строки) будет корневой. Условие может быть практически любым, можно даже использовать функции или внутренние запросы: pid is null, или или даже substr(title, 1, 1) = ‘Р’.
Как же получить нормальную иерархию? Нужно использовать специальный оператор, который называется PRIOR. Это обычный унарный оператор, точно такой же как + или -. “Позвоните родителям” – говорит он, заставляя Оракл обратиться к предыдущей записи. С его помощью можно написать правило pid = PRIOR id (или PRIOR как говорится, от перестановки мест…).
Что получается? Оракл находит первую запись, удовлетворяющую условию в START WITH, и принимается искать следующую. При этом к той первой записи можно обратиться через PRIOR. Если мы все сделали правильно, то Оракл будет искать записи, в которых в поле для хранения информации о родителе (pid) будет содержаться значение, равное идентификатору id нашей первой записи. Таким образом будут найдены все потомки корневой записи. А так как процесс рекурсивный, аналогичный поиск будет продолжаться с каждой найденной строкой, пока не отыщутся все потомки.
Теперь у нас есть все необходимое, чтобы написать иерархический запрос в Oracle. Но прежде чем мы его напишем, расскажу еще об одной штуке. Порядок строк это хорошо, но нам было бы трудно понять, две строки рядом это родитель и его потомок или два брата-потомка одного родителя. Пришлось бы сверять id и pid. К счастью, Oracle предлагает в помощь дополнительный псевдостолбец LEVEL. Как легко догадаться, в нем записывается уровень записи по отношению к корневой. Так, 1-ая запись будет иметь уровень 1, ее потомки уровень 2, потомки потомков — 3 и т.д.
Неплохо. Все дочерние строки оказываются под своими родителями. Сортировку бы еще добавить, чтобы записи одного уровня выводились не абы-как, а по алфавиту. Ну чтож, сортировка это просто: добавим в конец запроса конструкцию ORDER BY title.
SELECT level , id, pid, title
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER BY title;
О, нет! Вся иерархия поломалась. Что же получилось? Оракл честно выбрал нужные строки в порядке иерархии (об этом говорит правильная расстановка level), а затем пересортировал их согласно правилу ORDER BY. Чтобы указать Ораклу, что сортировать надо только в пределах одного уровня иерархии, нам поможет маленькая добавка в виде оператора SIBLINGS. Достаточно изменить условие сортировки на ORDER SIBLINGS BY title – и все встанет на свои места.
Кстати, возможно все еще не понятно, почему этот порядок строк является деревом. Можно убрать все “лишние” поля и добавить отступы, станет более наглядно:
SELECT lpad( ' ' , 3* level )||title as Tree
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER SIBLINGS BY title;
Ну вот, теперь все в точности, как на картинке в самом начале статьи.
Помните, файловые менеджеры обычно пишут путь к каталогу, в котором вы находитесь: /home/maovrn/documents/ и т.п.? Неплохо было бы и нам сделать так же. А сделать это можно абсолютно не напрягаясь: специалисты из Oracle все уже сделали за нас. Просто берем и используем функцию SYS_CONNECT_BY_PATH(). Она принимает два параметра через запятую: название колонки и строку с символом-разделителем. Будем не оригинальны, напишем так: SYS_CONNECT_BY_PATH(title, ‘/’).
Заодно ограничим вывод, выбрав только одну строку. Для этого, как всегда, нужно добавить условие WHERE. Даже в иерархическом запросе ограничивающее условие применяется ко всем строкам. Вставить его надо до иерархической конструкции, сразу после FROM. Для примера определим путь до “Сервер 1”, который у нас записан с > SELECT SYS_CONNECT_BY_PATH(title, '/' ) as Path
FROM test_table
WHERE > START WITH pid is null
CONNECT BY PRIOR >
Еще может быть полезен псевдостолбец CONNECT_BY_ISLEAF. Его можно использовать так же, как LEVEL. В этом псевдостолбце напротив каждой строки проставляется 0 или 1. Если есть потомки – проставится 0. Если потомков нет, такой узел в дереве называется “листом”, тогда и значение в поле CONNECT_BY_ISLEAF будет равно 1.
Устали? Осталось немного, самое страшное уже позади. Раньше мы использовали оператор PRIOR, который ссылался к родительской записи. Помимо него есть другой унарный оператор CONNECT_BY_ROOT, который ссылается (ни за что не догадаетесь!) на корневую запись, т.е. на самую первую в выборке.
SELECT id, pid, title, level ,
CONNECT_BY_ISLEAF as IsLeaf,
PRIOR title as Parent,
CONNECT_BY_ROOT title as Root
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER SIBLINGS BY title;
Стоит отметить, что если в результате выполнения запроса обнаружится петля, Oracle выдаст ошибку. К счастью, ее можно обойти, хотя если в данных содержатся петли – это явно ошибка, в деревьях не бывает петель. На картинке с “бусами” запроса был нарисован оператор NOCYCLE после CONNECT BY – его мы и будем применять. Теперь запрос не будет вылетать. А чтобы определить “больной” участок, воспользуемся псевдостолбцом CONNECT_BY_ISCYCLE – в нем во всех хороших строках будет записано 0, а в тех, которые приводят к петлям, волшебным образом окажется 1.
Чтобы проиллюстрировать это, придется немного подпортить данные. ЛискиПресс ссылается у нас на город Лиски; изменим запись Лиски, чтобы она ссылалась на ЛискиПресс (не забудьте про commit – я вечно забываю):
Если мы запустим какой-нибудь из предыдущих запросов, увидим, что и Лиски, и ЛискиПресс выпали из выборки, будто их нет совсем. Бегая в цикле, Оракл просто перестал на них натыкаться, т.к. нет пути от записи Россия к городу Лиски. Изменим условия START WITH, чтобы начинать с города Лиски – появится ошибка. Умный Оракл видит что запись уже выбиралась ранее и отказывается бегать в бесконечном цикле. Исправляем ошибку:
SELECT CONNECT_BY_ISCYCLE as cycl, id, pid, title
FROM test_table
START WITH > CONNECT BY NOCYCLE PRIOR >
Практические примеры
Иерархические запросы можно применять не только там, где есть явная иерархия.
Например, рассмотрим задачу получения списка пропущенных номеров из последовательности. Это бывает нужно, когда в некоей таблице id генерируется автоматически путем увеличения на 1, но часть записей были удалены. Нужно получить список удаленных номеров. По хорошей традиции, это следует сделать одним селектом.
Подготовим тестовые данные. Удалим из нашей таблицы пару записей:
С чего начнем? Во-первых, неплохо было бы получить список номеров подряд от 1 до максимального значения в нашей таблице, чтобы было с чем сравнивать. Выяснить максимальное значение id из таблицы, думаю, не составит никаких трудностей:
А вот чтобы сгенерировать последовательность от 1 до max как раз и понадобится рекурсивный запрос. Ведь как здорово просто взять и получить нужное количество строк! Достаточно будет их пронумеровать – и вот список готов.
Конструкция “SELECT … FROM dual” используется, когда надо вычислить значение функции, не производя при этом выборки данных. Dual – это системная таблица, состоящая из одного столбца и одной строки. Запрос из нее всегда возвращает одну строку со значением ‘X’. Благодаря такой умопомрачительной стабильности, эту таблицу удобно использовать в качестве источника строк.
Обычно таблицу, которую нагло используют для получения нужного количества строк, не выбирая сами данные, называют pivot. В качестве такой таблицы может выступать любая большая таблица, в том числе системная. Но использование dual в Oracle является более разумным решением.
Теперь, когда список номеров подряд уже есть, достаточно пройтись по нему и сравнить, есть ли такой номер в проверяемой таблице:
SELECT sq.rn
FROM ( SELECT rownum as rn FROM dual
CONNECT BY level WHERE sq.rn not in ( SELECT id FROM test_table)
ORDER BY rn;
Всё. Ведро начищено до блеска, лошадь вооружена биноклем для остроты зрения. Да не коснется вас ROLLBACK. COMMIT!
Будет ли это работать? SELECT COLUMN_VALUE FROM TABLE(split('A,B,C,D,E,F', ',')) Кстати, какую версию Oracle вы используете?
@vipin.huddar Правда? Oracle 11gR2 говорит мне, что «такой функции нет». Где в документации Oracle вы это нашли?
Используя REGEXP_SUBSTR:
Использование XMLTABLE:
Попробуйте использовать следующий запрос:
Ниже запрос с идентификатором:
РЕДАКТИРОВАТЬ: Попробуйте использовать приведенный ниже запрос для нескольких идентификаторов и множественного разделения:
Не зафиксировано. Для конкретного идентификатора может быть любое количество значений, разделенных запятыми.
@Nisha Это не будет работать для нескольких идентификаторов во входных данных (подзапрос с одной строкой возвращает более одной строки) - я думаю, вам нужно добавить предложение WHERE для идентификатора в вашем подзапросе для LENGTH.
Спасибо за помощь, я нашел направление. Но значение ID жестко закодировано, чего не будет со мной. Да, и, как сказал Фрэнк, это не сработает для нескольких идентификаторов.
я решил подобную проблему таким образом.
Вы можете попробовать что-то вроде этого:
Разве я не могу сделать это с помощью одного запроса, так как я хочу избежать добавления новой функции или чего-то подобного в мою требуемую среду БД.
Не используйте CONNECT BY или REGEXP, которые приводят к декартовому произведению сложного запроса. Кроме того, приведенные выше решения предполагают, что вы знаете возможные результаты (A, B, C, D, E, F), а не список комбинаций.
COLUMN_VALUE — это псевдостолбец, принадлежащий xmltable. Это быстро и правильно и позволяет вам ссылаться на столбец, не зная его значений.
Это берет столбец и создает таблицу значений "item", "item2", "item3" и автоматически присоединяется к исходной таблице (CONTACTS). Это было проверено на тысячах строк
Обратите внимание на ';' в xmltable разделитель в поле столбца.
Читайте также: