[ORACLE] MERGE(머지)

이번엔 머지에 대해서 알아 볼 것이다.

---

  MERGE(머지)
   MERGE는 하나 혹은 그 이상의 테이블에 대한 INSERT나 UPDATE 작업을 수행할 때 여러 명령어들을 묶어서 질의하게 해주는 기능이다. 이 명령어는 여러 개의 동작을 조합해서 실행하게 해주므로 쿼리 작업이 매우 편리하다. INSERT나 UPDATE에서 조건을 추가할 수도 있고 여러 행 삭제 DELETE도 한 번에 수행하게 해준다. 단일 테이블에 MERGE 문은 자주 사용하므로 잘 알아 두는 것이 좋다. 다른 테이블이 아니라 직접 내용을 넣는다면 USING 절에 dual 테이블을 사용한다. 

  예를 들어 ON 조건에 일치하면 UPDATE하고, 불일치하면 INSERT를 하는 쿼리문이라면 
'MERGE INTO 테이블_명 USING SELECT ~ ON 조건 
  WHEN MATCHED THEN UPDATE ~나 DELETE ~ 구문
  WHEN NOT MATCHED THEN
    INSERT 구문 
  WHERE ~ 조건'식으로 사용할 수 있다.   

  merge를 사용해서 사번 100번이 있으면 부서를 20으로 변경하고, 없으면 사번 101번 Neena 사용자를 20번 부서에 넣어보자.  
  MERGE INTO employees USING dual ON (employee_id = 100)
    WHEN MATCHED THEN
      UPDATE 
        SET deptment_id = 20
    WHEN NOT MATCHED THEN
      INSERT (employee_id, last_name, department_id) VALUES(101, 'Neena', 20); 해본다. ‘1행이 병합되었습니다’ 메시지가 나온다.

---

●예제

/* Sales 부서의 사원들에게 급여의 10%와 $100을 보너스로 주고, 그 외의 부서에 대해서는 급여의 10%만 보너스로 주되, 
   급여가 $8,000 이하의 직원에 대해서만 적용시키시오. */
create table bonuses(employee_id number, bonus number default 100);  

insert into bonuses(employee_id)
select employee_id from employees e JOIN departments d
                   ON d.department_id = e.department_id
where department_name = 'Sales';

commit; 

select * from bonuses;

merge into bonuses b using (select employee_id, salary, department_id from employees) e
      on (e.employee_id = b.employee_id)
when matched then
  update 
    set bonus = salary * 1.1
    delete where salary > 8000
when not matched then
  insert values (e.employee_id, salary * 0.1)
  where salary <= 8000; 

 

/*머지를 사용해서 사번 100번이 있으면 부서를 20으로 변경하고 없으면, 101번Neena 사용자를 20번 부서로 넣으시오.*/ 
select last_name, first_name from employees
where employee_id = 101;

merge into employees1 using dual on (employee_id=100)
when matched then update
set department_id =20
when not matched then
insert (employee_id, last_name, department_id) values(101, 'Neena', 20);

select * from employees1
where employee_id =100;

select * from employees1
where first_name = 'Neena';

/*  판매 부서의 사원들에게 급여의 10%와 $100을 보너스로 주고, 그외의 부서에 대해서는 급여의 10%만 보너스로 주되, 급여가 $ 8000이하의 직원에 대해서만 적용시키시오. */
create table bonuses(employee_id number, bonus number default 100); 
// 보너스 항목에 값을 안주면 디폴트 값이 100으로 준다
//hire_date default SYSDATE 

insert into bonuses(employee_id)
select employee_id from employees e JOIN departments d
             ON d.department_id = e.department_id
where department_name = 'Sales';

commit;*/

select * from bonuses;

merge into bonuses b using (select employee_id, salary, department_id from employees) e
       on (e.employee_id = b.employee_id)
when matched then 
update
 set bonus = salary * 1.1
 delete where salary > 8000
when not matched then
 insert values (e.employee_id, salary * 0.1)
 where salary <= 8000;

select b.employee_id, bonus, salary, department_id from 
bonuses b JOIN employees e
on e.employee_id =  b.employee_id;