[Oracle] INSERT

데이터 조작어(DML: Data Manipulation Language)

: 테이블에 데이터를 입력하거나 기존 데이터를 수정 또는 삭제하는 명령어

 

종류

• INSERT : 데이터 입력
  • 단일 행 입력 : 한번에 하나의 행을 입력
  • 다중 행 입력 : 서브 쿼리를 이용하여 한번에 여러 행을 입력
• UPDATE : 기존 데이터 수정
• DELETE : 기존 데이터 삭제
• MERGE : 두개의 테이블을 하나의 테이블로 병합

 

단일 행 입력

INSERT INTO table [ (column1, column2 ...) ]
VALUES (value [, value, ...]);

• INTO 절에 명시한 칼럼에 VALUES 값들을 넣는다.
• INTO 절에 칼럼을 명시하지 않으면 테이블 생성시 정의한 칼럼 순서로 입력된다.
• 입력되는 데이터 타입은 동일해야한다.
• CHAR, VARCHAR2, DATE 타입의 입력 데이터는 단일 인용부호 ('')로 입력한다.

 

EX) 학생 테이블에 '홍길동' 학생의 데이터를 입력

INSERT INTO student
VALUES (10110, '홍길동', 'hong', '1', '8501011143098',
        '85/01/01', '055)777-7777',
        170, 70, 202, 9903);

 

NULL 입력

• 묵시적인 방법: INSERT INTO 절에 해당 칼럼 이름과 값을 생략 (예외로, NOT NULL 제약조건이 지정된 경우 불가능)
• 명시적인 방법: VALUES 절의 칼럼 값에 NULL, '' 사용

 

명시적인 NULL 입력

INSERT INTO department
VALUES (301, '환경보건학과', NULL, NULL);

또는

INSERT INTO department
VALUES(301, '환경보건학과', '', '');

 

묵시적인 NULL 입력

INSERT INTO department (deptno, dname)
VALUES (301, '환경보건학과');

 

날짜 데이터 입력

필요에 따라서 TO_DATE 함수를 사용한다. ( TO_DATE('2005/01/01', 'YYYY/MM/DD') )

 

EX) 형식을 이용한 입력

-- 입사일 2005년 1월 1일로 입력

-- (1) 퍼스널 오라클의 기본 날짜 형식인 'YY/MM/DD' 이용
INSERT INTO professor (profno, name, position, hiredate, deptno)
VALUES (9920, '최윤식', '조교수', '05/01/01', 301);

-- (2) 'YYYY/MM/DD' 형식을 사용
INSERT INTO professor (profno, name, position, hiredate, deptno)
VALUES (9920, '최윤식', '조교수', TO_DATE('2005/01/01', 'YYYY/MM/DD'), 301);

 

EX) SYSDATE 함수로 현재 날짜를 입력

INSERT INTO professor
VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 301);

 

다중행 입력

• INSERT 명령문에서 서브쿼리 절을 이용
• 한번에 여러 행을 동시에 입력

 

(1) 서브 쿼리를 이용한 다중 행 입력

INSERT INTO table [ (column1, column2, ...) ]
서브쿼리 작성

• 서브쿼리를 이용하여 자신이나 다른 테이블에 데이터를 복사하여 여러 행을 동시 입력
• 서브쿼리에서 검색된 결과 집합을 한꺼번에 입력

 

EX)

INSERT INTO professor
SELECT *
FROM professor
WHERE profno > 9905; -- 9905 이상을 professor 테이블에 넣는다.
-- 이때 데이터 입력시 제약 조건에 위배되는경우 입력 오류가 발생한다.

INSERT INTO salgrade
SELECT *
FROM salgrade
WHERE grade=3;

 

(2) INSERT ALL 명령문 (Unconditional INSERT ALL)

INSERT ALL|FIRST
INTO [table1] VALUES [ (column1, column2, ...) ]
INTO [table2] VALUES [ (column1, column2, ...) ]
...
subquery;

• 서브쿼리의 결과 집합을 조건 없이 여러 테이블에 동시 입력
• 서브쿼리의 칼럼 이름과 입력되는 테이블의 칼럼 이름이 동일해야 함.
• ALL : 서브쿼리의 결과 집합을 INSERT 절에 모두 입력
• FIRST : 서브쿼리의 결과 집합을 해당하는 첫번쨰 INSERT절에 입력

EX) 2학년 이상의 학생을 height_info 테이블에는 키 weight_info에는 몸무게를 입력

INSERT ALL
INTO height_info VALUES (studno, name, height)
INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight
FROM student
WHERE grade >= '2';

 

(3) Conditional INSERT ALL 명령문

INSERT ALL
WHEN 조건절1 THEN
    INTO table1 VALUES (column1, column2, column3, ...)
WHEN 조건절2 THEN
    INTO table2 VALUES (column1, column2, column3, ...)
ELSE
    INTO talbe3 VALUES (column1, column2, column3, ...)
subquery;

• 서브쿼리의 결과 집합에 대해 WHEN 조건절을 지정하여 만족하는 행을 해당하는 테이블에 각각입력
• 서브쿼리의 결과를 만족하는 조건 행이 여러개라면 해당 테이블에 모두 입력합니다.
• ALL : WHEN~THEN~ELSE의 조건을 만족하는 테이블 모두에 입력
• WHEN 조건절 THEN : 서브쿼리의 결과 집합에 대한 조건
• 조건절을 만족하는 곳에 데이터를 삽입하는데 어느 조건절도 만족하지 않는 행은 table3에 입력

 

EX)

-- 2학년 이상의 학생을 검색하여 height_info 테이블에는 키가 170 보다 큰 학생의 키
-- weight_info 테이블에는 몸무게가 70보다 큰 학생의 몸무게를 각각 입력
INSERT ALL
WHEN height > 170 THEN
     INTO height_info VALUES (sutdno, name, height)
WHEN weight > 70 THEN
     INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight
FROM student
WHERE grade >= '2';

 

(4) Conditional-First INSERT 명령문

INSERT FIRST
WHEN 조건절1 THEN
    INTO table1 VALUES (column1, column2, ...)
WHEN 조건절2 THEN
    INTO table2 VALUES (column1, column2, ...)
ELSE
    INTO table3 VALUES (column1, column2, ...)
subquery;

• 조건을 만족하는 첫번째 WHEN 절에서 지정한 테이블에만 입력한다. 나머지 WHEN 절은 안들어감.

EX)

-- 2학년 이상의 학생을 검색하여 height_info 테이블에는 키가 170보다 큰 학생의 키
-- weight_info 테이블에는 몸무게가 70보다 큰 학생의 몸무게를 각각 입력한다.
-- 단, 키가 170보다 크고, 몸무게가 70보다 큰 학생은 height_info 테이블에만 입력한다.
INSERT FIRST
WHEN height > 170 THEN
     INTO height_info VALUES (studno, name, height)
WHEN weight > 70 THEN
     INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight
FROM student
WHERE grade >= '2';

 

(5) PIVOTING INSERT

• 하나의 행을 여러개의 행으로 나누어서 입력하는 기능

 

아래의 사진처럼 4주차(WEEK_NO)에 월~금의 판매량 5주차(WEEK_NO)에 월~금의 판매량이 있다.

PIVOTING INSERT 명령문을 사용하여 SALES 테이블의 요일별 데이터를 통합하여 하나의 행으로 입력

즉, 아래의 상태로 만들어 보자.

 

CREATE TABLE sales_data(
sale_no NUMBER(4),
week_no NUMBER(2),
day_no NUMBER(2),
sales NUMBER(7,2)
);

INSERT ALL
INTO sales_data VALUES (sales_no, week_no, '1', sales_mon)
INTO sales_data VALUES (sales_no, week_no, '2', sales_tue)
INTO sales_data VALUES (sales_no, week_no, '3', sales_wed)
INTO sales_data VALUES (sales_no, week_no, '4', sales_thu)
INTO sales_data VALUES (sales_no, week_no, '5', sales_fri)
SELECT sales_no, week_no, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
FROM sales;

SELECT * FROM sales_data ORDER BY sale_no, day_no;