Oracle 19c中从Apex集合生成父子关系表的问题求助

需要在Oracle 19c中创建如下结构的父子关系表：

数据源是Oracle Apex导入Excel生成的集合，结构如下：

尝试用WITH子句创建临时表并联合查询，但每次执行结果不一致，排序也无效，求解决方法。原代码如下：

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT  = ''YYYY-MM-DD HH24:MI:SS'';

BEGIN
    insert /*+ append */ into conn_category
    
    WITH  
        default_data as (
        SELECT c001, c002, c003
        FROM APEX_collections
        WHERE collection_name = ''DL_CATEGORIE_PORTALE_COLLECTION''
   
        ),
        cat1_lvl as (
        select ROW_NUMBER() OVER( ORDER BY c001 asc) as ID_PARENT, c001 as c001_new
        from default_data
        group by c001 ),
    
        cat2_lvl as (
            select distinct null as ID_PARENT,  a.c001 from
            default_data a
            , cat1_lvl b
            where
            a.c001 = b.c001_new
            UNION ALL
            select distinct  b.ID_PARENT,  a.c002 from
            default_data a
            , cat1_lvl b
            where
            a.c001 = b.c001_new),
    
        cat3_lvl as (
            select rownum as ID_PARENT, a.c001 from
            ( cat2_lvl ) a ),
        
        result as (
            select 
            ID_PARENT as ID_CATEGORY_PARENT,
            c001 as NAME
            from cat2_lvl
            UNION ALL
            select  
            b.ID_PARENT as ID_CATEGORY_PARENT,
            a.c003 as NAME
            from
            default_data a,
            cat3_lvl b
            where
            a.c002 = b.c001
            AND a.c003 is not null    
        ),
        export as (
        select 
        rownum as ID,
        name,
        id_category_parent,
        TO_CHAR(sysdate, ''YYYY-MM-DD HH24:MI:SS'') as created_at,
        TO_CHAR(sysdate, ''YYYY-MM-DD HH24:MI:SS'') as updated_at,
        NULL as processed_at
        from result )
        
        select * from export order by id;

END;

原代码的核心问题在于依赖rownum和未明确排序的临时表，Oracle在处理无明确排序的数据集时，返回顺序不固定，导致每次执行结果不一致。需要明确每个层级的排序逻辑，并用稳定的方式生成ID和父子关联。

以下是修正后的代码：

BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';

    INSERT /*+ append */ INTO conn_category
    WITH  
        -- 获取原始集合数据，保留seq_id用于排序（确保顺序和Excel一致）
        default_data AS (
            SELECT seq_id, c001, c002, c003
            FROM APEX_collections
            WHERE collection_name = 'DL_CATEGORIE_PORTALE_COLLECTION'
        ),
        -- 提取所有唯一的分类节点，按层级和原始顺序整理
        all_categories AS (
            -- 一级分类
            SELECT DISTINCT c001 AS category_name, NULL AS parent_name, seq_id
            FROM default_data
            WHERE c001 IS NOT NULL
            UNION ALL
            -- 二级分类
            SELECT DISTINCT c002 AS category_name, c001 AS parent_name, seq_id
            FROM default_data
            WHERE c002 IS NOT NULL
            UNION ALL
            -- 三级分类
            SELECT DISTINCT c003 AS category_name, c002 AS parent_name, seq_id
            FROM default_data
            WHERE c003 IS NOT NULL
        ),
        -- 为每个分类生成唯一ID，按原始seq_id排序保证顺序稳定
        category_ids AS (
            SELECT 
                ROW_NUMBER() OVER (ORDER BY seq_id, category_name) AS id,
                category_name,
                parent_name
            FROM all_categories
        ),
        -- 关联父ID，生成最终结果
        final_result AS (
            SELECT 
                ci.id,
                ci.category_name AS name,
                cp.id AS parent_id,
                TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS created_at,
                TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS updated_at,
                NULL AS processed_at
            FROM category_ids ci
            LEFT JOIN category_ids cp ON ci.parent_name = cp.category_name
            ORDER BY ci.id
        )
        SELECT * FROM final_result;
END;
/

关键改进点： #

• 保留原始seq_id用于排序，确保分类生成顺序和Excel导入顺序一致，避免结果混乱
• 先提取所有唯一分类节点，再统一生成ID，避免多层嵌套导致的顺序不稳定
• 使用自关联方式匹配父ID，逻辑更清晰，避免依赖不稳定的临时表排序
• 去掉不必要的DISTINCT和冗余临时表结构，简化查询逻辑

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