Oracle 18c编写函数转换SDO_ORDINATE_ARRAY为字符串用于函数索引

你之前写的函数触发ORA-01422错误，核心问题是函数内的SELECT语句直接查询了test_table全表，没有基于传入的单个shape入参处理，一次查询返回了表中所有行的结果，违反了select into仅允许返回单行的规则。

另外要创建函数索引，函数必须满足两个硬性要求：

• 必须声明为DETERMINISTIC，保证相同输入永远返回相同输出
• 函数逻辑不能直接依赖查询原表，否则会有依赖异常，也不符合确定性函数的要求

方案1：纯PL/SQL循环实现（性能最优，推荐用于函数索引） #

直接遍历传入几何对象的sdo_ordinates数组做拼接，没有PL/SQL和SQL的上下文切换开销，性能最好，完全满足函数索引创建要求：

-- 先创建自定义函数，注意将my_owner替换为实际使用的schema名
create or replace function my_owner.ordinates_to_varchar2(shape in sdo_geometry) 
return varchar2 deterministic
is
    v_ordinates varchar2(32767);
    v_arr_count number;
begin
    -- 做空值判断避免异常
    if shape is null or shape.sdo_ordinates is null then
        return null;
    end if;
    v_arr_count := shape.sdo_ordinates.count;
    if v_arr_count = 0 then
        return null;
    end if;
    -- 循环遍历数组拼接字符串
    for i in 1..v_arr_count loop
        if i = 1 then
            v_ordinates := to_char(shape.sdo_ordinates(i));
        else
            v_ordinates := v_ordinates || ',' || to_char(shape.sdo_ordinates(i));
        end if;
    end loop;
    return v_ordinates;
end;
/

函数创建完成后即可正常创建函数索引：

create index ordinates_string_idx on test_table (my_owner.ordinates_to_varchar2(shape));

执行查询验证结果，完全匹配预期输出：

select my_owner.ordinates_to_varchar2(shape) as ordinates from test_table;

查询输出：

ORDINATES
------------------------------
10,20,30,40,50,60
70,80,90,100
110,120,130,140,150,160,170,180

方案2：LISTAGG语法实现（兼容原有查询逻辑） #

如果你希望沿用之前测试过的listagg写法，只需要把查询对象从原表改成入参自带的数组即可，不需要关联原表，查询只会返回单行结果：

create or replace function my_owner.ordinates_to_varchar2(shape in sdo_geometry) 
return varchar2 deterministic
is
    v_ordinates varchar2(32767);
begin
    if shape is null or shape.sdo_ordinates is null then
        return null;
    end if;
    select listagg(column_value, ',') within group (order by rownum)
    into v_ordinates
    from table(shape.sdo_ordinates);
    return v_ordinates;
end;
/

这个写法逻辑上也能满足需求，但因为函数内部执行了SQL语句，会存在PL/SQL到SQL的上下文切换开销，性能比纯循环方案差，不建议在数据量大的表上用这个版本建函数索引。

• 同schema下创建函数和索引不需要加schema前缀，示例中的my_owner仅为对应你给出的索引创建语句格式
• 如果坐标需要保留固定小数位、避免科学计数法，可以在to_char时指定格式，例如to_char(shape.sdo_ordinates(i), 'fm999999990.000')
• 如果你的几何对象坐标点极多，拼接后长度可能超过varchar232767字节的上限，这种场景需要把返回值类型改成CLOB

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