如何在 Oracle SQL 查询中将一行与该行内的数组联接

Question

我有一些自定义预言机类型声明如下：

create type S_TYPE as table of VARCHAR2(4000);
CREATE TYPE "OBJ_ORDER_ADD" AS object (
  ADD_TYPE NUMBER,
  ADDITION s_type
  -- other fields not represented here for brevity
);
create type ARRAY_ORDER_ADD as table of OBJ_ORDER_ADD;

这意味着我可以创建如下所示的ARRAY_ORDER_ADD实例：

add_type  | addition
---------------------
      10  | ['a', 'b']
      20  | ['x', 'y', 'z']

我需要创建可以将包含的数据合并到相应order_add表中的FUNCTION，其中addition列是VARCHAR。将上面的对象合并到一个空表中后，我希望该表包含：

add_type | addition
-------------------
      10 | 'a'
      10 | 'b'
      20 | 'x'
      20 | 'y'
      20 | 'z'

我知道我可以使用具有两个嵌套循环的简单解决方案来实现这一点，但是这个解决方案会导致很多 SQL/PLSQL 上下文切换，我想避免它们。

我的理想目标是使用单个MERGE查询来执行此操作。

这可能吗？

我尝试了几种SELECT ... FROM TABLE(myRecord)组合，并试图以某种方式用TABLE(myRecord.addition)制作每张唱片的笛卡尔乘积，但我似乎无法正确地将它们组合在一起。

Answer 1

我不太确定您的实际实现是什么，但这向您展示了如何在纯SQL中取消嵌套嵌套表。

WITH 子句创建示例数据的集合，然后我们使用连续的 table() 函数来解压缩嵌套的表对象：

with cte as (
    select ARRAY_ORDER_ADD(OBJ_ORDER_ADD(10, s_type('a', 'b')) 
                          , OBJ_ORDER_ADD(20, s_type('a', 'b', 'c'))) as nt 
    from dual
    )
select t.add_type
      , x.column_value as s_type_element
 from cte
       , table(cte.nt)  t
       , table(t.addition) x;