Oracle 11g中如何解析关联表数据并填充文本占位符？

需求说明 #

现有两张通过ID关联的表：

• Table1：存储带%1至%10占位符的文本
• Table2：存储对应ID的占位符值，值以/分隔

需要通过SQL或自定义函数将Table1中的占位符替换为Table2的对应值，生成完整文本。

方法一：创建自定义PL/SQL函数（推荐） #

通过自定义函数循环处理10个占位符，代码简洁且可复用：

CREATE OR REPLACE FUNCTION replace_placeholders(p_text IN VARCHAR2, p_values IN VARCHAR2) RETURN VARCHAR2 IS
    v_result VARCHAR2(4000) := p_text;
    v_value VARCHAR2(4000);
    v_pos NUMBER;
    v_next_pos NUMBER;
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 10 LOOP
        -- 给值前后加/，方便定位第n个值的位置
        v_pos := INSTR('/' || p_values || '/', '/', 1, v_count);
        v_next_pos := INSTR('/' || p_values || '/', '/', 1, v_count + 1);
        
        IF v_pos > 0 AND v_next_pos > v_pos THEN
            v_value := SUBSTR('/' || p_values || '/', v_pos + 1, v_next_pos - v_pos - 1);
            v_result := REPLACE(v_result, '%' || v_count, v_value);
        ELSE
            -- 若对应位置无值，保留原占位符；如需替换为空，替换NULL为下方注释行
            -- v_result := REPLACE(v_result, '%' || v_count, '');
            NULL;
        END IF;
        
        v_count := v_count + 1;
    END LOOP;
    
    RETURN v_result;
END;
/

查询语句 #

调用函数关联两张表获取结果：

SELECT 
    t2.CODE,
    replace_placeholders(t1.TEXT, t2.VALUES) AS "OUTPUT TEXT"
FROM 
    Table1 t1
JOIN 
    Table2 t2 ON t1.ID = t2.ID
ORDER BY 
    t2.CODE;

方法二：嵌套REPLACE语句（无需创建函数） #

如果不想创建函数，可直接用嵌套REPLACE结合REGEXP_SUBSTR拆分值，适合一次性查询：

SELECT 
    t2.CODE,
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        t1.TEXT,
        '%1', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 1), '%1')),
        '%2', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 2), '%2')),
        '%3', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 3), '%3')),
        '%4', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 4), '%4')),
        '%5', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 5), '%5')),
        '%6', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 6), '%6')),
        '%7', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 7), '%7')),
        '%8', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 8), '%8')),
        '%9', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 9), '%9')),
        '%10', NVL(REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, 10), '%10')) AS "OUTPUT TEXT"
FROM 
    Table1 t1
JOIN 
    Table2 t2 ON t1.ID = t2.ID
ORDER BY 
    t2.CODE;

说明 #

• REGEXP_SUBSTR(t2.VALUES, '[^/]+', 1, n)用于提取第n个/分隔的值
• NVL函数确保如果对应位置无值，保留原占位符；如需替换为空，去掉NVL直接使用REGEXP_SUBSTR即可

验证结果 #

两种方法运行后均会输出预期结果：

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