如何逐行遍历Oracle SQL查询结果并生成子查询获取分组样本数据

方案可行性确认 #

你提出的PL/SQL存储过程方案完全可行，实现逻辑通顺：

• 第一步创建临时表存储分组统计的make、year、color三个维度值
• 第二步用游标遍历临时表每一行，将三个字段赋值给变量
• 第三步动态拼接WHERE条件，执行子查询取每组最多3条数据插入结果表即可

该方案灵活度极高，后续要调整分组逻辑、样本返回数量、过滤条件都可以直接修改存储过程代码，符合你提到的复用和调整需求。

更优实现方案 #

如果没有特别复杂的自定义逻辑，完全不需要写存储过程，用Oracle原生窗口函数即可单条SQL实现需求，性能比游标遍历高很多，尤其适合数据量较大的场景：

SELECT *
FROM (
    SELECT 
        c.*,
        -- 按三个维度分组，每组内按自定义规则排序生成行号，此处ORDER BY可替换为你需要的样本排序规则，比如车辆ID、上牌时间等
        ROW_NUMBER() OVER (PARTITION BY make, year, color ORDER BY 1) AS group_rn
    FROM cars c
) t
WHERE t.group_rn <= 3
-- 若需要按分组的统计数量倒序排列最终结果，可增加下面的排序逻辑
ORDER BY COUNT(*) OVER (PARTITION BY make, year, color) DESC;

该方案的优势：

• 无需编写存储过程、无需创建临时表，代码简洁易维护
• 调整成本极低：修改PARTITION BY后的字段即可调整分组维度，修改group_rn <=后的数字即可调整每组样本返回数量，修改OVER内的ORDER BY即可调整样本的排序优先级
• 执行效率远高于游标遍历+动态拼接SQL的方案，Oracle优化器可以直接做全表扫描+分区排序，没有额外的游标开销。

PL/SQL存储过程参考实现 #

如果你确实需要用存储过程（比如后续要加入非常复杂的动态逻辑，窗口函数无法满足），可以参考下面的简化实现，不需要额外创建临时表存分组结果，直接用游标遍历分组查询即可：

DECLARE
    -- 定义变量存储分组维度值
    v_make cars.make%TYPE;
    v_year cars.year%TYPE;
    v_color cars.color%TYPE;
    -- 定义游标遍历分组统计结果
    CURSOR cur_group IS
        SELECT make, year, color
        FROM cars
        GROUP BY make, year, color
        ORDER BY COUNT(*) DESC;
BEGIN
    -- 清空结果表（也可以用Oracle全局临时表存储结果，会话结束自动清理）
    EXECUTE IMMEDIATE 'TRUNCATE TABLE car_group_sample_result';
    
    OPEN cur_group;
    LOOP
        FETCH cur_group INTO v_make, v_year, v_color;
        EXIT WHEN cur_group%NOTFOUND;
        -- 插入当前分组的最多3条样本
        INSERT INTO car_group_sample_result
        SELECT *
        FROM cars
        WHERE make = v_make
          AND year = v_year
          AND color = v_color
          AND ROWNUM <= 3; -- 此处也可以加ORDER BY控制样本排序规则
    END LOOP;
    CLOSE cur_group;
    COMMIT;
END;
/

如果需要动态拼接SQL（比如分组维度是动态可变的），可以在存储过程中拼接INSERT语句后用EXECUTE IMMEDIATE执行即可。

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