오늘은 다중테이블 INSERT와 MERGE, 컬럼의 추가, 수정, 삭제, 그리고 Primary key, Foreign key 제약조건에 대해 배웠다.

다중테이블 INSERT

: 하나의 SOURCE TABLE에서 데이터를 추출해서 여러 개의 TARGET TABLE에 데이터를 로드(INSERT)하는 SQL문이다. 다중테이블 INSERT는 Extraction(추출)하고, Transformation(변형)하고, Loading(적재)하는 ETL에 해당한다.

INSERT INTO hr.sal_history(employee_id, hire_date, salary) 
SELECT employee_id, hire_date, salary
FROM hr.employees;

-- hr.employees 테이블로부터 데이터 추출하여 hr.sal_history에 INSERT

INSERT INTO hr.mgr_history(employee_id, manager_id, salary) 
SELECT employee_id, manager_id, salary
FROM hr.employees;

-- hr.employees 테이블로부터 데이터 추출하여 hr.mgr_history에 INSERT

위와 같은 SQL문이 있다고 가정하자. 위 SQL문은 똑같은 hr.employees 테이블에 두 번이나 액세스해야하는 문제점이 있다. 만약 테이블의 데이터가 많으면 부하가 걸릴 수 있으니 테이블에 여러번 액세스하는 것은 별로 좋은 방법이 아니다. 이 문제점을 해결하기 위해 다중테이블 INSERT를 이용하면 된다.

INSERT ALL

: 한 번에 여러 개의 테이블에 무조건 INSERT를 할 수 있는 형태이다.

INSERT ALL의 기본적인 형태는 다음과 같다.

INSERT ALL
INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
SELECT 
FROM 소스테이블;

INTO 절에는 여러 개의 TARGET TABLE을 나열하고 VALUES 절에는 SOURCE TABLE에서 별칭해준 컬럼이름으로 적어주어야 한다.

예를 들어보자.

INSERT ALL
INTO hr.sal_history(employee_id, hire_date, salary) VALUES(id, day, sal) 
INTO hr.mgr_history(employee_id, manager_id, salary) VALUES(id, mgr, sal)
SELECT employee_id id, hire_date day, manager_id mgr, salary*1.1 sal
FROM hr.employees;

COMMIT;

hr.employees라는 테이블의 employee_id, hire_date, manager_id, salary*1.1를 hr.sal_histroy와 hr.mgr_history 테이블에 INSERT 하라는 뜻이다.

조건 INSERT ALL

: 한 번에 여러 개의 테이블에 조건을 걸어서 INSERT를 할 수 있는 형태이다.

조건 INSERT ALL의 기본적인 형태는 다음과 같다.

INSERT ALL
WHEN 조건1 THEN
    INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
WHEN 조건2 THEN
    INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
SELECT
FROM 소스테이블;

여기서 WHEN 절이 계속되는 것을 OR의 개념이라고 생각하면 된다.

예를 들어보자.

INSERT ALL
WHEN day < to_date('2005-01-01','yyyy-mm-dd') AND sal >= 5000  THEN
    INTO hr.emp_history(employee_id, hire_date, salary) VALUES(id, day, sal)   
WHEN comm IS NOT NULL THEN
    INTO hr.emp_sal(employee_id, commission_pct, salary) VALUES(id, comm, sal)
SELECT employee_id id, hire_date day, salary sal, commission_pct comm
FROM hr.employees;

COMMIT;

hr 유저의 employees 테이블에서 입사 날짜가 2005년 이전이고 연봉이 5000 이상이거나 commission_pct 값이 null이 아닌 데이터의 employee_id, hire_date, salary, commission_pct를 모두 출력하라는 뜻이다.

조건 INSERT FIRST

: 조건을 만족하는 첫 번째 WHEN~THEN의 테이블에만 INSERT를 할 수 있는 형태이다.

조건 INSERT FIRST의 기본적인 형태는 다음과 같다.

INSERT FIRST
WHEN 조건1 THEN
    INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
WHEN 조건2 THEN
    INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
ELSE
    INTO 타겟테이블(소스테이블컬럼, ...) VALUES(소스테이블컬럼별칭, ...)
SELECT
FROM 소스테이블;

예를 들어보자.

INSERT FIRST
WHEN sal < 5000 THEN
    INTO hr.sal_low(employee_id, last_name, salary) VALUES(id, name, sal)
WHEN sal BETWEEN 5000 AND 10000 THEN
    INTO hr.sal_mid(employee_id, last_name, salary) VALUES(id, name, sal)
ELSE
    INTO hr.sal_high(employee_id, last_name, salary) VALUES(id, name, sal)
SELECT employee_id id, last_name name, salary sal
FROM hr.employees;

COMMIT;

hr 유저의 employees 테이블에서 연봉이 5000 미만이면 hr.sal_low 테이블에 INSERT 하고, 연봉이 5000~10000이면 hr.sal_mid 테이블에 INSERT 하고, 그 이상이면 hr.sal_high 테이블에 INSERT 해서 employee_id, last_name, salary를 출력하라는 뜻이다.

MERGE

: INSERT, UPDATE, DELETE 문을 한꺼번에 수행하는 SQL문이다.

기본적인 형태는 다음과 같다.

MERGE INTO 타겟테이블
USING 소스테이블
ON 조인조건술어
WHEN MATCHED THEN -- 키 값이 일치하면 UPDATE or DELETE
UPDATE SET
DELETE WHERE
WHEN NOT MATCHED THEN -- 키 값이 일치하지 않으면 INSERT
INSERT
VALUES

예를 들어보자.

MERGE INTO hr.dw_emp d
USING hr.oltp_emp o
ON (d.employee_id = o.employee_id)
WHEN MATCHED THEN
    UPDATE SET 
        d.salary = o.salary * 1.1
    DELETE WHERE o.flag = 'd'
WHEN NOT MATCHED THEN 
    INSERT(d.employee_id, d.last_name, d.salary, d.department_id)
    VALUES(o.employee_id, o.last_name, o.salary, o.department_id);
    
COMMIT;    

hr.dw_emp 테이블로 hr.oltp_emp 테이블의 데이터를 끌어올건데, 두 테이블은 employee_id로 연결되어야한다. 그리고 employee_id 키 값이 일치하면, d 테이블의 연봉을 o 테이블의 연봉의 10프로만큼 인상하여 수정하거나 o 테이블의 flag 값이 d이면 삭제한다. 그리고 employee_id 키 값이 일치하지 않으면 d 테이블의 employee_id, last_name, salary, department_id 컬럼에 o 테이블의 값을 INSERT 하겠다는 뜻이다.

ADD, MODIFY, DROP

ADD

: 테이블의 컬럼을 추가할 때 사용한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD 컬럼이름 데이터타입;

예를 들어보자.

ALTER TABLE hr.emp ADD job_id varchar2(30);

hr.emp라는 테이블에 job_id라는 컬럼을 추가하는데, 이 컬럼은 최대 30자의 문자컬럼이라는 뜻이다.

MODIFY

: 테이블의 컬럼의 타입을 수정할 때 사용한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 MODIFY 컬럼이름 데이터타입; 

예를 들어보자.

ALTER TABLE hr.emp MODIFY job_id varchar2(20);

hr.emp라는 테이블에 job_id 컬럼을 최대 20자의 문자가 들어갈 수 있도록 수정하라는 뜻이다.

단, 테이블 안에 이미 데이터가 들어있는 경우 타입 변경을 주의해야 한다. 예를 들어 이미 필드값으로 글자의 길이가 30자인 데이터가 들어있는데 위의 예제처럼 varchar2(20), 즉 최대 20자의 문자만 들어갈 수 있게 데이터 타입을 수정한 경우 오류가 발생하게 된다.

DROP

: 테이블의 컬럼을 삭제할 때 사용한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 DROP 컬럼이름; 

예를 들어보자.

ALTER TABLE hr.emp DROP job_id;

hr.emp라는 테이블에서 job_id 컬럼을 삭제하라는 뜻이다.

단, DML 작업을 하면 ROLLBACK 등으로 수정이 가능했지만 컬럼 삭제의 경우에는 절대 복원할 수 없으므로 주의하도록 한다.

제약조건 ★★★

: 테이블의 데이터에 대한 규칙을 만드는 것으로 데이터에 대한 품질을 향상시키기 위해 사용한다.

제약조건 정보 확인

SELECT * FROM user_constraints WHERE table_name = 'EMPLOYEES';

-- employees 테이블의 제약조건 정보를 확인

SELECT * FROM user_cons_columns WHERE table_name = 'EMPLOYEES';

-- employees 테이블의 제약조건이 걸린 컬럼을 확인

인덱스 정보 확인

SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';

-- employees 테이블의 인덱스 정보를 확인

SELECT * FROM user_ind_columns WHERE table_name = 'EMPLOYEES';

-- employees 테이블의 인덱스가 걸린 컬럼을 확인

제약조건 추가

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 제약조건(컬럼)

여기서 제약조건이름은 중복되지 않는 고유한 값이어야 한다.

제약조건 삭제

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 DROP CONSTRAINT 제약조건이름

PRIMARY KEY

: 테이블의 대표키. UNIQUE(유일한 값), NULL 값은 허용할 수 없다. 테이블 당 하나만 생성되고 자동으로 UNIQUE INDEX를 생성한다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼이름);

예를 들어보자.

ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(id);

hr.emp라는 테이블에 emp_id_pk라는 이름의 제약조건을 추가할 건데, 그 제약조건은 PRIMARY KEY 제약조건이고 id라는 컬럼에 걸겠다는 뜻이다.

PRIMARY KEY 제약조건의 경우,

ALTER TABLE hr.emp ADD PRIMARY KEY(id);	-- 추가할 때
ALTER TABLE hr.emp DROP PRIMARY KEY;	-- 삭제할 때

라고 쓸 수도 있다. 그리고 생성할 때 내가 제약조건 이름을 따로 지정하지 않으면 오라클에서 'sys_c숫자'의 형태로 자동으로 제약조건 이름을 만들어준다.

그런데 만약 PRIMARY KEY 제약조건을 사용해서 데이터를 조회할 경우 null 값을 허용하지 않기 때문에 누락되는 데이터가 생길 수 있다. 또한 필터되지 않은 방대한 양의 데이터를 관리할 때, 이를 테면 부서 id가 들어가야하는 곳에 이 회사에 없는 부서의 id가 들어가는 등 데이터 품질을 떨어뜨리는 요인들이 있을 수 있다. 이럴 때 요긴하게 사용할 수 있는 것이 바로 FOREIGN KEY 제약조건이다.

FOREIGN KEY

: 외래키. 참조무결성 제약조건이기도 하다. 동일한 테이블이나 다른 테이블의 PRIMARY KEY 또는 UNIQUE KEY 제약조건을 참조한다. 즉, 참조무결성이란 참조하는 테이블에 존재하지 않는 데이터들은 추가할 수 없다는 뜻이다.

기본적인 형태는 다음과 같다.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름
FOREIGN KEY(컬럼이름) REFERENCES 참조테이블이름(컬럼이름);

FOREIGN KEY 제약조건은 중복값과 NULL 값을 허용한다. 또한 FOREIGN KEY 제약조건에 종속되는 행을 삭제할 수 없다. 참조를 당하는 테이블의 삭제도 불허한다.

예를 들어보자.

ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id);

hr.emp라는 테이블에 emp_dept_id_fk라는 이름의 FOREIGN KEY 제약조건을 추가할 건데, 그 제약조건은 dept_id라는 컬럼에 걸리고 그 컬럼은 hr.dept라는 테이블의 dept_id 컬럼을 참조한다는 뜻이다.

FOREIGN KEY 제약조건은 PRIMARY KEY와 UNIQUE 제약조건만 참조하므로, 테이블을 생성한 뒤 PRIMARY KEY나 UNIQUE 제약조건이 먼저 선언되어야만 FOREIGN KEY 제약조건을 걸 수 있다.

CASCADE

만약 테이블 삭제 시, FOREIGN KEY로 종속되고 있는 테이블이 있어서 삭제가 안될 땐 CASCADE 키워드를 사용하여 FOREIGN KEY 제약조건을 삭제할 수 있다.

예를 들어보자.

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

hr.emp라는 테이블에 걸린 FOREIGN KEY 제약조건을 영구히 삭제할 수 있다는 뜻이다.

CASCADE 키워드는 제약조건이 없는 테이블을 삭제할 때에도 사용할 수 있다. 따라서 테이블에 데이터나 제약조건이 많아서 관리가 어려운 경우 CASCADE를 습관적으로 사용해줘도 전혀 영향을 끼치지 않기 때문에 테이블 삭제 시 유용하게 쓸 수 있다.