Oracle Multi-Table INSERT statement insert All/insert First

about insert all and insert
First
One, unconditional INSERT all
Ii. Conditions INSERT All
Three, conditions INSERT
First

Insert ... Select
Inserting records into multiple tables with the Insert Select implementation
One, unconditional INSERT all

---------------------------------------------------------------------------------------------

INSERT All

Insert_into_clause Values_clause_1

[Insert_into_clause values_clause_2]
......

subquery;

----------------------------------------------------------------------------------------------
1. Specify all the following multi-table insert_into_clauses to perform unconditional multi-table insertion;

2. For each row returned by a subquery, Oracle
The server executes each insert_into_clause once.



Ii. Conditions INSERT All

---------------------------------------------------------------------------------------------

INSERT All

When condition then Insert_into_clause values_clause

[When condition then] [Insert_into_clause Values_clause]
......
[ELSE]
[Insert_into_clause Values_clause]
subquery;

--------------------------------------------------------------------------------------------

1, specify conditional_insert_clause to perform a condition multiple table insert;

2, the Oracle server through the corresponding when
Conditions filter each Insert_into_clause to determine whether to perform this insert_into_clause;


3. A single multiple-table INSERT statement can contain a maximum of 127 when clauses.


Third, conditions INSERT first

--------------------------------------------------------------------------------------------

INSERT First

When condition then Insert_into_clause values_clause

[When condition then] [Insert_into_clause Values_clause]
......
[ELSE]
[Insert_into_clause Values_clause]
subquery;

--------------------------------------------------------------------------------------------

1. The Oracle server evaluates each of the When clauses appearing in the statement order;

2. If the value of the first when clause is true,oracle
The server executes the appropriate into clause for the given row, and skips the back when
Clause (the subsequent when statement no longer considers the record that satisfies the first when clause, even if the record satisfies the condition in the When statement).



Note: Multi-table INSERT
Constraints on a statement

A, you can only perform multiple table insertions on the table but not on the view;

b, you cannot perform a multi-table insert into a remote table;


C, when performing a multiple-table insert, you cannot specify a table set expression;

D, in a multi-table insert, all the insert_into_clauses
Can not be combined to specify more than 999 mesh columns;

E. Rollback will only work if the table data in all insert_into_clauses has not been updated.