2026年3月11日 星期三

[Oracle]從A資料表同步log資料到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基準欄位的最大值」



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;

/


2025年10月28日 星期二

[SQL Server]利用Linked Server跨資料庫單向同步資料表

以下預存程序達成這些目標

1. 查詢LocalDB的資料表,若RemoteDB不存在同名資料表則建立資料表。

2. 清空列出的RemoteDB端的資料表,並寫入資料。

3. 印出LocalDB端的資料表筆數、RemoteDB端的資料表筆數


注意事項

1. LinkedServer使用的帳號權限要夠。

2. 建立Cursor的時候可以指定同步哪些資料表,若不指定則會查詢LocalDB的所有資料表。

3. 若是資料表的欄位有異動,可移除RemoteDB端的資料表,執行預存程序會直接依照LocalDB建立資料表即可同步資料表。


執行語法

SyncTablesToRemote

    @LocalDB='LocalDBName',

    @RemoteDB='RemoteDBName',

    @LinkedServer='LinkedServerName'

程式內容

CREATE PROCEDURE [dbo].[SyncTablesToRemote]

    @LocalDB NVARCHAR(128),

    @RemoteDB NVARCHAR(128),

    @LinkedServer NVARCHAR(128)

AS

BEGIN

    SET NOCOUNT ON;

 

    DECLARE @TableName NVARCHAR(128);

    DECLARE @SQL NVARCHAR(MAX);

    DECLARE @LocalCountOut INT;

    DECLARE @RemoteCountOut INT;

    DECLARE @ColumnList NVARCHAR(MAX);

 

    DECLARE @Tables TABLE (TableName NVARCHAR(128));

 

    INSERT INTO @Tables

    SELECT name FROM sys.tables WHERE is_ms_shipped = 0

-- 可以指定要同步那些資料表 and name in ('TableName1','TableName2','TableName3')

     ;

    DECLARE TableCursor CURSOR FOR

    SELECT TableName FROM @Tables;

 

    OPEN TableCursor;

    FETCH NEXT FROM TableCursor INTO @TableName;

 

    WHILE @@FETCH_STATUS = 0

    BEGIN

        -- 建立遠端資料表(若不存在)

        SET @SQL = '

        IF NOT EXISTS (

            SELECT 1 FROM OPENQUERY([' + @LinkedServer + '],

                ''SELECT TABLE_NAME FROM [' + @RemoteDB + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''' + @TableName + ''''''')

        )

        BEGIN

            DECLARE @CreateSQL NVARCHAR(MAX);

            SELECT @CreateSQL = ''CREATE TABLE [' + @TableName + '] ('' + STUFF((

                SELECT '', ['' + c.name + ''] '' +

                    CASE

                        WHEN t.name IN (''varchar'', ''nvarchar'') THEN

                            CASE

                                WHEN c.max_length = -1 OR c.max_length > 4000 THEN t.name + ''(4000)''

                                ELSE t.name + ''('' + CAST(c.max_length AS VARCHAR) + '')''

                            END

                        WHEN t.name IN (''decimal'', ''numeric'') THEN t.name + ''('' + CAST(c.precision AS VARCHAR) + '','' + CAST(c.scale AS VARCHAR) + '')''

                        ELSE t.name

                    END +

                    CASE WHEN c.is_nullable = 0 THEN '' NOT NULL'' ELSE '' NULL'' END

                FROM [' + @LocalDB + '].sys.columns c

                JOIN [' + @LocalDB + '].sys.types t ON c.user_type_id = t.user_type_id

                WHERE c.object_id = OBJECT_ID(''' + @LocalDB + '.dbo.' + @TableName + ''')

                FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'')

            , 1, 2, '''') + '')''

 

            EXEC(''USE [' + @RemoteDB + ']; '' + @CreateSQL) AT [' + @LinkedServer + '];

 

            PRINT ''已於遠端 [' + @LinkedServer + '] 建立資料表 [' + @TableName + ']''

        END

        '

        EXEC(@SQL);

 

        -- 清空遠端資料表

        SET @SQL = '

        EXEC(''

            USE [' + @RemoteDB + '];

            TRUNCATE TABLE [' + @TableName + '];

        '') AT [' + @LinkedServer + ']';

        EXEC(@SQL);

 

        -- 產生欄位清單

        SELECT @ColumnList = STUFF((

            SELECT ', ' + QUOTENAME(name)

            FROM sys.columns

            WHERE object_id = OBJECT_ID(@LocalDB + '.dbo.' + @TableName)

            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

        , 1, 2, '');

 

        -- 匯入資料到遠端

        SET @SQL = '

        INSERT INTO OPENQUERY([' + @LinkedServer + '],

            ''SELECT ' + @ColumnList + ' FROM [' + @RemoteDB + '].dbo.[' + @TableName + ']'')

        SELECT ' + @ColumnList + ' FROM [' + @LocalDB + '].dbo.[' + @TableName + '];';

        EXEC(@SQL);

 

        -- 比對筆數(簡化版)

        DECLARE @TmpLocalCount TABLE (C INT);

        SET @SQL = 'SELECT COUNT(1) AS C FROM [' + @LocalDB + '].dbo.[' + @TableName + ']';

        INSERT INTO @TmpLocalCount EXEC(@SQL);

        SELECT @LocalCountOut = C FROM @TmpLocalCount;

 

        DECLARE @TmpRemoteCount TABLE (C INT);

        SET @SQL = '

        SELECT * FROM OPENQUERY([' + @LinkedServer + '],

            ''SELECT COUNT(1) AS C FROM [' + @RemoteDB + '].dbo.[' + @TableName + ']'')';

        INSERT INTO @TmpRemoteCount EXEC(@SQL);

        SELECT @RemoteCountOut = C FROM @TmpRemoteCount;

 

        PRINT '資料表 [' + @TableName + '] 已同步完成,本地筆數為 ' + CAST(@LocalCountOut AS NVARCHAR) + ',遠端筆數為 ' + CAST(@RemoteCountOut AS NVARCHAR);

 

        FETCH NEXT FROM TableCursor INTO @TableName;

    END

 

    CLOSE TableCursor;

    DEALLOCATE TableCursor;

END