Oracle PL/SQL递归层级查询如何实现全分支提前期累计传递

问题说明 #

• 业务基于BOM树状结构，要求从根节点出发沿分支逐段累计提前期，当前节点的累计提前期等于根节点到当前节点路径上所有节点的提前期之和
• 传统CONNECT BY层级语法中，PRIOR仅能获取直接上级节点值，CONNECT_BY_ROOT仅能获取根节点值，原生无法直接实现全路径跨层累计
• 原有查询仅能实现当前节点+直接父节点的两级求和，不符合业务要求，原有语句如下：

SELECT LEVEL AS struct_level
   ,t.part_no AS parent_part
   ,t.component_part AS comp_part_no
   ,NVL(t.lead_time ,0) AS comp_part_lead_time
   ,NVL(t.lead_time ,0) + NVL(PRIOR t.lead_time ,0) AS cumul_lead_time
FROM   ifsapp.prod_structure_cfv t
START  WITH (t.part_no) IN 'PART A'
CONNECT BY PRIOR (t.component_part) = (t.part_no)
ORDER  SIBLINGS BY t.component_part;

可用实现方案 #

方案1：递归CTE（11gR2及以上版本推荐，性能最优） #

递归CTE（公用表表达式）通过锚点成员定义根节点初始值，递归成员逐层级关联上一层计算结果，天然支持逐路径累加，不需要额外依赖函数：

WITH recursive_bom (
    struct_level,
    parent_part,
    comp_part_no,
    comp_part_lead_time,
    cumul_lead_time
) AS (
    -- 锚点：定义根节点（PART A）初始值
    SELECT 
        1 AS struct_level,
        CAST(NULL AS VARCHAR2(100)) AS parent_part,
        t.part_no AS comp_part_no,
        NVL(t.lead_time, 0) AS comp_part_lead_time,
        NVL(t.lead_time, 0) AS cumul_lead_time
    FROM ifsapp.prod_structure_cfv t
    WHERE t.part_no = 'PART A'
    -- 若根节点PART A仅作为父件存在、无对应自身BOM行，可替换锚点为固定值：
    -- SELECT 1, CAST(NULL AS VARCHAR2(100)), 'PART A', 0, 0 FROM dual

    UNION ALL

    -- 递归逻辑：关联上一层累计值，累加当前节点提前期
    SELECT
        rb.struct_level + 1 AS struct_level,
        rb.comp_part_no AS parent_part,
        t.component_part AS comp_part_no,
        NVL(t.lead_time, 0) AS comp_part_lead_time,
        rb.cumul_lead_time + NVL(t.lead_time, 0) AS cumul_lead_time
    FROM ifsapp.prod_structure_cfv t
    INNER JOIN recursive_bom rb 
        ON t.part_no = rb.comp_part_no
    -- 若BOM存在循环引用，可加层级上限避免死循环，数值根据实际BOM最大深度调整
    WHERE rb.struct_level < 100
)
SELECT * FROM recursive_bom
ORDER SIBLINGS BY comp_part_no;

方案2：CONNECT BY + 路径聚合（兼容11gR2以下老版本） #

如果数据库版本不支持递归CTE，可借助SYS_CONNECT_BY_PATH拼接全路径提前期，再拆分求和，不需要自定义函数：

SELECT 
    LEVEL AS struct_level,
    t.part_no AS parent_part,
    t.component_part AS comp_part_no,
    NVL(t.lead_time,0) AS comp_part_lead_time,
    -- 拆分路径上的所有提前期值求和
    (
        SELECT SUM(TO_NUMBER(xt.leadtime_val))
        FROM XMLTABLE(
            ('"' || REPLACE(SYS_CONNECT_BY_PATH(NVL(t.lead_time,0), ','), ',', '","') || '"')
        ) xt(leadtime_val)
    ) AS cumul_lead_time
FROM ifsapp.prod_structure_cfv t
START WITH t.part_no = 'PART A'
CONNECT BY PRIOR t.component_part = t.part_no
ORDER SIBLINGS BY t.component_part;

关于自定义递归函数的说明 #

不推荐使用PL/SQL自定义递归函数实现该需求：逐行调用PL/SQL函数的性能远低于上述纯SQL集合运算方案，BOM数据量较大时会出现明显的性能瓶颈，且维护成本更高。

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