카테고리 없음
oracle merge
[czar]
2010. 1. 25. 10:42
http://radiocom.kunsan.ac.kr/lecture/oracle/sql/merge.html
merge 문
merge 문은 구조가 같은 두 개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작이다.
예를 들어, 하루에 수만건씩 발생하는 데이터를 하나의 테이블에 관리할 경우 대량의 데이터로 인해 질의문의 성능이 저하된다.
이런 경우, 지점별로 별도의 테이블에서 관리하다가 년말에 종합 분석을 위해 하나의 테이블로 합칠 때 merge 문을 사용하면 편리하다.
merge하고자 하는 소스 테이블의 행을 읽어 타킷 테이블에 매치되는 행이 존재하면 새로운 값으로 UPDATE를 수행하고, 만일 매치되는 행이 없을 경우 새로운 행을 타킷 테이블에서 INSERT를 수행한다.
Merge는 여러 데이터 소스로부터 다양한 데이터를 수집해서 작업을 수행하는 dataware housing등에 적용하면 유용하다.
merge 문에서 where 절은 사용할 수 없으며 대신 on이 사용된다. 또한 when matched then 절과 when not matched then 절에는 테이블명 대신 alias를 사용한다.
【형식】
MERGE [hint] INTO [schema.] {table ¦ view} [t_alias]
USING {{[schema.] {table ¦ view}} ¦
subquery} [t_alias]
ON (condition) [merge_update_clause] [merge_insert_clause] [error_logging_clause];
【merge_update_clause 형식】
WHEN MATCHED THEN UPDATE SET {column = {expr ¦ DEFAULT},...}
[where_clause] [DELETE where_clause]
【merge_insert_clause 형식】
WHEN MATCHED THEN INSERT [(column,...)]
VALUES ({expr,... ¦ DEFAULT}) [where_clause]
【where_clause 형식】
WHERE condition
【error_logging_clause 형식】
LOG ERROR [INTO [schema.] table] [(simple_expression)]
[REJECT LIMIT {integer ¦ UNLIMITED}]
【예제】
SQL> create table emp(
2 id number primary key,
3 name varchar2(10) not null,
4 salary number,
5 bonus number default 100);
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(10)
SALARY NUMBER
BONUS NUMBER
SQL> insert into emp(id,name,salary) values(1001,'jijoe',150);
SQL> insert into emp(id,name,salary) values(1002,'cho',130);
SQL> insert into emp(id,name,salary) values(1003,'kim',140);
SQL> select * from emp;
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1001 jijoe 150 100
1002 cho 130 100
1003 kim 140 100
SQL> create table bonus(id number, bonus number default 100);
Table created.
SQL> insert into bonus(id)
2 (select e.id from emp e);
3 rows created.
SQL> select * from bonus;
ID BONUS
---------- ----------
1001 100
1002 100
1003 100
SQL> merge into bonus D
2 using (select id,salary from emp) S
3 on (D.id = S.id)
4 when matched then update set
5 D.bonus=D.bonus + S.salary*0.01
6 when not matched then insert(D.id, D.bonus)
7 values(S.id,S.salary*0.01);
3 rows merged.
SQL> select * from bonus;
ID BONUS
---------- ----------
1001 101.5
1002 101.3
1003 101.4
SQL>
【예제】
SQL> delete from bonus where id=1003;
1 row deleted.
SQL> merge into bonus D
2 using (select id,salary from emp) S
3 on (D.id = S.id)
4 when matched then update set
5 D.bonus=D.bonus + S.salary*0.02
6 when not matched then insert(D.id, D.bonus)
7 values(S.id, S.salary*0.01);
3 rows merged.
SQL> select * from bonus;
ID BONUS
---------- ----------
1001 104.5
1002 103.9
1003 1.4
SQL>
【예제】
SQL> show user
USER is "OE"
SQL> CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
Table created.
SQL> INSERT INTO bonuses (employee_id)
2 (SELECT e.employee_id FROM employees e, orders o
3 WHERE e.employee_id = o.sales_rep_id
4 GROUP BY e.employee_id);
7 rows created.
SQL> SELECT * FROM bonuses ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 100
154 100
156 100
158 100
159 100
160 100
161 100
7 rows selected.
SQL> MERGE INTO bonuses D
2 USING (SELECT employee_id, salary, department_id FROM employees
3 WHERE department_id = 80) S
4 ON (D.employee_id = S.employee_id)
5 WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary * .01
6 DELETE WHERE (S.salary > 8000)
7 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
8 VALUES (S.employee_id, S.salary*0.1)
9 WHERE (S.salary <= 8000);
7 rows merged.
SQL> SELECT * FROM bonuses ORDER BY employee_id;
EMPLOYEE_ID BONUS
----------- ----------
153 180
154 175
159 180
160 175
161 170
SQL>
merge에 의한 row 삽입
http://radiocom.kunsan.ac.kr/lecture/oracle/sql/merge_insert.htmlMerge를 사용하여 row 삽입
merge를 하려고 하는 source 테이블의 행을 읽어 target 테이블에 매치되는 행이 있을 경우에는 새로운 값으로 UPDATE를 수행하고,
만일 매치되는 행이 없을 경우에는 새로운 행으로 인식하여 target 테이블에 INSERT를 수행한다.
merge 문을 수행하기 위해서는 다음과 같은 권한이 주어져야 한다.
• target 테이블 새로운 행을 삽입하기 위한 insert 권한.
• target 테이블에서 기존 행을 갱신하기 위한 update 권한.
• source 테이블 행을 검색하기 위한 select 권한.【형식】 MERGE INTO 테이블명 alias USING [테이블명 | 뷰명 | subquery] alias ON 조건 WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ... VALUES ...;• merge into 뒤에 테이블을 삽입하거나 target 테이블명을 지정한다.
• WHERE 절은 사용할 수 없고, 대신 ON 절에 JOIN 조건을 기술한다.
• WHEN MATCHED THEN 절에는 ON 절에서 기술한 JOIN 조건을 만족하는 즉, source 테이블과 target 테이블간에 매치되는 행이 존재할 경우 target 테이블의 행을 새로운 값으로 UPDATE하기 위한 구문을 기술한다.
• WHEN NOT MATCHED THEN 절에는 ON 절에서 기술한 JOIN 조건을 만족하지 않는 경우 즉, target 테이블에 행을 INSERT하기 위한 구문을 기술한다.
• WHEN MATCHED THEN 절과 WHEN NOT MATCHED THEN 절에는 테이블명 대신 alias를 사용한다.【예제】 SQL> create table dept1 2 AS select * from dept; 테이블이 생성되었습니다. SQL> create table dept2 2 AS select * from dept; 테이블이 생성되었습니다. SQL> insert into dept2 values(50,'KIM_gun','COREA'); 1 개의 행이 만들어졌습니다. SQL> update dept2 2 set dname='LEE_gun' where deptno=30; 1 행이 갱신되었습니다. SQL> select * from dept2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 LEE_gun CHICAGO 40 OPERATIONS BOSTON 50 KIM_gun COREA SQL> select * from dept1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> merge into dept1 d 2 using dept2 de 3 on (d.deptno=de.deptno) ☜ ON 조건절에 들어 있는 컬럼은 update절에 넣으면 오류발생 4 when matched then 5 update set 6 d.dname=de.dname, 7 d.loc=de.loc 8 when not matched then 9 insert values(de.deptno, de.dname, de.loc); 5 행이 병합되었습니다. SQL> select * from dept1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 LEE_gun CHICAGO 40 OPERATIONS BOSTON 50 KIM_gun COREA SQL>
| 테이블 aa를 bb로 merge ====================> |
| 테이블 bb의 값이 바뀜 ====================> |
|
SQL> create table aa(id number(4), name varchar2(10));
테이블이 생성되었습니다.
SQL> create table bb (id number(4), name varchar2(10));
테이블이 생성되었습니다.
SQL> insert into aa values(1111,'홍길동');
1 개의 행이 만들어졌습니다.
SQL> insert into aa values(2222,'길동무');
1 개의 행이 만들어졌습니다.
SQL> insert into aa values(3333,'죽마고우');
1 개의 행이 만들어졌습니다.
SQL> insert into bb values(1111,'홍길동');
1 개의 행이 만들어졌습니다.
SQL> insert into bb values(2222,'옛친구');
1 개의 행이 만들어졌습니다.
SQL> select * from aa;
ID NAME
---------- ----------
1111 홍길동
2222 길동무
3333 죽마고우
SQL> select * from bb;
ID NAME
---------- ----------
1111 홍길동
2222 옛친구
SQL> merge into bb target
2 using aa source
3 on (source.id=target.id)
4 when matched then
5 update set
6 target.name=source.name
7 when not matched then
8 insert values(source.id,source.name);
3 행이 병합되었습니다.
SQL> select * from bb;
ID NAME
---------- ----------
1111 홍길동
2222 길동무
3333 죽마고우
SQL>