November 18 2015

insert all

 

如何使用insert 语句同时向a b两张表中插入数据呢

oracle 提供了insert all的语法:

multi_table_insert.gif

create table a(id);

create table b(id);

首先创始两张测试用的表,用于测试insert all语句

现在我想把 1 2 放到a 表 把1 放到b表

SQL> insert all
2 into a values(1)
3 into a(id) values(2)
4 into b values(1)
5 select * from dual;

3 rows created.

—提交后检查:

SQL> commit;

Commit complete.

SQL> select * from a;

ID
———-
1
2

SQL> select * from b;

ID
———-
1

因为语法的关系 这里不可以不写select语句

ERROR at line 1:
ORA-00928: missing SELECT keyword

所以当我们把一张基表的不同(或相同的)列 放到不同的表中

SQL> insert all
2 into a values(col1)
3 into b values(col2)
4 select 3 col1,4 col2 from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from a;

ID
———-
1
2
3

 

当insert all into的时候 我们往往是不同的条件下 向不同的表中插入不同的值 所以在此基础上 oracle又提供了更完善的语法:

conditional_insert_clause.gif

 

 

用个例子来看一下:

SQL> select level l from dual connect by level <=9;

L
———-
1
2
3
4
5
6
7
8
9

9 rows selected.

 

我要把 123  456 789分别放到s m l三张表里

SQL> create table s(id number);

Table created.

SQL> create table m(id number);

Table created.

SQL> create table l(id number);

Table created.

 

语句如下;
1 insert all
2 when l<4 then
3 into s values(l)
4 when l>3 and l<7 then
5 into m
6 when l>6 then
7 into l
8* select level l from dual connect by level<10
SQL> /

9 rows created.

SQL> select * from s;

ID
———-
1
2
3

SQL> select * from m;

ID
———-
4
5
6

 

 

同样 这条sql也可以改写成:

1 insert all
2 when l<4 then
3 into s values(l)
4 when l>3 and l<7 then
5 into m
6 else
7 into l
8* select level l from dual connect by level<10
SQL> /

9 rows created.

使用else来放不符合要求的数据放到一张表中

有些时候 有些条件会有交集 然后你并不希望把它重复的放到同一个表里 这个时候就可以用first来做

insert first/all 是对每一行来进行判断
两者区别:
insert first:对于每一行数据,只插入到第一个when条件成立的表,不继续检查其他条件。
insert all :对于每一行数据,对每一个when条件都进行检查,如果满足条件就执行插入操作

 

再来看最后一个例子
1 insert all
2 when l<4 then
3 into s values(l)
4 when l<7 then
5 into m
6 else
7 into l
8* select level l from dual connect by level<10
SQL> /

12 rows created.

 

这个时候一共insert了12行  检查可以发现 123并重复的insert到了s 和m 两张表里

换成first

1 insert first
2 when l<4 then
3 into s values(l)
4 when l<7 then
5 into m
6 else
7 into l
8* select level l from dual connect by level<10
SQL> /

9 rows created.

 

这个时候 只有9行被insert了 因为123三行数据在满足了第一个when的时候就不再继续判断了。

 

同时要注意:
You can perform multitable inserts only on tables, not on views or materialized views.
只能是表 不可以是视图或物化视图
You cannot perform a multitable insert into a remote table.
不可以操作远端表
You cannot specify a TABLE collection expression when performing a multitable insert.
不能使用表集合表达式;
In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.
不能超过999个目标列
Multitable inserts are not parallelized if any target table is index organized or if any target table has a bitmap index defined on it.
目标表是索引组织表或目标表上建有BITMAP索引时,插入语句不能并行
Plan stability is not supported for multitable insert statements.
不支持Plan stability
You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.
你不能指定一个序列在任何一部分。插入被认为是一个SQL语句。所以第一个参考NEXTVAL生成的值,和所有后续所有引用的值都是一个。


Copyright 2019. All rights reserved.

Posted 2015年11月18日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *