Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.