Oracle Sql技巧 – Upsert, Multitable Insert, Undrop

近期參加OCP培訓，講師說的太快，之前一直是SQLSERVER，很多東西要惡補了。

UpSert功能：

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;

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 ALLINTO sal_history VALUES(empid,hiredate,sal)INTO mgr_history VALUES(empid,mgr,sysdate) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGRFROM employees WHERE employee_id > 200;

 

-- Pivoting insert to split non-relational data

INSERT ALLINTO 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 ALLWHEN SAL>10000 THENINTO sal_history VALUES(EMPID,HIREDATE,SAL)WHEN MGR>200 THENINTO mgr_history VALUES(EMPID,MGR,SYSDATE)SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGRFROM employees WHERE employee_id > 200;

 

-- Insert into the FIRST table with a matching condition

INSERT FIRSTWHEN SAL > 25000THENINTO special_sal VALUES(DEPTID,SAL)WHEN HIREDATE like ('%00%') THENINTO hiredate_history_00 VALUES(DEPTID,HIREDATE)WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)ELSEINTO hiredate_history VALUES(DEPTID, HIREDATE)SELECT department_id DEPTID, SUM(salary) SAL,MAX(hire_date) HIREDATEFROM 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.

Undrop功能

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Flashback complete.