오라클 데이터 조작어(DML, Data Manipulation Language)란, INSERT,단일행, 멀티행 INSERT
DML소개
DML명령문은 오라클에서 데이터를 저장하는 논리적인 단위인 테이블의 데이터에 접근해서 데이터를 조작하는 명령문 입니다.
종류
SELECT : 테이블의 데이터에 대해 접근 후 추출만 가능
INSERT : 테이블에 행을 삽입하기 위한 명령어
UPDATE : 테이블의 데이터를 수정하기 위한 명령어
DELETE : 테이블의 데이터를 삭제하기 위한 명령어
MERGE : 두 테이블의 데이터를 하나의 테이블로 병합하기 위한 명령어
SELECT를 제외한 명령어들은 실행 후 오라클 DB에 영구히 반영하려면 반드시 COMMIT을 해야 하고 작업을 취소 하려면 ROLLBACK을 해야 합니다.
유용한 경우
데이터베이스를 구축 하는 이유는 많은 양의 데이터를 저장(입력/수정/삭제)하고 이를 빠른 시간내에 검색하기 위해서 입니다. 데이터 조작어(DML)은 DB의 논리적인 저장공간인 테이블(Table)에 값을 입력/수정.삭제하는 명령어 이므로 데이터베이스 조작을 위해 반드시 알아두어야 하는 명령 입니다.
8.1. INSERT 명령어
테이블에 데이터를 입력하기 위한 명령어로 테이블에 데이터를 입력하기 위해서는 사용자 소유의 테이블이거나 테이블에 대해 INSERT 권한이 있어야 한다.
8.1.1. 단일행 입력(Single Row Insert)
[기본 형식]
INSERT INTO table_name [(column_name1, column_name2,,, )] VALUES (value1, value2,,,) |
- 문자데이터 및 날짜 데이터는 VALUES 구에서 단일 따옴표(‘)로 싸야 합니다.
- 테이블의 모든 컬럼에 대해 데이터를 삽입하는 경우에는 INTO 테이블이름 다음에 컬럼을 기술하지 않아도 되고 VALUES절에서 테이블에 정의된 컬럼의 순서대로 값을 기술하면 됩니다. (이 경우 추후 테이블에 컬럼이 추가 된다면 오류 발생 합니다)
- 테이블의 일부 컬럼에만 값을 입력하는 경우에는 INTO 테이블이름 다음에 반드시 컬럼을 기술해야 합니다.
- 컬럼에 NULL을 입력하는 방법은, INSERT INTO 테이블명 다음에 컬럼명 나열 시 NULL을 입력 할 컬럼을 기술하지 않으면 묵시적으로 NULL이 입력되고 명시적으로 VALUES절에서 NULL 또는 ‘’을 입력해 주면 됩니다.
- VALUES 절에는 현재 사용자 계정을 나타내는 USER, 컬럼의 기본값을 나타내는 DEFAULT와 SYSDATE와 같은 함수 사용 가능 합니다.
- 날짜 데이터는 날짜데이터로 변환해서 입력하거나 문자열을 입력한다면 해당 시스템의 NLS_DATE_FORMAT에 맞추어 입력해야 합니다.
실습을 위해 t1 이라는 이름으로 테이블을 하나 생성 합니다. 혹시 이미 생성되어 있다면 DROP TABLE t1 명령으로 먼저 삭제 후 생성 하세요. |
CREATE TABLE t1 ( a NUMBER, b VARCHAR2(10), c DATE NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY(a), ❶ CONSTRAINT ck_t1_b CHECK (b IN ('oracle','java')) ❷ ); |
<실행결과>
Table T1이(가) 생성되었습니다.
❶에서는 t1 pk_t1 이라는 이름으로 테이블의 PRIMARY KEY 제약조건을 설정 했는데요, 주키(Primaey Key) 컬럼으로 a 를 지정 했습니다. 오라클은 PK 컬럼으로 지정하면 그 컬럼으로 별도의 물리적 영역에 인덱스를 생성 합니다.
❷에서는 ck_t1_b 라는 이름으로 CHECK 제약조건을 생성 했습니다. b 컬럼에는 “oracle”, “java”의 두가지의 문자열만 받을 수 있도록 지정 했습니다.
t1 테이블은 a, b, c 3개의 컬럼을 가지며 pk_t1 이라는 Primary Key 제약조건, ck_t1_b 라는 Check 제약조건을 가집니다.
오라클에서는 Primary Key도 제약조건으로 해당 컬럼은 NOT NULL, Unique Key 제약 조건을 가지며 별도의 영역에 인덱스도 생성이 된다는 것 기억해 주세요. 그러므로 테이블 생성시 “a NUMBER NOT NULL” 처럼 특별히 NOT NULL 이라고 기술을 하지 않아도 됩니다.
a 컬럼이 Pri,ary Key 컬럼으로 지정 되었으니 당연히 중복된 값은 들어 갈 수 없습니다. |
INSERT INTO T1 VALUES (1, 'oracle', ❶SYSDATE) |
<실행결과>
1 행 이(가) 삽입되었습니다.
INSERT INTO T1 VALUES (1, 'oracle', SYSDATE) |
<실행결과>
ORA-00001: 무결성 제약 조건(SCOTT.PK_T1)에 위배됩니다
오라클에서 DATE형 컬럼에 컬럼에 현재날짜, 시간을 저장하기 위해서는 ❶SYSDATE 함수를 주로 사용 합니다. 즉SYSDATE 함수는 현재 날짜와 시간을 오라클 서버 기준으로 얻어 오는 함수로 년월일 시분초 까지 표현이 가능 합니다. (참고로 오늘은 2021년 3월3일 입니다.)
INSERT 구문을 실행 후 오라클 DB에 영구히 저장을 위해서는 COMMIT 명령을 실행해야 합니다. 이 부분에 대한 설명은 트랜잭션과 관련된 내용으로 이 장의 끝부분에 나와 있으니 해당 내용을 참조 하세요. |
COMMIT |
<실행결과>
커밋 완료.
SELECT * FROM t1 |
<실행결과>
A | B | C | |
1 | 1 | oracle | 21/03/03 |
a 컬럼은 Primary Key 컬럼이므로 NULL 값은 입력이 불가능 합니다,. |
INSERT INTO t1 VALUES (NULL, 'java', SYSDATE) |
<실행결과>
ORA-01400: NULL을 ("SCOTT"."T1"."A") 안에 삽입할 수 없습니다.
c 컬럼은 DATE 형식으로 NOT NULL 제약조건이 정의되어 있으므로 NULL이 아닌 값이 반드시 값이 입력 되어야 합니다. INTO t1 다음에 컬럼을 기술하지 않으면 테이블의 모든 컬럼에 값을 기술하겠다는 의미로 VALUES구 다음의 값의 수는 컬럼의 개수와 일치해야 하며 형식도 맞아야 합니다. |
INSERT INTO t1 VALUES (30, 'java', NULL) |
<실행결과>
ORA-01400: NULL을 ("SCOTT"."T1"."C") 안에 삽입할 수 없습니다
INSERT INTO t1(a, b) VALUES (30, 'oracle'); |
<실행결과>
ORA-01400: NULL을 ("SCOTT"."T1"."C") 안에 삽입할 수 없습니다
b 컬럼은 CHECK 제약조건이 걸려 있어 ‘oracle’, ‘java’ 값만 입력이 가능 합니다. |
INSERT INTO t1 VALUES (30, 'c#', SYSDATE) |
<실행결과>
ORA-02290: 체크 제약조건(SCOTT.CK_T1_B)이 위배되었습니다
c 컬럼이 DATE형이지만 NLS_DATE_FORMAT에 맞게 문자열을 입력하면 잘 입력 됩니다. NLS_DATE_FORMAT은 현재 시스템의 기본 날짜 입출력 형태를 지정하는 파라미터 이며 TO_CHAR, TO_DATE 함수의 기본 DATE FORMAT 입니다. 오라클 19C에서 기본 형식은 RR/MM/DD 입니다. 현재가 2021년 으로 년도뒤 두자리가 21인데 0~49 사이이므로 입력값이 0~49 사이인 경우는 현재 세기가 되고 50~99이면 이전세기가 됩니다. 아래예문은 현재세기인 2021년이 됩니다. |
INSERT INTO t1(a, b, C) VALUES (30, 'oracle', '21/03/03'); |
<실행결과>
1 행 이(가) 삽입되었습니다.
NLS_DATE_FORMAT은 다음과 같이 조회가 가능 합니다. |
SELECT * FROM nls_session_parameters WHERE PARAMETER = 'NLS_DATE_FORMAT' |
<실행결과>
PARAMETER | VALUE | |
1 | NLS_DATE_FORMAT | RR/MM/DD |
아래 INSERT 구문에서 b 컬럼을 기술하지 않았지만 NULL을 허용하는 컬럼이므로 오류는 발생하지 않습니다. 묵시적으로 NULL이 입력 됩니다. |
INSERT INTO t1(a, C) VALUES (40, SYSDATE) |
<실행결과>
1 행 이(가) 삽입되었습니다.
COMMIT SELECT * FROM t1 |
<실행결과>
A | B | C | |
1 | 1 | oracle | 21/03/03 |
2 | 30 | oracle | 21/03/03 |
3 | 40 | (null) | 21/03/03 |
COMMIT 명령을 실행하지 않아도 SELECT 결과는 동일 합니다. 현재 사용자의 세션에서는 COMMIT 명령을 통해 영구히 DB에 저장하지 않아도 실행한 DML의 결과에 대한 확인은 가능 합니다., 물론 다른 사용자가 동일한 계정으로 로그인 했다면 COMMIT 하지 않은 결과에 대해서는 확인이 불가능 합니다.
t1 테이블의 b 컬럼에 대해 기본값(Default Value)를 설정할 수 있습니다. 이러한 경우 해당 컬럼에 값이 입력되지 않으면 정의한 기본값으로 설정이 됩니다. |
ALTER TABLE t1 MODIFY b DEFAULT 'oracle' |
<실행결과>
Table T1이(가) 변경되었습니다.
물론 t1 테이블을 생성할 때 다음과 같이 기술하는 것도 가능 합니다.
b VARCHAR2(10) default 'oracle'
b 컬럼에 값을 입력하지 않으면 기본값인 ‘oralce’로 설정 됩니다. 만약 VALUES 구에서 b 컬럼의 값을 NULL등으로 설정하면 기본값은 입력되지 않습니다. |
INSERT INTO t1(a, C) VALUES (50, SYSDATE) |
<실행결과>
1 행 이(가) 삽입되었습니다.
COMMIT SELECT * FROM t1 |
<실행결과>
A | B | C | |
1 | 1 | oracle | 21/03/03 |
2 | 30 | oracle | 21/03/03 |
3 | 40 | (null) | 21/03/03 |
4 | 50 | oracle | 21/03/03 |
현재 오라클 서버에 접속한 사용자를 알아낼때 USER 함수를 INSERT 구문에서 이용할 수 있습니다. 현재접속한 사용자는 scott 입니다. b 컬럼의 CHECK 제약조건을 삭제 후 USER 함수를 이용해 봅니다. DML등에서 USER 함수가 사용되면 오라클은 “SELECT USER FROM DUAL”을 실행하여 접속한 사용자를 리턴합니다. |
ALTER TABLE t1 DROP CONSTRAINT ck_t1_b |
<실행결과>
Table T1이(가) 변경되었습니다.
INSERT INTO t1 (a, b, C) VALUES (60, USER, SYSDATE); |
<실행결과>
1 행 이(가) 삽입되었습니다.
COMMIT SELECT * FROM t1 |
<실행결과>
A | B | C | |
1 | 1 | oracle | 21/03/03 |
2 | 30 | oracle | 21/03/03 |
3 | 40 | (null) | 21/03/03 |
4 | 50 | oracle | 21/03/03 |
5 | 60 | SCOTT | 21/03/03 |
INSERT 구문에 컬럼의 기본값을 나타내는 DEFAULT 키워드를 사용할 수 있습니다. b 컬럼에는 기본값인 ‘oracle’이 입력 됩니다. |
INSERT INTO t1 (a, b, c) VALUES (70, DEFAULT, SYSDATE); |
<실행결과>
1 행 이(가) 삽입되었습니다.
COMMIT SELECT * FROM t1 |
<실행결과>
A | B | C | |
1 | 1 | oracle | 21/03/03 |
2 | 30 | oracle | 21/03/03 |
3 | 40 | (null) | 21/03/03 |
4 | 50 | oracle | 21/03/03 |
5 | 60 | SCOTT | 21/03/03 |
6 | 70 | oracle | 23/03/03 |
8.1.2. 복수행 입력(Multi Row Insert)
여러행을 한번에 입력하기 위한 방법은 INSERT … SELECT… 와 같이 서브쿼리를 이용하는 방법과 오라클9i 이후 사용가능한 INSERT ALL[FIRST] 등을 이용하는 방법이 있습니다.
서브쿼리를 이용한 다중행 입력
[기본 형식]
INSERT INTO table_name [(column_name1, column_name2,,, )] Sub Query |
- 서브쿼리는 SELECT문이며 서브쿼리의 결과(자신이나 다른 테이블의 SELECT 결과)로 넘어오는 레코드를 한번에 INSERT 합니다.
- INSERT INTO 테이블명 다음에 컬럼을 기술하는 경우에는 해당 컬럼만 데이터를 INSERT 하겠다는 의미로 서브쿼리에서 SELECT 되는 컬럼 개수와 타입이 일치해야 합니다. 컬럼을 기술 안하는 것은 테이블의 모든 컬럼에 대해 INSERT 하겠다는 의미 입니다.
- 서브쿼리의 결과로 SELECT되는 ROW가 없다면 한건도 INSERT 되지 않습니다.
EMP 테이블의 구조만 복사하여 EMP_TEST 테이블을 생성합니다. 데이터는 복사하지 않습니다. CREATE, DROP. ALTER등의 DDL 명령은 COMMIT이 필요하지 않습니다. 오라클은 DDL 구문이 실행되기전에 COMMIT을 내부적으로 실행 합니다. |
CREATE TABLE emp_test (sabun, name, salary, deptno) AS SELECT empno, ename, sal, deptno FROM emp WHERE 1 = 2 ❶ |
<실행결과>
Table EMP_TEST이(가) 생성되었습니다.
❶ WHERE절의 조건은 100% 거짓 이므로 emp 테이블에서 SELECT 되는 데이터는 없고 emp 테이블의 컬럼 구조를 참조하여 emp_test 테이블이 생성 됩니다.
SELECT * FROM emp_test |
<실행결과>
SABUN | NAME | SALARY | DEPTNO |
출력되는 행 없습니다.
EMP 테이블에서 10번 부서원들만 SELECT하여 EMP_TEST 테이블에 입력하는 예문 입니다. |
INSERT INTO emp_test (sabun, name, salary, deptno) SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10 COMMIT |
<실행결과>
2개 행 이(가) 삽입되었습니다.
SELECT * FROM emp_test |
<실행결과>
SABUN | NAME | SALARY | DEPTNO | |
1 | 7782 | CLARK | 2450 | 10 |
2 | 7934 | MILLER | 1300 | 10 |
INSERT ALL/FIRST을 이용한 다중행 입력
[기본 형식]
INSERT ALL | FIRST INTO [table] VALUES [(Column1, Column2,,,)] INTO [table] VALUES [(Column1, Column2,,,)] …… Sub Query |
Oracle 9i 이후에 사용 가능한 명령으로 하나의 테이블에 여러 데이터를 입력하거나, 여러 테이블에 하나의 INSERT 명령으로 동시에 데이터를 입력할 수 있습니다.
Unconditional Insert ALL : 서브쿼리의 결과를 조건없이 모든 테이블에 입력 실습을 위해 테이블 2개를 생성 합니다. 사원명(ename)과 직무(job) 컬럼을 가지는 EMP_JOB_INFO와 사원명(ename)과 급여(sal) 컬럼을 가지는 EMP_SAL_INFO 테이블을 생성 합니다. |
CREATE TABLE emp_job_info AS SELECT ename, JOB FROM emp WHERE 1 > 100 CREATE TABLE emp_sal_info AS SELECT ename, sal FROM emp WHERE 1 > 100 |
<실행결과>
Table EMP_JOB_INFO이(가) 생성되었습니다.
Table EMP_SAL_INFO이(가) 생성되었습니다.
현재 EMP 테이블의 데이터는 14건 입니다. |
SELECT empno, ename, deptno FROM emp |
<실행결과>
EMPNO | ENAME | DEPTNO | |
1 | 7369 | SMITH | 20 |
... | ... | ... | ... |
14 | 7934 | MILLER | 10 |
EMP 테이블의 모든 데이터를 조건없이 EMP_JOB_INFO, EMP_SAL_INFO 에 입력 합니다. |
INSERT ALL INTO emp_job_info VALUES (ename, JOB) INTO emp_sal_info VALUES (ename, sal) SELECT ename, sal, JOB FROM emp |
<실행결과>
28개 행 이(가) 삽입되었습니다.
SELECT * FROM emp_job_info |
<실행결과>
ENAME | JOB | |
1 | SMITH | CLERK |
... | ... | ... |
14 | MILLER | CLERK |
SELECT * FROM emp_sal_info |
<실행결과>
ENAME | SAL | |
1 | SMITH | 800 |
... | ... | ... |
14 | MILLER | 1300 |
Conditional Insert ALL : 모든 데이터를 같거나 다른 테이블에 다중 INSERT 가능하지만 특정 조건에 따라 INSERT 합니다..동시에 여러 조건에 만족한다면 모든 INSERT문이 실행됩니다. 실습을 위해 이전에 생성한 EMP_JOB_INFO, EMP_SAL_INFO 테이블의 데이터를 삭제합니다. |
DELETE FROM emp_job_info DELETE FROM emp_sal_info |
<실행결과>
14개 행 이(가) 삭제되었습니다.
14개 행 이(가) 삭제되었습니다.
emp 테이블에서 모든 행을 선택하여 EMP_JOB_INFO에는 10번부서 사원명(ename), 직무(job) 데이터를, EMP_SAL_INFO에는 20부서 사원의 사원명(ename), 급여(sal) 데이터를 입력 하세요. |
INSERT ALL WHEN deptno = 10 THEN INTO emp_job_info VALUES (ename, JOB) WHEN deptno = 20 THEN INTO emp_sal_info VALUES (ename, sal) SELECT ename, sal, JOB, deptno FROM emp |
<실행결과>
7개 행 이(가) 삽입되었습니다.
SELECT * FROM emp_job_info |
<실행결과>
ENAME | JOB | |
1 | CLARK | MANAGER |
2 | MILLER | CLERK |
SELECT * FROM emp_sal_info |
<실행결과>
ENAME | SAL | |
1 | SMITH | 800 |
2 | JONES | 2975 |
3 | SCOTT | 3000 |
4 | ADAMS | 1100 |
5 | FORD | 3000 |
10번 부서는 EMP_JOB_INFO에 20번 부서는 EMP_SAL_INFO에 입력하고, 부서관계없이 급여가 2500 보다 큰 사원은 EMP_SAL_INFO에 입력하세요. 두 테이블의 데이터는 삭제 후 아래 실습을 하세요. |
INSERT ALL WHEN deptno = 10 THEN INTO emp_job_info VALUES (ename, job) WHEN deptno = 20 THEN INTO emp_sal_info VALUES (ename, sal) WHEN sal > 2500 THEN INTO emp_sal_info VALUES (ename, sal) ❶ SELECT ename, sal, job, deptno FROM emp |
<실행결과>
12개 행 이(가) 삽입되었습니다.
❶ WHEN sal > 2500 THEN INTO emp_sal_info VALUES (ename, sal) 조건에는 위의 2가지 조건을 만족했던 행 이라도 sal > 2500을 만족한다면 INTO 절이 실행되어 EMP_SAL_INFO 테이블에 데이터가 입력 됩니다. EMP_SAL_INFO테이블에서 “SCOTT”, “JONES”, “FORD”는 두번 INSERT 되었습니다.
SELECT * FROM emp_job_info |
<실행결과>
ENAME | JOB | |
1 | CLARK | MANAGER |
2 | MILLER | CLERK |
SELECT * FROM emp_sal_info |
<실행결과>
ENAME | SAL | |
1 | SMITH | 800 |
2 | JONES | 2975 |
3 | SCOTT | 3000 |
4 | ADAMS | 1100 |
5 | FORD | 3000 |
6 | JONES | 2975 |
7 | BLAKE | 2850 |
8 | SCOTT | 3000 |
9 | KING | 5000 |
10 | FORD | 3000 |
Conditional Insert First : 서브쿼리의 데이터를 WHEN 조건절에서 지정한 조건을 만족하는 첫번째 테이블에만 INSERT 하고, 다음 레코드도 이와 같은 방식으로 INSERT 합니다. EMP 테이블에서 모든 데이터를 선택하여 EMP_JOB_INFO 에는 job이 ‘MANAGER’인 데이터를, EMP_SAL_INFO 에는 sal값이 2000보다 큰 데이터를 입력하세오. 단 두조건을 모두 만족하는 경우라면 EMP_JOB_INFO에만 입력하고 job이 ‘MANAGER’도 아니고 sal값이 2000보다 작거나 같은 경우에는 EMP_SAL_INFO에 입력 하세오. 이전에 생성한 EMP_JOB_INFO, EMP_SAL_INFO 테이블의 데이터를 삭제 후 실습하세요. |
INSERT FIRST WHEN job = 'MANAGER' THEN INTO emp_job_info VALUES (ename, job) ❶ WHEN sal > 2000 THEN INTO emp_sal_info VALUES (ename, sal) ❷ ELSE INTO emp_sal_info VALUES (ename, sal) ❸ SELECT ename, sal, job, deptno FROM emp |
<실행결과>
14개 행 이(가) 삽입되었습니다.
❶ 에서 job이 ‘MANAGER’인 경우 EMP_SAL_INFO 테이블에 사원명(ename), 급여(sal)가 입력 됩니다.
❷ 에서 sal이 2000 보다 큰 경우 EMP_JOB_INFO 테이블에 사원명(ename), 직무(job)가 입력 됩니다.
❸에서 위 두가지 조건을 만족하지 않는 경우 즉 job이 ‘MANAGER’도 아니고 sal <= 2000 인 경우에 EMP_SAL_INFO 테이블에 사원명(ename), 급여(sal)가 입력 됩니다.
SELECT * FROM emp_job_info |
<실행결과>
ENAME | JOB | |
1 | JONES | MANAGER |
2 | BLAKE | MANAGER |
3 | CLARK | MANAGER |
SELECT * FROM emp_sal_info |
<실행결과>
ENAME | SAL | |
1 | SCOTT | 3000 |
2 | KING | 5000 |
3 | FORD | 3000 |
4 | SMITH | 800 |
5 | ALLEN | 1600 |
6 | WARD | 1250 |
7 | MARTIN | 1250 |
8 | TURNER | 1500 |
9 | ADAMS | 1100 |
10 | JAMES | 950 |
11 | MILLER | 1300 |
#DML, #오라클DML, #멀티행저장, #INSERT, #UPDATE, #DELETE, #SELECT, #SQLDML, #ORACLE, #ORACLE교육
