PL/SQL创建基于动态表名的视图

Question

我试图创建一个视图，访问所有表以相同的名称开始，他们有完全相同的结构，并在时间会有更多。

表名称:

TEMP_ENTITIES_1000
TEMP_ENTITIES_1001
TEMP_ENTITIES_1002

，将来会有

TEMP_ENTITIES_1003
TEMP_ENTITIES_1004

等等…

我需要的是使用下面脚本中的表列表，然后使用结果对象名称的列表result在视图中访问。

select object_name
from user_objects 
where object_type  = 'TABLE'
and object_name like upper('temp_entities_%');
create view entities_join as
select * from (object_name)

有可能实现吗?

Answer 1

在Oracle的现代版本中，您可以使用SQL表宏而不调度任何内容。它将构建动态查询，并可以用作普通视图。

示例如下:

SQL> insert all 
2    when mod(rownum, 5) = 0 then into TEMP_ENTITIES_1000 values (l, dt, val) 
3    when mod(rownum, 5) = 1 then into TEMP_ENTITIES_1001 values (l, dt, val) 
4    when mod(rownum, 5) = 2 then into TEMP_ENTITIES_1002 values (l, dt, val) 
5    when mod(rownum, 5) = 3 then into TEMP_ENTITIES_1003 values (l, dt, val) 
6    when mod(rownum, 5) = 4 then into TEMP_ENTITIES_1004 values (l, dt, val) 
7  
8  select 
9    level as l, 
10    sysdate + level as dt, 
11    level as val 
12  from dual 
13  connect by level < 10;
9 rows inserted.
SQL> 
SQL> create or replace function f_temp_entities_union 
2  /*Create a macro*/
3  return varchar2 SQL_MACRO
4  is 
5    v_union varchar2(4000); 
6  begin 
7    select listagg('select ''' || table_name || ''' as src, a.* from ' || table_name || ' a ', chr(10) || 'union all' || chr(10)) 
8      into v_union 
9    from all_tables 
10    where table_name like 'TEMP_ENTITIES%' 
11      and owner = sys_context('USERENV', 'CURRENT_SCHEMA') 
12    ; 
13  
14    return v_union; 
15  end;
16  /
Function F_TEMP_ENTITIES_UNION compiled
SQL> 
SQL> select * 
2  from f_temp_entities_union();
TEMP_ENTITIES_1000          5 20.09.21          5
TEMP_ENTITIES_1001          1 16.09.21          1
TEMP_ENTITIES_1001          6 21.09.21          6
TEMP_ENTITIES_1002          2 17.09.21          2
TEMP_ENTITIES_1002          7 22.09.21          7
TEMP_ENTITIES_1003          3 18.09.21          3
TEMP_ENTITIES_1003          8 23.09.21          8
TEMP_ENTITIES_1004          4 19.09.21          4
TEMP_ENTITIES_1004          9 24.09.21          9
9 rows selected. 
SQL> 
SQL> /*Add new table*/
SQL> create table TEMP_ENTITIES_1005 as select * from TEMP_ENTITIES_1001 where 1 = 0;
Table TEMP_ENTITIES_1005 created.
SQL> insert into TEMP_ENTITIES_1005
2  select 1000 + rownum, sysdate + rownum, rownum - 100 from TEMP_ENTITIES_1000;
1 row inserted.
SQL> 
SQL> /*Check that new data is here*/
SQL> select *
2  from v_demo;
TEMP_ENTITIES_1000          5 20.09.21          5
TEMP_ENTITIES_1001          1 16.09.21          1
TEMP_ENTITIES_1001          6 21.09.21          6
TEMP_ENTITIES_1002          2 17.09.21          2
TEMP_ENTITIES_1002          7 22.09.21          7
TEMP_ENTITIES_1003          3 18.09.21          3
TEMP_ENTITIES_1003          8 23.09.21          8
TEMP_ENTITIES_1004          4 19.09.21          4
TEMP_ENTITIES_1004          9 24.09.21          9
TEMP_ENTITIES_1005       1001 16.09.21        -99
10 rows selected. 

Answer 2

可能吗?是的，使用动态SQL。

然而，从你所描述的，看起来你使用的数据模型是错误的． 您应该只有一个具有标识符的表(那些1000、1001、…)值).

那么你就根本不需要视图了，所有的事情都用那个表来做。我建议你考虑这样做。

---

这是一个你可能会做的例子(你问的方式):

几个示例表:

SQL> select * from temp_entities_1000;
ID NAME
---------- ------
1000 Little
SQL> select * from temp_entities_1001;
ID NAME
---------- ----
1001 Foot
SQL> select * from temp_entities_1002;
ID NAME
---------- -----
1002 Scott

程序:

SQL> DECLARE
2     l_str  VARCHAR2 (2000);
3  BEGIN
4     FOR cur_r IN (SELECT object_name
5                     FROM user_objects
6                    WHERE     object_type = 'TABLE'
7                          AND object_name LIKE 'TEMP_ENTITIES%')
8     LOOP
9        l_str :=
10           l_str || ' select * from ' || cur_r.object_name || ' union all ';
11     END LOOP;
12
13     l_str :=
14           'create or replace view entities_join as '
15        || RTRIM (l_str, ' union all');
16
17     EXECUTE IMMEDIATE l_str;
18  END;
19  /
PL/SQL procedure successfully completed.

结果:

SQL> select * from entities_join;
ID NAME
---------- ------
1000 Little
1001 Foot
1002 Scott
SQL>

您必须在每次创建新表时运行该过程，以便将其包含到视图中。