MultiTable Inserts功能:
Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This
statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's
main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format:
-- Unconditional insert into ALL tables
INSERT ALL
INTO sal_history VALUES(empid,hiredate,sal)
INTO mgr_history VALUES(empid,mgr,sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Pivoting insert to split non-relational data
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;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000THEN
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;
The restrictions on multitable INSERTs are:
Multitable inserts can only be performed on tables, not on views or materialized views.
You cannot perform a multitable insert via a DB link.
You cannot perform multitable inserts into nested tables.
The sum of all the INTO columns cannot exceed 999.
Sequences cannot be used in the subquery of the multitable insert statement
假如我们有表sales,结构如下:
Prodid | Prodname | Mon_Amt | Tue_Amt | Wed_Amt | Thu_Amt | Fri_Amt | Sat_Amt |
101 102 | AIWA AKAI | 2000 1900 | 2500 2100 | 2230 2130 | 2900 3100 | 3000 2800 | 2100 2120 |
现在,我打算把 SALES 表中的行,增加到 Week_Sales 表中,增加后的表数据结构如下:
Prodid
| Prodname
| WeekDay
| Amount |
101 101 101 101 101 101 102 102 102 102 102 102 | AIWA AIWA AIWA AIWA AIWA AIWA AKAI AKAI AKAI AKAI AKAI AKAI | Mon Tue Wed Thu Fri Sat Mon Tue Wed Thu Fri Sat | 2000 2500 2230 2900 3000 2100 1900 2100 2130 3100 2800 2120 |
为了达到上面的效果,我们采用Multi table INSERT的语法方式进行插入,语句如下:
Insert all
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Mon’,mon_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Tue’,tue_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Wed’,wed_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Thu’,thu_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Fri’,fri_amt)
Into week_sales(prodid,prodname,weekday,amount)
Values (prodid,prodname,’Sat’,sat_amt)
Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
Fri_amt,sat_amt from sales;
不但可以插入同一个表,也可以插入到不同表,例如如下的脚本,同时插入suppliers表和customers表中:
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;
Undrop功能
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP; Flashback complete.
UpSert功能:
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;