Oracle如何用循环替代UNION ALL拼接ft_expenses系列年度分表数据

方案1：预生成统一UNION ALL视图（最常用，性能最优） #

该方案和你原本手写UNION ALL的执行效率完全一致，一次配置后仅需每年新增分表时执行一次更新逻辑，所有上层查询无需修改：

• 执行下方PL/SQL块即可自动生成全量分表合并视图：

DECLARE
    v_sql CLOB := 'CREATE OR REPLACE VIEW v_ft_expenses_all AS ';
BEGIN
    -- 遍历2002到当前年份的所有表，可根据实际需求调整起始年份
    FOR year_num IN 2002..EXTRACT(YEAR FROM SYSDATE) LOOP
        IF year_num != 2002 THEN
            v_sql := v_sql || ' UNION ALL ';
        END IF;
        v_sql := v_sql || 'SELECT * FROM ft_expenses_' || year_num;
    END LOOP;
    EXECUTE IMMEDIATE v_sql;
END;
/

• 日常使用时直接查询v_ft_expenses_all即可，和查询单表的逻辑完全一致
• 每年新增ft_expenses_xxxx分表后，重新执行一次上述PL/SQL块即可更新视图

方案2：无硬编码自动识别分表（零维护成本） #

如果连年份范围都不想硬编码，可直接读取数据字典自动匹配所有符合命名规则的分表，新增分表后完全不需要修改代码：

DECLARE
    v_sql CLOB := 'CREATE OR REPLACE VIEW v_ft_expenses_all AS ';
    v_is_first BOOLEAN := TRUE;
BEGIN
    -- 从数据字典匹配所有命名符合 ft_expenses_四位年份 规则的表
    FOR table_rec IN (
        SELECT table_name
        FROM user_tables
        WHERE REGEXP_LIKE(table_name, '^FT_EXPENSES_\d{4}$')
        ORDER BY table_name
    ) LOOP
        IF NOT v_is_first THEN
            v_sql := v_sql || ' UNION ALL ';
        ELSE
            v_is_first := FALSE;
        END IF;
        v_sql := v_sql || 'SELECT * FROM ' || table_rec.table_name;
    END LOOP;
    EXECUTE IMMEDIATE v_sql;
END;
/

若分表归属于其他用户，将user_tables替换为all_tables，并增加owner = '你的用户名'过滤条件即可。

方案3：PL/SQL FOR LOOP实时计算（适用于临时统计场景） #

如果不需要创建视图，仅做一次性统计计算，可以直接在循环中遍历分表做聚合：

DECLARE
    v_total_expense NUMBER := 0;
    v_year_expense NUMBER;
BEGIN
    FOR year_num IN 2002..EXTRACT(YEAR FROM SYSDATE) LOOP
        EXECUTE IMMEDIATE 'SELECT SUM(expense_amount) FROM ft_expenses_' || year_num INTO v_year_expense;
        v_total_expense := v_total_expense + NVL(v_year_expense, 0);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('累计总费用：' || v_total_expense);
END;
/

长期优化建议 #

如果你使用的是Oracle 12c及以上版本，建议将所有分表合并为按年份的范围分区表，后续新增年度数据仅需新增分区即可，查询时直接查询单表，Oracle会自动路由到对应分区，性能比视图方案更好，也完全不需要维护UNION逻辑。

注意事项 #

• 执行上述PL/SQL的用户需要具备CREATE VIEW权限，以及所有ft_expenses_xxxx分表的查询权限
• 若分表结构发生变更，需要重新执行视图生成逻辑同步字段

内容的提问来源于stack exchange，提问作者Gabriel Braico Dornas