[20150115]insert多个表.txt

[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/，这种技巧不常用，有点忘记了。
--实际上还可以选择有条件的插入，特此做一个记录。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1403737/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1403737/