Выберите непосредственно в иерархическом типе пользователя

Можно ли сделать выбор непосредственно в иерархическом типе пользователя?

Представьте себе такую ​​структуру таблицы:

PARENT
------
ID
NAME

CHILD
-----
ID
PARENT_ID
NAME

Кроме того, у меня есть такие типы пользователей:

create or replace type child_item as object
(
  ID NUMBER(10),
  NAME VARCHAR(255)
);

create or replace type children_table as table of child_item;

create or replace type parent_item as object
(
  ID NUMBER(10),
  NAME VARCHAR(255),
  CHILDREN CHILDREN_TABLE
);

create or replace type parent_table as table of parent_item;

И такое заявление:

select * from parent p inner join child c on p.id = c.parent_id;

Теперь я хочу, чтобы результат этого оператора был в объекте типа parent_table. Возможно ли это как-то без использования сложного цикла FOR?


sql oracle plsql user-defined-types
person Daniel Hilgarth    schedule 09.05.2012    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Вы можете использовать COLLECT:

SELECT parent_item(p.ID, 
                   p.NAME, 
                   CAST(COLLECT(child_item(c.id, c.NAME)) AS children_table))
  FROM PARENT p
 INNER JOIN child c ON p.id = c.parent_id
 GROUP BY p.ID, p.NAME

Это вернет список PARENT_ITEM, которые вы можете ОБЪЕМНО СОБИРАТЬ в PARENT_TABLE.

Вы можете снова использовать COLLECT во внешнем запросе, чтобы напрямую получить PARENT_TABLE:

SELECT CAST(COLLECT(parents) AS parent_table)
  FROM (SELECT parent_item(p.ID, 
                           p.NAME, 
                           CAST(COLLECT(child_item(c.id, c.NAME)) 
                             AS children_table)
                          ) parents
          FROM PARENT p
         INNER JOIN child c ON p.id = c.parent_id
         GROUP BY p.ID, p.NAME)
person Vincent Malgrat    schedule 09.05.2012
comment
Спасибо за Ваш ответ. Предположим, что в PARENT есть 3 элемента, и каждый из них имеет три связанных элемента в CHILDREN. Вернет ли это 3 parent_items с 3 child_items каждое или это вернет 9 parent_items с 1 child_item каждым? Я спрашиваю, потому что результатом моего примера запроса в вопросе будет 9 строк. - person Daniel Hilgarth; 09.05.2012
comment
Чтобы ответить на мой собственный вопрос: он возвращает 3 родителей с 3 детьми каждый. - person Daniel Hilgarth; 09.05.2012
comment
Хороший ответ - моя немедленная реакция на этот вопрос заключалась в том, что это невозможно сделать, но, к счастью, я исправился и сегодня кое-чему научился. - person Stephen ODonnell; 09.05.2012

arrow_upward
1
arrow_downward

Используйте 1_:

with data as (
select p.id pid,p.name pname,cast(multiset(select c.id,c.name 
from child c where c.parent_id=p.id) AS children_table) ct
from parent p
)
select cast(multiset(select pid,pname,ct from data) as parent_table) 
from dual;
person Phil    schedule 09.05.2012
comment
Спасибо, пока выглядит отлично. Но это не приведет к объекту типа parent_table. Как бы я это сделал? Я предполагаю, что мне нужно каким-то образом привести полный результат к parent_table и выбрать его в переменную этого типа, но я не совсем уверен, как это будет выглядеть. Может быть, что-то вроде этого? select cast(multiset(select p.id, p.name, cast(multiset(...) as children_table)) as parent_table) into variable from dual - person Daniel Hilgarth; 09.05.2012
comment
Спасибо! Однако я принял решение Винсента, потому что оно позволяет мне использовать мои уже существующие операторы с соединениями. - person Daniel Hilgarth; 09.05.2012