Oracle 18c自定义成员函数用负数索引提取varray末尾元素

实现逻辑 #

Oracle varray集合自带COUNT属性，可直接返回集合内存储的元素总数量，基于该属性即可完成负数反向索引的转换，规则完全对齐原生sdo_util.get_coordinate的索引逻辑：

• 传入正索引时，直接使用原下标从varray中取值
• 传入负索引时，按公式转换为正向索引：实际索引 = varray总长度 + 传入的负索引 + 1
• 传入索引为0、转换后索引不在1~总长度区间、sdo_ordinates属性为空时，统一返回null，避免触发下标越界错误

修改后的类型实现代码 #

CREATE OR REPLACE TYPE BODY my_sdo_geom_type AS
  MEMBER FUNCTION GetOrdinates(
    self IN my_sdo_geom_type,
    idx  IN NUMBER
  ) RETURN NUMBER
  IS
    v_array_length NUMBER;
    v_actual_index NUMBER;
  BEGIN 
    -- 空集合直接返回null
    IF self.shape.sdo_ordinates IS NULL THEN
      RETURN NULL;
    END IF;

    v_array_length := self.shape.sdo_ordinates.COUNT;

    IF SIGN(idx) = 1 THEN
      v_actual_index := idx;
    ELSIF SIGN(idx) = -1 THEN
      -- 负索引转正向索引
      v_actual_index := v_array_length + idx + 1;
    ELSE
      -- 索引为0非法，返回null
      RETURN NULL;
    END IF;

    -- 索引合法性校验
    IF v_actual_index BETWEEN 1 AND v_array_length THEN
      RETURN self.shape.sdo_ordinates(v_actual_index);
    ELSE
      RETURN NULL;
    END IF;
  END;
END;
/

测试验证 #

沿用原有测试表与测试数据，执行查询：

select (my_sdo_geom_col).GetOrdinates( 1) as first_ordinate,
       (my_sdo_geom_col).GetOrdinates(-1) as last_ordinate
from   lines;

返回结果如下，符合预期：

额外验证场景：

• 调用GetOrdinates(-2)返回50，GetOrdinates(-6)返回10，符合反向取值预期
• 传入超过数组长度的正索引、绝对值超过数组长度的负索引、0值，均返回null，无运行时报错

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