oracle insert all 基本语法-2

      之前介绍了insert all的基本语法，今天给大家介绍insert all的两个特性。

     特性一：事务一致性

     大家先看下面二个SQL

SQL> create table t as select object_name,rownum as object_id from dba_objects where rownum<=10;

Table created.

SQL> create table t1 as select * from t where 1=2;

Table created.

SQL> create table t2 as select * from t where 1=2;

Table created.

SQL> 
SQL> insert all   --测试SQL1
  2  into t1(object_name,object_id)
  3  into t2(object_name,object_id)
  4  select * from t;

20 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> delete t1;

10 rows deleted.

SQL> delete t2;

10 rows deleted.

SQL> commit;

Commit complete.

SQL>  
SQL> insert  into t1(object_name,object_id) select * from t; --测试SQL2-A

10 rows created.

SQL> insert  into t2(object_name,object_id) select * from t; --测试SQL2-B


10 rows created.

SQL> commit;

Commit complete.

SQL> 

         在上面的SQL中，测试SQL1和测试SQL2(A&B)看起来，效果是一样，但是真的一样吗？如果运行完测试SQL2A，有人insert了T2表，那么再运行SQL2B，大家应该知道效果了吧。所以insert all还是有事务特性的。

     

      特性二：可以有条件的insert all/first

SQL> truncate table t1;

Table truncated.

SQL> truncate table t2;

Table truncated.

SQL> insert first
  2  when object_id = 1 then
  3  into t1(object_name,object_id)
  4  when object_id <=5 then                      
  5  into t2(object_name,object_id)
  6  select * from t;

5 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> col OBJECT_NAME format a20
SQL> select * from t1;

OBJECT_NAME           OBJECT_ID
-------------------- ----------
ICOL$                         1

SQL> select * from t2;

OBJECT_NAME           OBJECT_ID
-------------------- ----------
I_USER1                       2
CON$                          3
UNDO$                         4
C_COBJ#                       5

SQL> 

 可以看出来，前面等于1的条件被<=5含在内，FIRST就表示前面插入了，后面不会再插入了。所以T1表有OBJECT_ID=1的记录，T2表没有这条记录

SQL> truncate table t1;

Table truncated.

SQL> truncate table t2;

Table truncated.

SQL> insert all
  2  when object_id = 1 then
  3  into t1(object_name,object_id)
  4  when object_id <=5 then                      
  5  into t2(object_name,object_id)
  6  select * from t;

6 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1;

OBJECT_NAME           OBJECT_ID
-------------------- ----------
ICOL$                         1

SQL>  select * from t2;

OBJECT_NAME           OBJECT_ID
-------------------- ----------
ICOL$                         1
I_USER1                       2
CON$                          3
UNDO$                         4
C_COBJ#                       5

SQL> 

那么一样的SQL，insert all的效果就没有那个限制了。