我有一個包含 400 多個表的龐大資料庫。每個表都具有相同id的主鍵列和“timestamp_modify”，其中完成了表的最后一次更改。
所以我想要的是兩件事：
現在我想要按 ID 和表名列出所有更改的串列，例如：
Table | id | timestamp_modiy
Kid | 1 | 24.10.2021 00:01
Parent | 1000 | 24.10.2021 00:02
我能想出的唯一非常糟糕的方法是，我創建一個視圖，在其中手動包含每個該死的表并讀出值...
有沒有更好的辦法？
uj5u.com熱心網友回復：
是的，唯一的方法是union all所有表，例如：
select id, timestamp_modify
from kid
union all
select id, timestamp_modify
from parent
union all
...
性能會很糟糕，因為每次都會掃描所有表:(
我想你可能會重新考慮你的資料庫設計......
uj5u.com熱心網友回復：
您可以procedure為此構建一個，但即便如此，它也會對性能產生一些影響。盡管有一個回圈，但使用 SQL Dynamic，您可能只需要 400 次迭代，并且在每次迭代中您都將插入該表的所有 id。
我正在做一些假設
- 你想要每個表的所有
IDs和它們對應的timestamp_modify - 我創建了一個表來存盤結果。如果您始終以相同的名稱使用它，它將回收該物件。如果沒有，您可以保留歷史記錄
- 我假設
timestamp_modify每個 ID只有一行 - 我只過濾包含兩列的架構表。
- 該表還包含 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;
/
uj5u.com熱心網友回復：
流水線函式怎么樣？
只需設定日期時間格式（您不必這樣做）：
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/336361.html
上一篇：為函式的回傳值選擇什么型別
下一篇：創建模式之間的鏈接時的權限問題