Oracle PL/SQL使用关联数组运行时排序如何保留ID和名称

你当前使用的关联数组仅存储了服务名称作为值，作为下标的SERVICE_ID不会被table()函数返回，因此查询时必然丢失ID信息。可通过以下两种方案实现同时保留ID和排序需求：

方案1：自定义记录类型集合（最推荐） #

该方案逻辑清晰无额外解析开销，适合绝大多数场景：

• 第一步修改包定义，新增存储ID+名称的记录类型和对应集合类型：

-- 包中新增定义
TYPE service_rec IS RECORD (
    service_id   PLS_INTEGER,
    service_name VARCHAR2(4000)
);
TYPE g_service_arr IS TABLE OF service_rec INDEX BY PLS_INTEGER;

• 第二步调整存储逻辑，将ID和名称成对存入集合：

-- 变量声明改为新的集合类型
service_list my_pkg.g_service_arr;
-- 循环赋值逻辑调整
LOOP
  service_list(services.SERVICE_ID).service_id := services.SERVICE_ID;
  service_list(services.SERVICE_ID).service_name := services.SERVICE_NAME;
END LOOP;

• 第三步排序查询即可同时获取两个字段：

for query_result_row in (
    SELECT service_id, service_name 
    FROM table(service_list) 
    ORDER BY service_name
) loop
    dbms_output.put_line('服务ID：'||query_result_row.service_id||' 服务名称：'||query_result_row.service_name);
    -- 后续业务直接调用两个字段即可
end loop;

提示：如果使用Oracle 12c以下版本，方案1的记录/集合类型需要定义为SQL级别的对象类型，才能在table()函数的SQL语句中正常调用。

方案2：字符串拼接拆分（兼容原有包定义） #

如果不允许修改原有包的类型定义，可以用特殊分隔符拼接ID和名称存入原有字符串集合，查询后再拆分使用：

• 调整存储逻辑，用不会出现在服务名称中的特殊字符（如|）拼接字段：

LOOP
  service_list(services.SERVICE_ID) := services.SERVICE_ID || '|' || services.SERVICE_NAME;
END LOOP;

• 查询后拆分获取两个字段：

for query_result_row in (
    SELECT COLUMN_VALUE 
    FROM table(service_list) 
    -- 按名称排序，取分隔符之后的内容排序
    ORDER BY substr(COLUMN_VALUE, instr(COLUMN_VALUE,'|')+1)
) loop
    v_service_id := to_number(substr(query_result_row.COLUMN_VALUE, 1, instr(query_result_row.COLUMN_VALUE,'|')-1));
    v_service_name := substr(query_result_row.COLUMN_VALUE, instr(query_result_row.COLUMN_VALUE,'|')+1);
    dbms_output.put_line('服务ID：'||v_service_id||' 服务名称：'||v_service_name);
end loop;

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