本帖最后由 南北思静 于 2015-1-16 23:49 编辑
一、INSERT ALL 不带条件用法
SQL> create table t_table1(tid number,tname varchar(100));
Table created
SQL> create table t_table2(tid number,tname varchar(100));
Table created
SQL> insert all
into t_table1(tid, tname)
values
(object_id, object_name)
into t_table2(tid, tname)
values
(object_id, object_name)
select object_id, object_name, object_type from dba_objectswhere wner = 'TEST';
8440 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_table1;
COUNT(1)
----------
4220
SQL> select count(1) from t_table2;
COUNT(1)
----------
4220
SQL>
指定所有跟随的多表，都执行无条件的多表插入；
二、INSERT ALL 带条件用法
SQL> create table t_table(tid number,tname varchar(100));
Table created
SQL> create table t_index(iid number,iname varchar(100));
Table created
SQL> create table t_other(oid number,oname varchar(100));
Table created
SQL> insert all when object_type = 'TABLE' then into t_table
2(tid, tname)
3values
4(object_id, object_name) when object_type = 'INDEX' then into t_index
5(iid, iname)
6values
7(object_id, object_name) else into t_other
8(oid, oname)
9values
10(object_id, object_name)
11select object_id, object_name, object_type
12from dba_objects
13 where wner = 'TEST';
4220 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_table;
COUNT(1)
----------
1025
SQL> select count(1) from t_index;
COUNT(1)
----------
1582
SQL> select count(1) from t_other;
COUNT(1)
----------
1613
SQL>
Oracle服务器通过相应的WHEN条件过滤，将查询结果分别插入到满足条件的表中；
三、INSERT FIRST 用法
SQL> create table t_table1(tid number,tname varchar(100));
Table created
SQL> create table t_table2(tid number,tname varchar(100));
Table created
SQL> create table t_table3(tid number,tname varchar(100));
Table created
SQL> insert first when object_idcommit;
Commit complete
SQL> select count(1) from t_table1;
COUNT(1)
----------
860
SQL> select count(1) from t_table2;
COUNT(1)
----------
2327
SQL> select count(1) from t_table3;
COUNT(1)
----------
1033
SQL>
可以看到,用FIRST后,凡是符合第一个条件的就都插入第一个表,其他的数据才在以后的条件里再判断。
=================================================
其实写到最后这让我想到了近期做预测项目中，遇到的匹配问题。（基于hive）
first很像在查询匹配中的like like只能找到符合条件的第一条记录
如果需要找到所有符合条件的并且提高效率，需要用正则或多模匹配算法。后绪有机会单说。
|
