제4. 5 장 테이블의 정의 복사하기

Q. 기존 테이블과 같은 열 집합을 가지는 새 테이블을 만들려고 한다

예를 들어, 'DEPT' 테이블의 사본을 생성하고 이를 'DETP_2'라고 부르고자 하는 것이다. 이 때 행은 복사하지 않고, 오직 테이블의 열 구조만 복사하려고 한다.

A-Oracle, Maria: 행을 반환하지 않는 서브쿼리와 함께 CREATE TABLE 구문을 사용한다

create table dept_2
as
select * from dept where 1 = 0;

WHERE절이 어색하게 보인다.

• Create Table As Select: CTAS문이라 부른다
  이 구문을 사용할 때, WHERE절에 거짓 조건을 지정하지 않으면 쿼리의 모든 행을 복사해버린다.
  따라서 거짓인 조건을 넣어서 만족하는 행이 없도록하고, 열만 반환하게 만들어준다.
  • 결과적으로 CATS문의 결과는 SELECT절에 있는 열에 기반을 두는 빈 테이블이 된다

---

4. 6 한 번에 여러 테이블 삽입하기

Q. 쿼리에서 반환된 행을 가져와서 이를 여러 테이블에 삽입하려고 한다

예를 들어, 'DEPT' 테이블의 행을 'DEPT_EAST', 'DEPT_WEST', 'DEPT_MID' 테이블에 넣으려고 하는 것이다.
단, 세 테이블 모두 'DEPT'와 동일한 열 및 데이터 유형을 가지며 비어 있는 상태로 가정한다.

A. 쿼리 결과를 대상 테이블에 삽입하는 것은 4.4장과 동일하다

다른 점은 대상 테이블이 여러 개라는 것이다.

case: Oracle

🥶 MARIA에서는 '한 번에 여러 테이블에 삽입하기'와 같은 기능은 지원하지 않는다.

• INSERT ALL 또는 INSERT FIRST문을 사용한다

/* Oracle */
insert all
	when loc in ('NEW YORK', 'BOSTON') then
    into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc = 'CHICAGO' then
    into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
    else
    into dept_west (deptno, dname, loc) values (deptno, dname, loc)
    
    select deptno, dname, loc
    	from dept;

• INSERT ALL 구문을 사용하였다
• CASE WHEN THEN END 구문을 앞서서 훈련해왔다
  이 구문 중 일부인 WHEN THEN을 사용하여 조건 - 결과 형식을 취했다
• 마지막으로 반환값은 SELECT ... 서브쿼리의 반환값이 된다

  INSERT FIRST는 조건이 참으로 평가되는 즉시 WHEN THEN ELSE 구문을 빠져나간다.
  반면, INSERT ALL은 이전 테스트가 참으로 평가되더라도 모든 조건을 평가한다.

/* Oracle */
create table dept_east as select * from dept where 1=0;
create table dept_west as select * from dept where 1=0;
create table dept_mid as select * from dept where 1=0;

insert first
	when loc in ('NEW YORK', 'BOSTON') then
    into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc = 'CHICAGO' then
    into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
    else
    into dept_west (deptno, dname, loc) values (deptno, dname, loc)
    
    select deptno, dname, loc
    	from dept;
>>
4 row(s) inserted.

select * from dept_mid;
select * from dept_east;
select * from dept_west;
>>
DEPTNO	DNAME	LOC
30		SALES	CHICAGO

DEPTNO	DNAME		LOC
10		ACCOUNTING	NEW YORK
40		OPERATIONS	BOSTON
2 rows selected.


DEPTNO	DNAME		LOC
20		RESEARCH	DALLAS

INSERT FIRST를 쓰면 조건이 참으로 평가되는 즉시 WHEN THEN ELSE절을 빠져나온다고 했는데, 왜 다 정상적으로 들어갔을까?

쿼리를 조금 바꿔야 FIRST와 ALL의 차이를 좀 더 명확하게 알 수 있다.

/* Oracle */
create table dept_east as select * from dept where 1=0;
create table dept_west as select * from dept where 1=0;
create table dept_mid as select * from dept where 1=0;

insert first
	when loc in ('NEW YORK', 'BOSTON') then
    into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc in ('NEW YORK', 'BOSTON') then
    into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
    else
    into dept_west (deptno, dname, loc) values (deptno, dname, loc)
    
    select deptno, dname, loc
    	from dept;
        
select * from dept_mid;
select * from dept_east;
select * from dept_west;
>>
no data found

Result Set 1
DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
40	OPERATIONS	BOSTON
2 rows selected.

Result Set 2
DEPTNO	DNAME	LOC
20	RESEARCH	DALLAS
30	SALES	CHICAGO
2 rows selected.

'DEPT_EAST'에 넣은 조건과 동일했는데, 'DEPT_MID'에는 아무 자료도 들어가지 않았다.
앞선 조건에서 이미 가져갔기 때문이다.
먼저 조건을 만족한 구문에서 결과셋을 이미 가져갔다. 조건을 만족하는 행이 남지 않았기 때문에 아무 것도 반환되지 않았다.
'중복 추첨 불가능'

INSERT ALL은 어떻게 될까?
조건만 만족한다면 해당되는 행을 무조건 반환할 것이다.

/* Oracle */
create table dept_east as select * from dept where 1=0;
create table dept_west as select * from dept where 1=0;
create table dept_mid as select * from dept where 1=0;


insert all
	when loc in ('NEW YORK', 'BOSTON') then
    into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc in ('NEW YORK', 'BOSTON') then
    into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
    else
    into dept_west (deptno, dname, loc) values (deptno, dname, loc)
    
    select deptno, dname, loc
    	from dept;
        
select * from dept_mid;
select * from dept_east;
select * from dept_west;
>>
DEPTNO	DNAME		LOC
10		ACCOUNTING	NEW YORK
40		OPERATIONS	BOSTON
Download CSV
2 rows selected.

Result Set 2
DEPTNO	DNAME	LOC
10		ACCOUNTING	NEW YORK
40		OPERATIONS	BOSTON
2 rows selected.

Result Set 3
DEPTNO	DNAME		LOC
20		RESEARCH	DALLAS
30		SALES		CHICAGO
2 rows selected.

조건에 맞는다면 무조건 행을 반환한다.
'중복 추첨 가능'

---

정리

DB또는 테이블을 내보내는 기능들이 요즘 GUI에 대부분 장착되어 있을 것이다.
이렇게 정직하게 쿼리를 작성하는 건 낭만파일지도 모른다...
그래도 FIRST나 ALL의 차이점이나 '거짓 조건' 같은 내용들은 참 참신하고 재미가 있다.
혹시나 리눅스 체제에서 DB를 만지게 된다면 유용하게 쓰일 것 같기도 하다.