Oracle中如何通过for循环让select查询生成多行重复数据

根因说明 #

• 你当前写法每次执行BULK COLLECT INTO都会覆盖表变量v_output_piece_table的原有内容，而非追加数据，因此循环结束后仅保留最后一次查询的1行结果
• 原查询中的DENSE_RANK () OVER (ORDER BY sc.seq)返回固定值1，即使实现追加也无法得到COIL_SEQ为2的行

修正方案 #

方案1：循环追加实现（兼容你现有的循环逻辑） #

先声明临时变量存储单次查询结果，每次循环后将数据追加到目标表变量，同时用循环变量sayac替换原有窗口函数生成COIL_SEQ：

DECLARE
    v_output_piece_table tbl_met_output_coil; 
    -- 声明临时表变量存储单次查询结果
    v_temp_row tbl_met_output_coil;
    -- 可动态修改该变量控制生成行数
    v_generate_count NUMBER := 2;
BEGIN
    FOR sayac IN 1..v_generate_count 
    LOOP
        -- 单次查询结果存入临时变量，用循环变量作为COIL_SEQ
        SELECT SUBSTR (sl.task_job_id, 1, 12) AS schedule_id,
               sayac AS coil_seq,
               round(p.ACTUAL_WEIGHT/3,3) AS weight,
               scd.so_id,
               scd.so_line_id
          BULK COLLECT INTO v_temp_row
          FROM sch_line            sl,
               sch_input_material  sim,
               sch_input_piece     sip,
               sch_output_material som,
               sch_cut             sc,
               sch_cut_detail      scd,
               piece               P
         WHERE sl.task_job_id = 180078
           AND sl.sch_line_num_id = sim.sch_line_num_id
           AND sl.sch_line_num_id = som.sch_line_num_id
           AND som.output_mat_num_id = sc.output_mat_num_id
           AND sc.schc_cut_num_id = scd.schc_cut_num_id
           AND sim.input_mat_num_id = sip.input_mat_num_id
           AND sip.piece_num_id = P.piece_num_id
         ORDER BY sl.seq, sim.seq, sip.seq;
        
        -- 将临时变量的内容追加到目标表变量
        FOR i IN 1..v_temp_row.COUNT LOOP
            v_output_piece_table.EXTEND;
            v_output_piece_table(v_output_piece_table.LAST) := v_temp_row(i);
        END LOOP;
    END LOOP;
    
    -- 后续可直接使用v_output_piece_table
END;
/

方案2：无循环批量生成（性能更优） #

如果不需要依赖循环内的其他逻辑，可直接用CONNECT BY生成指定行数，一次查询得到结果：

DECLARE
    v_output_piece_table tbl_met_output_coil; 
    v_generate_count NUMBER := 2;
BEGIN
    SELECT SUBSTR (sl.task_job_id, 1, 12) AS schedule_id,
           t.seq AS coil_seq,
           round(p.ACTUAL_WEIGHT/3,3) AS weight,
           scd.so_id,
           scd.so_line_id
      BULK COLLECT INTO v_output_piece_table
      FROM sch_line            sl,
           sch_input_material  sim,
           sch_input_piece     sip,
           sch_output_material som,
           sch_cut             sc,
           sch_cut_detail      scd,
           piece               P,
           -- 生成指定行数的序列
           (SELECT LEVEL seq FROM DUAL CONNECT BY LEVEL <= v_generate_count) t
     WHERE sl.task_job_id = 180078
       AND sl.sch_line_num_id = sim.sch_line_num_id
       AND sl.sch_line_num_id = som.sch_line_num_id
       AND som.output_mat_num_id = sc.output_mat_num_id
       AND sc.schc_cut_num_id = scd.schc_cut_num_id
       AND sim.input_mat_num_id = sip.input_mat_num_id
       AND sip.piece_num_id = P.piece_num_id
     ORDER BY t.seq, sl.seq, sim.seq, sip.seq;
END;
/

两种方案都可以实现你要的效果，修改v_generate_count的值即可动态调整生成的行数。

内容的提问来源于stack exchange，提问作者Muhammet AÇIK