[Oracle SQL] DML - INSERT문

앞서 배운 SELECT문은 DQL(Data Query Language), 질의어로서 데이터를 검색할 때 사용하는 SQL문이었습니다. 이번에는 실제 데이터를 입력(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)할 수 있는 DML(Data Manipulation Language), 조작어에 대해 알아보겠습니다. 먼저 데이터를 입력하기 위한 INSERT문입니다. 

 

 

INSERT 문

 

1. 단일행 INSERT 문

 

한 번에 하나의 행을 입력합니다.

INSERT INTO 테이블명 [(컬럼명, 컬럼명2,...)]
VALUES (값, 값2,...);

INTO절에 명시한 컬럼의 순서대로 VALUES절에 컬럼값을 지정해야 합니다. INTO절의 컬럼명은 생략할 수 있으며, 생략하면 VALUES절에 모든 컬럼값을 순서에 맞게 지정해야 합니다. 

 

다음과 같이 dept 테이블에 새로운 부서 정보를 입력하는 SQL문을 작성해 볼 수 있습니다.

INSERT INTO dept VALUES (50,'영업','서울');

SELECT문을 사용하여 변경된 데이터를 확인할 수 있습니다.

 

그런데 여기서 주의해야 할 점은, 이렇게 변경된 데이터는 임시적이며 실제 DB에는 아직 저장되지 않았다는 것입니다. 처음 INSERT문을 실행한 순간 트랜잭션(Transaction)이 시작되어 종료되지 않았기 때문입니다.

 

* 트랜잭션(Transaction)

트랜잭션은 데이터베이스의 논리적인 작업 단위로서, 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미합니다. DML 명령이 실행되면 트랜잭션이 자동으로 시작되며, 트랜잭션을 종료시키기 전까지의 모든 DML문은 하나의 트랜잭션으로 인식됩니다. 트랜잭션이 종료되기 전까지 변경된 데이터는 임시적이므로 다른 사용자가 변경된 데이터를 확인할 수 없고, 변경된 행은 내부적으로 잠금(Lock)이 걸리게 되어 다른 사용자가 해당 행을 변경할 수 없습니다. COMMIT(반영) 혹은 ROLLBACK(복구)라는 TCL(Transaction Control Language) 명령을 실행해야 트랜잭션이 종료됩니다.  

 

dept 테이블의 원본 데이터를 유지하기 위해 변경 내용을 복구시키겠습니다. 원래대로 돌아간 것을 확인할 수 있습니다.

ROLLBACK;

 

* null값의 저장

null값을 저장하는 방법에는 두 가지가 있습니다. 

 

(1) 묵시적 방법

INTO절에 컬럼명을 생략하면 해당 컬럼값에 자동으로 null값이 입력됩니다.

INSERT INTO dept (deptno,dname) VALUES (1,'개발');

 

(2) 명시적 방법

null 혹은 ''(빈문자열)을 사용하면 null값이 입력됩니다.

INSERT INTO dept (deptno,dname,loc) VALUES (70,'인사',NULL);
INSERT INTO dept (deptno,dname,loc) VALUES (71,'인사','');

 

 

 

2. 복수행 INSERT 문

 

VALUES절 대신 서브쿼리를 이용하면, 기존 테이블의 데이터를 가져와서 하나의 INSERT문을 사용하여 여러 행을 저장할 수 있습니다. 이때 INTO절의 컬럼의 개수와 데이터 타입이 서브쿼리가 실행된 결과와 동일해야 합니다.

INSERT INTO 테이블명 [(컬럼명, 컬럼명2,...)]
Subquery;

 

실습을 위해 테이블 하나를 생성해 보겠습니다. 기존에 존재하는 테이블을 이용하여 새로운 테이블을 생성하는 방법은 다음과 같이 서브쿼리를 사용하며, CTAS(씨탁스)라고 부릅니다. 테이블을 생성하는 자세한 방법은 추후 다루어집니다.

CREATE TABLE 테이블명 [(컬럼명, 컬럼명2,...)]
AS
Subquery;

테이블의 구조만 복사하기 위해서 서브쿼리의 WHERE절의 조건이 false가 되도록 할 수 있습니다.

CREATE TABLE mydept
AS
SELECT * FROM dept
WHERE 1=2;

데이터 없이 구조만 복사된 것을 확인할 수 있습니다. dept 테이블의 deptno, dname 컬럼 데이터를 가져오는 서브쿼리를 이용해, mydept 테이블의 deptno, dname 컬럼에 입력하겠습니다. 

INSERT INTO mydept (deptno,dname)
SELECT deptno,dname
FROM dept;

SQL문을 실행하면 4개 행이 삽입되었다는 메세지가 뜹니다. 한 번에 여러 행이 입력된 것을 확인할 수 있습니다.

 

서브쿼리를 실행하지 않고 데이터를 직접 지정하여 복수개의 INSERT문을 작성하고 싶으면, 다음과 같이 무의미한 서브쿼리를 이용해 직접 지정한 데이터로 INSERT가 실행되도록 합니다.

INSERT ALL
INTO mydept (deptno, dname, loc) VALUES (1, '영업', '서울')
INTO mydept (deptno, dname, loc) VALUES (2, '개발', '경기')
SELECT * FROM dual;

 

 

 

3. 다중 테이블 다중행 INSERT 문

 

앞에서는 하나의 테이블에 하나의 행을 저장하거나 여러 행을 저장하는 방법을 알아보았습니다. 이번에는 여러 테이블에 여러 행을 저장하는 방법을 알아보겠습니다. 

INSERT ALL
  [WHEN 조건식1 THEN]
    INTO 테이블1 VALUES (컬럼명, 컬럼명2,..., 컬럼명n)
  [WHEN 조건식2 THEN]
    INTO 테이블2 VALUES (컬럼명, 컬럼명2,..., 컬럼명n)
Subquery;

조건식을 사용하여 서브쿼리가 실행된 결과가 조건에 해당할 경우만 해당 테이블에 입력되도록 할 수도 있고, 조건식을 생략해서 서브쿼리가 실행된 결과가 해당 테이블에 모두 입력되도록 할 수도 있습니다. 

 

실습을 위해 emp테이블에서 4개의 컬럼으로 구성된 myemp_hire 테이블과 3개의 컬럼으로 구성된 myemp_mgr 테이블을 생성해 보겠습니다.

CREATE TABLE myemp_hire
AS
SELECT empno,ename,hiredate,sal
FROM emp
WHERE 1=2;

CREATE TABLE myemp_mgr
AS
SELECT empno,ename,mgr
FROM emp
WHERE 1=2;

 

두 테이블에 여러 행을 한 번에 입력하기 위해 서브쿼리를 사용해 emp 테이블의 데이터를 가져올 수 있습니다. 각 12개 행씩 총 24개 행이 삽입된 것을 확인할 수 있습니다. 

INSERT ALL
  INTO myemp_hire VALUES (empno,ename,hiredate,sal)
  INTO myemp_mgr VALUES (empno,ename,mgr)
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

 

위의 작업을 복구(ROLLBACK)한 후, 이번에는 조건식을 사용해 데이터를 입력해 보겠습니다. 월급이 2000 보다 큰 사원만 myemp_hire 테이블에 입력하고, 관리자 사번이 7698인 사원만 myemp_mgr 테이블에 입력하는 SQL문입니다. 각 조건에 맞는 사원만 입력되어 총 10개 행이 삽입된 것을 확인할 수 있습니다.

INSERT ALL
  WHEN sal>2000 THEN
    INTO myemp_hire VALUES (empno,ename,hiredate,sal)
  WHEN mgr=7698 THEN
    INTO myemp_mgr VALUES (empno,ename,mgr)
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

 

마찬가지로 서브쿼리 실행 결과 없이 직접 지정한 데이터를 여러번 INSERT하고 싶으면 다음과 같이 무의미한 서브쿼리를 이용해 작성하면 됩니다.

INSERT ALL
  INTO myemp_hire VALUES (1,'홍길동',sysdate,0)
  INTO myemp_mgr VALUES (2,'이순신',7698)
SELECT * FROM dual;

 

 

다음 글에서 계속하여 DML을 알아보겠습니다.