Oracle 18c中CROSS JOIN LATERAL为何拆分数组内SDO_GEOMETRY为独立属性

Oracle 18c:

• 所用制图软件存在功能限制：单表仅支持处理1个几何列，若表中存在多个几何列，软件会直接抛出错误。
• 需求：在表中新增额外几何列时，将其存储为制图软件无法识别的数据类型，让软件自动忽略该列。
• 初步方案：将SDO_GEOMETRY存储为SDO_GEOMETRY_ARRAY数据类型，软件无法识别该数组类型即可实现忽略效果，实际使用时始终仅在数组中存储单个几何对象。

初始测试代码：

with data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select geom_array from data

SQL Developer中返回结果：

GEOM_ARRAY
----------------------------------------------
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])

测试发现直接查询该数组列时，返回结果为整个数组，而非内部存储的SDO_GEOMETRY对象（即使数组中仅存在单个值），因此需要寻找简洁易用的方式从数组中提取SDO_GEOMETRY对象。

方案1：自定义函数 #

该方案可正常返回预期结果，代码如下：

with 
function get_geom_from_array(geom_array sdo_geometry_array) return sdo_geometry is
begin
    return geom_array(1);
end;    
data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select
    get_geom_from_array(geom_array)
from   
    data

返回结果：

SDO_GEOM
---------------
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]

方案2：CROSS JOIN LATERAL #

该方案也可得到正确结果，代码如下：

select
    v.*
from   
    data d
cross join lateral (
    select sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates) as sdo_geom
    from   table(d.sdo_array)
    ) v

返回结果与自定义函数一致。但测试发现直接在子查询中查询数组内容时，会将SDO_GEOMETRY拆分为其各个属性组件返回：

select
    v.*
from   
    data d
cross join lateral (
    select *
    from   table(d.sdo_array)
    ) v

返回结果：

SDO_GTYPE SDO_SRID SDO_POINT              SDO_ELEM_INFO SDO_ORDINATES
--------- -------- ---------------------- ------------- -------------
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null

这种场景下必须手动通过拆分出的属性重建几何对象，写法为sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates)，操作略有不便。

为何使用CROSS JOIN LATERAL结合TABLE()查询SDO_GEOMETRY_ARRAY时，会将数组内的SDO_GEOMETRY对象拆分为独立属性返回，而自定义函数直接读取数组元素不会出现该现象？

这个现象是Oracle的对象类型默认处理规则和TABLE()函数的运作机制共同导致的：

• SDO_GEOMETRY是Oracle Spatial定义的结构化对象类型，原生包含SDO_GTYPE、SDO_SRID、SDO_POINT、SDO_ELEM_INFO、SDO_ORDINATES5个顶层属性。Oracle为所有自定义/内置对象类型提供了默认的展开能力：当对象类型作为TABLE()函数输出的行集元素、且查询时使用*通配符，Oracle会默认将对象的顶层属性拆分为独立列返回，而非将整个对象作为单列输出。
• 自定义函数的逻辑是通过PL/SQL数组下标直接访问元素：geom_array(1)是直接读取数组指定位置的对象值，整个过程不会触发对象类型的默认展开逻辑，PL/SQL会将读取到的SDO_GEOMETRY作为单个完整值返回，因此不会出现属性拆分的情况。

如果不想手动重建几何对象，不需要写冗长的属性拼接代码，在CROSS JOIN LATERAL子查询中直接引用TABLE()输出的默认列column_value即可拿到完整的几何对象，写法更简洁：

select
    v.sdo_geom
from   
    data d
cross join lateral (
    select column_value as sdo_geom
    from   table(d.geom_array)
    ) v

column_value是Oracle对集合类型通过TABLE()函数展开时，为集合元素默认分配的列名，直接查询该列就会返回完整的SDO_GEOMETRY对象，不需要手动拼接属性。

内容的提问来源于stack exchange，提问作者User1974