Oracle SQL skills-upsert, multitable insert, undrop

Recently, I attended the OCP training. The lecturer said it was too fast. I have been using sqlserver for a long time, and many things have to be done.

Upsert function:

Merge Using <table_view_or_query>
On (<condition>)
When matched then <update_clause>
When not matched then <insert_clause>;

Multitable inserts functions:

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 faster Ming 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 AllIntoSal_historyValues(Empid, hiredate, Sal)IntoMgr_historyValues(Empid, Mgr, sysdate)SelectEmployee_id empid, hire_date hiredate, salary Sal, manager_id MgrFromEmployeesWhereEmployee_id> 200;
 

 

-- Inserting ting 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_infoValues  (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_historyValues  (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 > 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;

 

The restrictions on multitable inserts are:

Multitable inserts can only be stored med 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 Function

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

SQL>Flashback table EMP to before drop;

Flashback complete.