Oracle PL/SQL动态处理源表可变公司名字段的父子表同步问题

问题背景 #

需要定期将源表数据同步到父表与子表：

• 父表存储公司名称、地址、唯一ID等核心字段
• 子表通过外键关联父表唯一ID，存储公司的多条明细记录
• 已实现从配置表读取多组源/目标表名，通过动态SQL完成多表同步
• 痛点：各源表的公司名字段名不固定（如comp_name、MKN02），需将配置表中存储的字符串字段名转为可在PL/SQL中引用的变量，用于比较记录间的公司名差异

原代码中关键问题点：

If my_rec.Comp_Name <> v_Prev Then -- 此处无法直接用变量替换Comp_Name字段名

解决方案 #

方法1：动态游标+字段别名（推荐） #

核心思路：在动态构造游标查询语句时，将配置表读取的动态公司名字段指定为固定别名，后续代码统一引用该别名即可，无需处理动态字段名的直接引用。

示例代码

Declare
    TYPE cur_typ Is Ref Cursor;
    cur cur_typ;
    -- 自定义记录类型，对应游标查询的固定字段别名
    TYPE sync_rec_typ IS RECORD (
        comp_name   VARCHAR2(80),
        address     VARCHAR2(200),
        invoice_no  VARCHAR2(50),
        amount      NUMBER
    );
    my_rec      sync_rec_typ;
    v_Prev      VARCHAR2(80) Default 'Empty';
    v_unique_id Number;
    v_SqlStmt   Varchar2(1000);
    -- 从配置表读取的参数：源表所属用户、表名、公司名字段名
    v_owner     VARCHAR2(30) := 'owner1';
    v_table     VARCHAR2(30) := 'table1';
    v_comp_col  VARCHAR2(30) := 'MKN02'; -- 假设从配置表查询得到
Begin
    -- 动态构造游标SQL，将动态公司名字段别名化为comp_name
    Open cur For 'Select ' || v_comp_col || ' as comp_name, 
                         address, 
                         invoice_no, 
                         amount 
                  From '|| v_owner ||'.'|| v_table ||' 
                  Order By ' || v_comp_col;
    Loop
        Fetch cur Into my_rec;
        Exit When cur%notfound;
        
        -- 统一使用别名comp_name进行比较，无需关心源字段名
        If my_rec.comp_name <> v_Prev Then 
            Select unique_id.nextval INTO v_unique_id FROM dual;
            -- 插入父表使用绑定变量，避免SQL注入和语法错误
            v_SqlStmt := 'Insert into Parent_Table(Comp_name, Address, ID)
                         Values (:comp_name, :address, :unique_id)';
            Execute Immediate v_SqlStmt 
                Using my_rec.comp_name, my_rec.address, v_unique_id;
        End If;
        
        -- 插入子表
        v_SqlStmt := 'Insert into Child_table(unique_id, Invoice_No, Amount)
                      Values (:unique_id, :Invoice_No, :Amount)';
        Execute Immediate v_SqlStmt
            Using v_unique_id, my_rec.Invoice_No, my_rec.Amount;
            
        v_Prev := my_rec.comp_name;
    End Loop;
    Close cur;
End;
/

方法2：动态SQL获取字段值（适配复杂场景） #

如果源表字段结构不固定，无法提前定义记录类型，可以通过动态SQL从当前记录中提取指定字段的值。

示例代码片段

-- 假设已经fetch到my_rec（使用%ROWTYPE），v_comp_col是配置表读取的字段名
v_current_comp VARCHAR2(80);
-- 动态构造SQL获取当前记录的公司名字段值
Execute Immediate 'Select :rec.' || v_comp_col || ' From dual'
    Into v_current_comp
    Using my_rec;

-- 后续使用v_current_comp进行比较
If v_current_comp <> v_Prev Then
    -- 逻辑处理...
End If;

注意事项 #

• 始终使用绑定变量替代字符串拼接，避免SQL注入风险和特殊字符导致的语法错误
• 动态构造SQL时，建议对字段名、表名做合法性校验（如查询ALL_TAB_COLUMNS确认字段存在），防止非法输入导致报错
• 方法1性能更优，适合字段需求固定的同步场景；方法2灵活性更高，适配字段结构多变的场景

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