如何用SQL脚本基于Oracle的document表填充document_closure表？

嘿，我来帮你解决这个闭包表填充的问题！咱们的目标是把document里的树形数据，转换成document_closure需要的所有祖先-后代关系，包括每个节点自身的关联、直接父子关联，还有跨层级的间接关联。

实现思路 #

闭包表的核心就是记录树形里所有可能的祖先-后代配对，包括节点自己。这里用Oracle的递归CTE（公共表表达式）来遍历整个树是最方便的——先搞定每个节点自己的关系，再一层层递归找出所有子节点的关联。

完整SQL脚本 #

-- 可选：如果需要清空旧数据，先执行这个
TRUNCATE TABLE document_closure;

-- 递归生成所有闭包关系并插入
INSERT INTO document_closure (id, parent_id, child_id, level)
WITH RECURSIVE closure_tree AS (
    -- 第一步：每个节点自己和自己的关系，level设为0
    SELECT 
        d.id AS parent_id,
        d.id AS child_id,
        0 AS level
    FROM document d
    
    UNION ALL
    
    -- 第二步：递归遍历所有父节点的子节点，level递增
    SELECT 
        ct.parent_id,
        d.id AS child_id,
        ct.level + 1 AS level
    FROM closure_tree ct
    JOIN document d ON ct.child_id = d.parent_id
)
SELECT 
    -- 用ROWNUM生成自增的id，如果你有自定义序列，换成 your_sequence.NEXTVAL 就行
    ROWNUM AS id,
    parent_id,
    child_id,
    level
FROM closure_tree
ORDER BY parent_id, level, child_id;

-- 提交更改
COMMIT;

重点说明 #

• 递归CTE的基础部分：先把每个节点自己作为父和子，level=0，这就是示例里前3条记录的来源。
• 递归部分：把已经找到的子节点当作父节点，去document表里找它的直接子节点，level加1，这样就能一层层挖到所有间接的祖先-后代关系，比如示例里1→3（level=2）的那条记录。
• id字段的处理：如果document_closure的id是自增主键，用ROWNUM就能生成唯一ID；要是你有专门的序列，把ROWNUM换成序列的NEXTVAL就行。
• 性能优化：因为数据量在1-2万条，这个脚本跑起来没问题，但建议给document表的parent_id加个索引，这样递归关联的时候速度会更快。

验证一下 #

执行完脚本后，跑个查询看看结果对不对：

SELECT * FROM document_closure ORDER BY parent_id, level, child_id;

应该能得到和你示例里完全一致的结构，包含所有需要的关联记录。

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