真是惭愧，以前认为Oracle的SQL语法没有懂各个具体参数么，所有使用格式都已经不在
话下了。结果看了1z0-047的内容，发现自己有点过于狂妄了。
这里要讲的都是insert的语法。
通常情况下，我们看到的insert的语法不外乎以下几种：
insert into [table_name]([column_list]) values([values_list]);--最简单的
insert into [table1]([column_list]) select ([column_list]) from [table2];--从table2中取数据插入到table1中
insert into [table_name] values row; --这里row是一个关键字，用在forall/for等的insert循环中
根据我以往的经验，总结下来，应该就只有这么三种了。
但是实际上Oracle还支持这样的语法：
第一种：
带检查的插入语句
insert into (select a,b,c from test1 t where t.id=10 with check option) values(1,2,3);
insert into (select a,b,c from test1 t where t.id=10 with check option) select 1,2,3 from dual;
该语法的含义是：在test1表中插入一条记录，该记录的id必须为10（因为有with check option选项）。
第二种：
insert first / insert all语法
用于向多个表插入数据：
insert first时首先根据第一个when进行检索，将结果插入第一条件指定的表。
当遇到第二个when时，将第一个when中检索结果集外的数据与第二个when做条件匹配
然后将匹配到的数据插入第二个条件指定的表。
以此类推。例子（转自网络）：
create table table_storage
(
table_name varchar2(30),
tablespace_name varchar2(30),
pct_free number,
pct_used number,
ini_trans number,
max_trans number,
initial_extent number,
next_extent number,
min_extents number,
max_extents number,
pct_increase number,
freelists number,
freelist_groups number
);
(
table_name varchar2(30),
tablespace_name varchar2(30),
pct_free number,
pct_used number,
ini_trans number,
max_trans number,
initial_extent number,
next_extent number,
min_extents number,
max_extents number,
pct_increase number,
freelists number,
freelist_groups number
);
create table table_stat
(
table_name varchar2(30),
num_rows number,
blocks number,
empty_blocks number,
avg_space number,
chain_cnt number,
avg_row_len number
);
(
table_name varchar2(30),
num_rows number,
blocks number,
empty_blocks number,
avg_space number,
chain_cnt number,
avg_row_len number
);
insert all
into table_storage values(table_name, tablespace_name, pct_free, pct_used, ini_trans,
max_trans, initial_extent, next_extent, min_extents, max_extents,
pct_increase, freelists, freelist_groups)
into table_stat values(table_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len)
select * from user_tables;
select * from table_storage
select * from table_stat
create table table_all(table_name varchar2(30));
create table index_all(index_name varchar2(30));
create table object_other(object_name varchar2(30), object_type varchar2(30));
insert all
when object_type = 'TABLE' then
into table_all values(object_name)
when object_type = 'INDEX' then
into index_all values(object_name)
when object_type not in('TABLE','INDEX') then
into object_other values(object_name, object_type)
select * from user_objects o;
when object_type = 'TABLE' then
into table_all values(object_name)
when object_type = 'INDEX' then
into index_all values(object_name)
when object_type not in('TABLE','INDEX') then
into object_other values(object_name, object_type)
select * from user_objects o;
select * from table_all;
truncate table table_all;
truncate table tablespace_users ;
truncate table tablespace_users ;
create table tablespace_users(name varchar2(30), type varchar2(30));
insert all
when (segment_type = 'TABLE') then
into table_all values (segment_name)
when (tablespace_name = 'LYONTBS') then
into tablespace_users values (segment_name, segment_type)
select segment_name, segment_type, tablespace_name
from user_segments;
select * from table_all ;
select * from tablespace_users;
select * from tablespace_users;
insert first
when (segment_type = 'TABLE') then
into table_all values (segment_name)
when (tablespace_name = 'LYONTBS') then
into tablespace_users values (segment_name, segment_type)
select segment_name, segment_type, tablespace_name
from user_segments;
when (segment_type = 'TABLE') then
into table_all values (segment_name)
when (tablespace_name = 'LYONTBS') then
into tablespace_users values (segment_name, segment_type)
select segment_name, segment_type, tablespace_name
from user_segments;
由于占用版面太大，只将语句放上来。