如何对START WITH / CONNECT BY层级查询做参数化实现批次谱系查询

你之前的尝试不符合预期的核心原因是START WITH子句的作用是定义层级查询的根节点范围，你使用PDL.LOT_NUMBER = PDL.LOT_NUMBER相当于将所有批次都标记为根节点，自然所有数据层级都是Level 1，也无法生成正确的关联关系。

1. 单个父批次参数化 #

直接将硬编码的批次号替换为绑定变量即可，不同开发语言/数据库工具的绑定变量语法略有差异，Oracle标准绑定变量写法如下：

Select
P.SAP_PLANT_CODE,
PLI.SAP_MATL_NO,
PLI.ITEM_DESC as Parent_Desctiption,
PLI.ITEM_ID as Parent_Material_Type,
PDL.LOT_NUMBER,
LI.SAP_MATL_NO as COMP_ITEM,
LI.ITEM_DESC as Comp_Desctiption,
LI.ITEM_ID as Comp_Mat_Type,
DL.LOT_NUMBER as COMP_LOT,
FG.QTY_ISSD,
Level,
CONNECT_BY_ROOT PDL.LOT_NUMBER as ROOT_LOT -- 可选：返回当前行所属的原始父批次
from  PGS_DW.FCT_GENEALOGY FG
INNER JOIN PGS_DW.DIM_LOT PDL on FG.LOT_KEY = PDL.LOT_KEY
INNER JOIN PGS_DW.LKUP_ITEM PLI on FG.ITEM_KEY = PLI.ITEM_KEY
INNER JOIN PGS_DW.LKUP_ITEM LI on FG.COMP_ITEM_KEY = LI.ITEM_KEY
INNER JOIN PGS_DW.DIM_LOT DL on FG.COMP_LOT_KEY = DL.LOT_KEY
INNER JOIN PGS_DW.DIM_SAP_PLANT_XREF P on PLI.SITE_KEY = P.SITE_KEY
Where LI.ITEM_ID in ('FERT', 'HALB')
START WITH PDL.LOT_NUMBER = :p_root_lot -- 绑定变量，传入单批次值即可
CONNECT BY NOCYCLE PRIOR DL.LOT_NUMBER = PDL.LOT_NUMBER -- 加NOCYCLE防止循环关联报错
Order by ROOT_LOT, Level, PDL.LOT_NUMBER

2. 多个父批次参数化 #

支持同时传入多个原始父批次，根据传参形式有两种实现方式：

方案A：数组传参（推荐，适用于应用层调用） #

使用Oracle集合类型接收批次数组：

-- START WITH 子句修改为如下
START WITH PDL.LOT_NUMBER IN (
    SELECT column_value FROM TABLE(:p_root_lot_list)
)

其中:p_root_lot_list为字符串集合类型，可直接传入SYS.ODCIVARCHAR2LIST('BATCH A','BATCH B','BATCH C')格式的参数。

方案B：逗号分隔字符串传参（适用于报表/BI工具调用） #

如果只能传入逗号拼接的批次字符串，可先拆分再匹配：

-- START WITH 子句修改为如下
START WITH PDL.LOT_NUMBER IN (
    SELECT REGEXP_SUBSTR(:p_root_lot_str, '[^,]+', 1, LEVEL)
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(:p_root_lot_str, '[^,]+', 1, LEVEL) IS NOT NULL
)

传入参数格式示例：'BATCH A,BATCH B,BATCH C'即可。

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