如何从数据库中的每个表中读取相同的列?

Question

我有一个巨大的数据库与400+表。每个表都有相同的列id作为Primary键和timestamp_modify">

我想要的是两件事:

现在我想要一个按ID和表名的所有更改的列表，如:

Table  | id   | timestamp_modiy
Kid    | 1    | 24.10.2021 00:01
Parent | 1000 | 24.10.2021 00:02

唯一的，非常糟糕的方式，我能想到的是，我做了一个视图，其中我包括每个该死的表手工读取值…

有更好的方法吗?

Answer 1

如何使用流水线函数?

只是设置日期时间格式(你不需要这样做):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.

类型:

SQL> create or replace type t_row as object
2    (table_name       varchar2(30),
3     id               number,
4     timestamp_modify date)
5  /
Type created.
SQL> create or replace type t_tab is table of t_row;
2  /
Type created.

函数:查询user_tab_columns，它的游标FOR循环获取包含ID和TIMESTAMP_MODIFY列的表，动态创建select语句返回最后一个(MAX函数，以避免too_many_rows)列的值为最后一个TIMESTAMP_MODIFY值(由子查询返回)。

SQL> create or replace function f_test
2    return t_tab pipelined
3  as
4    l_str              varchar2(500);
5    l_id               number;
6    l_timestamp_modify date;
7  begin
8    for cur_r in (select table_name from user_tab_columns
9                  where column_name = 'ID'
10                  intersect
11                  select table_name from user_tab_columns
12                  where column_name = 'TIMESTAMP_MODIFY'
13                 )
14    loop
15      l_str := 'select max(a.id) id, max(a.timestamp_modify) timestamp_modify ' ||
16               'from ' || cur_r.table_name   || ' a ' ||
17               'where a.timestamp_modify = ' ||
18               '  (select max(b.timestamp_modify) '   ||
19               '   from ' || cur_r.table_name         || ' b ' ||
20               '   where b.id = a.id)';
21      execute immediate l_str into l_id, l_timestamp_modify;
22      pipe row(t_row(cur_r.table_name, l_id, l_timestamp_modify));
23    end loop;
24  end;
25  /
Function created.

测试:

SQL> select * from table(f_test);
TABLE_NAME                             ID TIMESTAMP_MODIFY
------------------------------ ---------- -------------------
TABA                                    1 24.10.2021 14:59:29
TAB_1                                   1 24.10.2021 15:03:16
TAB_2                                  25 24.10.2021 15:03:36
TEST                                    5 24.10.2021 15:04:24
SQL>

Answer 2

是的，唯一的方法是union all所有的表，如:

select id, timestamp_modify
from kid
union all
select id, timestamp_modify
from parent
union all
...

性能将会很糟糕，因为每次都会扫描所有的表:(

我想你可能会重新考虑你的db设计…

Answer 3

您可以为此构建一个procedure，但即使这样，它也会对性能产生一些影响。虽然存在循环，但是使用SQL Dynamic，您可能只需要400次迭代，并且在每次迭代中插入该表的所有id。

我正在做一些假设

• 你想要所有的IDs和它们对应的timestamp_modify每个表
我创建了一个表来存储结果。如果您使用相同的名称，它总是会回收对象。如果没有，可以保留历史记录
• 我假设每个ID只有一个timestamp_modify行存在
• 我只过滤包含两个列的模式表。
• 该表还包含table_name，您可以识别记录来自何处。

一个例子

create or replace procedure pr_build_output ( p_tmp_table in varchar2 default 'TMP_RESULT' ) 
is
vcounter   pls_integer;
vsql       clob; 
vtimestamp date; -- or timestamp 
begin 
-- create table to store results 
select count(*) into vcounter from all_tables where table_name = upper(p_tmp_table) and owner = 'MY_SCHEMA';
if vcounter = 1 
then 
execute immediate ' drop table '||p_tmp_table||' purge ' ;
end if;
vsql := ' create table '||p_tmp_table||' 
( table_name varchar2(128) , 
id number, 
timestamp_modify date -- or timestamp
) ';
execute immediate vsql ;
-- Populate rows
for h in 
( select a.table_name from all_tables a 
where a.owner = 'MY_SCHEMA'
and a.table_name in ( select distinct b.table_name from all_tab_columns b where b.owner = 'MY_SCHEMA'
and b.column_name = 'ID' and b.column_name = 'TIMESTAMP_MODIFY'  
)
) 
loop 
vsql := ' insert into '||p_tmp_table||' ( table_name , id, timestamp_modify ) 
select '''||h.table_name||''' as table_name , id , timestamp_modify 
from my_schema.'||h.table_name||' 
' ;
execute immediate vsql ;
commit ;
end loop;
exception when others then raise;
end;
/