-增量同步

-----增量同步

逻辑：用源表的数据更新目标表，如果目标表中存在该数据则更新，如果不存在则插入
实现逻辑：首先，判断目标表中是否存在源表的数据(比较字段)
如果有，则用源表中的数据更新目标表中的对应的该数据
如果没有，则直接将源表中的数据插入到目标表


--有两种方式：
1、游标 （sp+游标的方式）
2、MERGE INTO （不适用于所有的数据库，oracle数据库独有）


---------游标的方式

--比如：将emp表中的数据通过增量同步的方式抽取到 EMP_TAG

--第一步：建表（表已经存在，就不需要建了）

--第二步：同步数据的存储过程(游标)
CREATE OR REPLACE PROCEDURE SP_EMP_TAG_CURSOR
IS
--定义一个游标指向数据来源的结果集
CURSOR C_EMP_TAG
IS
SELECT E.empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
E.sal,
E.comm,
E.deptno,
SYSDATE AS CHECK_DATE
FROM EMP E; ---注意：不要用 *，而是把所有的列名写出来
CT NUMBER;
BEGIN
FOR X IN C_EMP_TAG LOOP
--判断目标表中是否存在源表的数据
SELECT COUNT(*)
INTO CT
FROM EMP_TAG T
WHERE T.EMPNO = X.EMPNO; --用来判断的值肯定是可以拿到一条具体的信息
--目标表中没有源表中的数据，插入数据
IF CT = 0 THEN
INSERT INTO EMP_TAG(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
CHECK_DATE)
/* SELECT E.empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
E.sal,
E.comm,
E.deptno,
SYSDATE
FROM EMP E
WHERE E.EMPNO = X.EMPNO;*/
VALUES(X.EMPNO,
X.ENAME,
X.JOB,
X.MGR,
X.HIREDATE,
X.SAL,
X.COMM,
X.DEPTNO,
X.CHECK_DATE);
COMMIT;
ELSIF CT = 1 THEN
UPDATE EMP_TAG T SET /*T.EMPNO = X.EMPNO,*/ ---比较字段不更新
T.ENAME = X.ENAME,
T.JOB = X.JOB,
T.MGR = X.MGR,
T.HIREDATE = X.HIREDATE,
T.SAL = X.SAL,
T.COMM = X.COMM,
T.DEPTNO = X.DEPTNO,
T.CHECK_DATE = X.CHECK_DATE
WHERE T.EMPNO = X.EMPNO;
COMMIT;
END IF;
END LOOP;
END;

---每写完一句dml语句时，都带一句tcl(commit/rollback)，提高性能的一个小小的点

 

--测试一下
SELECT * FROM EMP_TAG FOR UPDATE;

BEGIN
SP_EMP_TAG_CURSOR;
END;

/*小练习一把：创建一个目标表EMP_M(EMP_NO,E_NAME,JOB,HIRE_DATE,DEPT_NO,D_NAME)，
将源表(EMP+DEPT)中的数据增量同步过来

*/
CREATE OR REPLACE PROCEDURE SP_EMP_M_CURSOR
IS
CURSOR C_EMP_M
IS
SELECT E.empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
D.deptno,
D.DNAME,
SYSDATE AS CHECK_DATE
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;
CT NUMBER;
BEGIN
FOR X IN C_EMP_M LOOP
SELECT COUNT(*)
INTO CT
FROM EMP_M T
WHERE T.EMP_NO = X.EMPNO;

IF CT = 0 THEN
INSERT INTO EMP_M(EMP_NO,
E_NAME,
JOB,
HIRE_DATE,
DEPT_NO,
D_NAME,
LAST_UPDATE_DATE)
VALUES (X.EMPNO,
X.ENAME,
X.JOB,
X.HIREDATE,
X.DEPTNO,
X.DNAME,
X.CHECK_DATE);
COMMIT;

ELSIF CT = 1 THEN
UPDATE EMP_M M SET M.E_NAME = X.ENAME,
M.JOB = X.JOB,
M.HIRE_DATE = X.HIREDATE,
M.DEPT_NO = X.DEPTNO,
M.D_NAME = X.DNAME,
M.LAST_UPDATE_DATE = X.CHECK_DATE
WHERE M.EMP_NO = X.EMPNO;
COMMIT;
END IF;
END LOOP;
END;

--测试一下
SELECT * FROM EMP_M FOR UPDATE;

BEGIN
SP_EMP_M_CURSOR;
END;

 

---------MERGE INTO的方式
--语法结构
MERGE INTO 目标表
USING (增量) ---就是数据源的一个结果集，一般就取个别名
ON (匹配字段) ---目标表中的某个字段 = 上面的这个结果集种的某个字段
WHEN MATCHED THEN UPDATE SET --UPDATE和SET之间不需要加表名
WHEN NOT MATCHED THEN INSERT VALUES; --NSERT和VALUES之间不需要加 INTO 表名(目标表的名),但是可以加 列名(目标表中的列)
COMMIT;

--比如：将emp表中的数据通过增量同步的方式抽取到 EMP_TAG

--第一步：建表（表已经存在，就不需要建了）

--第二步：同步数据的存储过程(MERGE INTO)
CREATE OR REPLACE PROCEDURE SP_EMP_TAG_MERGE
IS
BEGIN
MERGE INTO EMP_TAG T
USING (SELECT empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
SYSDATE AS check_date FROM EMP) F
ON (T.EMPNO = F.EMPNO)
---在目标表中可以匹配到源表中的数据
WHEN MATCHED THEN UPDATE SET /* T.EMPNO = F.EMPNO*/ ---匹配字段不更新
T.ENAME = F.ENAME,
T.JOB = F.JOB,
T.MGR = F.MGR,
T.HIREDATE = F.HIREDATE,
T.SAL = F.SAL,
T.COMM = F.COMM,
T.DEPTNO = F.DEPTNO,
T.CHECK_DATE = F.CHECK_DATE
---在目标表中匹配不到源表中的数据
WHEN NOT MATCHED THEN INSERT VALUES ( F.EMPNO,
F.ENAME,
F.JOB,
F.MGR,
F.HIREDATE,
F.SAL,
F.COMM,
F.DEPTNO,
F.CHECK_DATE);
COMMIT; ---不要忘
END;

--测试一下
SELECT * FROM EMP_TAG FOR UPDATE;

BEGIN
SP_EMP_TAG_MERGE;
END;

/*小练习一把：创建一个目标表EMP_M(EMP_NO,E_NAME,JOB,HIRE_DATE,DEPT_NO,D_NAME)，
将源表(EMP+DEPT)中的数据增量同步过来

*/
CREATE OR REPLACE PROCEDURE SP_EMP_M_MERGE
IS
BEGIN
MERGE INTO EMP_M M
USING(SELECT E.empno,
E.ename,
E.job,
E.mgr,
E.hiredate,
D.deptno,
D.DNAME,
SYSDATE AS CHECK_DATE
FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO) F
ON (M.EMP_NO = F.EMPNO)
WHEN MATCHED THEN UPDATE SET M.E_NAME = F.ENAME,
M.JOB = F.JOB,
M.HIRE_DATE = F.HIREDATE,
M.DEPT_NO = F.DEPTNO,
M.D_NAME = F.DNAME,
M.LAST_UPDATE_DATE = F.CHECK_DATE
WHEN NOT MATCHED THEN INSERT (M.EMP_NO,
M.E_NAME,
M.JOB ,
M.HIRE_DATE ,
M.DEPT_NO ,
M.D_NAME ,
M.LAST_UPDATE_DATE)
VALUES (F.EMPNO,
F.ENAME,
F.JOB,
F.HIREDATE,
F.DEPTNO,
F.DNAME,
F.CHECK_DATE);
COMMIT;
END;
--测试一下
SELECT * FROM EMP_M FOR UPDATE;

BEGIN
SP_EMP_M_MERGE;
END;