sql - 直接选择进入分级用户类型

Question

是否可以直接选择分层用户类型？

想象一下这样的表结构:

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 循环，这是否可以以某种方式实现？

Answer 1

您可以使用 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)