05 ---- 同步数据

审计字段：即记录数据的创建人、创建时间、修改人、修改时间的字段等，体现在每一张数据库表中。通过审计字段，可以知道数据怎么来的，什么时候同步的，若出现问题也可追溯。

4.1 全量抽取

将目标表的数据全部删除，再将源系统的数据全部插入目标表。此方法保证了数据的质量，但是对于数据量大的表而言，性能太差。

【例】同步EMP表

--创建目标表表结构
CREATE TABLE EMPEMP
          (  EMPNO  NUMBER(4)  
            ,ENAME  VARCHAR2(10)
            ,JOB  VARCHAR2(9)   
            ,MGR  NUMBER(4)     
            ,HIREDATE  DATE     
            ,SAL  NUMBER(7,2)   
            ,COMM  NUMBER(7,2)   
            ,DEPTNO  NUMBER(2),
            LAST_UPDATE_DATE DATE
          )
ALTER TABLE EMPEMP ADD CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO)
--创建存储过程
CREATE OR REPLACE PROCEDURE SP_EMPA 
IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMPEMP';
INSERT INTO EMPEMP EE
          ( EE.EMPNO,
            EE.ENAME,
            EE.JOB,
            EE.MGR,
            EE.HIREDATE,
            EE.SAL,
            EE.COMM,
            EE.DEPTNO,
            EE.LAST_UPDATE_DATE )
SELECT  E.EMPNO,
        E.ENAME,
        E.JOB,
        E.MGR,
        E.HIREDATE,
        E.SAL,
        E.COMM,
        E.DEPTNO,
            SYSDATE
FROM EMP E;
COMMIT;
END;
--调用
BEGIN
  SP_EMPA;
END;

注意：
1.尽量不使用，不直观，且有时会使数据插入不对应的字段。
2.表最好别名，给系统减轻识别负担，不易产生分歧。
3.工作中做全量抽取时，通常先判断源表是否有数据，再执行。如果源表没有数据了，同步之后目标表也没有数据了，只能从硬盘里找回数据，成本代价高。

4.2 增量抽取

只需抽取新增的或修改的数据。此方法性能好，但容易遗漏。有时源表更新的字段，在目标表中不存在，则不需要更新。以时间戳取增量，对源表删除的数据无能为力。

① —— MERGE INTO

语法格式：MERGE（不是所有数据库都通用）

MERGE INTO 目标表
USING (增量)
ON (匹配字段)
WHEN MATCHED THEN UPDATE SET   --UPDATE和SET之间不需要加表名
WHEN NOT MATCHED THEN INSERT VALUES
--INSERT和VALUES之间不需要加INTO 表名

【例】同步EMP表数据

CREATE OR REPLACE PROCEDURE SP_EMPC(P_START_DATE VARCHAR2,
                                    P_END_DATE VARCHAR2)IS
                                    V_START_DATE DATE:= TO_DATE(P_START_DATE,'YYYY-MM-DD HH24:MI:SS');
                                    V_END_DATE   DATE:= TO_DATE(P_END_DATE,'YYYY-MM-DD HH24:MI:SS');
BEGIN
      MERGE INTO EMPEMP A
      USING(SELECT  E.EMPNO,
                    E.ENAME,
                    E.JOB,
                    E.MGR,
                    E.HIREDATE,
                    E.SAL,
                    E.COMM,
                    E.DEPTNO
            FROM EMP E
            WHERE E.LAST_UPDATE_DATE >= V_START_DATE
            AND E.LAST_UPDATE_DATE < V_END_DATE  )   B
      ON (A.EMPNO = B.EMPNO)
      WHEN MATCHED THEN
              UPDATE
              SET A.ENAME                   = B.ENAME,
                  A.JOB                     = B.JOB,
                  A.MGR                      = B.MGR,
                  A.HIREDATE                = B.HIREDATE,
                  A.SAL                     = B.SAL,
                  A.COMM                    = B.COMM,
                  A.DEPTNO                  = B.DEPTNO,
                  A.LAST_UPDATE_DATE     = SYSDATE
      WHEN NOT MATCHED THEN
              INSERT
                    (A.EMPNO,A.ENAME,A.JOB,A.MGR,A.HIREDATE,A.SAL,A.COMM,A.DEPTNO,A.LAST_UPDATE_DATE)
              VALUES
                    (B.EMPNO,B.ENAME,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.COMM,B.DEPTNO,SYSDATE);
         COMMIT;
END;
BEGIN
            SP_EMPC('2017-04-05 00:00:00','2017-04-06 00:00:00');
END;

② —— 范围之内删除，再插入

先将目标表的记录在增量范围之内的删除，然后再将增量插入

CREATE OR REPLACE PROCEDURE SP_EMPB(P_START_DATE VARCHAR2,
                                    P_END_DATE VARCHAR2)IS
                                    V_START_DATE DATE:= TO_DATE(P_START_DATE,'YYYY-MM-DD HH24:MI:SS');
                                    V_END_DATE   DATE:= TO_DATE(P_END_DATE,'YYYY-MM-DD HH24:MI:SS');
BEGIN
      DELETE FROM EMPEMP WHERE EMPNO IN(SELECT E.EMPNO
                                        FROM EMP E
                                        WHERE E.LAST_UPDATE_DATE >= V_START_DATE
                                        AND E.LAST_UPDATE_DATE < V_END_DATE);
      INSERT INTO EMPEMP A
      (A.EMPNO,A.ENAME,A.JOB,A.MGR,A.HIREDATE,A.SAL,A.COMM,A.DEPTNO,A.LAST_UPDATE_DATE)
      SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,E.DEPTNO,SYSDATE
      FROM  EMP E
      WHERE LAST_UPDATE_DATE >= V_START_DATE
      AND   LAST_UPDATE_DATE < V_END_DATE;
      COMMIT;
END;
BEGIN
            SP_EMPB('2017-04-05 00:00:00','2017-04-06 00:00:00');
END;

③ —— 游标

使用游标，逐一判断每一条增量记录是需要更新还是插入

CREATE OR REPLACE PROCEDURE SP_EMPD(P_START_DATE VARCHAR2,
                                    P_END_DATE VARCHAR2)IS
                                    V_START_DATE DATE:= TO_DATE(P_START_DATE,'YYYY-MM-DD HH24:MI:SS');
                                    V_END_DATE   DATE:= TO_DATE(P_END_DATE,'YYYY-MM-DD HH24:MI:SS');
      CURSOR C_UPDATE IS
              SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,E.DEPTNO
              FROM EMP E
              WHERE E.LAST_UPDATE_DATE >= V_START_DATE
              AND E.LAST_UPDATE_DATE < V_END_DATE;
      CT NUMBER;
BEGIN
      FOR X IN C_UPDATE LOOP
          SELECT COUNT(1)INTO CT FROM EMPEMP A WHERE A.EMPNO = X.EMPNO;
          IF CT = 1THEN
              UPDATE EMPEMP A
              SET A.ENAME                       = X.ENAME,
                  A.JOB                     = X.JOB,
                  A.MGR                     = X.MGR,
                  A.HIREDATE                = X.HIREDATE,
                  A.SAL                     = X.SAL,
                  A.COMM                    = X.COMM,
                  A.DEPTNO                  = X.DEPTNO,
                  A.LAST_UPDATE_DATE     = SYSDATE
              WHERE A.EMPNO = X.EMPNO;
          ELSIF CT = 0THEN
              INSERT INTO EMPEMP A
                  (A.EMPNO,A.ENAME,A.JOB,A.MGR,A.HIREDATE,A.SAL,A.COMM,A.DEPTNO,A.LAST_UPDATE_DATE)
              VALUES
                      (X.EMPNO,X.ENAME,X.JOB,X.MGR,X.HIREDATE,X.SAL,X.COMM,X.DEPTNO,X.LAST_UPDATE_DATE,SYSDATE);
          END IF;
      END LOOP;
      COMMIT;
END;
BEGIN
            SP_EMPD('2017-04-05 00:00:00','2017-04-06 00:00:00');
END;

—方法四
将增量记录逐一插入目标表，如果违反唯一索引，则更新

4.3 若数据量大时，MERGE删除比DELETE好。

MERGE INTO EMP E
USING(SELECT * FROM EMP S WHERE S.DEPTNO =10) S
ON(S.EMPNO = E.EMPNO)
WHEN MATCHED THEN
      UPDATE
      SET E.COMM =1 
      DELETE WHERE 1=1;   --SET随便改，后面接DELETE

Q：如果源表数据删除了怎么办？

源表的数据一般不会被删除，通常表后面会加一个删除标识字段，用来标识这条记录是否失效，即软删除（华为称为打PD），证明这条记录存在过。
如果源表有数据删除了，可以查看源表的 ，看哪些记录被删除了，在目标表相应记录上加上删除标识，但是此方法代价大，成本高。

Q：两张表A、B关联抽数如何抽取？

--方法1
两张表全量关联，A表的时间或B表的时间在时间范围内

--方法2
(A表的增量与B表的全量关联)
UNION ALL
(B表的增量与A表的全量关联)

--方法3
(A的增量主键
union all
B增量主键
group by 主键)
left join A
on 主键
left join B
on 主键