Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1120906
  • 博文数量: 159
  • 博客积分: 3063
  • 博客等级: 中校
  • 技术积分: 2703
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-01 01:51
文章分类

全部博文(159)

文章存档

2013年(48)

2012年(111)

分类: Oracle

2012-05-31 23:26:07

昨天学习了INSERT ALL/FIERT有条件多表插入

现在来总结一下今天做的几个相关实验,首先创建表

create table t as select rownum rn from user_objects where rownum <=100;
create table small (c varchar2(10));
create table medium (c varchar2(10));
create table large (c varchar2(10));
create table special (c varchar2(10));

 

实验1INSERT ALL

INSERT ALL
   WHEN rn < 10 THEN
      INTO small
         VALUES('small')
   WHEN rn > 10 and rn < 20 THEN
      INTO medium
         VALUES('medium')
   WHEN rn > 20 THEN
      into large
         VALUES('large')
   WHEN rn > 29 THEN
      INTO special
         VALUES('special')     
   SELECT rn      
      FROM t;

SELECT * from small;

SELECT * from medium;

SELECT * from large;

SELECT * from special;

得到的结果分别是:9,9,80,71

 

实验2INSERT FIRST

先清空刚才实验插入的记录

delete small;
delete medium;
delete large;
delete special;

这里用delete 表名比用savepoint要方便,有的时候savepoint会不能rollback。

INSERT FIRST

   WHEN rn < 10 THEN

      INTO small

         VALUES('small')

   WHEN rn > 10 and rn < 20 THEN

      INTO medium

         VALUES('medium')

   WHEN rn > 20 THEN

      into large

         VALUES('large')

   WHEN rn > 29 THEN

      INTO special

         VALUES('special')    

   SELECT rn      

      FROM t;

SELECT * from small;

SELECT * from medium;

SELECT * from large;

SELECT * from special;

得到的结果分别是:9,9,80,0

 

实验3INSERT FIRST

再把rn>29放到第3个when条件,我们来看一下区别

INSERT FIRST

   WHEN rn < 10 THEN

      INTO small

         VALUES('small')

   WHEN rn > 10 and rn < 20 THEN

      INTO medium

         VALUES('medium')

   WHEN rn > 29 THEN

      INTO special

         VALUES('special')     

   WHEN rn > 20 THEN

      into large

         VALUES('large')

   SELECT rn      

      FROM t;

SELECT * from small;

SELECT * from medium;

SELECT * from special;

SELECT * from large;

得到的结果分别是:9,9,71,9

 

总结:

使用INSERT ALL/FIRST多表插入可以很方便的把目标数据进行归类,按WHEN指定的条件,把目标数据插入相应的表中,以上实验分别把100以内的数据插入了small,medium,large,special4张不同的表中。

1.当我们使用INSERT ALL时,所有100条数据都要根据每个WHEN指定的条件对INTO后面的表插入数据,每个WHEN都是独立的,包含所有数据(100),每个WHEN条件都要用走一遍,第1WHEN,插入9条数据;第2WHEN,插入9条数据;第3WHEN,插入80条数据;第4WHEN,插入71条数据,每次判断WHEN条件时,都是用100这个标准去计算有多少条数据时符合WHEN条件的,再把符合条件的数据插入相应的表。

2.当我们使用INSERT FIRST进行多表插入的时候,每个WHEN条件并不独立,是有先后顺序的,排在最前面的WHEN条件首先对全部数据进行过滤,符合条件的则INSERT INTO相应表;过滤完以后,才把剩下的数据在之后的WHEN条件中进行过滤,从最开始的100,每走一个WHEN条件,数据就会被过滤掉一批,具体过滤多少是和WHEN条件有关的;也就是说越往后面,每个WHEN判断的数据会越来越少(被之前的WHEN过滤掉了)比如在实验2中,第1WHEN以后,就把1的记录过滤了,9条符合条件的记录插入small表;到了第2WHEN,再把10的记录过滤掉,9条符合记录插入medium表;以此类推,到了第3WHEN20的记录有80条,而第4WHEN的条件是29,我们看这里2WHEN条件中符合条件的数据其实有共同区间,那ORACLE是如何来判断这个插入操作呢?INSERT FIRST的定义就是对于每行数据,只插入到第一个符合WHEN条件的表,而不继续检查之后的WHEN条件。其实可以这样理解这句话:因为排在后面的WHEN的数据都被前面的WHEN过滤完了,那自然就不会再进行条件判断并插入记录。那我们看,这里第3和第4WHEN条件是有交集的,即29那以哪个为主来插入数据呢?按WHEN的顺序,第一个符合条件的才插入,那就是按照20来插入,所以插入80条记录到large表,而之后的那个sepcial表,就不再插入数据了(符合条件的记录都被第3WHEN过滤完了,只剩2条数据,1020,而1020显然不符合rn>29的条件,就不会在special表中插入数据),所以结果是0。同样,我们看在实验3中的WHEN条件3和条件4互换了一下位置,先按照29来插入,所以结果插入了71条记录到special表,但为什么之后的WHEN条件4又会插入9条记录到large表呢?不是只插入第一个满足WHEN条件的表,后面就不检查WHEN条件了吗?这里我们看条件4中,20的这9条记录并不在这个29的交集中的,也就是9条数据并没有被之前的第3WHEN条件过滤掉,所以最后还是会插入这9条记录到large表的。

     以上是我对今天做的INSERT FIRST/ALL实验的理解,还有,在实验中发现一个现象,当INSERT是有条件的时候,如INSERT WHEN...THEN...INTO VALUES...的形式,则此时如果是全部插入,可以省略关键字ALL,即INSERT ALL写成INSERT也是可以的,结果也是对全部符合条件的表插入多条记录(INSERT ALL的方式)。如果觉得我的理解有偏差,欢迎留言指正,谢谢! 

阅读(2437) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~