테이블 동기화

테이블 동기화를 위한 쿼리를 작성해보자.

예제를 위해 아래와 같이 테이블을 생성하자. ts 테이블이 소스 테이블, tt 테이블이 타켓 테이블이다.

-- 1
DROP TABLE ts PURGE;
DROP TABLE tt PURGE;
CREATE TABLE ts (c1 NUMBER, c2 NUMBER, CONSTRAINT ts_pk PRIMARY KEY (c1));
CREATE TABLE tt (c1 NUMBER, c2 NUMBER, CONSTRAINT tt_pk PRIMARY KEY (c1));

INSERT INTO ts VALUES (1, 0);
INSERT INTO ts VALUES (2, 2);
INSERT INTO ts VALUES (4, 4);
INSERT INTO tt VALUES (1, 1);
INSERT INTO tt VALUES (2, 2);
INSERT INTO tt VALUES (3, 3);
COMMIT;

아래 쿼리로 동일, 신규, 변경, 삭제된 행을 식별할 수 있다.

-- 2
SELECT   c1, c2, COUNT (ts) AS ts, COUNT (tt) AS tt
    FROM (SELECT c1, c2, 1 AS ts, NULL AS tt FROM ts UNION ALL
          SELECT c1, c2, NULL AS ts, 1 AS tt FROM tt)
GROUP BY c1, c2
ORDER BY 1, 3, 4;

C1 C2 TS TG
-- -- -- --
 1  1  0  1 -- 변경
 1  0  1  0 -- 변경
 2  2  1  1 -- 동일
 3  3  0  1 -- 삭제
 4  4  1  0 -- 신규

5 행이 선택되었습니다.

아래 쿼리로 입력, 수정, 삭제할 행을 식별할 수 있다.

-- 3
SELECT a.*
     , CASE
           WHEN cn = 1 AND ts = 1 THEN 'C' -- 입력
           WHEN cn = 2 AND ts = 1 THEN 'U' -- 수정
           WHEN cn = 1 AND tt = 1 THEN 'D' -- 삭제
       END AS tp
  FROM (SELECT   c1, c2, COUNT (ts) AS ts, COUNT (tt) AS tt, COUNT (*) OVER (PARTITION BY c1) AS cn
           FROM (SELECT c1, c2, 1 AS ts, null AS tt FROM ts UNION ALL
                 SELECT c1, c2, null AS ts, 1 AS tt FROM tt)
        GROUP BY c1, c2
          HAVING COUNT (ts) <> COUNT (tt)) a
 WHERE (   (cn = 1 AND ts = 1)
        OR (cn = 2 AND ts = 1)
        OR (cn = 1 AND tt = 1));

C1 C2 TS TT CN TP
-- -- -- -- -- --
 1  0  1  0  2 U
 3  3  0  1  1 D
 4  4  1  0  1 C

3 행이 선택되었습니다.

선분 이력을 관리한다면 아래의 코드를 응용할 수 있다.

-- 4
BEGIN
    FOR c1 IN (...) LOOP
        IF c1.tp IN ('U', 'D') THEN UPDATE ... END IF;
        IF c1.tp IN ('C', 'U') THEN INSERT ... END IF;
    END LOOP c1;
END;