如何在Oracle PLSQL和SQL中实现带命名占位符的字符串插值功能

以下实现完全符合需求约定，同时支持PL/SQL关联数组传参、SQL侧CSV键值对传参，内置双大括号转义处理，做了基础性能优化适配低频调用场景。

包规范（pkg_interpolation.pks） #

CREATE OR REPLACE PACKAGE pkg_interpolation IS
    -- 定义关联数组字典类型，供PLSQL侧调用传参
    TYPE ty_dictionary IS TABLE OF NVARCHAR2(1000) INDEX BY VARCHAR2(200);

    -- PLSQL调用入口：传入模板和关联数组参数
    FUNCTION fn_format(p_template IN NVARCHAR2, p_vars IN ty_dictionary) RETURN NVARCHAR2;

    -- SQL调用入口：传入模板和CSV格式键值对（格式：键1,值1,键2,值2...）
    FUNCTION fn_format(p_template IN NVARCHAR2, p_csv_kv IN VARCHAR2) RETURN NVARCHAR2;
END pkg_interpolation;
/

包体（pkg_interpolation.pkb） #

CREATE OR REPLACE PACKAGE BODY pkg_interpolation IS
    -- 临时转义标记，需确保不会出现在业务模板/参数中
    C_ESC_OPEN CONSTANT VARCHAR2(20) := '§_ESC_OPEN_§';
    C_ESC_CLOSE CONSTANT VARCHAR2(20) := '§_ESC_CLOSE_§';

    FUNCTION fn_format(p_template IN NVARCHAR2, p_vars IN ty_dictionary) RETURN NVARCHAR2 IS
        v_result NVARCHAR2(32767);
        v_key VARCHAR2(200);
    BEGIN
        -- 第一步：先转义双大括号，避免被识别为占位符
        v_result := REPLACE(REPLACE(p_template, '{{', C_ESC_OPEN), '}}', C_ESC_CLOSE);

        -- 第二步：遍历所有参数替换占位符
        v_key := p_vars.FIRST;
        WHILE v_key IS NOT NULL LOOP
            v_result := REPLACE(v_result, '{' || v_key || '}', p_vars(v_key));
            v_key := p_vars.NEXT(v_key);
        END LOOP;

        -- 第三步：还原转义的大括号
        v_result := REPLACE(REPLACE(v_result, C_ESC_OPEN, '{'), C_ESC_CLOSE, '}');

        RETURN v_result;
    END fn_format;

    FUNCTION fn_format(p_template IN NVARCHAR2, p_csv_kv IN VARCHAR2) RETURN NVARCHAR2 IS
        v_vars ty_dictionary;
        v_kv_list DBMS_UTILITY.UNCL_ARRAY;
        v_kv_count BINARY_INTEGER;
    BEGIN
        -- 拆分CSV键值对
        DBMS_UTILITY.COMMA_TO_TABLE(
            list => TRIM(BOTH ',' FROM p_csv_kv),
            tablen => v_kv_count,
            tab => v_kv_list
        );

        -- 按顺序读取键、值存入字典
        FOR i IN 1..v_kv_count STEP 2 LOOP
            IF i+1 <= v_kv_count THEN
                v_vars(TRIM(v_kv_list(i))) := TRIM(v_kv_list(i+1));
            END IF;
        END LOOP;

        -- 调用重载方法执行替换
        RETURN fn_format(p_template, v_vars);
    END fn_format;
END pkg_interpolation;
/

调用示例 #

PLSQL 侧调用 #

DECLARE 
    v_template NVARCHAR2(500);
    v_name NVARCHAR2(50) := 'Jane';
    v_age NVARCHAR2(50) := '26';
    v_output NVARCHAR2(1000); 
    v_vars pkg_interpolation.ty_dictionary;
BEGIN
    v_template := 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}';
    v_vars('name') := v_name;
    v_vars('age') := v_age;

    v_output:= pkg_interpolation.fn_format(v_template, v_vars);
    -- 输出：Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name}, {age}
    dbms_output.put_line('output: ' || v_output);
END;
/

SQL 侧调用（CSV传参） #

SELECT pkg_interpolation.fn_format(
    'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}',
    'name, Jane, age, 26'
) AS interpolated_result 
FROM dual;

执行后返回结果与预期完全一致。

性能优化说明 #

• 仅做3轮全字符串替换（转义、参数替换、转义还原），避免逐字符扫描模板
• 重复占位符无需重复匹配，REPLACE函数一次性替换所有同名字段
• 无递归正则、嵌套循环等高开销逻辑，即使长模板执行效率也可控
• 采用Oracle内置的DBMS_UTILITY.COMMA_TO_TABLE拆分CSV，比自定义正则拆分效率更高

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