直接选择进入分层用户类型 [英] Select directly into hierarchical user type

问题描述

是否可以直接在分层用户类型中进行选择?

Is it possible to do a select directly into a hierarchical user type?

想象一下这样的表结构:

Imagine a table structure like this:

PARENT
------
ID
NAME

CHILD
-----
ID
PARENT_ID
NAME

此外，我的用户类型如下:

Additionally, I have user types like this:

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

Now, I want the result of that statement to be in an object of type parent_table. Is this somehow possible without the use of a complex FOR loop?

推荐答案

您可以使用这将返回一个PARENT_ITEM列表，您可以将其批量收集到PARENT_TABLE中.

This will return a list of PARENT_ITEM that you can BULK COLLECT into a PARENT_TABLE.

您可以在外部查询中再次使用COLLECT来直接获取PARENT_TABLE:

You could use COLLECT again in an outer query to get a PARENT_TABLE directly:

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)

这篇关于直接选择进入分层用户类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！