Oracle 12c中如何通过循环生成数据且无需创建临时/永久表？

问题描述 #

我正在使用PaaS（Asset Works）开发一份带循环逻辑的报表，需求是从公交表生成未来50年的公交替换信息电子表格：部分公交每10年替换一次，其余每12年替换一次，要列出所有替换记录。

我已经用到了for Counter in 1..MaxCounter loop循环结构，也知道怎么查询后存入表，但因为不是自己的Oracle数据库，无法创建临时表或永久表，不知道该怎么暂存查询结果来生成报表。

附上当前的PL/SQL代码：

Declare
Counter number;
MaxCounter number := 50;
StartYear number := 2021;

BEGIN

for Counter in 1..MaxCounter loop
    Select * From eq_main where year(Delivery_Date) = startyear + maxcounter;
    -- Save this rowset somewhere...

end Loop;
commit;
end;

可行解决方案 #

1. 使用PL/SQL集合暂存数据 #

直接在PL/SQL中定义自定义集合类型，把查询结果存在内存里，无需创建数据库表。示例如下：

-- 先定义与eq_main表结构匹配的记录类型和集合类型
TYPE eq_main_rec IS RECORD (
    asset_id eq_main.asset_id%TYPE,
    delivery_date eq_main.delivery_date%TYPE,
    replace_cycle NUMBER, -- 标记10/12年替换周期
    -- 根据实际表结构添加其他字段
);
TYPE eq_main_tab IS TABLE OF eq_main_rec;

DECLARE
    v_replace_records eq_main_tab := eq_main_tab();
    v_counter NUMBER;
    v_max_counter NUMBER := 50;
    v_start_year NUMBER := 2021;
BEGIN
    FOR v_counter IN 1..v_max_counter LOOP
        -- 用BULK COLLECT批量把查询结果存入集合
        SELECT 
            asset_id,
            delivery_date,
            CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END AS replace_cycle
            -- 其他字段
        BULK COLLECT INTO v_replace_records
        FROM eq_main
        WHERE EXTRACT(YEAR FROM delivery_date) + CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END = v_start_year + v_counter;
    END LOOP;
    
    -- 遍历集合生成报表输出
    FOR i IN v_replace_records.FIRST..v_replace_records.LAST LOOP
        -- 这里可以对接报表工具的输出接口，或直接打印成CSV格式
        DBMS_OUTPUT.PUT_LINE(v_replace_records(i).asset_id || ',' || v_replace_records(i).delivery_date || ',' || v_replace_records(i).replace_cycle);
    END LOOP;
END;

注意：把replace_type = '10Y'替换成实际区分10年/12年替换公交的判断条件。

2. 循环中直接输出报表内容 #

如果不需要统一暂存所有数据，每次查询后直接把结果输出到报表（比如写入CSV流、传递给平台报表组件），省掉暂存步骤。示例调整：

DECLARE
    v_counter NUMBER;
    v_max_counter NUMBER := 50;
    v_start_year NUMBER := 2021;
BEGIN
    -- 先输出报表表头
    DBMS_OUTPUT.PUT_LINE('资产ID,交付日期,替换周期,替换年份');
    
    FOR v_counter IN 1..v_max_counter LOOP
        -- 用游标处理每行查询结果，直接输出
        FOR rec IN (
            SELECT 
                asset_id,
                delivery_date,
                CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END AS replace_cycle,
                v_start_year + v_counter AS replace_year
            FROM eq_main
            WHERE EXTRACT(YEAR FROM delivery_date) + CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END = v_start_year + v_counter
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(rec.asset_id || ',' || rec.delivery_date || ',' || rec.replace_cycle || ',' || rec.replace_year);
        END LOOP;
    END LOOP;
END;

这种方式适合生成流式报表，内存占用更小，数据量大时更高效。

3. 用递归SQL替代循环，直接生成全量数据 #

完全跳过PL/SQL循环，用Oracle递归查询一次性算出未来50年的所有替换记录，直接交给平台报表工具生成电子表格：

WITH years AS (
    SELECT 2021 + LEVEL AS replace_year
    FROM dual
    CONNECT BY LEVEL <= 50
)
SELECT 
    e.asset_id,
    e.delivery_date,
    CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END AS replace_cycle,
    y.replace_year
FROM eq_main e
JOIN years y ON EXTRACT(YEAR FROM e.delivery_date) + CASE WHEN replace_type = '10Y' THEN 10 ELSE 12 END = y.replace_year
ORDER BY y.replace_year, e.asset_id;

这个SQL直接输出所有需要的替换记录，不需要任何暂存操作，适配大多数PaaS平台的报表生成功能。

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