Как выбрать запросом данные в коллекцию, каждый элемент которой содержит другую коллекцию?

Question

Пытаюсь найти способ массовой выборки в многоуровневый тип данных, т.е. в коллекцию, каждый элемент которой, содержит другую коллекцию. Пример ниже поможет понять то, что пытаюсь сделать.

Есть таблица с денормализованным списком округов и городов:

create table county_town (county varchar2(20), town varchar2(20));
insert into county_town values ('Surrey', 'Dorking');
insert into county_town values ('Surrey', 'Woking');
insert into county_town values ('Surrey', 'Guildford');
insert into county_town values ('Oxfordshire', 'Thame');
insert into county_town values ('Oxfordshire', 'Abingdon');

Хочу загрузить эту таблицу в многоуровневый тип данных, который выглядит так:


create or replace type towns_type force as table of varchar2(20)
/
create or replace type county_type as object (county varchar2(20), towns towns_type)
/
create or replace type counties_type force as table of county_type
/

counties counties_type;

Можно ли выбрать с BULK COLLECT из таблицы county_town данные в counties?

Если с BULK COLLECT не получится, то есть ли другой простой способ сделать это?

_{Свободный перевод вопроса Select into multilevel type in Oracle от участника @David Taylor}

Answer 1

На больших таблицах так будет предпочтительней:

var rc refcursor
declare 
    counties counties_type;
begin 
    select county_type (county, cast (collect (town) as towns_type)) as counties_type
    bulk collect into counties
    from county_town
    group by county;
    
    open :rc for 
        select * from table (counties);
end;
/

Если данных не так много, то можно обойтись без BULK COLLECT таким запросом:

select cast (multiset (
    select  county_type (county, cast (collect (town) as towns_type)) as counties_type
    from county_town
    group by county
    ) as counties_type) into counties from dual;

Результат обоих запросов будет идентичен:

COUNTY               TOWNS                                           
-------------------- ------------------------------------------------
Oxfordshire          TOWNS_TYPE('Thame', 'Abingdon')                 
Surrey               TOWNS_TYPE('Dorking', 'Guildford', 'Woking')

На db<>fiddle.

БЛОГ НА HUSL

Как выбрать запросом данные в коллекцию, каждый элемент которой содержит другую коллекцию?

Ответы (1 шт):