14. 다중 INSERT문, merge문

■ -다중 insert  문   4가지(3권 164페이지)

 

1. 무조건 insert

 

2. 조건부 insert

 

3. 조건부 insert first

 

4. pivoting insert

 

 

행 insert 할 때

여러개 한번에 하는것.

insert all into~

 

 

"다중 insert 문은 여러개의 테이블에 하나의 데이터를

 동시에 입력하는 SQL 문"

 

 

 

문제484.

아래의 테이블을 생성하고 아래의 테이블에

emp 테이블의 empno, ename, sal, deptno 를

입력하시오

 

create table emp1

as

select * from emp

where 1=2;

 

 

create table emp2000

as

 select * from emp

 where 1=2;

 

 

create table emp3000

 as

  select * from emp

   where 1=2;

 

 

 

insert into emp1(empno, ename, sal, deptno)

 select empno, ename, sal, deptno

 from emp;

 

 

insert into emp2000(empno, ename, sal, deptno)

 select empno, ename, sal, deptno

 from emp;

 

 

 

insert into emp3000(empno, ename, sal, deptno)

 select empno, ename, sal, deptno

 from emp;

 

 

 

문제485.

위의 문제를 다시 해결하는데

이번에는 emp1, emp2000, emp3000 에

다중 insert 문을 이용해서

한번에 입력되게 하시오 (162페이지 참고)

 

insert all  into emp1 (empno, ename, sal, deptno)

                 into emp2000 (empno, ename, sal, deptno)

                 into emp3000 (empno, ename, sal, deptno)

   select empno, ename, sal, deptno

   from emp;

 

근데

지정컬럼 말고

emp 에 있는 모든 컬럼이 null 값으로 들어감.

처음에 emp 테이블 구조만 모두 따왔으니까

where 1=2

 

 

 

문제486.(점심시간 문제)

조건부 insert 를 이용해서 170페이지를 잘 보고

emp 테이블에서 부서번호 10번은 emp1 에 입력

                     부서번호 20번은 emp2000 에

                     부서번호 30번은 emp3000 에

입력되게 하시오

컬럼은 전체 사원 다 입력되게 하시오

 

INSERT all

WHEN deptno=10 THEN into emp1

WHEN deptno=20 THEN INTO emp2000

WHEN deptno=30 THEN INTO emp3000

SELECT * FROM emp;

 

SELECT * FROM emp1;

SELECT * FROM emp2000;

SELECT * FROM emp3000;

 

 

 

 

문제487.

우리반 테이블의 구조를 담는 테이블3개를

아래와 같이 만드시오

 

create table emp2_sk

 as

 select * from emp2

 where 1=2;

 

create table emp2_lg

 as

 select * from emp2

 where 1=2;

 

create table emp2_kt

 as

 select * from emp2

 where 1=2;

 

 

 

문제488.

조건부 다중 INSERT 를 이용해서 우리반 테이블의 데이터 중에서

sk 는 emp2_sk로

lg 는 emp2_lg로

kt 는 emp2_kt로 데이터를 이행 하시오

 

insert all

when lower(telecom)='sk' then into emp2_sk

WHEN lower(telecom)='lg' THEN INTO emp2_lg

WHEN lower(telecom)='kt' THEN INTO emp2_kt

SELECT * FROM emp2;

 

**sk는 lower 안 쓰면 안 나옴

 

SELECT * FROM emp2_sk;

SELECT * FROM emp2_lg;

SELECT * FROM emp2_kt;

 

 

 

■ 다중 insert  문   4가지(3권 164페이지)

 

1. 무조건 insert

 

2. 조건부 insert

 

3. 조건부 insert first

 

4. pivoting insert

 

문제489.

위의 테이블 3개를 다 truncate 시키고

다시 데이터를 입력하는데

 

나이가 30대 이상이면

통신사와 상관없이 emp2_sk 에 입력하고

나머지 20대인 학생들 중에서

통신사가 lg 면 emp2_lg 에 입력하고

통신사가 kt 면 emp2_kt 에 입력하시오(책 173 페이지 참고)

 

truncate table emp2_sk;

truncate table emp2_kt;

truncate table emp2_lg;

 

 

INSERT FIRST

WHEN age >= 30 THEN INTO emp2_sk

WHEN age < 30 and lower(telecom) = 'kt' THEN INTO emp2_kt

WHEN age < 30 and lower(telecom) = 'lg' THEN INTO emp2_lg

 SELECT * FROM emp2;

 

ELSE 안 써도 나옴.

 

 

선생님 답

INSERT FIRST

WHEN age >= 30 THEN INTO emp2_sk

WHEN  lower(telecom) = 'kt' THEN INTO emp2_kt

WHEN  lower(telecom) = 'lg' THEN INTO emp2_lg

 SELECT * FROM emp2;

 

30대가 이미 걸러졌으니까

나머지 20대 데이터 가지고 telecom 으로 나눠먹음

 

 

 

■pivoting insert

"컬럼이 데이터가 되어서 테이블에 입력되어야 할 때 사용하는 insert 문"

 

선생님이 주신 예제

create table order2

( ename  varchar2(10),

  bicycle  number(10),

  camera   number(10),

  notebook  number(10) );

 

insert  into  order2  values('SMITH', 2,3,1);

insert  into  order2  values('ALLEN',1,2,3 );

insert  into  order2  values('KING',3,2,2 );

 

commit;

 

 

 

문제490.

unpivot 문을 이용해서

order2 를 아래와 같이 출력하시오!

 

select *

from order2

unpivot ( cnt for item in(BICYCLE , CAMERA , NOTEBOOK ) ) ;

 

in 안에는 별칭지정이라서 더블쿼테이션 쓰거나 아예 아무것도 안쓰거나.

 

 

 

문제491.

아래의 지역별 범죄현황 데이터를 오라클 db 에

입력하시오

create table crime_age

( local varchar2(100),

type varchar2(20),

sum_tot number(10),

under_6 number(10),

under_12 number(10),

under_15 number(10),

under_20 number(10),

under_30 number(10),

under_40 number(10),

under_50 number(10),

under_60 number(10),

over_60 number(10),

unkonwn number(10),

gender varchar2(20),

year number(10)

);

 

엑셀파일 넣어용

 

 

 

문제492.

crime_age 테이블을 unpivot 해서

아래의 결과를 출력하시오

 

 

화면 캡처: 2018-11-02 오후 2:32

select * from crime_age;

출력해서 컬럼명 확인하고

그 컬럼명 그대로 언피봇

 

select  *

 from crime_age

unpivot(cnt for AGE in (under_6,under_12, under_15, under_20, under_30, under_40,  under_50, under_60, over_60, unkonwn));

 

 

 

문제493.

2014년도에 살인의 피해자인 나이대, 나이대별 건수를

출력하시오.

 

 

table/ view 이용

create  table crime_age2

as

select  *

 from crime_age

unpivot(cnt for AGE in (under_6,under_12, under_15, under_20, under_30, under_40,  under_50, under_60, over_60, unkonwn));

**view 를 활용해서도 하면 됩니다.

 

   select age, sum(cnt)

      from crime_age2

     where type = '살인'   and year = 2014

     group by age

    order by sum(cnt) desc;

 

 

with 절 이용

with crime_age_v as (select *

                      from crime_age

                      unpivot(cnt for age in

                        (UNDER_6, UNDER_12, UNDER_15, UNDER_20, UNDER_30, UNDER_40, UNDER_50, UNDER_60, OVER_60, UNKONWN ) )

                        )

select age, sum(cnt)SC    

from crime_age_v

where type = '살인'

 and  year = 2014

group by age

order by SC desc ;

 

 

 

문제494.

pivoting insert 문을 이용해서

나이대에 대한 정보를 테이블의 data 로 만드시오.

 

create table crime_age2

 (local varchar2(100),

  type varchar2(20),

 sum_tot number(10),

 age_type varchar2(20),

 cnt number(10) );

 

 

 

pivoting insert 문

 

insert all

 into crime_age2 values(local, type, sum_tot, 'under_6', under_6)

 into crime_age2 values(local, type, sum_tot, 'under_12', under_12)

 into crime_age2 values(local, type, sum_tot, 'under_15', under_15)

 into crime_age2 values(local, type, sum_tot, 'under_20', under_20)

 into crime_age2 values(local, type, sum_tot, 'under_30', under_30)

 into crime_age2 values(local, type, sum_tot, 'under_40', under_40)

 into crime_age2 values(local, type, sum_tot, 'under_50', under_50)

 into crime_age2 values(local, type, sum_tot, 'under_60', under_60)

 into crime_age2 values(local, type, sum_tot, 'over_6', over_60)

select local, type, sum_tot, under_6, under_12, under_15,

                     under_20, under_30, under_40, under_50,

                    under_60, over_60

 from crime_age;

 

 

 

문제495.

서울시 내에서 살인이 가장 많이 일어나는 지역이

어디인지 지역이름, 건수, 순위를 출력하시오.

 

select local, sum(cnt)SC,

 dense_rank()over(order by sum(cnt) desc)순위

from crime_age2

where type = '살인'

group by local

order by SC desc;

 

 

 

 

 

*오라클의 데이터 베이스 설명

 

               11g                                      12c

 

데이터베이스 이름: xe                      이름: orcl

기능이 약한 소프트웨어                 오라클의 모든 기능이 다

                                                 패키징 되어있다.

 

scott 유저 생성                            scott 유저가 이미 생성

demobld 직접 넣음                 emp, dept, salgrade 이미 존재

 

 

 

 

 

■ merge 문 (3권 180페이지)

 "insert, update, delete 를 한 번에 수행하는 명령어"

 

merge into 테이블이름 alias

using 테이블이름(서브쿼리) alias

on (조인)

when matched then

update set

컬럼 1 = 컬럼1 var

컬럼2= 

 

 

 

문제496.

emp 테이블로 emp5000 생성하고

emp5000 테이블에 데이터 절반을 지우시오

 

 create table emp5000

 as

 select * from emp;

 

delete from emp5000

where rownum < 8;

 

commit;

 

 

sal 0 으로 바꾸시오.

update emp5000

 set sal = 0;

 commit;

 

 

 

 

문제497.

emp 테이블에 있는 데이터를 emp5000 에 merge 하는데

emp 테이블과 emp5000 에 양쪽에 다 존재하는 사원들은

emp5000 의 사원들의 월급을 emp 테이블의 사원의 월급으로

변경하고

그렇지 않고

emp 테이블과 emp5000 에 양쪽에 다 존재하지 않고

emp 에만 존재하는 사원들은 emp5000에 입력하시오.

(즉

절반 날라간 애들은 emp서 사원정보 다 집어넣고,

남은 절반은 월급0으로 만든걸

emp 랑 같은 월급으로 만들어라.)

 

merge into emp5000 e5

 using emp e

 on (e5.empno = e.empno)

when matched then

update set e5.sal = e.sal

when not matched then

insert (empno, ename, sal, job, hiredate, mgr, comm, deptno)

values(e.empno, e.ename, e.sal, e.job, e.hiredate, e.mgr, e.comm, e.deptno);

 

 

 

 

문제498.(마지막 문제)(3권 181페이지 참고)

위 상황을 다시 똑같이 만들고(절반 날리고, 남은애들 월급0처리)

emp 테이블에서 emp5000 테이블에 있는 사원 한 명을 지우시오

(작업 시작전 emp 서 한 명 날려야 함 *emp5000 에 있는.)

 

merge 문으로 3가지 수행

 

1. emp의 월급으로 -------> emp5000 의 월급 갱신

2. emp----------------------------> emp5000 에 insert

>>>>> 2번 까지는 497번 답

 

3. emp 테이블에는 존재하지 않는데

 emp5000 에만 존재하는 사원들은 지우시오.

 

 

일단 EMP 테이블에서 WARD 날리겠음.

delete from emp

where ename = 'WARD';

 

 

***WHEN NOT MATCHED 가 먼저 와야 에러 안뜬다.

 

 

merge into emp5000 e5

using emp e1

on ( e5.empno = e1.empno )

when matched then

update set e5.sal = e1.sal

when not matched then

insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

values ( e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno )

Delete where = (select ename from emp5000

                       minus

                      select ename from emp);

 

소현

merge into emp5000 e5

using (select * from emp5000 union select * from emp) e

on (e5.empno = e.empno)

when not matched then

insert (empno, ename, sal, job, hiredate, mgr, comm, deptno)

values (e.empno, e.ename, e.sal, e.job, e.hiredate, e.mgr, e.comm, e.deptno)

when matched then

update set e5.sal = e.sal

delete where e5.ename = (select ename from emp5000 minus select ename from emp);

 

 

 

 

 

 

 

 

merge into emp5000 e5

using emp e1

on ( e5.empno = e1.empno )

when not matched then

insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

values ( e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno )

when matched then

update set e5.sal = e1.sal

Delete where e5.ename = (select ename from emp5000

                       minus

                      select ename from emp);

이렇게 하면 둘 다 14 rows 생김.

우리가 원하는건 둘다 13 rows.

 

 

 

merge into emp5000 e5

using emp e1

on ( e5.empno = e1.empno )

when not matched then

insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

values ( e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno );

여기까지 돌리면

 0이었던 SAL 도 다 들어가있다. (대체 왜….)

심지어 WARD  도 들어있음.

sal 이 0 이라는 것도 not matched 니까?

 

 

 

 

 

merge into emp5000 e5

using emp e1

on ( e5.empno = e1.empno )

when not matched then

insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

values ( e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno )

이렇게 돌리면

EMP 는 13 ROWS

EMP5000 은 14 ROWS

 

 

 

merge into emp5000 e5

using emp e1

on ( e5.empno = e1.empno )

when not matched then

insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )

values ( e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno )

Delete where e5.ename = (select ename from emp5000

                       minus

                      select ename from emp);

이렇게 하면 둘다 14rows

 

 

Emp 에서 WARD 지움.

merge into emp5000 e5

using (select * from emp5000 union select * from emp) e

on (e5.empno = e.empno)

when not matched then

insert (empno, ename, sal, job, hiredate, mgr, comm, deptno)

values (e.empno, e.ename, e.sal, e.job, e.hiredate, e.mgr, e.comm, e.deptno)

when matched then

update set e5.sal = e.sal

delete where e5.ename not in (select ename from emp);

답나옴.

 

 

왜 union  을 써야 하는지를 모르겠음.

Merge 같이 update 기능의 DML 문은

부모 관계가 확실해야 한다.

 

 

full outer join 안 돌아감.

Merge into emp5000 e5

Using (select * from emp e_2 full outer join emp5000 e5_2

 on e_2.empno = e5_2.empno) e

when not matched then

insert (empno, ename, sal, job, hiredate, mgr, comm, deptno)

values (e.empno, e.ename, e.sal, e.job, e.hiredate, e.mgr, e.comm, e.deptno)

when matched then

update set e5.sal = e.sal

delete where e5.ename not in (select ename from emp);

 

 

 

insert into emp

select * from emp5000

 where ename='WARD';

 

 

merge into emp5000 e5

using (select * from emp5000 union select * from emp) e

on (e5.empno = e.empno)

when not matched then

insert (empno, ename, sal, job, hiredate, mgr, comm, deptno)

values (e.empno, e.ename, e.sal, e.job, e.hiredate, e.mgr, e.comm, e.deptno)

when matched then

update set e5.sal = e.sal

delete where e5.ename not in (select ename from emp);