gpt4 book ai didi

sql - 为什么 SHAPE.SDO_ORDINATES(1) 在 PL/SQL 中有效,但在 SQL 中无效?

转载 作者:行者123 更新时间:2023-12-05 04:28:36 27 4
gpt4 key购买 nike

甲骨文 18c:

我可以在自定义 PL/SQL 函数中使用 SHAPE.SDO_ORDINATES(1) 从 SDO_GEOMETRY 中提取起点 X 坐标:

with 
function startpoint_x(shape in sdo_geometry) return number
is
begin
return
shape.sdo_ordinates(1);
end;

select
startpoint_x(shape) as startpoint_x
from
(select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
from dual)

STARTPOINT_X
------------
1

但如果我尝试纯粹在 SQL 查询中执行此操作,则会出现错误:

select
(shape).sdo_ordinates(1) as startpoint_x
from
(select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
from dual)

ORA-00904: "MDSYS"."SDO_GEOMETRY"."SDO_ORDINATES": invalid identifier

就其值(value)而言,如果我要删除 (1) 并选择整个 sdo_ordinates 属性,那么它会起作用:

select
(shape).sdo_ordinates as ordinates
from
(select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
from dual)

ORDINATES
------------------------
SDO_ORDINATE_ARRAY(1, 2)

db<>fiddle

当然,这不是我想要的。我想以数字形式获取起点 X 坐标。

为什么 SHAPE.SDO_ORDINATES(1) 在 PL/SQL 中有效,但在 SQL 查询中无效?


有些相关:Get X & Y coordinates from GEOM_SEGMENT_START_PT()

最佳答案

Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in an SQL query?

因为SQL不支持通过索引提取集合元素的语法。它不仅是 SDO 对象,而且是任何集合:

SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;

输出:

ORA-03001: unimplemented feature

和:

SELECT l.list(1)
FROM (SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c') AS list FROM DUAL) l;

输出:

ORA-00904: "L"."LIST": invalid identifier

(我认为这意味着它正在尝试将其解析为函数,但错误消息不如前一个有用/明显。)


有获取值的方法,但它更复杂,因为您需要使用表集合表达式取消引用整个集合,然后过滤以获取所需的行:

SELECT (
SELECT COLUMN_VALUE
FROM TABLE(s.shape.sdo_ordinates)
FETCH FIRST ROW ONLY
) as startpoint_x
FROM (
select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape from dual
) s

db<> fiddle here

关于sql - 为什么 SHAPE.SDO_ORDINATES(1) 在 PL/SQL 中有效,但在 SQL 中无效?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/72570611/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com