Oracle SQL实现用翻译表数据替换主表中的变量

首先明确两张表的结构及测试数据（可直接用于验证）：

主表（TEXT_TBL）：

CREATE TABLE TEXT_TBL (
    TYPE VARCHAR2(1),
    TXT VARCHAR2(100)
);

INSERT INTO TEXT_TBL VALUES ('A', 'this is some text for %1 and %2');
INSERT INTO TEXT_TBL VALUES ('B', 'this is another step for %1');

翻译表（TRANSLATION_TBL）：

CREATE TABLE TRANSLATION_TBL (
    TYPE VARCHAR2(1),
    VARIABLE VARCHAR2(3),
    DESCR VARCHAR2(20)
);

INSERT INTO TRANSLATION_TBL VALUES ('A', '%1', '#person1#');
INSERT INTO TRANSLATION_TBL VALUES ('A', '%2', '#person2#');
INSERT INTO TRANSLATION_TBL VALUES ('B', '%1', '#manager#');

方案一：自定义PL/SQL函数（直观易维护） #

单条REPLACE仅能处理单次替换，自定义函数可循环遍历对应type的所有变量，逐个完成替换：

CREATE OR REPLACE FUNCTION replace_variables(p_type IN VARCHAR2, p_txt IN VARCHAR2)
RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := p_txt; -- 初始化结果为原文本
BEGIN
    -- 遍历当前type下的所有变量与对应描述
    FOR rec IN (SELECT variable, descr FROM TRANSLATION_TBL WHERE type = p_type) LOOP
        v_result := REPLACE(v_result, rec.variable, rec.descr);
    END LOOP;
    RETURN v_result;
END;
/

调用函数获取结果：

SELECT type, replace_variables(type, txt) AS TXT
FROM TEXT_TBL;

执行后返回期望结果：

方案二：纯SQL递归CTE实现（无需自定义对象） #

若不想创建函数，可通过递归CTE逐次替换每个变量：

WITH var_list AS (
    -- 给每个type下的变量排序，确定替换顺序
    SELECT 
        type,
        variable,
        descr,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY variable) AS seq
    FROM TRANSLATION_TBL
),
recursive_replace AS (
    -- 初始步骤：取原文本和第一个变量的序号
    SELECT 
        t.type,
        t.txt AS current_txt,
        1 AS current_seq,
        MAX(v.seq) OVER (PARTITION BY t.type) AS max_seq
    FROM TEXT_TBL t
    LEFT JOIN var_list v ON t.type = v.type
    GROUP BY t.type, t.txt
    UNION ALL
    -- 递归替换：用上一轮结果替换下一个变量
    SELECT 
        r.type,
        REPLACE(r.current_txt, v.variable, v.descr) AS current_txt,
        r.current_seq + 1 AS current_seq,
        r.max_seq
    FROM recursive_replace r
    JOIN var_list v ON r.type = v.type AND v.seq = r.current_seq + 1
    WHERE r.current_seq < r.max_seq
)
-- 取每个type最后一次替换的结果，补充无变量的原文本
SELECT type, current_txt AS TXT
FROM recursive_replace
WHERE current_seq = max_seq
UNION ALL
SELECT type, txt AS TXT
FROM TEXT_TBL
WHERE type NOT IN (SELECT DISTINCT type FROM var_list);

方案说明 #

• 方案一适合频繁复用替换逻辑的场景，代码可读性高，维护成本低；
• 方案二为纯SQL实现，无需创建数据库对象，适合临时查询需求。

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