Jcat
宠辱不惊，闲看庭前花开花落～～
posts - 173,comments - 67,trackbacks - 0
multi-insert: first vs all
Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. With a multitable insert, you can make a single pass through the source data and load the data into more than one table.

[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]

If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.


--test case
create table insert_test_case
(
id number(1)
)

create table insert_test01
(
id number(1)
)

create table insert_test02
(
id number(1)
)

create table insert_test03
(
id number(1)
)

insert into insert_test_case values(1)
insert into insert_test_case values(2)
insert into insert_test_case values(3)
insert into insert_test_case values(4)
insert into insert_test_case values(5)


--3 rows inserted
insert first 
  when id=1 then into insert_test01 values(id)
  when id=2 then into insert_test02 values(id)
  when id=3 then into insert_test03 values(id)
select id from insert_test_case

--3 rows inserted
insert all
  when id=1 then into insert_test01 values(id)
  when id=2 then into insert_test02 values(id)
  when id=3 then into insert_test03 values(id)
select id from insert_test_case


--1 rows inserted
insert first
  when id=1 then into insert_test01 values(id)
  when id=1 then into insert_test02 values(id)
  when id=1 then into insert_test03 values(id)
select id from insert_test_case

--3 rows inserted
insert all
  when id=1 then into insert_test01 values(id)
  when id=1 then into insert_test02 values(id)
  when id=1 then into insert_test03 values(id)
select id from insert_test_case
posted on 2006-12-13 13:32 Jcat 阅读(269) 评论(0)  编辑  收藏 所属分类: Database

只有注册用户登录后才能发表评论。
网站导航:
 
Copyright ©2024 Jcat Powered By博客园 模板提供：沪江博客