[Oracle]從A資料表同步log資料到B資料表

由於LOG資料表比較不會有update資料的問題，
所以可以忽略不更新舊資料

目標如下：

1. 清理舊資料：刪除 2 年前的資料。只保留兩年內的資料

2. 清理異常新資料：刪除前 2 天後的資料。(避免異常資料)

3. 增量同步：抓取 B 表最大時間，將 A 表更新的資料補進 B 表。

程式Symc_b_from_a，包含三個變數
A資料表
B資料表
C基準欄位 datetime

刪除B資料表的C基準欄位，2年以前的資料。
刪除B資料表的C基準欄位，前2天以後的資料。  (這可以依需求調整(ㄈ
先查詢B資料表的C基準欄位，找到B資料表中C基準欄位的最大值，
從A資料表insert進B資料表，條件是「A資料表的C基準欄位」必須大於「B資料表中C基準欄位的最大值」

執行

SET SERVEROUTPUT ON; BEGIN     sync_b_from_a(         p_table_a  => 'REMOTE.TABLE_A@DBLINK',         p_table_b  => ' LOCAL.TABLE_B',         p_col_name => ' CREATE_DATE' -- 您的基準欄位     ); END;

 

程式

CREATE OR REPLACE PROCEDURE sync_b_from_a (     p_table_a IN VARCHAR2, -- 來源資料表 (例如 'REMOTE.TABLE_A@DBLINK`')     p_table_b IN VARCHAR2, -- 目標資料表 (例如 'LOCAL.TABLE_B')     p_col_name IN VARCHAR2  -- 基準時間欄位名稱 (例如 'CREATE_DATE') ) AS     v_sql         VARCHAR2(4000);     v_max_time_b  DATE;     v_row_count   NUMBER; BEGIN     -- 1. 刪除 B 表中 2 年以前的資料     v_sql := 'DELETE FROM ' || p_table_b ||              ' WHERE ' || p_col_name || ' < ADD_MONTHS(SYSDATE, -24)';     EXECUTE IMMEDIATE v_sql;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('已刪除 2 年前舊資料: ' || v_row_count || ' 筆');       -- 2. 刪除 B 表中前 2 天以後 (未來) 的資料     v_sql := 'DELETE FROM ' || p_table_b ||              ' WHERE ' || p_col_name || ' > SYSDATE - 2';     EXECUTE IMMEDIATE v_sql;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('已刪除前 2 天後資料: ' || v_row_count || ' 筆');       -- 3. 找出 B 表中該時間欄位的最大值     v_sql := 'SELECT MAX(' || p_col_name || ') FROM ' || p_table_b;     EXECUTE IMMEDIATE v_sql INTO v_max_time_b;       -- 如果 B 表是空的，給予一個預設起始值 (例如 2 年前)     IF v_max_time_b IS NULL THEN         v_max_time_b := ADD_MONTHS(SYSDATE, -24);     END IF;         DBMS_OUTPUT.PUT_LINE('B 表目前最大時間點: ' || TO_CHAR(v_max_time_b, 'YYYY-MM-DD HH24:MI:SS'));       -- 4. 從 A 表 Insert 大於該時間點的資料進 B 表     v_sql := 'INSERT INTO ' || p_table_b ||              ' SELECT * FROM ' || p_table_a ||              ' WHERE ' || p_col_name || ' > :max_t';         EXECUTE IMMEDIATE v_sql USING v_max_time_b;     v_row_count := SQL%ROWCOUNT;     DBMS_OUTPUT.PUT_LINE('從 A 表新增資料至 B 表: ' || v_row_count || ' 筆');       -- 5. 完成     COMMIT;   EXCEPTION     WHEN OTHERS THEN         ROLLBACK;         DBMS_OUTPUT.PUT_LINE('錯誤發生: ' || SQLERRM);         RAISE; END; /