[Orcale] 오라클 INSERT 문에 대한 모.든.것. aka DML

오라클 INSERT 문이란,

INSERT 문은 오라클 DML(Data Manipulation Language)에 속하는 문으로 테이블에 새로운 데이터를 추가(저장)하기 위해 사용되는 문이다.

 

크게 INSERT 문에는,

 

- 단일행을 추가하는 '단일행 INSERT 문'

- 복수행을 추가하는 '복수행 INSERT 문'

- 다중 테이블에 다중 데이터를 무조건 넣는 '무조건 INSERT ALL 문'

- 다중 테이블에 조건에 맞는 데이터만 넣는 '조건 INSERT ALL 문'

- '조건 INSERT FIRST문'

 

있다.

 

아래 쿼리의 예시는 오라클에서 제공하는 SCOTT 계정에 있는 데이터를 사용했다. SCOTT을 사용하는 방법은 링크에 정리 되어있다. (loghada.tistory.com/20)

 

---

INSERT 문의 특징

1) INTO 절에서 명시된 컬럼은 VALUES절에서 지정한 컬럼값을 일대일 대응이 되도록 순서대로 입력해야한다.

2) INTO 절의 컬럼명은 생략할 수 있다.

3) 지정되는 데이터의 타입은 컬럼의 데이터 타입과 같아야한다.

4) 데이터의 크기는 지정된 컬럼의 크기보다 작아야 한다.

5) PK와 UNIQUE로 지정된 컬럼을 동일한 값을 갖을 수 없다.

6) INTO 절에서 생략된 컬럼은 자동으로 NULL값이 저장된다.

 

널(NULL) 갑 저장하는 명시적 vs 묵시적 방법

- 묵시적 방법

묵시적 방법은 자동으로 컬럼값에 널(NULL)값을 저장하는 방법으로 INTO 절에서 해당 컬럼을 생략하면 된다. 단, 생략된 컬럼에는 자동으로 널값이 지정되기 때문에 NOT NULL 제약조건이 설정된 컬럼의 경우에는 사용이 불가능하다.

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

- 명시적 방법

명시적 방법은 VALUES 절의 컬럼값에 널(NULL) 값 또는 '' 빈문자열을 직접 지정하는 것이다.

INSERT INTO dept(deptno, dname, loc)
VALUES ( 80, '인사', NULL)

INSERT INTO dept(deptno, dname, loc)
VALUES ( 80, NULL, '경기')

 

INSERT 문 사용시 에러 발생 예

1) INTO 절에 명시된 컬럼의 수와 VALUSE 절에 명시된 컬럼값의 개수가 일치하지 않는 경우 "ORA-00947(값의 수가 충분치 않습니다.)" 에러가 발생한다.

 

2) INTO 절에서 컬럼명을 생략하는 경우에는 반드시 VALUES 절에서 테이블의 모든 컬럼값을 누락하지 않고 순서대로 지정해야한다. 만얀 누락시에는 "ORA-00947(값의 수가 충분치 않습니다.)" 에러가 발생한다.

 

3) INTO 절에 컬럼명의 데이터 타입과 VALUES 절이 컬럼값의 데이터 타입이 일치하지 않으면 에러 "ORA-01722(수치가 부적합합니다.)"가 발생한다.

 

4) VALUES 절의 컬럼값 지정시 반드시 리터럴 형식에 맞춰야한다. 문자/날짜는 ''로 묶어야 하며 수치는 '' 없이 사용한다. 아니면 에러 "ORA-00984(열을 사용할 수 없습니다.)"가 발생한다.

 

INSERT INTO dept(deptno, dname, loc)
VALUES ( 11, '인사');

INSERT INTO dept
VALUES ( 11, '인사');

INSERT INTO dept(deptno, dname, loc)
VALUES ( '개발', 13, '인사');

INSERT INTO dept(deptno, dname, loc)
VALUES ( '개발', 13, 인사);

---

단일행 INSERT 문

INSERT 문은 테이블에 데이터를 저장하기 위한 제이터 조작어이다.

 

기본 Syntax는, 

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

 

복수행 INSERT 문

이전의 단일행 INSERT 문은 하나의 행을 생성했다면 이번 복수행 INSERT 문은 하나의 INSERT 문을 사용하여 여러 행을 저장하는 방법이다.

 

기본 Syntax는,

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

여기서 왜 서브쿼리에 괄호가 생략되었는지 궁금할 수 있다. 쿼리의 Subquery에 괄혹호가 생략되는 이유는 SELECT 절에 들어가는 서브쿼리가 아니기 때문이다.

 

복수행 INSERT 문 예제

INSERT INTO mydept
SELECT deptno, dname, loc
FROM dept;

 

다중행 테이블에 다중행 INSERT 문

한 번의 INSERT시 여러 테이블에 복수 데이터를 저장하는 방법이다.

 

기본 Syntax는,

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

서브쿼리를 실행한 결과가 INTO 절에 서 지정한 테이블 1,2에 자동으로 INSERT 된다. WHEN절은 생략할 수 있으며 생략하면 무조건 INSERT ALL이라고 부르고 WHEN 절이 있으면 조건식이 TRUE 인 경우에만 INSERT 되기 때문에 조건 INSERT ALL이라고 부른다. 

 

무조건 INSERT ALL

아래 쿼리의 목표는 여러 번의 SELECT 문이나 INSERT 문을 사용하지 않고도 INSERT ALL을 사용하여 한번의 INSERT 문을 사용하여 다중 테이블에 다중 데이터를 저장하는것을 확인하기 위함이다. CTAS 문으로 테이블의 구조만 생성한 myemp_hire, myemp_mgr 테이블에 하나의 쿼리만으로 기존 emp 테이블에 있는 데이터를 옮기는것을 확인할 수 있다.

 

-- myemp_hire 테이블 생성
CREATE TABLE myemp_hire
AS
SELECT empno, ename, hiredate, sal
FROM emp
WHERE 1=2;

-- myemp_mgr 테이블 생성
CREATE TABLE myemp_mgr
AS
SELECT empno,ename, mgr
FROM emp
WHERE 1=2;

-- 테이블 구조 확인
SELECT *
FROM myemp_hire;
SELECT * 
FROM myemp_mgr;

-- INSERT ALL 문으로 다중 데이블에 다중 데이터 삽입
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; 

-- 테이블 확인
SELECT *
FROM myemp_hire;
SELECT * 
FROM myemp_mgr;

 

조건 INSERT ALL

아래의 쿼리는 무조건 INSERT ALL문에서 WHEN 절을 추가하여, 특정 조건에 맞는 데이터만 테이블에 넣기 위한 쿼리이다. 위와 동일한 방법으로, CTAS 문으로 테이블의 구조만 생성한 myemp_hire2, myemp_mgr2 테이블에 INSERT ALL문으로 데이터를 넣는다. 이때 특정정 데이터만 담기 위한 조건식으로 WHEN 절을 사용한다. 

 

-- myemp_hire2 테이블 생성
CREATE TABLE myemp_hire2
AS
SELECT empno, ename, hiredate, sal 
FROM emp
WHERE 1=2;

-- myemp_mgr2 테이블 생성
CREATE TABLE myemp_mgr2
AS 
SELECT empno, ename, mgr
FROM emp
WHERE 1=2;

-- 조건 INSERT_ALL 문
INSERT ALL 
  WHEN sal > 3000 THEN
    INTO myemp_hire2 VALUES (empno, ename, hiredate, sal)
  WHEN mgr = 7698 THEN
    INTO myemp_mgr2 VALUES (empno, ename, mgr)
SELECT empno, ename, hiredate, sal, mgr
FROM emp;

-- 테이블 확인
SELECT *
FROM myemp_mgr2; 
SELECT *
FROM myemp_hire2;

 

INSERT FIRST 문

'조건 INSERT FIRST문'은 WHEN 절에 지정된 조건이 중복되는 경우 처음 조건에 일치하는 테이블만 저장되고 이후에는 조건이 일치해도 테이블에 저장되지 않는 방법이다.

 

INSERT FIRST
  WHEN sal = 800 THEN
    INTO table1 VALUES ( empno, ename, hiredate, sal)
  WHEN sal < 2500 THEN
    INTO table2 VALUES ( empno, ename, mgr)
  SELECT empno, ename, hiredate, sal, mgr
FROM emp;

위의 쿼리를 보면 월급이 800이면 첫번째 조건에도 만족하고 두번째 조건에도 만족한다. 만약 동일한 쿼리에서 INSERT ALL이었다면 table1, table2 모두 '월급 = 800' 인 사람들이 들어갔을것이다. 하지만, INSERT FIRST 에서는 첫번째 조건에 만족한 table1에는 정보가 들어가지만, 두번째 table2에는 정보가 들어가지 않는다.