다중 테이블 insert

select *

from tab;

 

create table sal_history(

    empId number(6),

    hiredate date,

    sal number(8,2)

);

 

create table mgr_history(

    empId number(6),

    mgr number(6),

    sal number(8,2)

);

 

select * from hr.sal_history;

select * from hr.mgr_history;

 

delete from sal_history;

delete from mgr_history;

 

--insert all

insert all

    into sal_history values(empid, hiredate, sal)

    into mgr_history values(empid, mgr, sal)

    select

        employee_id empid,

        hire_date hiredate,

        salary sal,

        manager_id mgr

    from employees

    where employee_id > 200;

   

--조건 insert all

insert all

    when sal > 10000 then

        into sal_history values(empid, hiredate, sal)

    when mgr > 200 then

        into mgr_history values(empid, mgr, sal)

    select

        employee_id empid,

        hire_date hiredate,

        salary sal,

        manager_id mgr

    from employees

    where employee_id > 200;

 

-----------------------------------------------

create table special_sal(

    deptid number(4),

    sal number(8,2)

);

 

create table hiredate_history_00(

    deptid number(4),

    hiredate date

);

 

create table hiredate_history_99(

    deptid number(4),

    hiredate date

);

 

create table hiredate_history(

    deptid number(4),

    hiredate date

);

-----------------------------------------------

--조건 first insert

insert first

    when sal > 25000 then

        into special_sal values(deptid, sal)

    when hiredate like('%00%') then

        into hiredate_history_00 values(deptid, hiredate)

    when hiredate like('%99%') then

        into hiredate_history_99 values(deptid, hiredate)

    else

        into hiredate_history values(deptid, hiredate)

    select

        department_id deptid,

        sum(salary) sal,

        max(hire_date) hiredate

     from employees

     group by department_id;

    

select * from special_sal;

select * from hiredate_history_00;

select * from hiredate_history_99;

select * from hiredate_history;

 

delete from special_sal;

delete from hiredate_history_00;

delete from hiredate_history_99;

delete from hiredate_history;

 

--피벗 insert

--준비과정----------------------------------------

create table sales_source_data(

    employee_id number(6),

    week_id number(2),

    sales_mon number(8,2),

    sales_tue number(8,2),

    sales_wed number(8,2),

    sales_thur number(8,2),

    sales_fri number(8,2)

);

 

insert into sales_source_data

values(176, 6, 2000, 3000, 4000, 5000, 6000);

 

create table sales_info(

    employee_id number(6),

    week_id number(2),

    sales number(8,2)

);

----------------------------------------------

 

--피벗 insert 실행

insert all

    into sales_info values(employee_id, week_id, sales_mon)

    into sales_info values(employee_id, week_id, sales_tue)

    into sales_info values(employee_id, week_id, sales_wed)

    into sales_info values(employee_id, week_id, sales_thur)

    into sales_info values(employee_id, week_id, sales_fri)

    select

        employee_id,

        week_id,

        sales_mon,

        sales_tue,

        sales_wed,

        sales_thur,

        sales_fri

    from sales_source_data;

   

select * from sales_source_data;

select * from sales_info;