INSERT와 조회 결과 입력
INSERT SELECT와 WITH CHECK OPTION, DEFAULT 및 INSERT ALL과 FIRST를 이용한 다중 테이블 입력
#핵심 요약
DML(Data Manipulation Language, 데이터 조작 언어)은 INSERT, UPDATE, DELETE, MERGE임
INSERT INTO 테이블(컬럼,...) VALUES (값,...)
문자는 작은따옴표로 감싸고, 날짜는 TO_DATE로 씀
INSERT INTO 테이블 SELECT...는 조회 결과를 한꺼번에 넣음
백업 테이블로 복구할 때 썼음
INSERT INTO (SELECT ... WHERE 조건 WITH CHECK OPTION) VALUES (...)는 조건에 맞는 값만 입력을 허용함
INSERT ALL은 한 번의 조회로 여러 테이블에 넣고, INSERT FIRST는 조건 중 처음 맞는 하나에만 넣음
#개념과 동작 원리
| INSERT 구문 | 입력 방식 |
|---|---|
INSERT INTO t(c1, c2) VALUES (v1, v2) |
한 행 입력 |
INSERT INTO t VALUES (...) |
모든 컬럼에 순서대로 입력 |
INSERT INTO t SELECT * FROM s |
조회 결과 입력 |
INSERT INTO (서브쿼리 WITH CHECK OPTION) VALUES (...) |
서브쿼리 조건을 벗어나는 값 입력 거부 |
INSERT ALL INTO t1 ... INTO t2 ... SELECT... |
여러 테이블 동시 입력 |
INSERT ALL WHEN 조건 THEN INTO... |
조건별 입력(맞는 것 모두) |
INSERT FIRST WHEN 조건 THEN INTO... |
조건별 입력(처음 맞는 것 하나) |
INSERT ALL INTO t VALUES (..., 'monday') INTO t VALUES (..., 'tuesday') SELECT... |
PIVOTING INSERT 가로 열을 세로 행으로 |
다중 테이블 INSERT는 소스 조회의 각 행마다 조건을 평가하여 대상 테이블에 입력함
#주요 용어와 구성 요소
| 구문 | 의미 |
|---|---|
| 대상 열 목록 | 값이나 소스 SELECT 열과 개수, 순서, 타입이 대응해야 함 |
| DEFAULT | 열 기본값을 사용하며 기본값 없는 열은 NULL이 되므로 NOT NULL 조건을 확인함 |
#필수 명령어 또는 SQL
| 항목 | 실행 조건 |
|---|---|
| 작업 대상 | emp, dept, 백업, 요일별 적재 대상과 소스 테이블 |
| 필요 권한 | INSERT 대상 권한과 소스 조회 권한, 테이블 생성 시 CREATE TABLE과 저장 공간 할당 조건 |
| 바꿀 입력값 | 테이블, 열 목록, &empno, &ename, &sal, WHEN 조건과 요일 값 |
| 결과 해석 | 실제 입력 건수와 연습 문제 답은 기재 없음 |
#기본 INSERT
INSERT INTO emp (empno, ename, sal, job, hiredate)
VALUES (2812, 'JACK', 3500, 'ANALYST', TO_DATE('2019/06/05', 'RRRR/MM/DD'));
INSERT INTO dept
VALUES (50, 'HR', 'SEOUL');
COMMIT;결과 해석:
실습에서는 이름이 NULL인 행과 공백 세 칸인 행을 넣은 뒤 "이름이 NULL이 아니고 공백도 아닌 사원"을 찾는 문제를 냈음(답 없음)
공백 세 칸은 NULL이 아니므로 IS NOT NULL만으로는 걸러지지 않고 TRIM(ename) IS NOT NULL 같은 조건이 필요함(Oracle의 길이 0 문자열 NULL 처리와 문자 함수에서 정리)
#조회 결과 입력
CREATE TABLE emp_backup2
AS
SELECT * FROM emp;
INSERT INTO emp
SELECT * FROM emp_backup2;
COMMIT;결과 해석:
실습에서는 그 사이에 TRUNCATE TABLE emp로 전부 지운 뒤 ROLLBACK을 시도하고, 위 INSERT SELECT로 복구했음TRUNCATE는 ROLLBACK으로 되돌릴 수 없어 백업 테이블에서 다시 넣은 것임
빈 구조만 복사하기:
CREATE TABLE emp_backup7
AS
SELECT * FROM emp WHERE 1 = 2;
INSERT INTO emp_backup7
SELECT * FROM emp;결과 해석: WHERE 1 = 2는 항상 거짓이라 구조만 만들어지고 행은 없음
#치환 변수와 WITH CHECK OPTION
INSERT INTO (SELECT empno, ename, sal
FROM emp
WHERE sal <= 9000
WITH CHECK OPTION)
VALUES (&empno, '&ename', &sal);결과 해석:
설명: 월급이 9000 이하인 데이터만 입력을 허용함
치환 변수로 반복 입력을 편하게 함
문제: 부서번호를 10, 20, 30만 허용하도록 같은 방식으로 작성하기
#DEFAULT 값
CREATE TABLE emp607
( empno NUMBER(4) NOT NULL,
ename VARCHAR2(20),
job VARCHAR2(20),
mgr NUMBER(4) DEFAULT 7788,
hiredate DATE,
sal NUMBER(7,2) );
INSERT INTO emp607 (empno, ename, sal, mgr)
VALUES (3929, 'allen', 4000, DEFAULT);
ALTER TABLE emp607 MODIFY job DEFAULT 'SALESMAN';
UPDATE emp607 SET job = DEFAULT WHERE ename = 'allen';결과 해석: DEFAULT 키워드를 쓰면 컬럼에 정의된 기본값(7788, 'SALESMAN')이 들어감
#INSERT ALL, INSERT FIRST
다중 테이블 입력 실습의 SQL은 다음과 같음
INSERT FIRST
WHEN sal >= 2500 THEN INTO emp_dept_10 VALUES (empno, ename, sal, deptno)
WHEN deptno = 20 THEN INTO emp_dept_20 VALUES (empno, ename, sal, deptno)
WHEN deptno = 30 THEN INTO emp_dept_30 VALUES (empno, ename, sal, deptno)
SELECT empno, ename, sal, deptno
FROM emp;결과 해석: 연습 문제2의 요구(월급 2500 이상은 모두 10번 테이블, 나머지 중 20번 부서는 20번 테이블, 30번 부서는 30번 테이블)임INSERT ALL이었다면 월급 2500 이상인 20번 부서 사원은 두 테이블에 모두 들어감
PIVOTING INSERT: 요일별 판매 열(sales_mon, sales_tue,...)을 가진 원천 테이블을 요일을 행으로 가진 sales_info로 바꿔 넣음
INSERT ALL
INTO sales_info VALUES (empno, ename, sales_mon, 'monday')
INTO sales_info VALUES (empno, ename, sales_tue, 'tuesday')
SELECT empno, ename, sales_mon, sales_tue
FROM sales_source_data;결과 해석:
예제는 월요일과 화요일의 판매 열을 행으로 나누는 구조임
실행 결과 확인 불가
#구문과 옵션 설명
| 구문 | 의미 |
|---|---|
| ALL | 참인 WHEN의 INTO를 모두 수행함 |
| FIRST | 작성 순서대로 처음 참인 WHEN만 수행함 |
| ELSE | 모든 WHEN이 거짓일 때 수행하며 생략하면 그 소스 행은 입력하지 않음 |
| WITH CHECK OPTION | 입력 후에도 서브쿼리 조건으로 조회되는 행만 허용함 |
#사용 조건과 주의사항
계정과 권한은 공통 사용자와 로컬 사용자, DB 링크는 데이터베이스 링크에 있음
다중 테이블 INSERT의 타깃은 테이블이며 뷰, 구체화 뷰, 원격 테이블에는 사용할 수 없음
기본값, NOT NULL, PK, FK 등 대상 제약과 INSERT SELECT의 열 대응을 먼저 확인함
본문의 COMMIT은 변경을 확정하므로 입력 대상과 중복 여부를 확인하는 단계와 구분함