[20150115]insert多个表.txt
--别人给我提出一个问题，要把表拆开2个表，能否快速完成这个工作。还是通过例子来说明：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t1 (id number,name varchar2(20),c1 varchar2(20),c2 varchar2(20),c3 varchar2(20),c4 varchar2(20));
insert into t1 values (1,'a','a1','a2','a3','a4');
insert into t1 values (2,'b','b1','b2',NULL,NULL);
insert into t1 values (3,'c','c1','c2','c3',NULL);
--要把表变成两个：
--表t1x:
1,'a'
2,'b'
3,'c'
--表t2x
1,1,'a1','a2'
1,2,'a3','a4'
2,1,'b1','b2'
3,1,'c1','c2'
--有错误的记录在另外的表中：
--t2err
3,2,'c3',NULL
--实际上真正生产系统要折返的字段更多，这个明显违反关系数据结构的设计，国内的开发团队那个时候能提高，在数据结构上出现错误
--，要修改代码的工程量很大的。
--自己开始考虑多个select在插入，明显效率不高。有考虑11g的pivot，可以参考链接：
--http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
--昨天看了sql 的参考手册，才想起来使用insert多个表的操作最简单。操作如下：
1.建立表：
create table t1x (id number,name varchar2(20));
create table t2x (id number,seq number,c1 varchar2(20),c2 varchar2(20));
create table t2err (id number,seq number,c1 varchar2(20),c2 varchar2(20));
2.执行如下:
insert all
when 1 =1 then
into t1x values (id,name)
when c1 is not null and c2 is not null then
into t2x values (id,1,c1,c2)
when c3 is not null and c4 is not null then
into t2x values (id,2,c3,c4)
when ( c1 is null and c2 is not null ) or ( c1 is not null and c2 is null ) then
into t2err values (id,1,c1,c2)
when ( c3 is null and c4 is not null ) or ( c3 is not null and c4 is null ) then
into t2err values (id,2,c3,c4)
select * from t1;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0yy08jpx5vcqk, child number 0
-------------------------------------
insert all when 1 =1 then into t1x values (id,name) when
c1 is not null and c2 is not null then into t2x values
(id,1,c1,c2) when c3 is not null and c4 is not null then into
t2x values (id,2,c3,c4) when ( c1 is null and c2 is not null ) or (
c1 is not null and c2 is null ) then into t2err values
(id,1,c1,c2) when ( c3 is null and c4 is not null ) or ( c3 is not
null and c4 is null ) then into t2err values (id,2,c3,c4)
select * from t1
Plan hash value: 1248537433
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 3 (100)| 0 |00:00:00.01 | 24 |
| 1 | MULTI-TABLE INSERT | | 1 | | | 0 |00:00:00.01 | 24 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 3 | 3 (0)| 3 |00:00:00.01 | 7 |
| 3 | INTO | T1X | 0 | | | 0 |00:00:00.01 | 0 |
| 4 | INTO | T2X | 0 | | | 0 |00:00:00.01 | 0 |
| 5 | INTO | T2X | 0 | | | 0 |00:00:00.01 | 0 |
| 6 | INTO | T2ERR | 0 | | | 0 |00:00:00.01 | 0 |
| 7 | INTO | T2ERR | 0 | | | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------
SCOTT@test> select * from t1x;
ID NAME
---------- --------------------
1 a
2 b
3 c
SCOTT@test> select * from t2x;
ID SEQ C1 C2
---------- ---------- -------------------- --------------------
1 1 a1 a2
2 1 b1 b2
3 1 c1 c2
1 2 a3 a4
SCOTT@test> select * from t2err;
ID SEQ C1 C2
---------- ---------- -------------------- --------------------
3 2 c3
--注意这里使用了all，要求记录走遍全部条件分支，满足就插入。使用first表示满足1个条件就插入，剩下的不再判断。
--btw ：以前也写过一篇http://blog.itpub.net/267265/viewspace-713115/，这种技巧不常用，有点忘记了。
--实际上还可以选择有条件的插入，特此做一个记录。
