사용 목적 :
서로 다른 테이블을 비교하여, 같거나 다를 경우 다른 Action을 수행 하기 위함.
구문 :
MERGE /*+ Hint */ INTO target_table
USING ( SELECT statement ) alias
ON join_condition
WHEN MATCHED THEN
UPDATE SET ...... WHERE
DELETE WHERE .....
WHEN NOT MATCHED THEN
INSERT () VALUES ()
WHERE .....
예)
TESTSQL.SQL
테이블 설명 :
T_EMP : 사원 테이블
T_SALGRAGE : SALARY 기준 테이블
T_BONUS_TABLE : 기준 보너스 테이블
T_BONUS : 보너스 지급 테이블 (데이터가 없으면 지급 안하는 Role)
1. T_EMP 테이블에 grade 컬럼의 값을 UPDATE 하기
MERGE INTO T_EMP
USING T_SALGRADE S
ON ( T_EMP.sal BETWEEN S.low AND s.high )
WHEN MATCHED THEN UPDATE SET t_emp.grade = s.grade ;
commit;
SELECT * FROM t_emp ;
EMPNO ENAME JOB SAL GRADE
1 HONG CLERK 1000 1
2 SMITH CLERK 1000 1
3 WARD SALESMAN 2000 2
4 SCOTT ANALYST 2500 3
5 JONES SALESMAN 2000 2
6 JAMES CYCLIST 3000 3
7 BMC CYCLIST 3000 3
8 SWORKS CYCLIST 3000 3
9 WILLIER CYCLIST 3000 3
10 KING MANAGER 6000 6
2. T_BONUS 테이블에 보너스 입력하기
MERGE INTO T_BONUS b
USING ( SELECT e.empno , e.grade , e.sal , bt.rate FROM T_emp e , T_bonus_table bt WHERE e.grade = bt.grade ) v
ON ( v.empno = b.empno )
WHEN MATCHED THEN UPDATE SET b.grade = v.grade , b.bonus = (v.sal + v.sal * v.rate/100) , update_date = sysdate
WHEN NOT MATCHED THEN INSERT ( b.empno , b.grade , b.bonus , b.update_date) VALUES ( v.empno , v.grade , (v.sal + v.sal * v.rate/100) , sysdate)
commit ;
SELECT * FROM t_bonus;
EMPNO GRADE BONUS UPDATE_DATE
2 1 2000 2014-01-14 오후 1:23:53
1 1 2000 2014-01-14 오후 1:23:53
3 2 6000 2014-01-14 오후 1:23:53
5 2 6000 2014-01-14 오후 1:23:53
9 3 12000 2014-01-14 오후 1:23:53
4 3 10000 2014-01-14 오후 1:23:53
7 3 12000 2014-01-14 오후 1:23:53
6 3 12000 2014-01-14 오후 1:23:53
8 3 12000 2014-01-14 오후 1:23:53
10 6 36000 2014-01-14 오후 1:23:53
INSERT가 제대로 되었는지 확인
SELECT e.empno , e.grade , e.sal , b.bonus , tb.rate , e.sal + e.sal * tb.rate/100 as bonus
FROM t_emp e , t_bonus b , t_bonus_table tb
WHERE e.empno = b.empno
AND b.grade = tb.grade ;
EMPNO GRADE SAL BONUS RATE BONUS_1
1 1 1000 2000 100 2000
2 1 1000 2000 100 2000
5 2 2000 6000 200 6000
3 2 2000 6000 200 6000
8 3 3000 12000 300 12000
6 3 3000 12000 300 12000
7 3 3000 12000 300 12000
4 3 2500 10000 300 10000
9 3 3000 12000 300 12000
10 6 6000 36000 500 36000
3. DELTE 절 사용
empno 가 1,3,5,7,9 인 사원의 sal을 5000으로 업데이트
UPDATE T_EMP SET SAL = 5000 where empno in ( 1,3,5,7,9) ;
commit ;
JOB이 CLERK과 CYCLIST인 사원만 보너스 인상. 그러나 기본 SALARY가 4000보다 많으면 보너스 지급 안 함.
MERGE INTO T_BONUS b
USING ( SELECT e.empno , e.grade , e.sal , bt.rate FROM T_emp e , T_bonus_table bt WHERE e.grade = bt.grade AND e.job IN ( 'CLERK' , 'CYCLIST' ) ) v
ON ( v.empno = b.empno )
WHEN MATCHED THEN UPDATE SET b.bonus = (v.sal + v.sal * v.rate/100) , update_date = sysdate
DELETE WHERE v.sal > 4000
WHEN NOT MATCHED THEN INSERT ( b.empno , b.grade , b.update_date) VALUES ( v.empno , v.grade , sysdate)
commit ;
SELECT * FROM t_bonus ;
EMPNO GRADE BONUS UPDATE_DATE
2 1 2000 2014-01-14 오후 1:30:53
3 2 6000 2014-01-14 오후 1:23:53
5 2 6000 2014-01-14 오후 1:23:53
4 3 10000 2014-01-14 오후 1:23:53
6 3 12000 2014-01-14 오후 1:30:53
8 3 12000 2014-01-14 오후 1:30:53
10 6 36000 2014-01-14 오후 1:23:53
1,7,9 사번을 가진 사원 제외 됨.
4. WHERE 절 사용
T_BONUS 테이블에 데이터가 없는 사원 중에서 JOB이 CLERK인 사원만 보너스 지급.
MERGE INTO T_BONUS b
USING ( SELECT e.empno , e.grade , e.sal , e.job , bt.rate FROM T_emp e , T_bonus_table bt WHERE e.grade = bt.grade AND e.job IN ( 'CLERK' , 'CYCLIST' ) ) v
ON ( v.empno = b.empno )
WHEN MATCHED THEN UPDATE SET b.bonus = (v.sal + v.sal * v.rate/100) , update_date = sysdate
DELETE WHERE v.sal > 4000
WHEN NOT MATCHED THEN INSERT ( b.empno , b.grade , b.bonus , b.update_date) VALUES ( v.empno , v.grade , (v.sal + v.sal * v.rate/100), sysdate)
WHERE v.job ='CLERK'
commit ;
SELECT * FROM t_bonus ;
EMPNO GRADE BONUS UPDATE_DATE
2 1 2000 2014-01-14 오후 1:33:45
1 1 10000 2014-01-14 오후 1:33:45
3 2 6000 2014-01-14 오후 1:23:53
5 2 6000 2014-01-14 오후 1:23:53
4 3 10000 2014-01-14 오후 1:23:53
6 3 12000 2014-01-14 오후 1:33:45
8 3 12000 2014-01-14 오후 1:33:45
10 6 36000 2014-01-14 오후 1:23:53
아까 빠졌던 1,7,9 중에서 1만 INSERT 됨