Недокумментированная функция Oracle WM_CONCAT

8 октября 2011 г.

В Oracle можно встретить недокумментированные функции, которые свою очередь весьма полезны. Хотелось бы рассмотреть одну из них используемую и появившуюся в Oracle версиях 10g. В Oracle версиях выше 11g R2 есть уже докумментированная функция выполняющая тот же функционал. Наверное, интересно что это за функционал. Давайте рассмотрим стандартную задачу. У нас есть департамент и сотрудники соответственно прикрепленные за этим департаментом. Нам дали задачу вывести все департаменты их описание и имена сотрудников работающих в нем. При этом строк должно быть равно количеству департамента. Естественно у нас появляется вопрос, ведь сотрудники и департамент имеет соотношение один ко многим, плюс к тому же мы не сможем точно знать количество сотрудников в каждый момент, и заводить столбец для каждого сотрудника пустое расстачительство место, которое не имеет смысла. Одним из решение которое выглядит весьма красивым — есть перечисление имена всех сотрудников через запятую в одном столбце. Как же решить это используя только SQL не прибегая к программированию на PL/SQL.

Вот в этом случаи к нам на помощь и приходит WM_CONCAT, которая поможет соединить строки с наименьшими усилиями.

К примеру есть таблица по которой запрос ввиде

SELECT deptno, ename AS employees
FROM scott.emp

возвращает следующие строки:

DEPTNO EMPLOYEES
 
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

Мы хотим отобразить имена сотрудников через запятую согласно департамента в котором трудится этот сотрудник. Написав и выполнив запрос:

SELECT deptno, wm_concat (ename) AS employees
FROM scott.emp
GROUP BY deptno;

Получим

DEPTNO EMPLOYEES
 
CLARK,KING,MILLER
SMITH,FORD,ADAMS,SCOTT,JONES
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Да впечатляет, легкость решение не простой задачи. Но здесь есть оговорки — Имена сотрудников можно перечислять только через запятую. Если количество сотрудников в одном департаменте составит больше определенного количество, то может вернутся ошибка. В некоторых релизах Oracle версиях 10g возможно данная функция будет работать иначе — это будет естественное поведение недокументированной функции, на то она и недокументированная.

В Oracle версиях выше 11g R2 появилась функция LISTAGG, в которой место запятой можно указать тот разделитель который вам хочется.
пример запроса используя LISTAGG:

SELECT deptno, LISTAGG(ename, ',') AS employees
FROM scott.emp
GROUP BY deptno;

Теги: Oracle
рубрика Программирование