oracle的多表insert操作

古城微笑少年丶 2022-06-16 02:08 327阅读 0赞

### oracle的多表insert操作   ###

2011-05-18 21:03:04|  分类： [ Oracle SQL][Oracle SQL] |  标签： |字号大中小 订阅

转自：http://blog.csdn.net/47522341/archive/2009/06/11/4259875.aspx

在一般操作中,我们可能会碰到一些相同或者雷同的数据源要插入到不同的数据表中的情况. 在oracle9i之前的版本中, 我们必须执行多次insert语句, 因此, 为了填充不同的表, 就在源表上添加了一些不必要的IO操作. Oracle9i引入了多表insert操作, 采用以下三个形式.

Unconditional(无条件的): 没有任何限制地向多个表中插入给定表的数据;

Pivoting: 用一个非规范化的结构把数据插入到一个或多个表中.

Conditional(有条件的): 根据已有的特定条件对每个约束进行控制.

### 测试环境建立 ###

**create table** z\_test(**id integer**, **name varchar2**(100));  
**create table** z\_test1(**id integer**, **name varchar2**(100));  
**create table** z\_test2(**id integer**);  
**create table** z\_test3( **name varchar2**(100));  
  
**insert into** z\_test  
**select rownum**, object\_name  
**from** dba\_objects do  
**where rownum** < 11;  
**commit**;  
/\*  
**drop table** z\_test;  
**drop table** z\_test1;  
**drop table** z\_test2;  
**drop table** z\_test3;

\*/

### 一, 无条件insert all ###

INSERT ALL

insert\_into\_clause values\_clause\_1

\[insert\_into\_clause values\_clause\_2\]

……

Subquery；

\---------------------------------------------------------------------

1、指定所有跟随着的多表 insert\_into\_clauses 执行无条件的多表插入；

2、对于每个由子查询返回的行， Oracle 服务器执行每一个 insert\_into\_clause一次。

**truncate table** z\_test1;  
**truncate table** z\_test3;  
**truncate table** z\_test2;  
  
**insert all  
into** z\_test1(**id**,**name**) **values**(**id**,**name**)  
**into** z\_test2(**id**) **values**(**id**)  
**into** z\_test3(**name**) **values**(**name**)  
**select** \* **from** z\_test;  
**commit**;  
  
**select** \* **from** z\_test1;

<table> 
 <tbody> 
  <tr> 
   <td> <p><strong>ID</strong></p> </td> 
   <td> <p><strong>NAME</strong></p> </td> 
  </tr> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p>ACCESS$</p> </td> 
  </tr> 
  <tr> 
   <td> <p>2</p> </td> 
   <td> <p>AGGXMLIMP</p> </td> 
  </tr> 
  <tr> 
   <td> <p>3</p> </td> 
   <td> <p>AGGXMLINPUTTYPE</p> </td> 
  </tr> 
  <tr> 
   <td> <p>4</p> </td> 
   <td> <p>ALL_ALL_TABLES</p> </td> 
  </tr> 
  <tr> 
   <td> <p>5</p> </td> 
   <td> <p>ALL_APPLY</p> </td> 
  </tr> 
  <tr> 
   <td> <p>6</p> </td> 
   <td> <p>ALL_APPLY_CONFLICT_COLUMNS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>7</p> </td> 
   <td> <p>ALL_APPLY_DML_HANDLERS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>8</p> </td> 
   <td> <p>ALL_APPLY_ERROR</p> </td> 
  </tr> 
  <tr> 
   <td> <p>9</p> </td> 
   <td> <p>ALL_APPLY_KEY_COLUMNS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>10</p> </td> 
   <td> <p>ALL_APPLY_PARAMETERS</p> </td> 
  </tr> 
 </tbody> 
</table>

**select** \* **from** z\_test2;

**select** \* **from** z\_test3;

<table> 
 <tbody> 
  <tr> 
   <td> <p><strong>NAME</strong></p> </td> 
  </tr> 
  <tr> 
   <td> <p>ACCESS$</p> </td> 
  </tr> 
  <tr> 
   <td> <p>AGGXMLIMP</p> </td> 
  </tr> 
  <tr> 
   <td> <p>AGGXMLINPUTTYPE</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_ALL_TABLES</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_CONFLICT_COLUMNS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_DML_HANDLERS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_ERROR</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_KEY_COLUMNS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_PARAMETERS</p> </td> 
  </tr> 
 </tbody> 
</table>

### 二, 条件insert all ###

INSERT ALL

WHEN condition THEN insert\_into\_clause values\_clause

\[WHEN condition THEN\] \[insert\_into\_clause values\_clause\]

……

\[ELSE\] \[insert\_into\_clause values\_clause\]

Subquery；

1、指定 conditional\_insert\_clause 来执行一个条件多表插入；

2、Oracle 服务器通过相应的 WHEN 条件过滤每一个 insert\_into\_clause，确定是否执行这个 insert\_into\_clause；

3、一个单个的多表插入语句可以包含最多 127 个 WHEN 子句。

truncate table z\_test1;  
truncate table z\_test3;  
truncate table z\_test2;  
  
insert all  
when mod(id,3) = 1 then into z\_test1(id,name) values(id,name)  
when mod(id,2) = 1 then into z\_test2(id) values(id)  
when mod(id,3) = 0 then into z\_test3(name) values(name)  
select \* from z\_test;  
commit;  
  
**select** \* **from** z\_test1;

**select** \* **from** z\_test2;

**select** \* **from** z\_test3;

<table> 
 <tbody> 
  <tr> 
   <td> <p><strong>NAME</strong></p> </td> 
  </tr> 
  <tr> 
   <td> <p>AGGXMLINPUTTYPE</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_CONFLICT_COLUMNS</p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_KEY_COLUMNS</p> </td> 
  </tr> 
 </tbody> 
</table>

### 三, 条件insert first ###

INSERT FIRST

WHEN condition THEN insert\_into\_clause values\_clause

\[WHEN condition THEN\] \[insert\_into\_clause values\_clause\]

……

\[ELSE\] \[insert\_into\_clause values\_clause\]

Subquery；

\-----------------------

1、Oracle 服务器对每一个出现在语句顺序中的 WHEN 子句求值；

2、如果第一个 WHEN 子句的值为 true，Oracle 服务器对于给定的行执行相应的 INTO 子句，并且跳过后面的 WHEN 子句(后面的when语句都不再考虑满足第一个When子句的记录，即使该记录满足when语句中的条件)。

truncate table z\_test1;  
truncate table z\_test3;  
truncate table z\_test2;  
  
insert first  
when mod(id,3) = 1 then into z\_test1(id,name) values(id,name)  
when mod(id,2) = 1 then into z\_test2(id) values(id)  
when mod(id,3) = 0 then into z\_test3(name) values(name)  
select \* from z\_test;  
commit;  
  
**select** \* **from** z\_test1;

**select** \* **from** z\_test2;

**select** \* **from** z\_test3;

<table> 
 <tbody> 
  <tr> 
   <td> <p><strong>NAME</strong></p> </td> 
  </tr> 
  <tr> 
   <td> <p>ALL_APPLY_CONFLICT_COLUMNS</p> </td> 
  </tr> 
 </tbody> 
</table>

### 四, 小结 ###

a、你只能在表而不能在视图上执行多表插入；

b、你不能执行一个多表插入到一个远程表；

c、在执行一个多表插入时，你不能指定一个表集合表达式；

d、在一个多表插入中，所有的 insert\_into\_clauses 不能组合指定多于 999 个目列；

e、只有当所有insert\_into\_clauses中的表数据都没有发生更新时，Rollback才会起作用

[Oracle SQL]: http://liusnowc.blog.163.com/blog/#m=0&t=1&c=fks_084064087082084071084082082095087083086075083086094074