[오라클, Oracle] DML 문 - INSERT 문

 

DML은 Data Manipulation Language의 약자다.

데이터 조작어로 해석할 수 있다.

DML 문을 사용하면 테이블에 신규 행을 삽입하거나, 테이블의 기존 행을 갱신, 삭제할 수 있다.

 

[INSERT 문]

INSERT 문을 사용하면 테이블에 신규 행을 삽입할 수 있다.

INSERT 문은 삽입할 테이블의 개수에 따라 단일 테이블 INSERT 문과 다중 테이블 INSERT 문으로 구분할 수 있다.

 

[단일 테이블 INSERT 문]

단일 테이블 INSERT 문은 하나의 테이블에 행을 삽입한다.

VALUES 절과 서브 쿼리 방식을 사용할 수 있다.

 

[VALUES 절]

INSERT 문에 VALUES 절을 사용하면 단일 행을 삽입할 수 있다.

 

INSERT

INTO {table | view | subquery} [t_alias] [(column [, column] ...)]

VALUES ({expr | DEFAULT} [, {expr | DEFAULT}] ...)

 

-- 예제를 위해 아래와 같이 테이블을 생성.

deptno 열의 기본값을 20으로 지정했다.

 

DROP TABLE T1 PURGE;

CREATE TABLE T1 (EMPNO NUMBER(4), ENAME VARCHAR2(10), DEPTNO NUMBER(2) DEFAULT 20);

 

아래 쿼리는 T1 테이블에 단일 행을 삽입한다.

INTO 절에 삽입할 열을 지정했고, VALUES 절에 삽입할 값을 기술했다.

 

INSERT INTO T1 (EMPNO, ENAME, DEPTNO) VALUES (7369, 'SMITH', 20);

 

아래는 T1 테이블을 조회한 결과다.

행이 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

아래 쿼리는 INTO 절에 EMPNO 열만 지정했다.

INTO 절에 지정되지 않은 열은 기본값이 있으면 기본값, 기본값이 없으면 널이 삽입된다.

 

INSERT INTO T1 (EMPNO) VALUES (7566);

 

아래 쿼리에서 결과를 확인할 수 있다.

ENAME 열은 널, DEPTNO 열은 기본값인 20이 삽입되었다.

 

SELECT * FROM T1;

 

아래 쿼리는 INTO 절에 열을 지정하지 않았다.

INTO 절에 열을 지정하지 않으면 VALUES 절에 전체 열의 값을 기술해야 한다.

 

INSERT INTO T1 VALUES (7788, 'SCOTT', 20);

 

기본값이 지정된 열은 VALUES 절에 DEFAULT 키워드를 기술할 수 있다.

아래 쿼리는 기본값이 지정된 DEPTNO 열에 DEFAULT 키워드를 기술했다.

 

INSERT INTO T1 VALUES (7876, 'ADAMS', DEFAULT);

 

아래는 T1 테이블을 조회한 결과다.

ADAMS의 DEPTNO 열에 기본값인 20이 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

 

열 개수와 값 개수가 일치하지 않으면 아래와 같이 에러가 발생한다.

 

INSERT INTO T1

VALUES (7902, 'FORD');

 

ORA-00947 : 값의 수가 충분하지 않습니다.

 

INSERT

INTO T1

VALUES (7902, 'FORD', 20, ANALYST);

 

ORA-00913 : 값의 수가 너무 많습니다.

 

예제를 위해 T1 테이블에 JOB 열을 추가하자.

 

ALTER TABLE T1 ADD JOB VARCHAR2(9);

 

아래 쿼리는 에러가 발생한다.

JOB 열이 추가되어 열 개수와 값 개수가 달라졌기 때문이다.

 

INSERT INTO T1 VALUES (7902, 'FORD', 20);

 

ORA-00947 : 값의 수가 충분하지 않습니다.

 

아래 쿼리는 INTO 절에 열을 지정했기 때문에 에러가 발생하지 않는다.

INTO 절에 열을 지정하는 편이 쿼리의 안정성 측면에서 바람직하다.

 

INSERT INTO T1 (EMPNO, ENAME, DEPTNO) VALUES (7902, 'FORD', 20);

 

[서브 쿼리]

INSERT 문에 서브 쿼리를 사용하면 서브 쿼리의 결과를 테이블에 삽입할 수 있다.

서브 쿼리의 결과가 다중 행이면 다중 행이 삽입된다.

 

INSERT

INTO {table | view | subquery} [t_alias] [(column [, column]...)]

subquery

 

아래는 INSERT 문에 서브 쿼리를 사용한 쿼리다.

job이 ANLAYST인 행을 T1 테이블에 삽입했다.

 

INSERT INTO T1 (EMPNO, ENAME)

SELECT EMPNO, ENAME FROM EMP WHERE JOB = 'ANALYST';

 

서브 쿼리에 UNION ALL 연산자를 사용하면 여러 테이블의 행을 삽입할 수도 있다.

 

INSERT INTO T1 (EMPNO, ENAME)

SELECT EMPNO, ENAME FROM EMP WHERE JOB = 'PRESIDENT'

UNION ALL

SEECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO = 10;

 

[다중 테이블 INSERT 문]

다중 테이블 INSERT 문은 다수의 테이블에 행을 삽입한다.

조건의 유무에 따라 무조건 INSERT 문과 조건부 INSERT 문으로 구분할 수 있다.

 

[무조건 INSERT 문]

무조건 (unconditional) INSERT 문은 INTO 절에 지정한 모든 테이블에 서브 쿼리의 결과를 삽입한다.

 

INSERT ALL

{INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)}...

subquery

 

예제를 위해 아래와 같이 테이블을 생성하자.

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

 

CREATE TABLE T1 (EMPNO NUMBER(4), JOB VARCHAR2(9));

CREATE TABLE T2 (EMPNO NUMBER(4), MGR NUMBER(4));

 

아래 쿼리는 서브 쿼리의 결과를 T1, T2 테이블에 삽입한다.

 

INSERT ALL

INTO T1 (EMPNO, JOB) VALUES (EMPNO, JOB)

INTO T2 (EMPNO, MGR) VALUES (EMPNO, MGR)

SELECT * FROM EMP WHERE DEPTNO = 10;

 

아래는 T1, T2 테이블을 조회한 결과다.

T1, T2 테이블에 각각 3행씩 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1;

SELECT * FROM T2;

 

무조건 INSERT 문을 사용하면 PIVOT한 데이터를 삽입할 수 있다.

 

DROP TABLE T1 PURGE;

CREATE TABLE T1 (DEPTNO NUMBER(2), TP VARCHAR2(3), SAL NUMBER(7, 2));

 

아래 쿼리는 INTO 절에 T1 테이블을 4번 기술했다.

서브 쿼리의 1행이 T1 테이블에 4번 삽입된다.

아래와 같은 INSERT 문을 PIVOT INSERT 문으로 부르기도 한다.

 

INSERT ALL

INTO T1 VALUES (DEPTNO, 'MIN', SAL_MIN)

INTO T1 VALUES (DEPTNO, 'MAX', SAL_MAX)

INTO T1 VALUES (DEPTNO, 'SUM', SAL_SUM)

INTO T1 VALUES (DEPTNO, 'AVG', SAL_AVG)

SELECT DEPTNO

, MIN (SAL) AS SAL_MIN, MAX (SAL) AS SAL_MAX

, SUM (SAL) AS SAL_SUM, AVG (SAL) AS SAL_AVG

FROM EMP

GROUP BY DEPTNO;

 

아래는 T1 테이블을 조회한 결과다.

위 쿼리의 SAL_MIN, SAL_MAX, SAL_SUM, SAL_AVG 열이 행으로 PIVOT되어 삽입된 것을 확인할 수 있다.

 

SELECT * FROM T1 ORDER BY 1, 2;

 

아래와 같이 UNPIVOT 절을 사용해도 동일한 결과를 얻을 수 있지만, PIVOT INSERT 문을 사용하는 편이 성능 측면에서 효율적이다.

 

INSERT

INTO T1 (DEPTNO, TP, SAL)

SELECT *

FROM (SELECT DEPTNO

, MIN (SAL) AS SAL_MIN, MAX (SAL) AS SAL_MAX

, SUM (SAL) AS SAL_SUM, AVG (SAL) AS SAL_AVG

FROM EMP

GROUP BY DEPTNO)

UNPIVOT (SAL FOR TP IN (SAL_MIN AS 'MIN', SAL_MAX AS 'MAX'

, SAL_SUM AS 'SUM', SAL_AVG AS 'AVG'));

 

[조건부 INSERT 문]

조건부 (condition) INSERT 문은 서브 쿼리의 결과에서 condition을 만족하는 행을 INTO 절에 지정한 테이블에 삽입한다.

모든 condition을 만족하지 않은 행은 ELSE 절에 지정한 테이블에 삽입되고, ELSE 절이 기술되지 않았다면 무시된다.

 

INSERT [ALL | FIRST]

WHEN condition THEN

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]

[ WHEN condition THEN

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]]

[ELSE

INTO table [(column [, column]...)] [VALUES ({expr | DEFAULT} [, {expr | DEFAULT}]...)]]

subquery

 

조건부 INSERT 문은 ALL 방식과 FIRST 방식을 사용할 수 있다.

 

항목 - 설명

ALL - 조건을 만족하는 모든 테이블에 삽입 (기본값)

FIRST - 조건을 만족하는 첫 번째 테이블에 삽입

 

예제를 위해 아래와 같이 테이블을 생성

DROP TABLE T1 PURGE;

DROP TABLE T2 PURGE;

DROP TABLE T3 PURGE;

 

CREATE TABLE T1 AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE 0 = 1;

CREATE TABLE T2 AS SELECT * FROM T1;

CREATE TABLE T3 AS SELECT * FROM T1;

 

아래는 ALL 방식을 사용한 쿼리다.

조건을 만족하는 모든 테이블에 서브 쿼리의 결과가 삽입된다.

 

INSERT ALL

WHEN SAL >= 2000 THEN INTO T1

WHEN SAL >= 3000 THEN INTO T2

ELSE INTO T3

SELECT EMPNO, ENAME, SAL FROM WHERE DEPTNO = 10;

 

아래는 T1, T2, T3 테이블을 조회한 결과다.

조건에 따라 테이블에 행이 삽입된 것을 확인할 수 있다.

KING은 T1, T2 테이블 모두에 삽입되었다.

 

SELECT * FROM T1;

SELECT * FROM T2;

SELECT * FROM T3;

 

다음 예제를 위해 롤백을 수행하자.

롤백은 DML 문의 변경 내용을 취소한다.

 

ROLLBACK;

 

아래는 FIRST 방식을 사용한 쿼리다.

FIRST 방식은 WHEN 절의 기술 순서에 주의해야 한다.

 

INSERT FIRST

WHEN SAL >= 2000 THEN INTO T1

WHEN SAL >= 3000 THEN INTO T2

ELSE INTO T3

SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;

 

아래는 T1, T2, T3 테이블을 조회한 결과다.

T2 테이블에 행이 삽입되지 않은 것을 확인할 수 있다.

SAL >= 2000 조건을 만족하는 모든 행이 SAL >= 3000 조건을 만족하기 때문이다.

 

SELECT * FROM T1;

SLEECT * FROM T2;

SELECT * FROM T3;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.