Выборка из списка значений в Oracle без UNION: решение

Быстрый ответ

Если перед вами стоит задача выбора данных по списку значений в Oracle, существует несколько распространённых способов. Наиболее часто используются подзапросы с конструкцией WITH или метод UNION ALL. Тем не менее, имеется и более изящное решение: применение функции TABLE вместе с sys.odcivarchar2list, что позволяет быстро сформировать одномерную таблицу:

SELECT * FROM TABLE(sys.odcivarchar2list('value1', 'value2', 'value3'));

Подобный метод предоставляет высокую эффективность и удобство при работе с динамически создаваемыми таблицами в запросе SELECT. Необходимо подходить к выбору инструмента обосновано, учитывая объём данных и поставленные перед вами требования к производительности.

Продвинутые подходы к выборке данных

В арсенале Oracle существует множество методов для эффективной выборки данных из списка значений.

Обработка списков значений, разделённых запятыми

Функция regexp_substr совместно с connect by level позволяет преобразовать строку в структурированную таблицу для комфортной работы со значениями, разделёнными запятой:

SELECT 
  trim(regexp_substr('value1,value2,value3', '[^,]+', 1, level)) AS value
FROM dual
CONNECT BY 
  regexp_substr('value1,value2,value3', '[^,]+', 1, level) IS NOT NULL;

Подробнее об этом расскажет наш спикер на видео

Работа с числовыми наборами

Для наборов числовых значений можно применять sys.odcinumberlist, что исключает необходимость создания отдельного типа данных:

SELECT * FROM TABLE(sys.odcinumberlist(1, 2, 3));

В версиях Oracle начиная с 12.2 появилась возможность прямой вставки подобных коллекций в таблицы:

INSERT INTO your_table
SELECT * FROM sys.odcinumberlist(1, 2, 3);

Пользовательская функция PL/SQL для парсинга

В более сложных ситуациях может оказаться эффективным создание функции PL/SQL, которая преобразует строку с CSV в таблицу:

CREATE OR REPLACE FUNCTION parse_csv (p_csv IN VARCHAR2)
   RETURN sys.odcivarchar2list PIPELINED IS
BEGIN
   FOR i IN (SELECT 
                trim(regexp_substr(p_csv, '[^,]+', 1, level)) AS value
             FROM dual
             CONNECT BY 
                 regexp_substr(p_csv, '[^,]+', 1, level) IS NOT NULL)
   LOOP
      PIPE ROW(i.value);
   END LOOP;
   RETURN;
END parse_csv;
/

SELECT * FROM TABLE(parse_csv('value1,value2,value3'));

Создание пользовательских типов

Определение SQL типа упорядочивает код и делает его последовательным:

CREATE TYPE varchar2_tab AS TABLE OF VARCHAR2(100);
/

После этого можно воспользоваться всеми достоинствами созданного типа:

SELECT * FROM TABLE(varchar2_tab('value1', 'value2', 'value3')) AS t;

Визуализация

В Oracle выбор данных из списка можно представить себе как точный выбор конфет из ассорти:

🎁 Коробка конфет (Список значений): [🍫, 🍬, 🍭, 🍡]

Запрос служит инструментом для выбора:

SELECT * FROM TABLE(list_of_values);

И в результате, вы получаете выбранные конфеты:

🍫🍬  == Конкретные значения из списка

Точно так же в SQL, ключ к успеху заключается в эффективности!

Совместимость со схемой

При применении собственных методов всегда контролируйте, чтобы структура базы данных была совместима с вашими решениями. Например, при использовании number_tab, убедитесь в совместимости его с целевой таблицей:

CREATE TYPE number_tab AS TABLE OF NUMBER;
/

INSERT INTO your_number_table (col)
SELECT * FROM TABLE(number_tab(1, 2, 3)) AS t;

Регулярное использование списков значений требует использования надёжных методов, основанных на регулярных выражениях. В таких ситуациях становится полезным подход, представленный Джастином Кейвом – это значительно упростит обработку данных!

Полезные материалы

1. SELECT — официальная документация Oracle по использованию подзапросов с WITH.
2. Oracle / PLSQL: UNION ALL Operator — руководство по использованию UNION ALL для объединения результатов из разных наборов.
3. sql – How do I limit the number of rows returned by an Oracle query after ordering? – Stack Overflow — обсуждение методов ограничения строк в запросах на Stack Overflow.
4. Ask TOM — сообщество Oracle, где обмениваются опытом по выборке данных из списков.