- 구조가 같은 두개의 테이블을 비교하여 하나의 테이블로 합치는 조작어인 MERGE와, 기본 키 값을 자동으로 생성하기 위해 일련번호를 생성하는 SEQUENCE에 대해 복습하고자 한다.
MERGE
- 분산되어 있던 대량의 데이터를 통합하여 분석하기 위한 업무에 유용하게 사용된다.
- MERGE의 사용법은 아래와 같다.
MERGE INTO [table_name] [alias] USING [table / view / subquery] [alias] ON [join condition] WHEN MATCHED THEN UPDATE SET ...... WHEN NOT MATCHED THEN INSERT INTO ...... VALUES ......;
- MERGE INTO : 하나의 테이블로 합치기 위한 결과 테이블을 정의한다. 즉, [table_name]에 기록된 테이블에는 합쳐진 결과가 기록된다.
- USING : 앞서 언급한 table_name에 병합시킬 데이터가 있는 테이블이나 뷰, 서브쿼리를 입력한 뒤 별명을 붙인다.
- ON : 조인의 조건을 지정한다. WHERE 절에서 조인하듯이(예: s.num = d.num) 설정하면 된다.
- WHEN MATCHED THEN UPDATE SET ~ : ON 절의 조인 조건을 만족하는 행이 존재하면, 조건에 맞게 행을 UPDATE하도록 한다. 즉, '값 수정'을 하는 구역이다.
- WHEN NOT MATCHED THEN INSERT INTO ~ : ON 절의 조인 조건을 만족하지 않을 경우에 새로운 행으로 INSERT를 수행한다. 즉, '값 입력'을 하는 구역이다.
- 주의할 점: THEN
~에서 앞의 USING 절의 정의된 별명을 사용하지 않으면 에러가 발생한다. - 실제 사용 예시를 보면서 이해해보자
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*.01)
WHERE (S.salary <= 8000);
- bonuses라는 테이블에 employees로부터 얻어낸 서브쿼리의 내용을 삽입할 예정인 것을 볼 수 있다.
- bonuses와 employees에는 employee_id라는 동일한 컬럼이 존재한다.
- 서브쿼리에는 employees에서 department_id = 80인 직원의 정보가 기록되어 있다. 따라서, 부서_id가 80인 직원들의 bonus를 salary의 0.01배 추가하려는 것으로 해석 가능하다.
- 'DELETE WHERE'절을 볼 때, salary가 8000이상인 employee_id에 해당하는 bonuses의 행을 삭제하는 것을 알 수 있다.
- bonuses에 만약 department_id = 80인 직원의 employee_id가 없다면, values ~ where ~ 구문에 맞는 값을 insert한다.
시퀀스
- 시퀀스는 유일한 식별자로, 기본 키 값을 자동으로 생성하기 위해 일련번호를 생성하는 객체이다.
- 이게 어디에 쓰이는고 하니, 예를 들면 웹 게시판에서 글이 등록되는 순서대로 번호를 하나씩 "할당"해 기본키로 지정하고자 할 때 시퀀스를 편리하게 이용할 수 있다. "자동"으로 "할당"하는 것에 의의가 있다.
- 그러면, 다음 예시를 통해 시퀀스의 동작 방식을 이해해보자.
CREATE[/ ALTER] SEQUENCE seq_name [INCREMENT BY n] [START WITH n] [MAXVALUE n / NOMAXVALUE] [MINVALUE n / NOMINVALUE] [CYCLE / NOCYCLE] [CACHE n / NOCACHE]
- CREATE / ALTER : 시퀀스 생성/수정을 의미한다.
- INCREMENT BY n : 일련번호를 1씩 증가시켜 얻을 수도 있지만, '나는 일련번호를 짝수로 하고 싶은데?' 혹은 '나는 1씩 감소시킬껀데?'하면 n에 원하는 값을 할당하면 된다.
- START WITH n : 일련번호가 시작되는 지점을 정한다.
- MAXVALUE n : 생성 가능한 시퀀스의 최대값을 정한다. 정하지 않으면 무한까지 간다.
- MINVALUE n : 생성 가능한 시퀀스이 최소값을 정한다. 정하지 않으면 마이너스 무한까지 간다.
- CYCLE n : 이는 최대-최소값이 정해져 있을 때 유의미한데, 지정한 범위를 넘어서면 다시 순환하도록 번호를 조정할 수 있다.
- CACHE n : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스의 개수이다. 쉽게 이야기 해서, 시퀀스를 미리 준비시키는 것과 같다. 기본 값은 20이다.
- 다음은 시퀀스를 다루는 두 함수를 소개한다.
- seq_name.CURRVAL : 해당 시퀀스에서 생성된 현재 번호를 '확인'한다.
- seq_name.NEXTVAL : 해당 시퀀스에서 다음 번호를 '생성'한다.
- 위 둘은 INSERT, UPDATE 문에서 주로 사용한다.
- 시퀀스 삭제는 간단하다.
DROP SEQUENCE seq_name
- 여기까지 MERGE와 SEQUENCE를 정리하였다.