Oracle使用listagg动态生成INSERT语句值缺失问题求解

你当前的SQL写法存在逻辑错误：

• 你存储的成对编码规则为[培训模块编码]-[学习模块编码]，其中单独出现的OTKCTEC_N_2022是公共学习模块（对应C_STUDYINTRAININGID的值598477608），另外两个带横杠的是培训模块（对应C_TRAININGOFSTUDYID的两个值608089239、608089260）
• 直接用in把所有编码传入后按m.c_code分组，会把重复出现的公共学习模块单独归为一组，这组匹配不到培训模块ID；另外两个培训模块组匹配不到学习模块ID，最终生成的语句自然会出现字段值空缺
• listagg是同组内的多行值聚合函数，无法实现跨组ID的配对拼接，完全不符合「公共学习ID和每个培训ID两两配对生成INSERT语句」的需求

不要直接对t_module做单表分组聚合，先将传入的编码列表拆分为公共学习模块、培训模块两部分，再做配对拼接即可，可直接运行的SQL如下：

-- 传入你从文件中读取到的所有编码，无需提前去重
WITH input_codes AS (
    SELECT column_value AS c_code 
    FROM TABLE(SYS.ODCIVARCHAR2LIST(
        'OTKCTEC-OTKBTES_N_2022',
        'OTKCTEC_N_2022',
        'OTKBNNK-OTKCTEC_N_2022',
        'OTKCTEC_N_2022'
    ))
),
-- 提取公共学习模块：编码不带横杠，自动去重
study_module AS (
    SELECT DISTINCT m.id AS study_id
    FROM t_module m
    JOIN input_codes ic ON m.c_code = ic.c_code
    WHERE INSTR(m.c_code, '-') = 0
),
-- 提取所有培训模块：编码带横杠，自动去重
training_modules AS (
    SELECT DISTINCT m.id AS training_id
    FROM t_module m
    JOIN input_codes ic ON m.c_code = ic.c_code
    WHERE INSTR(m.c_code, '-') > 0
)
-- 配对生成标准INSERT语句
SELECT 
    'insert into T_TRAINING_STUDY (C_STUDYINTRAININGID,C_TRAININGOFSTUDYID,CREATED,ID,LASTCHANGED,SERIAL) values ('
    || sm.study_id || ',' 
    || tm.training_id || ',sysdate, seq_id_generator.nextval, sysdate, 0 );' AS insert_sql
FROM study_module sm, training_modules tm;

• 后续导入文件数据时，只需要把读取到的所有编码按逗号分隔填入SYS.ODCIVARCHAR2LIST()的参数列表中即可，重复编码会在CTE逻辑中自动去重，无需手动预处理
• 如果后续出现多学习模块、多培训模块配对的场景，只要编码保持[培训编码]-[学习编码]的规则，只需要调整两个模块提取CTE的逻辑，将横杠前后的编码拆分后分别关联t_module取ID再配对即可，不会出现空值问题
• 不要用listagg实现跨维度ID拼接，该函数仅适合同组多行值合并，ID两两配对场景用明确的关联或笛卡尔积逻辑才能保证值不空缺

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