我有表格t1，t2并且它們都有created_on包含創建每條記錄時的時間戳的列。平常事。
現在我想創建一個視圖，該視圖將顯示我的表和MAX(created_on)相應表中上次創建的記錄 ( )的時間戳。
結果應如下所示：
table | last_record
====== ============
t1 | 10.05.2019
t2 | 12.11.2020
例如，我可以使用以下命令檢索我的表串列：
SELECT * FROM USER_TABLES WHERE table_name LIKE 'T%'
我想獲取每個表的最后一條記錄的時間戳。
如何創建這個視圖？
uj5u.com熱心網友回復：
這可能取決于表格的描述；我認為它們在某種程度上彼此相關。
無論如何：這就是我理解這個問題的方式。閱讀代碼中的注釋。
SQL> with
2 -- sample data
3 t1 (id, name, created_on) as
4 (select 1, 'Little', date '2021-12-14' from dual union all --> max for Little
5 select 2, 'Foot' , date '2021-12-13' from dual union all --> max for Foot
6 select 2, 'Foot' , date '2021-12-10' from dual
7 ),
8 t2 (id, name, created_on) as
9 (select 2, 'Foot' , date '2021-12-09' from dual union all
10 select 3, 'SBrbot', date '2021-12-14' from dual --> max for SBrbot
11 )
12 -- query you'd use for a view
13 select id, name, max(created_on) max_created_on
14 from
15 -- union them, so that it is easier to find max date
16 (select id, name, created_on from t1
17 union all
18 select id, name, created_on from t2
19 )
20 group by id, name;
ID NAME MAX_CREATE
---------- ------ ----------
1 Little 14.12.2021
2 Foot 13.12.2021
3 SBrbot 14.12.2021
SQL>
當你固定的問題，這是更容易了; 查看查詢從第 12 行開始：
SQL> with
2 -- sample data
3 t1 (id, name, created_on) as
4 (select 1, 'Little', date '2021-12-14' from dual union all
5 select 2, 'Foot' , date '2021-12-13' from dual union all
6 select 2, 'Foot' , date '2021-12-10' from dual
7 ),
8 t2 (id, name, created_on) as
9 (select 2, 'Foot' , date '2021-12-09' from dual union all
10 select 3, 'SBrbot', date '2021-12-14' from dual
11 )
12 select 't1' source_table, max(created_on) max_created_on from t1
13 union
14 select 't2' source_table, max(created_on) max_created_on from t2;
SO MAX_CREATE
-- ----------
t1 14.12.2021
t2 14.12.2021
SQL>
如果它必須是dynamic，一種選擇是創建一個回傳參考游標的函式：
SQL> create or replace function f_max
2 return sys_refcursor
3 is
4 l_str varchar2(4000);
5 rc sys_refcursor;
6 begin
7 for cur_r in (select distinct c.table_name
8 from user_tab_columns c
9 where c.column_name = 'CREATED_ON'
10 order by c.table_name
11 )
12 loop
13 l_str := l_str ||' union all select ' || chr(39) || cur_r.table_name || chr(39) ||
14 ' table_name, max(created_on) last_updated from ' || cur_r.table_name;
15 end loop;
16
17 l_str := ltrim(l_str, ' union all ');
18
19 open rc for l_str;
20 return rc;
21 end;
22 /
Function created.
測驗：
SQL> select f_max from dual;
F_MAX
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
TA LAST_UPDAT
-- ----------
T1 14.12.2021
T2 14.12.2021
SQL>
uj5u.com熱心網友回復：
我有 30 多個表，并希望避免對這些表中的每一個都使用硬編碼 SELECT 陳述句并將它們全部聯合起來。我期待一些解決方案，我會在有點陣列中插入表名并創建 JOIN 以顯示所有最后記錄的結果。我知道問題是表名是可變的！
您不能在 SQL 中執行此操作，因為 aVIEW在編譯時設定并且表必須是已知的；您能做的最好的事情是在 PL/SQL 中動態創建 SQL 陳述句EXECUTE IMMEDIATE，然后如果要重新創建視圖，請使用并重新運行它：
DECLARE
v_tables SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
'TABLE1', 'TABLE2', 'table3', 'TABLE5'
);
v_sql CLOB := 'CREATE OR REPLACE VIEW last_dates (table_name, last_date) AS ';
BEGIN
FOR i in 1 .. v_tables.COUNT LOOP
IF i > 1 THEN
v_sql := v_sql || ' UNION ALL ';
END IF;
v_sql := v_sql || 'SELECT '
|| DBMS_ASSERT.ENQUOTE_LITERAL(v_tables(i))
|| ', MAX(created_on) FROM '
|| DBMS_ASSERT.ENQUOTE_NAME(v_tables(i), FALSE);
END LOOP;
EXECUTE IMMEDIATE v_sql;
END;
/
然后對于示例表：
CREATE TABLE table1 (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE table2 (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE "table3" (created_on) AS
SELECT SYSDATE - LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
CREATE TABLE table5 (created_on) AS
SELECT SYSDATE FROM DUAL;
運行 PL/SQL 塊后，然后：
SELECT * FROM last_dates;
輸出：
TABLE_NAME LAST_DATE 表格1 2021-12-13 13:21:58 表2 2021-12-13 13:21:59 表3 2021-12-13 13:21:59 表5 2021-12-14 13:21:59
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/385730.html