The usage of insert all and insert first statements
The insert all and insert first statements, which are used to insert data into multiple tables at the same time under a given condition, are recorded below.
1. Unconditional insert all
Used to insert a batch of data into several tables at the same time regardless of conditions.
Set up a test table
Create table T1 (a number, b varchar2 (20))
Insert into T1 values (1, 'aaa')
Insert into T1 values (2, 'bbb')
Insert into T1 values (3, 'ccc')
Commit
Create table T2 (a number, b varchar2 (20))
Create table T3 (a number, b varchar2 (20))
Create table T4 (a number, b varchar2 (20))
Get data from the first table and write to several other tables at the same time, each table can have different values
Insert all into T2 values (a + 1, b)
Into T3 values (a + 2, b)
Select a, b from t1
Commit
If each table inserts the same data, the above can also be simplified
Insert all into t2
Into t3
Select a, b from t1
Commit
2. Conditional insert all
Insert different tables according to the different values of the query data
Insert all when a > = 1 then
Into t2
When a > = 2 then
Into t3
Else
Into t4
Select a, b from t1
Commit
Observe the query results of several tables
Select * from T1
A B
--
1 aaa
2 bbb
3 ccc
Select * from T2
A B
--
1 aaa
2 bbb
3 ccc
Select * from T3
A B
--
2 bbb
3 ccc
Select * from T4
No rows selected
3. Conditional insert first
If the value of the first when clause is true, the corresponding into clause is executed for the given row, and the subsequent when clause is skipped, and the subsequent insert statement is no longer executed
Insert first when a > = 1 then
Into t2
When a > = 2 then
Into t3
Else
Into t4
Select a, b from t1
Commit
Observe the query results of the table
Select * from T1
A B
--
1 aaa
2 bbb
3 ccc
Select * from T2
A B
--
1 aaa
2 bbb
3 ccc
Select * from T3
No rows selected
Select * from T4
No rows selected
