Oracle SQL：如何从嵌套游标外部取值实现预订分组？

问题背景 #

需要按预订类型对预订记录进行分组，现有模板不支持该操作，必须通过SQL实现。模板要求使用SQL游标，当前采用嵌套游标结构，但尚未实现子游标reservation_groupee筛选出与父游标reservation中FAMILLE_OBJET值相同的TYPE_RESERVATION数据。

现有SQL代码 #

select cursor(
    -- 客户信息已省略，简化阅读
    cursor (
        select OBJETS_FAMILLES.LIBELLE as FAMILLE_OBJET, -- 作为预订分类，用于归组同类别预订
        cursor (
            -- 选择预订信息
            select RESERVATIONS.LIBELLE as LIBELLE_RESERVATION,
            OBJETS_FAMILLES.LIBELLE as TYPE_RESERVATION, -- 用于和FAMILLE_OBJET做比较
            to_char(RESERVATIONS.DATE_DEBUT,'dd.mm.yyyy') as DATE_RESERVATION,
            RESERVATIONS.NUMERO as NUM_RES,
            OBJETS.LIBELLE as LIBELLE,
            LIGNES_RESERVATIONS.QTE_RESERVE as QTE_RESERVE,
            LIGNES_RESERVATIONS.RABAIS_FRANCS as RABAIS_FRANCS,
            LIGNES_RESERVATIONS.PRIX_UNITAIRE_FORCE as PRIX_UNITAIRE,
            LIGNES_RESERVATIONS.DUPL_MONTANT as MONTANT_TTC,
            OBJETS.COMPLEMENT as COMPLEMENT,
            UNITES.LIBELLE as UNITE
            from COMMUNES COMMUNES,
            TARIFS TARIFS,
            OBJETS_FAMILLES OBJETS_FAMILLES,
            OBJETS OBJETS,
            RESERVATIONS RESERVATIONS,
            LIGNES_RESERVATIONS LIGNES_RESERVATIONS,
            POLITESSES POLITESSES,
            CLIENTS CLIENTS,
            UNITES UNITES
            where LIGNES_RESERVATIONS.OBJ_NUMERO=OBJETS.NUMERO
            and LIGNES_RESERVATIONS.OBJ_SOCIETES_ID=OBJETS.SOCIETES_ID
            and RESERVATIONS.CLI_NUMERO=CLIENTS.NUMERO
            and RESERVATIONS.CLI_SOCIETES_ID=CLIENTS.SOCIETES_ID
            and OBJETS.OBJ_FAM_NUMERO=OBJETS_FAMILLES.NUMERO
            and OBJETS.OBJ_FAM_SOCIETES_ID=OBJETS_FAMILLES.SOCIETES_ID
            and LIGNES_RESERVATIONS.RES_NUMERO=RESERVATIONS.NUMERO
            and LIGNES_RESERVATIONS.RES_SOCIETES_ID=RESERVATIONS.SOCIETES_ID
            and CLIENTS.COM_NUMERO=COMMUNES.NUMERO
            and CLIENTS.COM_SOCIETES_ID=COMMUNES.SOCIETES_ID
            and LIGNES_RESERVATIONS.TRF_NUMERODEP=TARIFS.NUMERODEP
            and LIGNES_RESERVATIONS.TRF_SOCIETES_ID=TARIFS.SOCIETES_ID
            and LIGNES_RESERVATIONS.TRF_OBJ_NUMERO=TARIFS.OBJ_NUMERO
            and TARIFS.UNI_NUMERO=UNITES.NUMERO
            and TARIFS.UNI_SOCIETES_ID=UNITES.SOCIETES_ID
            and RESERVATIONS.SOCIETES_ID = 5
            and CLIENTS.POL_NUMERO=POLITESSES.NUMERO
            and CLIENTS.POL_SOCIETES_ID=POLITESSES.SOCIETES_ID
            and LIGNES_RESERVATIONS.res_numero in (select numero from reservations where numero = 93688 or res_parent = 93688)
            -- 此处需添加TYPE_RESERVATION与FAMILLE_OBJET相等的条件
        ) as "reservation_groupee"
        from OBJETS_FAMILLES OBJETS_FAMILLES
    ) as "reservation"
) as "client" from dual

解决方案 #

核心问题是子游标需要引用父游标的FAMILLE_OBJET值，需注意表别名冲突：父游标中的OBJETS_FAMILLES需要单独设置别名，避免和子游标中的同表混淆。修改步骤如下：

1. 给父游标中的OBJETS_FAMILLES设置别名OF_PARENT
2. 在子游标reservation_groupee的WHERE条件中添加OBJETS_FAMILLES.LIBELLE = OF_PARENT.LIBELLE，实现按类型分组筛选

修改后的完整SQL #

select cursor(
    -- 客户信息已省略，简化阅读
    cursor (
        select OF_PARENT.LIBELLE as FAMILLE_OBJET, -- 作为预订分类，用于归组同类别预订
        cursor (
            -- 选择预订信息
            select RESERVATIONS.LIBELLE as LIBELLE_RESERVATION,
            OBJETS_FAMILLES.LIBELLE as TYPE_RESERVATION, -- 用于和FAMILLE_OBJET做比较
            to_char(RESERVATIONS.DATE_DEBUT,'dd.mm.yyyy') as DATE_RESERVATION,
            RESERVATIONS.NUMERO as NUM_RES,
            OBJETS.LIBELLE as LIBELLE,
            LIGNES_RESERVATIONS.QTE_RESERVE as QTE_RESERVE,
            LIGNES_RESERVATIONS.RABAIS_FRANCS as RABAIS_FRANCS,
            LIGNES_RESERVATIONS.PRIX_UNITAIRE_FORCE as PRIX_UNITAIRE,
            LIGNES_RESERVATIONS.DUPL_MONTANT as MONTANT_TTC,
            OBJETS.COMPLEMENT as COMPLEMENT,
            UNITES.LIBELLE as UNITE
            from COMMUNES COMMUNES,
            TARIFS TARIFS,
            OBJETS_FAMILLES OBJETS_FAMILLES,
            OBJETS OBJETS,
            RESERVATIONS RESERVATIONS,
            LIGNES_RESERVATIONS LIGNES_RESERVATIONS,
            POLITESSES POLITESSES,
            CLIENTS CLIENTS,
            UNITES UNITES
            where LIGNES_RESERVATIONS.OBJ_NUMERO=OBJETS.NUMERO
            and LIGNES_RESERVATIONS.OBJ_SOCIETES_ID=OBJETS.SOCIETES_ID
            and RESERVATIONS.CLI_NUMERO=CLIENTS.NUMERO
            and RESERVATIONS.CLI_SOCIETES_ID=CLIENTS.SOCIETES_ID
            and OBJETS.OBJ_FAM_NUMERO=OBJETS_FAMILLES.NUMERO
            and OBJETS.OBJ_FAM_SOCIETES_ID=OBJETS_FAMILLES.SOCIETES_ID
            and LIGNES_RESERVATIONS.RES_NUMERO=RESERVATIONS.NUMERO
            and LIGNES_RESERVATIONS.RES_SOCIETES_ID=RESERVATIONS.SOCIETES_ID
            and CLIENTS.COM_NUMERO=COMMUNES.NUMERO
            and CLIENTS.COM_SOCIETES_ID=COMMUNES.SOCIETES_ID
            and LIGNES_RESERVATIONS.TRF_NUMERODEP=TARIFS.NUMERODEP
            and LIGNES_RESERVATIONS.TRF_SOCIETES_ID=TARIFS.SOCIETES_ID
            and LIGNES_RESERVATIONS.TRF_OBJ_NUMERO=TARIFS.OBJ_NUMERO
            and TARIFS.UNI_NUMERO=UNITES.NUMERO
            and TARIFS.UNI_SOCIETES_ID=UNITES.SOCIETES_ID
            and RESERVATIONS.SOCIETES_ID = 5
            and CLIENTS.POL_NUMERO=POLITESSES.NUMERO
            and CLIENTS.POL_SOCIETES_ID=POLITESSES.SOCIETES_ID
            and LIGNES_RESERVATIONS.res_numero in (select numero from reservations where numero = 93688 or res_parent = 93688)
            -- 添加条件：TYPE_RESERVATION与父游标的FAMILLE_OBJET相等
            and OBJETS_FAMILLES.LIBELLE = OF_PARENT.LIBELLE
        ) as "reservation_groupee"
        from OBJETS_FAMILLES OF_PARENT -- 父表设置别名，避免和子查询同表冲突
    ) as "reservation"
) as "client" from dual

关键说明 #

• 父游标中的OBJETS_FAMILLES使用OF_PARENT别名，确保子游标能正确引用父级的LIBELLE值
• 子游标WHERE子句末尾添加的条件，实现了按FAMILLE_OBJET对TYPE_RESERVATION相同的记录进行分组
• 保留了原查询的所有关联条件和过滤逻辑，仅新增分组关联条件

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