В предыдущем посте
рассматривались решения разбиения строки по разделителю. В этот раз попробуем
разные способы собрать строку из столбцов.
Для
демонстрации решений используем таблицу all_tables, и будем ее ограничивать в
большинстве случаев по псевдостолбцу rownum, чтобы не выходить за рамки
размерности varchar2.
Если
работаем с ORACLE 12c и выше и используем для вывода результата агрегации
VARCHAR2, то нужно выяснить какая размерность VARCHAR2. Для этого смотрим,
какое значение имеет параметр MAX_STRING_SIZE.
select * from v$parameter
where name = 'max_string_size'
Этот
параметр может иметь два значения: STANDARD и EXTENDED, по умолчанию STANDARD.
VALUE:
STANDARD, это означает, что максимальный размер 4000 bytes для VARCHAR2 и NVARCHAR2
VALUE:
EXTENDED, это означает, что максимальный размер 32767 byte.
Изменить в
БД через alter system MAX_STRING_SIZE можно со STANDARD на EXTENDED, но
не наоборот.
Превышение
размерности проявляется ошибкой ORA-00910.
Начнем с
наиболее часто применяемой функции.
💧 LISTAGG
Аналитическая функция LISTAGG была представлена в
Oracle 11g Release 2, что заметно упростило агрегирование строк. Эта функция
хороша тем, что она также позволяет упорядочивать элементы в объединенном
списке.
select owner,
listagg(table_name, ',') within group (order by table_name) as vtables
from all_tables t1
where rownum <=10
group by owner;
LISTAGG обеспечивает наилучшую производительность,
если сортировка является обязательной. В 12с функция была улучшена, что
позволило корректно обрабатывать ошибки переполнения.
Синтаксис:
- LISTAGG(<COLUMN>, '<DELIMITER>' ON OVERFLOW TRUNCATE '<TRUNCATION-INDICATOR>' <WITH/WITHOUT COUNT>) WITHIN GROUP (ORDER BY <COLUMN>)
Закомментируем
строку, ограничивающую набор данных:
select owner,
listagg(table_name, ',') within group (order by table_name) as vtables
from all_tables t1
-- where rownum <=10
group by owner;
И получим ошибку:
ORA-01489: result of string concatenation is too long
Это поведение по умолчанию функционально эквивалентно
явному указанию предложения о переполнении ON OVERFLOW ERROR.
select owner,
listagg(table_name, ',' ON OVERFLOW ERROR) within group (order by table_name) as vtables
from all_tables t1
-- where rownum <=10
group by owner;
Если ошибку нет необходимости видеть или выносить в exception используем TRUNCATE
(WITH COUNT is default).
ON OVERFLOW TRUNCATE
'~~~~' : это будет
отображать ~~~~ (5512)
в конце строки (по умолчанию ... - троеточие)
ON
OVERFLOW TRUNCATE '' : с одинарными кавычками без символов внутри будет
отображать 4000 или 32767 символов без какой-либо завершающей строки, только
количество символов, например (5512)
ON
OVERFLOW TRUNCATE WITHOUT COUNT : это не будет отображать общее
количество символов в конце после завершающих символов.
💧 Функция WM_CONCAT - до 12с
select owner,
wm_concat(table_name) AS tables
from all_tables
where rownum <= 10;
Это
недокументированная функция, и к сожалению начиная с 12с мы вынуждены с ней
попрощаться. Для того, чтобы не переписывать весь legacy после миграции, можно
написать свою standalone function с таким же названием (например, User-Defined
Aggregate Function - см.ниже).
Несмотря на
то, что в 11g появился LISTAGG, этой функции очень не хватает.
Различия:
|
WM_CONCAT
|
LISTAGG
|
|
Разрешает использовать distinct
|
Не разрешает
|
|
Не позволяет сделать сортировку внутри агрегации
(order by)
|
Сортировка предусмотрена
|
|
Нельзя указать разделитель, всегда запятая
|
Разделитель указывается
|
|
Можно использовать в OLAP функциях вместе с order by
|
Нельзя использовать в OLAP функциях вместе с
order by. Без – можно.
|
|
Можно использовать с KEEP
|
Нельзя использовать с KEEP
|
|
Возвращает CLOB (до 10.2.0.5 возвращала VARCHAR2)
|
Возвращает VARCHAR2
|
💙 User-Defined Aggregate Function
Oracle
предоставляет ряд предопределенных агрегатных функций, таких как MAX, MIN и
SUM, для выполнения операций с набором строк. Эти предопределенные агрегатные
функции могут использоваться только со скалярными данными, но не со сложными
типами данных. Мы можем создать определяемую пользователем агрегатную функцию
используя ODCIAggregate как методы внутри типа объекта, поэтому реализация
может быть на любом языке, поддерживаемом Oracle, PL / SQL, C, C ++ или Java.
После определения типа объекта и использования ODCIAggregate в теле типа
создаем агрегатную функцию.
В данном
случае мы агрегируем строковые данные - то, что когда-то могла делать функция
WM_CONCAT. Varchar2 или CLOB - это зависит о того, какой тип переменной g_string
будет объявлен в типе.
Создаем тип:
create or replace type t_string_agg as object
(
g_string varchar2(32767),
static function
odciaggregateinitialize(sctx in out t_string_agg)
return number,
member function
odciaggregateiterate(self in out t_string_agg,
value in varchar2 )
return number,
member function
odciaggregateterminate(self in t_string_agg,
returnvalue out varchar2,
flags in number)
return number,
member function
odciaggregatemerge(self in out t_string_agg,
ctx2 in t_string_agg)
return number
);
create or replace type body t_string_agg is
static function
odciaggregateinitialize(sctx in out t_string_agg)
return number is
begin
sctx := t_string_agg(null);
return odciconst.success;
end;
member function
odciaggregateiterate(self in out t_string_agg,
value in varchar2 )
return number is
begin
self.g_string := self.g_string || ',' || value;
return odciconst.success;
end;
member function
odciaggregateterminate(self in t_string_agg,
returnvalue out varchar2,
flags in number)
return number is
begin
returnvalue := substr(self.g_string, 2);
return odciconst.success;
end;
member function
odciaggregatemerge(self in out t_string_agg,
ctx2 in t_string_agg)
return number is
begin
self.g_string := self.g_string || ctx2.g_string;
return odciconst.success;
end;
end;
Создаем
функцию с использованием типа:
create or replace function
string_agg(p_input varchar2)
return varchar2
parallel_enable aggregate using t_string_agg;
Проверяем в
запросе:
select owner,
string_agg(table_name) as vtables
from all_tables
where rownum <= 10
group by owner;
💧 Функция для конктретного случая (Specific Function)
create or replace function
get_tables (p_owner in all_tables.owner%type)
return varchar2
is
l_text varchar2(32767) := null;
begin
for cur_rec in (select table_name
from all_tables
where owner = p_owner
and rownum <=10)
loop
l_text := l_text || ',' || cur_rec.table_name;
end loop;
return ltrim(l_text, ',');
end;
Проверяем
в запросе:
select owner,
get_tables(owner)
from all_tables
where rownum <=10
group by owner;
💧 Функция с использованием Ref Cursor
create or replace function
concatenate_list (p_cursor in sys_refcursor)
return varchar2
is
l_return varchar2(32767);
l_temp varchar2(32767);
begin
loop
fetch p_cursor
into l_temp;
exit when p_cursor%notfound;
l_return := l_return || ',' || l_temp;
end loop;
return ltrim(l_return, ',');
end;
Проверяем в
запросе:
select owner,
concatenate_list(cursor(select table_name from all_tables t2 where rownum <=10 and t1.owner = t2.owner)) as vtables
from all_tables t1
where rownum <=10
group by owner;
💧 ROW_NUMBER() и SYS_CONNECT_BY_PATH (Oracle 9i)
SYS_CONNECT_BY_PATH
работает только в иерархичесих запросах. Поэтому для агрегации строк необходимо
искусственно создать иерархию, и для этого и используется ROW_NUMBER().
select owner,
ltrim(max(sys_connect_by_path(table_name,';')) keep (dense_rank last order by current_id), ';') as vtable_names
from (select t.owner,
t.table_name,
row_number() over (partition by t.owner order by t.table_name) as current_id,
row_number() over (partition by t.owner order by t.table_name) - 1 as previous_id
from all_tables t
where rownum <= 10)
group by owner
connect by previous_id = prior current_id and owner = owner
start with current_id = 1;
💧 COLLECT function (Oracle 10g)
COLLECT
обеспечивает наилучшую производительность, если сортировка не требуется.
Создаем тип:
create or replace type t_varchar2_tab
as table of varchar2(4000);
Создаем функцию:
create or replace function
tab_to_string (p_varchar2_tab in t_varchar2_tab,
p_delimiter in varchar2 default ',') return varchar2 is
l_string varchar2(32767);
begin
for i in p_varchar2_tab.first .. p_varchar2_tab.last
loop
if i != p_varchar2_tab.first then
l_string := l_string || p_delimiter;
end if;
l_string := l_string || p_varchar2_tab(i);
end loop;
return l_string;
end tab_to_string;
Проверяем в запросе:
select owner,
tab_to_string(cast(collect(table_name) as t_varchar2_tab)) as vtables
from all_tables t1
where rownum <=10
group by owner;
💧 XMLAGG
Если нужен чистый XMLTYPE - убираем rtrim.
select rtrim(xmlagg(xmlelement(E,table_name||';')).extract('//text()')) as vtables
FROM all_tables
where rownum <= 10;
💧 XML&CLOB - возвращаем CLOB.
select owner
,sys_xmlagg(xmlelement( "QWER", x) ).extract('ROWSET/QWER/COL/text()').getclobval() s
from (select t.owner,
xmlagg( xmlelement("COL", t.table_name || ',') order by t.table_name) x
from all_tables t
where rownum < 100
group by t.owner) a
group by owner;
💧 И снова для гурманов - MODEL
select owner,
rtrim(vtables, ',') vtables
from (select owner,
vtables,
rn
from all_tables
where rownum <= 10
model
partition by (owner)
dimension by (row_number() over
(partition by owner order by table_name) rn
)
measures (table_name, cast(null as varchar2(4000)) as vtables)
rules (vtables[any] order by rn desc = table_name[cv()] || ',' || vtables[cv()+1])
)
where rn= 1
order by owner;
💧
TABLE_TO_COMMA
Функция из
того же пакета DBMS_UTILITY, что и COMMA_TO_TABLE. Имеет те же ограничения:
- не более 30 символов;
- должны начинаться с буквы;
- не должны
содержать спецсимволов и т.д.
DBMS_UTILITY.TABLE_TO_COMMA (
tab IN UNCL_ARRAY,
tablen OUT
BINARY_INTEGER,
list OUT
VARCHAR2);
На вход
нужно подать коллекцию (тип DBMS_UTILITY.uncl_array), если подходить
практически - не всегда хочется заморачиваться, плюс ограничения. Поэтому
указана в списке справочно, на случай внезапной встречи в БД.
Для примера
разобьем и соберем строку:
DECLARE
l_list1 VARCHAR2(50) := 'Tom,Dick,Harry,William';
l_list2 VARCHAR2(50);
l_tablen BINARY_INTEGER;
l_tab DBMS_UTILITY.uncl_array;
BEGIN
DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);
DBMS_UTILITY.comma_to_table (
list => l_list1,
tablen => l_tablen,
tab => l_tab);
FOR i IN 1 .. l_tablen LOOP
DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;
DBMS_UTILITY.table_to_comma (
tab => l_tab,
tablen => l_tablen,
list => l_list2);
DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);
END;
На этом все.
Надеюсь, будет полезно.
Источники:
0 Комментарии