INSERT ALL/FIRST实验

昨天学习了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));

 

实验1．INSERT 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

 

实验2．INSERT 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

 

实验3．INSERT 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,special这4张不同的表中。

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

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

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