oracle的多表插入

 

Q5 ，oracle 的多表插入操作。在业务处理过程中，经常会碰到将业务数据按照条件分别插入不同的数据表的问题，按照传统的处理方式，需要分条件执行多次检索后分别插入不同的表单，这样因为执行了重复的检索造成cpu和内存的浪费，从oracle9i开始引入了insert all关键字支持将某张表的数据同时插入多张表单。语法如下：

Insert all Insert_into_clause [value_clause] subquery;

Insert conditional_insert_clause subquery;

如上所示，insert_into_clause用于指定insert子句；value clause用于指定值子句；subquery用于指定提供数据的子查询；condition_insert_clause用于指定insert条件子句。

当使用all操作符执行多表插入时，在每个条件子句上都要执行into子句后的子查询，并且条件中使用的列必须在插入和子查询的结果集中：

-- 创建测试用表

create table tdate(

id varchar2 ( 10 ),

name varchar2 ( 20 ),

birthday date default sysdate

);

-- 插入数据

insert into tdate values ( 1 , 'zhangsan' ,to_date( '1980-05-10' , 'YYYY-MM-DD' ));

insert into tdate values ( 1 , 'zhangsan' ,to_date( '1980-05-10' , 'YYYY-MM-DD' ));

insert into tdate values ( 2 , 'lisi' ,to_date( '1980-05-10' , 'YYYY-MM-DD' ));

insert into tdate values ( 3 , 'wangwu' , default );

insert into tdate( id , name ) values ( 4 , 'zhangsan' );

commit ;

-- 创建接收用测试表

create table tdate1 as select * from tdate where 1 = 0 ;

create table tdate2 as select * from tdate where 1 = 0 ;

commit ;

-- 使用 all 关键字执行多表插入操作

insert all

when birthday > '01-1 月 -08' then into tdate1

when birthday < '01-1 月 -08' then into tdate2

when name = 'zhangsan' then into tdate1

when name = 'lisi' then into tdate2

select * from tdate;

在上述操作语句中，如果原表tdate中存在既满足 birthday > '01-1 月 -08' 又满足 name = 'zhangsan' 的数据，那么将执行两次插入。而使用 first 关键字就可以避免这个问题。使用 first 关键字时，如果有记录已经满足先前条件，并且已经被插入到某个表单中（未必非要是同一个表），那么该行数据在后续插入中将不会被再次使用。也就是说使用 first 关键字，原表每行数据按照执行顺序只会被插入一次。

insert first

when birthday > '01-1 月 -08' then into tdate1

when birthday < '01-1 月 -08' then into tdate2

when name = 'zhangsan' then into tdate1

when name = 'lisi' then into tdate2

select * from tdate;