sql入門之23 pivoting insert等

Oracle 9i Extensions to DML and DDL Statements
-- 大綱：
Use the following types of multitable inserts
?Unconditional INSERT
?Pivoting INSERT
?Conditional ALL INSERT
?Conditional FIRST INSERT
:) Create and use external tables
:) Name the index at the time of creating a primary key constraint[@more@]

unconditional insert: all into_clause
The oracle server executes each insert_into_clause once for each row returned by the subquery.
在使用all關鍵字時，oracle將執行每個insert，不進行排除
SQL> insert all
2 into sal_history values(empid,hiredate,sal)
3 into mgr_history values(empid,mgr,sal)
4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
5 from employees
6 where employee_id>200;

8 rows created.
SQL> select employee_id from sal_history
2 union all
3 select employee_id from mgr_history
EMPLOYEE_ID
-----------
201
202
205
206
201
202
205
206

-- 有條件的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;

-- insert first是符合第一個的記錄不再應用到下面的條件判斷
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;

-- 什麼是pivoting 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 number(2),
sales number(8,2)
);
-- 現在要將上表的資料轉換到下表中，
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;