insert all/ insert first/ pivoting insert

--把子查询作为insert的目标：
--实际上就是向视图中插入
--插入一个欧洲的部门
insert into (select l.location_id,l.city,l.country_id
                  from locations l
                  join countries c
                  on (l.country_id = c.country_id)
                  join regions r
                    using (region_id)
                    where region_name='Europe')
   values(5500,'bsg','UK');                 

--在子查询中使用with check option选项提供约束，
--避免插入非法数据

--插入一个美国的部门，会抛异常
--SQL 错误: ORA-01402: 视图 WITH CHECK OPTION where 子句违规
insert into (select l.location_id,l.city,l.country_id
               from locations l
               where country_id in(
                    select country_id
                      from countries                 
                      join regions 
                      using (region_id)
                      where region_name='Europe')
              with check option
             )
   values(5600,'Washington','US');  

--DEFAULT关键字可用在INSERT and UPDATE 语句中
--来确定一个默认的列值。如果没有默认值存在，就使用null值。

--DEFAULT选项可让你摆脱必须在你的程序里硬编码默认值或者
--查询字典来找到它。

--
insert into departments(department_id,department_name,manager_id)
  values(340,'sample',default);

--多表INSERT语句在数据仓库场景中是有用的。
--4种
--1、无条件的insert all：将数据源中的每一行无条件的插入到
--所有的目标表中

--将编号大于200的雇员同时插入到2个表中
create table sal_history
  as select employee_id empid,hire_date hiredate,
            salary sal
      from employees
      where 1=2;
      
create table mgr_history
  as select employee_id empid,manager_id mgr,
            salary sal
      from employees
      where 1=2;      

insert all
  into sal_history values(empid,hiredate,sal)
  into mgr_history values(empid,mgr,sal)
  select employee_id empid,manager_id mgr,
            salary sal,hire_date hiredate
      from employees
      where employee_id>200;


--2、有条件的insert all：将数据源中的每一行插入到满足条件的
--所有的目标表中。
--注意：对任何一行，所有的条件都要判断

--将所有雇员按照两个条件插入到两个表中
create table emp_history
  as select employee_id empid,hire_date hiredate,
            salary sal
      from employees
      where 1=2;

create table emp_sales
  as select employee_id empid,commission_pct comm,
            salary sal
      from employees
      where 1=2;

insert all
  when hiredate < to_date('2005-01-01','YYYY-MM-DD') then
    into emp_history values(empid,hiredate,sal)
  when comm is not null then
    into emp_sales values(empid,comm,sal)
  select employee_id empid,commission_pct comm,
            salary sal,hire_date hiredate
      from employees;


--3、有条件的insert first：将数据源中的每一行插入到满足条件的
--第一个目标表中。
--注意：对任何一行，只要一个条件为true，那么其他的条件不再判断

--根据雇员的薪水多少，将他们分别插入到不同的表中
create table sal_low
  as select employee_id ,last_name,
            salary sal
      from employees
      where 1=2;

create table sal_mid
  as select employee_id ,last_name,
            salary sal
      from employees
      where 1=2;
      
create table sal_high
  as select employee_id ,last_name,
            salary sal
      from employees
      where 1=2;      

insert first
  when sal < 5000 then
    into sal_low values(employee_id ,last_name,sal)
  when sal between 5000 and 10000 then
    into sal_mid values(employee_id ,last_name,sal)
  else
    into sal_high values(employee_id ,last_name,sal)
  select employee_id ,last_name,
            salary sal
      from employees;

--4、pivoting insert：“列转行”操作
--将以列来表示的数据转换成以行来表示
create table sales_source_data(
  employee_id number,
  week_id number,
  sales_mon number,
  sales_tue number,
  sales_wed number,
  sales_thur number,
  sales_fri number
);

insert into sales_source_data
  VALUES(176,6,2000,3000,4000,5000,6000);
  
SELECT * FROM SALES_SOURCE_data;

create table sales_info(
  employee_id number,
  week number,
  sales number
);

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_info;

--在11g中，专门提供了2个关键字做行列转换查询：
--pivote和unpivote