PL/SQL如何基于前缀匹配的动态表名创建统一访问视图？

该需求可以实现，但你当前写的静态SQL逻辑仅支持一次性创建包含现有匹配表的视图，无法自动纳入后续新增的同规则表，需要结合动态SQL方案处理，以下为Oracle环境下的具体实现方式：

一次性静态视图创建方案 #

如果不需要自动适配后续新增的表，仅需要整合当前已有的TEMP_ENTITIES_xxx表，可以直接通过查询拼接视图创建语句：

-- 执行该查询可直接拿到视图创建的完整SQL
SELECT 'CREATE OR REPLACE VIEW entities_join AS ' || LISTAGG('SELECT * FROM ' || object_name, ' UNION ALL ') WITHIN GROUP (ORDER BY object_name) AS view_create_sql
FROM user_objects 
WHERE object_type = 'TABLE'
  AND object_name LIKE 'TEMP_ENTITIES_%';

直接执行查询返回的SQL结果即可完成视图创建，后续新增表时需要重新执行上述操作手动更新视图定义。

自动适配新增表的动态方案 #

如果需要后续新增的同规则表自动纳入视图，需要通过存储过程+触发机制实现：

步骤1：创建动态刷新视图的存储过程 #

CREATE OR REPLACE PROCEDURE refresh_entities_join_view AS
    v_sql CLOB;
BEGIN
    -- 拼接所有符合规则表的UNION ALL语句
    SELECT LISTAGG('SELECT * FROM ' || object_name, ' UNION ALL ') WITHIN GROUP (ORDER BY object_name)
    INTO v_sql
    FROM user_objects 
    WHERE object_type = 'TABLE'
      AND object_name LIKE 'TEMP_ENTITIES_%';

    -- 动态执行视图重构语句
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW entities_join AS ' || v_sql;
END;
/

步骤2：触发视图更新 #

• 手动触发：新增TEMP_ENTITIES_xxx表后，执行EXEC refresh_entities_join_view;即可完成视图更新
• 自动触发：可以创建DDL触发器，监听CREATE TABLE事件，当新增表名符合命名规则时自动调用存储过程刷新视图

注意事项 #

• 所有TEMP_ENTITIES_xxx表的字段数量、字段顺序、字段类型必须完全一致，否则UNION ALL逻辑会报错
• 建议替换视图逻辑中的SELECT *为明确的字段列表，避免某张表结构变动导致视图失效
• 如果匹配的表数量较多导致LISTAGG返回长度超限，可以使用XMLAGG等函数替代SQL拼接逻辑
• 性能允许的前提下更推荐将所有同结构表合并为分区表，以后缀数字作为分区键，无需动态维护视图，查询性能更优

内容的提问来源于stack exchange，提问作者Ana Pinheiro Torres