- DML: INSERT 내용이 많고 방대해서, 배운 내용을 기록하고자 한다.
데이터 입력, INSERT
INSERT INTO table_name [col_name] VALUES [values]
- 가장 간단한 INSERT문으로, [col_name]에 지정한대로 [values]을 입력하여 데이터를 삽입한다.
INSERT INTO department(deptno, dname)
VALUES (300, '생명공학부');- 위의 경우, department라는 테이블의 deptno, dname이라는 컬럼에 각각 300, '생명공학부'라는 값을 입력하였다.
INSERT INTO student VALUES(10110, '홍길동' ,'hong', '1', '8501011143098', '85/01/01', '041)630-3114', 170, 70, 101, 9903);- 위처럼 [col_name]을 지정하지 않으면, 테이블 생성 시 정의한 칼럼 순서와 동일한 순서대로 입력하여야 한다.
- 칼럼 순서는 DESC table_name으로 확인할 수 있다.
NULL값 (묵시적, 명시적) 입력하기
- 일반적으로 NOT NULL 제약조건이 없는 COLUMN에 한해, 값을 입력하지 않으면 묵시적으로 NULL이 입력된다.
INSERT INTO department(deptno, dname)
VALUES (300, '생명공학부');- 위의 경우, department 테이블에서 deptno와 dname이 아닌 COLUMN에는 NULL 값이 입력된다.
- 명시적으로 NULL, ''을 입력할 수도 있다.
INSERT INTO department VALUES (301, '환경보건학과', '', NULL);
날짜 데이터 입력하기
- Oracle에서는 'yy/mm/dd'형태로 날짜를 입력한다. 문제는 이러한 형태로 입력되지 않을 때인데, TO_DATE 함수를 통해 Oracle에 맞는 날짜 형태로 변환이 가능하다.
INSERT INTO professor(profno, name, position, hiredate, deptno) VALUES (9920, '최윤식', '조교수', TO_DATE('2006/01/01', 'YYYY/MM/DD'), 102); - 위의 예시에서, TO_DATE 함수를 통해 Oracle의 Date 형태와 맞지 않는 값을 변환하여 입력시킨 것을 볼 수 있다.
INSERT INTO professor VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 101);- 또한, SYSDATE 함수를 통해 현재 날짜를 입력할 수도 있다.
다중 행 입력
- MySQL에서는
# IN MySQL # INSERT INTO 테이블명 (컬럼1, 컬럼2,,,,) VALUES ('값1','값2'), ('값1','값2'), ('값1','값2');
Oracle에서는 다음과 같이 입력하여야 한다.
# In Oracle #
INSERT ALL
INTO department(deptno, dname) VALUES (400, '철학과')
INTO department(deptno, dname) VALUES (401, '심리학과')
SELECT * FROM DUAL;- 이런 점은 Oracle이 아쉽게 느껴진다. 이렇게 다중 행 입력을 하는 경우가 많이 없어서 그런가싶기도 하다.
- 다음은 테이블의 데이터를 복사하는 경우이다. 크게 2가지로 나뉘는데,
- 테이블의 프레임만 복사
CREATE TABLE T_STUDENT AS SELECT * FROM STUDENT WHERE 1=0;
- 이 경우, WHERE절에 거짓이 되는 조건을 붙여 복사되는 데이터가 없도록 하는 것이 포인트이다.
- 테이블의 데이터도 같이 복사
CREATE TABLE T_STUDENT AS SELECT * FROM STUDENT WHERE studno < 20000;
- 위의 경우에서 WHERE 절만 빠지면, 모든 데이터를 복사하게 된다.
- 만약 테이블 프레임은 준비가 되어있고, 데이터만 복사한다면 INSERT문을 이용하면 된다.
INSERT INTO T_STUDENT SELECT * FROM student;
INSERT ALL / FIRST
- 여기서부터 INSERT 난이도가 높아지기 시작한다 :(
- 단일 테이블의 경우에는 위처럼 ALL이 필요 없지만, 여러 테이블에 동시에 입력하기 위해서는 ALL / FIRST가 필요하다.
- 사용 방법은 다음과 같다.
INSERT [ALL / FIRST] INTO table_name1 VALUES [col1, col2, ...] INTO table_name2 VALUES [col1, col2, ...] ... SUBQUERY - ALL은 서브쿼리의 결과 집합을 해당하는 INSERT절에 모두 입력한다.
- FIRST는 서브쿼리의 결과 집합을 해당하는 '첫번째' INSERT절에만 입력한다.
- 실제 사용 예시를 보도록 하자.
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'; - 위에서 주목할 점은 서브쿼리(select문)의 형태이다.
- 해당 서브쿼리는 studno, name, height, weight 컬럼을 추출한다.
- 각 column은 "values (col1, col2, ...)"의 column_name에 대응하는 것을 알 수 있다.
- 다음은 ALL이 아닌 FIRST를 사용하는 경우다.
INSERT FIRST
INTO height_info VALUES (studno, name, height)
INTO weight_info VALUES (studno, name, weight)
SELECT studno, name, height, weight
FROM student
WHERE grade >= '2';
- 이렇게 쓰면 어떻게 될까? 정답은 '에러가 발생'한다.
- INSERT FIRST는 위처럼 사용하는 용도가 아니다. INSERT FIRST는 ""WHEN ~ THEN ~ ELSE"절에서 사용하는 용도로 쓰인다.
- WHEN ~ THEN ~ ELSE?
- WHEN ~ THEN ~ ELSE는 Oracle에서의 if ~ else문과 같다. 그러면 어떻게 사용하는가? 우선 INSERT ALL / FIRST에서는 이렇게 사용한다.
INSERT ALL / FISRT
WHEN 조건절1 THEN
INTO table1 VALUES (c1, c2, ...)
WHEN 조건절2 THEN
INTO table2 VALUES (c1, c2, ...)
.....................................
ELSE
INTO tableX VALUES (c1, c2, ...)
SUBQUERY;
- 이때, INSERT ALL / FIRST 에 따라 해석이 달라진다.
- INSERT ALL인 경우: 서브쿼리의 결과 집합 중에서, 조건절을 만족하는 경우에 조건절 아래에 있는 INSERT INTO ~ 가 작동한다. 이때 '어느 조건절도 만족하지 않는 행'은 ELSE 아래에 있는 INSERT INTO ~ 가 적용된다.
- INSERT FIRST인 경우: 위처럼 조건절을 만족하는 경우에 조건절 아래에 있는 INSERT INTO ~ 가 작동하나, 조건을 만족하는 '첫번째' INSERT절에만 입력한다. ELSE는 위와 같다.
- 쉽게 이야기하자면, 'INSERT ALL'은
if (condition):
do1
if (condition):
do2
if (condition):
do3와 같다면, 'INSERT FIRST'는
if (condition):
do1
elif (condition):
do2
elif (condition):
do3와 같은 형태이다.
예를 들어,
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';- INSERT FIRST 관점에서 이를 해석하면, height >170이고 weight > 70인 경우에 첫번째 조건절의 INTO heigt_info만 적용되고 다음 조건절의 INTO weight_info에는 적용이 안된다.
다중 행 입력, PIVOTING INSERT
- OLTP 업무에서 사용되는 데이터를 데이터웨어하우스 업무에서 사용되는 분석용 데이터로 변환하는 경우에 유용하다. 주로 하나의 행을 여러 개의 행으로 나누어 입력하는 기능이다.
- 이게 무슨 소리인고 하니, 다음 예시를 통해 알 수 있다.
- 다음과 같은 'sales'라는 테이블이 있다고 가정하자.
CREATE TABLE sales( -- pivoting insert 실습을 위한 예제 테이블 sales_no number(4), week_no number(2), sales_mon number(7,2), sales_tue number(7,2), sales_wed number(7,2), sales_thu number(7,2), sales_fri number(7,2)); - 그리고 다음과 같은 'sales_data'가 있다고 가정하자.
CREATE TABLE sales_data( sales_no number(4), week_no number(2), day_no number(2), sales number(7,2) ); - 상사가 "'sales'라는 테이블, 컬럼도 많고 지저분한데 'sales_data'라는 테이블 형태로 표현할 수 있겠나?"라고 이야기 한다면? 이때 pivoting insert를 사용하면 된다.
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;- 위와 같이 변환하면 sales 하나의 행이 sales_data의 5개의 행으로 변환하게 된다. 그렇지만 column이 줄어들기 때문에 분석하기 더 좋은 형태의 테이블이 되었다.
- 지금까지 Oracle의 DML-INSERT에 관련된 내용을 정리해보았다.