Агрегатная функция для конкатенации строк в Oracle

Мне не так чтобы часто, но регулярно требуется с помощью SQL сделать конкатенацию всех строк в выборке где-то в таком духе:

Проблема в том, что ни в стандартном SQL, ни в Oracle (до самого недавнего времени) не было функции вроде CONCAT. Что можно сделать?

Пользовательская функция

Можно написать свою собственную агрегатную функцию на PL/SQL. Не то чтобы это было совсем не фан, но коду нужно прилично настрочить.

Главная проблема такого подхода — привязка SQL к вашей базе данных. Если вы кому-то даете свой SQL, вы должны дать ему и функцию.

WM_CONCAT

Оказывается, в Oracle есть функция под названием WM_CONCAT, которая делает ровно то, что нужно:

Она даже поддерживает DISTINCT. Все бы хорошо, но эта функция не поддерживается Oracle официально, и СТРАШНО ГЛЮЧИТ. Она может втихую вернуть NULL. Может вдруг (именно что вдруг, вчера все работало, а сегодня план поменялся) начать выдавать ошибки, например ORA-22922. Так что не используйте эту функцию!

LISTAGG

В Oracle 11g Release 2 наконец-то добавили стандартную встроенную агрегатную функцию для конкатенации: LISTAGG. Она всем хороша: явно указывается порядок сортировки значений, можно задать произвольный разделитель, работает с DISTINCT, ее можно использовать как аналитическую функцию.

Недостатков у функции всего два. Во-первых, нет способа вернуть CLOB, а не VARCHAR2; значит, длина результата ограничена. WM_CONCAT, к примеру, возвращает CLOB. Но этот недостаток может даже преимущество скорее, поскольку такие длинные строки обычно не нужны, а касты лишний раз делать — некрасиво. Во-вторых… ну, это временный недостаток, но пока актуальный: еще не все перешли на последний Oracle.

Upd от 03.09.2015

У LISTAGG есть еще один, регулярно крайне досаждающий недостаток: не поддерживается DISTINCT. Что мешало прикрутить фичу есть загадка выше моего понимания. Раз уж сделана сортировка значений, то какие технические проблемы в том, чтобы дубликаты по пути убрать?

Что делать, приходится изгаляться. Один из вариантов — это описанный ниже вариант со вложенным запросом, в котором DISTINCT уже сделан. Громоздко и не всегда возможно. Народ придумал другой способ: убрать дубликаты регулярными выражениями. Вот пример из моей практики:

Для тех, кто не очень знаком с регулярными выражениями, поясню суть происходящего: каждая найденная последовательность одинаковых значений, разделенных (в данном случае) запятой, заменяется первым значением из найденной последовательности. Естественно, чтобы это работало, сами значения не должны содержать разделителя. В моем случае все ОК, номера счетов в моей таблице запятых содержать не могут по определению.

Регулярные выражения — это явно не самый эффективный способ убирать дубликаты, но зачастую это не проблема.

XML

Так что те, кто сидит на более старой версии, чем 11g-2 (а также мазохисты, которым все же нужен CLOB), используют расширения Oracle для работы с XML:

Выглядит это жутко, но зато работает, работает надежно, и на всех версиях. Из минусов — нет DISTINCT и красивого определения порядка значений в результате. Минусы при желании обходятся, так же длинно и неуклюже. Например, DISTINCT можно заменить SELECT’ом из SELECT’а:

Итого

Если вас не интересует обратная совместимость и вы уже на 11g-2 или выше, используйте LISTAGG. В противном случае извращайтесь с XML, ну или совсем уж извращайтесь и пишите свою собственную агрегатную функцию.

  2 comments for “Агрегатная функция для конкатенации строк в Oracle

  1. Иван
    01.06.2013 at 14:25

    И с каких это пор listagg «работает с DISTINCT»?

    • mgsxx
      01.06.2013 at 14:32

      Ты прав, я ошибся. LISTAGG с DISTINCT не работает.
      Странно, почему так. Лавры монополиста позволяют забивать на очевидные вещи?

Добавить комментарий