使用负数提取数组列表末尾的元素(在自定义成员函数中)

Question

Oracle 18c:

我有一个自定义成员函数，它按预期工作。它允许我通过索引从数组中提取一个元素。

为例,提取第一个纵坐标SDO_GEOMETRY的sdo_ordinatesvarray属性使用GetOrdinates(1)=10。

CREATE TYPE my_sdo_geom_type AS OBJECT( 
shape SDO_GEOMETRY, 
MEMBER FUNCTION GetOrdinates(
self IN my_sdo_geom_type,
idx  IN NUMBER
) RETURN NUMBER
) 
/ 
CREATE TYPE BODY my_sdo_geom_type AS
MEMBER FUNCTION GetOrdinates(
self IN my_sdo_geom_type,
idx  IN NUMBER
) RETURN NUMBER
IS
BEGIN 
IF SIGN(idx) = 1 THEN
return shape.sdo_ordinates(idx);
ELSE
return null;        --Temporarily returns null, since I don't know how to implement that functionality yet.
END IF;
END;
END;
/ 
create table lines (my_sdo_geom_col my_sdo_geom_type);
insert into lines (my_sdo_geom_col) values (my_sdo_geom_type(sdo_geometry('linestring(10 20, 30 40, 50 60)')));
select (my_sdo_geom_col).GetOrdinates( 1) as first_ordinate,
(my_sdo_geom_col).GetOrdinates(-1) as last_ordinate
from   lines

LAST_ORDINATE

Answer 1

您可以使用:

CREATE TYPE BODY my_sdo_geom_type AS
MEMBER FUNCTION GetOrdinates(
self IN my_sdo_geom_type,
idx  IN NUMBER
) RETURN NUMBER
IS
BEGIN 
IF idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
return shape.sdo_ordinates(idx);
ELSIF -idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
RETURN shape.sdo_ordinates(shape.sdo_ordinates.COUNT + 1 + idx);
ELSE
RETURN NULL;
END IF;
END;
END;
/ 

db<此处小提琴>