Oracle Apex 如何将复选框存储的冒号分隔返回值转换为报表显示值

方案1：拆分拼接法（全动态，推荐） #

核心逻辑是先把冒号分隔的存储值拆分为单行，关联值列表匹配显示值后，再重新拼接为冒号分隔的结果，完全不需要硬编码枚举值，新增LOV项无需调整代码。

Oracle 12c及以上版本最简写法：

SELECT 
    ft.id, -- 替换为你表中需要保留的其他字段
    LISTAGG(ll.display, ':') WITHIN GROUP (ORDER BY t.value_pos) AS fruit_display
FROM fruit_table ft
-- 拆分冒号分隔字符串为单行
CROSS APPLY (
    SELECT 
        REGEXP_SUBSTR(ft.fruit_column, '[^:]+', 1, LEVEL) AS return_val,
        LEVEL AS value_pos
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(ft.fruit_column, '[^:]+', 1, LEVEL) IS NOT NULL
) t
-- 关联LOV匹配显示值
LEFT JOIN lov_list ll 
    ON ll.return = t.return_val 
    AND ll.type = 'fruit'
GROUP BY ft.id -- 和上面保留的字段同步

如果是11g及更早版本，不支持CROSS APPLY可以用以下兼容写法：

SELECT 
    ft.id,
    LISTAGG(NVL(ll.display, t.return_val), ':') WITHIN GROUP (ORDER BY t.value_pos) AS fruit_display
FROM (
    SELECT 
        ft.*,
        REGEXP_SUBSTR(ft.fruit_column, '[^:]+', 1, LEVEL) AS return_val,
        LEVEL AS value_pos
    FROM fruit_table ft
    CONNECT BY REGEXP_SUBSTR(ft.fruit_column, '[^:]+', 1, LEVEL) IS NOT NULL
        AND PRIOR ft.id = ft.id
        AND PRIOR SYS_GUID() IS NOT NULL
) t
LEFT JOIN lov_list ll 
    ON ll.return = t.return_val 
    AND ll.type = 'fruit'
GROUP BY ft.id

方案2：封装为自定义函数（适合频繁调用场景） #

如果需要在多个报表或查询中用到该转换，可以封装为PL/SQL函数，调用更简洁：

CREATE OR REPLACE FUNCTION get_fruit_display(p_return_list VARCHAR2) RETURN VARCHAR2 IS
    v_result VARCHAR2(4000);
BEGIN
    SELECT LISTAGG(ll.display, ':') WITHIN GROUP (ORDER BY LEVEL)
    INTO v_result
    FROM DUAL
    LEFT JOIN lov_list ll 
        ON ll.return = REGEXP_SUBSTR(p_return_list, '[^:]+', 1, LEVEL)
        AND ll.type = 'fruit'
    CONNECT BY REGEXP_SUBSTR(p_return_list, '[^:]+', 1, LEVEL) IS NOT NULL;

    RETURN v_result;
END;
/

调用时直接在查询中使用即可：

SELECT id, get_fruit_display(fruit_column) AS fruit_display FROM fruit_table

注：两种方案都默认保留原始值的选择顺序，未匹配到的返回值可通过NVL配置为保留原始值或显示默认占位符

内容的提问来源于stack exchange，提问作者T.O.Lloyd