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)|작성자 남잭슨