如何在Oracle中遍历含共同列的所有表并批量删除匹配记录

步骤1：生成所有含DOCUMENT_ID=1记录的删除脚本 #

通过动态SQL自动生成所有包含DOCUMENT_ID列的表的删除语句：

SELECT 'DELETE FROM ' || owner || '.' || table_name || ' WHERE DOCUMENT_ID = 1;' AS delete_statement
FROM all_tab_columns
WHERE column_name = 'DOCUMENT_ID'
ORDER BY table_name;

执行该查询后，复制输出的所有删除语句批量执行即可。

步骤2：处理关联的DOCTYPE_ID记录 #

需同时删除DOCUMENT_ID=1关联的所有DOCTYPE_ID对应记录，分两步操作：

2.1 获取目标DOCTYPE_ID值 #

从存储文档主数据的表（假设为DOCUMENTS）中提取关联的DOCTYPE_ID：

SELECT DISTINCT DOCTYPE_ID
FROM DOCUMENTS
WHERE DOCUMENT_ID = 1;

2.2 生成含DOCTYPE_ID列的表的删除脚本 #

基于上述结果生成批量删除语句，若DOCTYPE_ID数量较多，用LISTAGG或XMLAGG拼接值：

-- 适用于值数量较少的场景
SELECT 'DELETE FROM ' || owner || '.' || table_name || ' WHERE DOCTYPE_ID IN (' || 
       (SELECT LISTAGG(DOCTYPE_ID, ',') WITHIN GROUP (ORDER BY DOCTYPE_ID) 
        FROM DOCUMENTS WHERE DOCUMENT_ID = 1) || ');' AS delete_statement
FROM all_tab_columns
WHERE column_name = 'DOCTYPE_ID'
ORDER BY table_name;

如果LISTAGG返回字符串过长，改用XMLAGG：

SELECT 'DELETE FROM ' || owner || '.' || table_name || ' WHERE DOCTYPE_ID IN (' || 
       RTRIM(XMLAGG(XMLELEMENT(E, DOCTYPE_ID, ',').EXTRACT('//text()') ORDER BY DOCTYPE_ID).GetClobVal(), ',') || ');' AS delete_statement
FROM all_tab_columns
WHERE column_name = 'DOCTYPE_ID'
CROSS JOIN (SELECT DISTINCT DOCTYPE_ID FROM DOCUMENTS WHERE DOCUMENT_ID = 1) t
GROUP BY owner, table_name
ORDER BY table_name;

步骤3：按外键依赖顺序执行删除 #

存在外键约束时，直接删除会触发报错，需先删子表记录再删主表。通过以下查询获取依赖关系：

SELECT 
    c.owner || '.' || c.table_name AS 子表,
    p.owner || '.' || p.table_name AS 父表,
    cc.column_name AS 子表关联列,
    pc.column_name AS 父表关联列
FROM all_constraints c
JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN all_constraints p ON c.r_owner = p.owner AND c.r_constraint_name = p.constraint_name
JOIN all_cons_columns pc ON p.owner = pc.owner AND p.constraint_name = pc.constraint_name
WHERE c.constraint_type = 'R'
AND (cc.column_name IN ('DOCUMENT_ID', 'DOCTYPE_ID'));

根据结果优先处理子表的删除操作，再执行父表的删除。

注意事项 #

• 删除前务必备份数据，或先执行SELECT *语句验证目标记录是否正确。
• 生产环境建议在低峰期操作，通过事务包裹删除语句，确认无误后再提交：

  BEGIN
      -- 粘贴所有删除语句
      COMMIT;
  EXCEPTION
      WHEN OTHERS THEN
          ROLLBACK;
          RAISE;
  END;
  

内容的提问来源于stack exchange，提问作者Peter The Angular Dude