如何在查询中取消嵌套嵌套表的集合？

Question

我有一个过程，它接受类似于下面所示的parent_arr 的输入，作为通过 ODP.Net 来自应用程序层的输入。在该过程的第一步中，我将数组中的数据存储在全局临时表中，以便我可以使用设置逻辑而不是 pl/sql 循环继续执行以下几个步骤。只要数组只有一个parent_typ 成员，一切都很好。但是，当有多个成员时，我收到 ORA-01427，单行查询返回不止一行。下面的查询返回两个集合。我需要在单个 sql 语句中取消嵌套这两个集合，该语句将显示 child.name 和 child.value。那怎么办呢？

示例对象

create type child_typ is object( name varchar2(100), value number );
create type child_arr is table of dropme_child_typ;
create type parent_typ is object( pname varchar2(100), child dropme_child_arr );
create type parent_arr is table of dropme_parent_typ;

下面的查询将抛出 ORA-01427

select * from table(
    select child
    from table( parent_arr(
        parent_typ( 'TEST1',
            child_arr(
                child_typ( 'C1', 1 ),
                child_typ( 'C2', 2 ) ) ),
        parent_typ( 'TEST2',
            child_arr(
                child_typ( 'C3', 3 ),
                child_typ( 'C4', 4 ) ) ) ) ) );

该查询有效，但返回一列对象 child_arr

select child
from table( parent_arr(
    parent_typ( 'TEST1',
        child_arr(
            child_typ( 'C1', 1 ),
            child_typ( 'C2', 2 ) ) ),
    parent_typ( 'TEST2',
        child_arr(
            child_typ( 'C3', 3 ),
            child_typ( 'C4', 4 ) ) ) ) );

该查询失败，因为我无法访问“child”中的值

select child.name, child.value from
 table( parent_arr(
     parent_typ( 'TEST1',
         child_arr(
             child_typ( 'C1', 1 ),
             child_typ( 'C2', 2 ) ) ),
     parent_typ( 'TEST2',
         child_arr(
             child_typ( 'C3', 3 ),
             child_typ( 'C4', 4 ) ) ) ) );

请告诉我有一种方法可以在不使用 pl/ 的情况下执行此操作sql 循环（这是迄今为止我能够成功的唯一方法）。速度至关重要。我尝试使用 forall 语句来循环 Parent_arr 的成员，但它会引发批量绑定错误。

Answer 1

您可以使用横向联接来解除子对象的嵌套：

SQL> WITH my_data AS (
  2     SELECT pname, child
  3       FROM TABLE(parent_arr(parent_typ('TEST1',
  4                                        child_arr(child_typ('C1', 1),
  5                                                  child_typ('C2', 2))),
  6                             parent_typ('TEST2',
  7                                        child_arr(child_typ('C3', 3),
  8                                                  child_typ('C4', 4)))))
  9  )
 10  SELECT my_data.pname, child.name, child.value
 11    FROM my_data, table(my_data.child) child;

PNAME    NAME       VALUE
-------- ----- ----------
TEST1    C1             1
TEST1    C2             2
TEST2    C3             3
TEST2    C4             4

这是一种外连接形式，您可以将父对象与其子对象连接起来。