DataBase-SQLPLUS(oracle 9i에서 향상된 DML과 DDL(다중테이블 insert-무조건insert, insert all, insert first, Pivoting insert)

Oracle 9i 에서 향상된 DML과 DDL

 

다중테이블 insert

 

무조건 insert

대량에 데이터를 한번에 넣음.

create table t1

as select empno, ename, hiredate

from emp

where 0=1;

 

create table t2

as select empno, ename, sal

from emp

where 0=1;

 

insert all

into t1 values(empno,ename, hiredate)

into t2 values(empno,ename, sal)

select empno, ename, hiredate ,sal

from emp

where deptno=10;

 

조건 insert all

조건에만 맞으면 들어감 , t1,t2 동시에 들어갈수도있음.

insert all

    when hiredate>='81/01/01' then

        into t1 values(empno,ename,hiredate)

    when sal>3000 then

        into t2 values(empno,ename,sal)

    select empno,ename,hiredate,sal

    from emp

조건 insert first

if - else 문과 같다 우선 검사 후 넘어가서

각자 같은값은 없다.

create table sal1000

as select empno,ename,sal from emp

where 0=1;

 

create table sal3000

as select empno,ename,sal from emp

where 0=1;

 

create table salmax

as select empno, ename,sal from emp

where 0=1;

테이블생성

insert first

    when sal<=1000 then

        into sal1000 values (empno, ename,sal)

    when sal<=3000 then

        into sal3000 values (empno, ename,sal)

    else

        into salmax values (empno, ename,sal)

    select empno,ename,sal from emp;

 

 

insert first

    when sal<=1000 then

        into sal1000 values (empno, ename,sal)

    when sal<=3000 then

        into sal3000 values (empno, ename,sal)

    else

        into salmax values (empno, ename,sal)

    select empno,ename,sal from emp;

 

피벗팅(Pivoting) insert

 

피벗팅(Pivoting)은 한개의 행을 한개의 테이블내 여러개의 행으로 입력하는방법.,

 

create table sales_data(

empno number(4),

spring_sale number(9),

summer_sale number(9),

autumn_sale number(9),

winter_sale number(9));

 

insert into sales_data values (100,3000,5000,6000,7000);

insert into sales_data values (200,4000,2000,3000,5000);

insert into sales_data values (300,6000,3000,4000,4000);

insert into sales_data values (400,3000,1000,5000,2000);

 

create table sales_season(

empno number(4),

season_code char,

sales number(9));

인설트

insert all

into sales_season values(empno, '1',Spring_sale)

into sales_season values(empno, '2',Summer_sale)

into sales_season values(empno, '3',autumn_sale)

into sales_season values(empno, '4',winter_sale)

select empno, spring_sale, summer_sale, autumn_sale,winter_sale

from sales_data;

 

[출처] DataBase-SQLPLUS(oracle 9i에서 향상된 DML과 DDL(다중테이블 insert-무조건insert, insert all, insert first, Pivoting insert)|작성자 남잭슨