寻求将分号分隔多国家名替换为两位ISO代码的更简便方案

以下是几种比你当前方案更简便的实现方式，适配不同Oracle版本：

方法1：JSON_TABLE（Oracle 12c+） #

无需递归，直接通过JSON格式拆分字符串，逻辑更直观：

假设业务表为business_data（含主键id和目标列country_names），国家代码表为country_code（含country_name和iso_code）：

SELECT 
    bd.id,
    LISTAGG(cc.iso_code, ';') WITHIN GROUP (ORDER BY occ.position) AS iso_codes
FROM business_data bd
LEFT JOIN JSON_TABLE(
    '["' || REPLACE(bd.country_names, ';', '","') || '"]',
    '$[*]' COLUMNS (
        country_name VARCHAR2(100) PATH '$',
        position FOR ORDINALITY
    )
) occ ON 1=1
LEFT JOIN country_code cc ON cc.country_name = occ.country_name
GROUP BY bd.id;

• 先将分号分隔的字符串转换为JSON数组，用JSON_TABLE拆分出每个国家名及原始位置（保证顺序一致）
• 关联代码表匹配ISO代码后，用LISTAGG按原顺序聚合回分号分隔格式

方法2：自定义PL/SQL函数 #

如果需要频繁调用转换逻辑，自定义函数能让代码更简洁：

CREATE OR REPLACE FUNCTION get_iso_codes(p_country_names VARCHAR2) RETURN VARCHAR2 IS
    v_result VARCHAR2(1000);
    v_country_name VARCHAR2(100);
    v_start_pos NUMBER := 1;
    v_end_pos NUMBER;
BEGIN
    IF p_country_names IS NULL THEN RETURN NULL; END IF;
    
    LOOP
        v_end_pos := INSTR(p_country_names, ';', v_start_pos);
        v_country_name := CASE 
            WHEN v_end_pos = 0 THEN SUBSTR(p_country_names, v_start_pos)
            ELSE SUBSTR(p_country_names, v_start_pos, v_end_pos - v_start_pos)
        END;
        
        -- 匹配ISO代码，无匹配时保留原名称或替换为占位符，按需调整
        SELECT NVL(iso_code, v_country_name) INTO v_country_name 
        FROM country_code 
        WHERE country_name = v_country_name;
        
        v_result := v_result || v_country_name || CASE WHEN v_end_pos != 0 THEN ';' END;
        EXIT WHEN v_end_pos = 0;
        v_start_pos := v_end_pos + 1;
    END LOOP;
    
    RETURN v_result;
END;
/

调用方式：

SELECT id, get_iso_codes(country_names) AS iso_codes FROM business_data;

方法3：层次查询拆分（Oracle 11g及以下） #

针对低版本Oracle，用正则表达式+层次查询替代CONNECTBY，代码更简洁：

SELECT 
    bd.id,
    LISTAGG(cc.iso_code, ';') WITHIN GROUP (ORDER BY occ.lvl) AS iso_codes
FROM business_data bd
LEFT JOIN (
    SELECT 
        id,
        REGEXP_SUBSTR(country_names, '[^;]+', 1, level) AS country_name,
        level AS lvl
    FROM business_data
    CONNECT BY LEVEL <= REGEXP_COUNT(country_names, ';') + 1
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL -- 避免重复生成数据
) occ ON occ.id = bd.id
LEFT JOIN country_code cc ON cc.country_name = occ.country_name
GROUP BY bd.id;

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