从9i开始增加了insert all ，可以将数据同时插入多个表中，这样可以大大的减少磁盘的IO以及代码的长度。
>create table inser_test_all(a varchar2(20),b number);
Table created.
>begin
2 for i in 1..10 loop
3 insert into inser_test_all values('a'||i,i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
>create table inser_test_when1(a varchar2(20));
Table created.
>create table inser_test_when2(b number);
Table created.
>create table inser_test(a varchar2(20),b number);
Table created.
>insert all
2 into inser_test values('number',b)
3 into inser_test values(a,888)
4 selecdt a,b from inser_test_all;
selecdt a,b from inser_test_all
*
ERROR at line 4:
ORA-00928: missing SELECT keyword
>ed
Wrote file afiedt.buf
1 insert all
2 into inser_test values('number',b)
3 into inser_test values(a,888)
4* select a,b from inser_test_all
>/
20 rows created.
>select * from inser_test;
A B
-------------------- ----------
number 1
number 2
number 3
number 4
number 5
number 6
number 7
number 8
number 9
number 10
a1 888
A B
-------------------- ----------
a2 888
a3 888
a4 888
a5 888
a6 888
a7 888
a8 888
a9 888
a10 888
20 rows selected.
>
其实这里也可以分别插入不同的表。这里是没有条件的insert all
下面看一下有条件的insert all
>ed
Wrote file afiedt.buf
1 insert all
2 when b >=1 and b<=5 then into inser_test_when1 values(a)
3 when b >=6 and b <=10 then into inser_test_when2 values(b)
4* select a,b from inser_test_all
>/
10 rows created.
>select * from inser_test_when1;
A
--------------------
a1
a2
a3
a4
a5
>select * from inser_test_when2;
B
----------
6
7
8
9
10
阅读(1636) | 评论(0) | 转发(0) |
