Multitable Insert

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