基于同表外键复制表行——Oracle SQL/PLSQL实现问题

原代码存在的核心问题 #

• 表名拼写错误：插入目标表写成了acv_folder_info，实际应为ACV_FOLDERS_INFO
• 语法错误：插入列列表中x.YEAR,末尾多了逗号，会直接触发SQL编译错误
• FIO_ID关联逻辑错误：用nvl(i.ID, l_ID)赋值新文件夹的父ID完全错误，子文件夹的父ID应该对应新生成的父文件夹ID，而非原文件夹ID
• 单变量无法处理多分支结构：仅用l_ID存储最新生成的ID，遇到多同级父文件夹时会覆盖旧值，导致子文件夹关联到错误的父节点
• 循环顺序错误：原查询的排序规则会打乱树形结构的父→子顺序，可能导致子文件夹先于父文件夹插入，触发外键约束异常

修正后的PL/SQL代码 #

declare
  -- 用关联数组存储原ID到新生成ID的映射，解决多分支树形的父ID关联问题
  type id_map_type is table of number index by number;
  v_id_map id_map_type;
  v_new_id number;
begin
  -- 按树形层级顺序（先父后子）查询需要复制的文件夹
  for i in (select f.*
            from acv_folders_info f
            join acv_folder_acl a 
              on f.ID = a.FIO_ID
            where a.privilege = 'B'
              and a.ERD_ID = 483
              and acv_get_validation.have_upd(p_erd_id => 483, p_fio_id => a.FIO_ID) = 'Y'
              and f.year = 2020
            start with f.FIO_ID is null
            connect by prior f.ID = f.FIO_ID
            order siblings by f.NAME) loop  -- 按同级文件夹名称排序，保持结构一致性
    
    begin
      insert into ACV_FOLDERS_INFO (
        OWNER,
        NAME,
        ALT_NAME,
        CATEGORY,
        DESCRIPTION,
        ICON,
        DELETED,
        FDR_PUBLIC,
        INHERIT_PARENT_ACL,
        REMARKS,
        FIO_ID,
        OTHER_ID,
        YEAR,
        REF
      ) values (
        483,
        i.NAME || ' - 2020 / 2021',
        i.ALT_NAME || ' - 2020 / 2021',
        i.CATEGORY,
        i.DESCRIPTION,
        i.ICON,
        i.DELETED,
        i.FDR_PUBLIC,
        i.INHERIT_PARENT_ACL,
        i.REMARKS,
        -- 根据原父ID获取对应的新生成父ID，根文件夹FIO_ID仍为NULL
        case when i.FIO_ID is not null then v_id_map(i.FIO_ID) end,
        i.OTHER_ID,
        2020,
        i.REF
      ) returning ID into v_new_id;
      
      -- 将当前原ID和新生成ID存入映射表
      v_id_map(i.ID) := v_new_id;
      
    exception
      when others then
        dbms_output.put_line('原ID: ' || i.ID || ' | 错误信息: ' || sqlerrm);
    end;
  end loop;
  
  commit; -- 按需添加提交，若需要批量提交可调整逻辑
end;
/

修正点说明 #

1. 使用**关联数组v_id_map**存储原文件夹ID到新生成ID的映射，完美解决多分支树形结构的父ID关联问题
2. 调整查询排序为order siblings by f.NAME，确保父文件夹先于子文件夹处理，同时保持同级文件夹的顺序一致性
3. 修正了表名和列列表的语法错误
4. FIO_ID赋值逻辑改为：根文件夹保持NULL，子文件夹通过原父ID从映射表中获取对应的新父ID
5. 补充了原代码遗漏的REF列插入（根据表结构，该列存在于原表中）

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