SET SERVEROUTPUT ON

-- 启动计时 以便观察脚本执行时间

SET TIMING ON

SET TIME ON

-- 数据初始化

DROP TABLE tab_test_C;

CREATE TABLE tab_test_C

(

    C1 VARCHAR2(512),

    C2 VARCHAR2(512),

    C3 VARCHAR2(512),

    C4 VARCHAR2(512),

    C5 VARCHAR2(512),

    C6 VARCHAR2(512),

    C7 VARCHAR2(512),

    C8 VARCHAR2(512),

    C9 VARCHAR2(512),

    C10 VARCHAR2(512)

);

DECLARE

    v_total number;

BEGIN

    v_total := 0;

    LOOP

        EXIT WHEN v_total >= 50000;

        for cur in (select owner, object_name, subobject_name, object_id, data_object_id, object_type,

            created, last_ddl_time, timestamp from all_objects where rownum < 101)

        loop

            insert into tab_test_C values (cur.owner, cur.object_name, cur.subobject_name,

            cur.object_id, cur.data_object_id,

            cur.object_type, cur.created,

            cur.last_ddl_time, cur.timestamp, v_total);

            v_total := v_total + 1;

        end loop;

    END LOOP;

    COMMIT;

END;

/

-- 建唯一索引

select count(1) from tab_test_C;

create UNIQUE INDEX uid_test_c_1 on tab_test_C(C10);

--初始化tab_test_Q表数据，先从tab_test_C生成同步40000条数据，剩下10000条数据使用脚本同步过来

DROP TABLE tab_test_Q;

CREATE TABLE tab_test_Q AS SELECT * FROM tab_test_C where rownum < 40001;

create UNIQUE INDEX uid_test_q_1 on tab_test_Q(C10);

-- 验证数据未同步成功 此时记录数差1000

select count(*) from tab_test_Q;

-- 使用merge语法同步tab_test_C的数据到tab_test_Q

DECLARE

    CURSOR cur is select * from tab_test_C;

    type mergeArray_t is table of tab_test_C % ROWTYPE index by BINARY_INTEGER;

    mergeArray mergeArray_t;

BEGIN

    OPEN cur;

    LOOP

        EXIT WHEN cur % NOTFOUND;

        FETCH cur bulk collect into mergeArray LIMIT 16; -- 每次限十几条记录，不要占用太多内存 这个数字调大点效率会更高

        BEGIN

            FORALL rw IN 1 .. mergeArray.count

                MERGE INTO tab_test_Q A

                USING (SELECT mergeArray(rw).C1 C1, mergeArray(rw).C2 C2, mergeArray(rw).C3 C3, mergeArray(rw).C4 C4,

                    mergeArray(rw).C5 C5, mergeArray(rw).C6 C6, mergeArray(rw).C7 C7, mergeArray(rw).C8 C8,

                    mergeArray(rw).C9 C9, mergeArray(rw).C10 C10 FROM DUAL) B

                ON (A.C10 = B.C10)

                WHEN MATCHED THEN

                    UPDATE SET A.C1 = mergeArray(rw).C1, A.C2 = mergeArray(rw).C2, A.C3 = mergeArray(rw).C3,

                        A.C4 = mergeArray(rw).C4, A.C5 = mergeArray(rw).C5,

                        A.C6 = mergeArray(rw).C6, A.C7 = mergeArray(rw).C7, A.C8 = mergeArray(rw).C8,

                        A.C9 = mergeArray(rw).C9

                WHEN NOT MATCHED THEN

                    INSERT (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) VALUES(mergeArray(rw).C1, mergeArray(rw).C2,

                        mergeArray(rw).C3, mergeArray(rw).C4, mergeArray(rw).C5, mergeArray(rw).C6,

                        mergeArray(rw).C7, mergeArray(rw).C8, mergeArray(rw).C9, mergeArray(rw).C10);

            -- DBMS_OUTPUT.PUT_LINE(mergeArray.count);

        EXCEPTION

            WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE('error1');

        END;

    END LOOP;

    CLOSE cur;

    COMMIT;

END;

/

--耗时0.04秒

-- 验证数据同步成功

select count(*) from tab_test_Q;

--初始化tab_test_Q表数据，先从tab_test_C生成同步40000条数据，剩下10000条数据使用脚本同步过来

DROP TABLE tab_test_Q;

CREATE TABLE tab_test_Q AS SELECT * FROM tab_test_C where rownum < 40001;

create UNIQUE INDEX uid_test_q_1 on tab_test_Q(C10);

-- 验证数据未同步成功 此时记录数差1000

select count(*) from tab_test_Q;

-- 使用常规语法同步tab_test_C的数据到tab_test_Q

BEGIN

    for cur in (select * from tab_test_C)

    LOOP

        BEGIN

            INSERT INTO tab_test_Q(C1, C2, C3, C4, C5, C6, C7, C8, C9, C10)

            VALUES(cur.C1, cur.C2, cur.C3, cur.C4, cur.C5, cur.C6, cur.C7, cur.C8, cur.C9, cur.C10);

        EXCEPTION

            WHEN DUP_VAL_ON_INDEX THEN --唯一索引冲突时更新

                UPDATE tab_test_Q SET C1 = cur.C1, C2 = cur.C2, C3 = cur.C3, C4 = cur.C4, C5 = cur.C5, C6 = cur.C6, C7 = cur.C7, C8 = cur.C8, C9 = cur.C9

                WHERE C10 = cur.C10;

            WHEN OTHERS THEN

                    DBMS_OUTPUT.PUT_LINE('error1');

        END;

    END LOOP;

    COMMIT;

END;

/

--耗时14.77秒

-- 验证数据同步成功

select count(*) from tab_test_Q;