insert all/first 使用与区别简介
来源:互联网 发布:锤子手机usb网络共享 编辑:程序博客网 时间:2024/06/10 19:17
insert all与insert first多表插入数据需要注意和说明的地方:
一、针对insert all
只能对表执行多表插入语句,不能对视图或物化视图执行;
不能对远端表执行多表插入语句;
不能使用表集合表达式;
不能超过999个目标列;
在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时,多表插入语句不能并行执行;
多表插入语句不支持执行计划稳定性;
多表插入语句中的子查询不能使用序列。
二、insert all与insert first 有条件与无条件的区别
all:不考虑先后关系,只要满足条件,就全部插入;
first:考虑先后关系,如果有数据满足第一个when条件又满足第二个when条件,则执行第一个then插入语句,第二个then就不插入第一个then已经插入过的数据了。
其区别也可描述为,all只要满足条件,可能会作重复插入;first首先要满足条件,然后筛选,不做重复插入
同时,insert all可以实现行列转换功能(insert all的旋转功能)
具体示例,如下:
create table edw_int
( agmt_no varchar2(40) not null,
agmt_sub_no varchar2(4) not null,
need_repay_int number(22,2),
curr_period number(4) not null
);
create table edw_int_2 as select * from edw_int;
insert into edw_int select * from edw_int;
select * from edw_int;
--insert all 不带条件
insert all
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
into edw_int_2(agmt_no,agmt_sub_no,curr_period)
values(agmt_no,'1234',curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
select * from edw_int;
select * from edw_int_1;
select * from edw_int_2;
truncate table edw_int_1;
truncate table edw_int_2;
--插入一条测试数据
insert into edw_int values('200012862','2104',1639.04,0);
--insert all 带条件
insert all
when curr_period=2 then
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
else
into edw_int_2(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
commit;
--insert first 带条件
insert first
when curr_period=7 then
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
when agmt_sub_no='2104' then
into edw_int_2(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
commit;
----利用insert all 实现行列转换(insert all 的旋转功能)
----建测试表
create table week_bal(id int,w1_bal number,w2_bal number,w3_bal number,w4_bal number,w5_bal number);
insert into week_bal values(1,10.09,12.98,23.89,89.08,1098.01);
commit;
select * from week_bal;
create table week_bal_new(id int,week int,bal number);
select * from week_bal_new;
----实现行列转换
insert all
into week_bal_new(id,week,bal)values(id,1,w1_bal)
into week_bal_new(id,week,bal)values(id,2,w2_bal)
into week_bal_new(id,week,bal)values(id,3,w3_bal)
into week_bal_new(id,week,bal)values(id,4,w4_bal)
select id,w1_bal,w2_bal,w3_bal,w4_bal from week_bal;
select * from week_bal_new;
一、针对insert all
只能对表执行多表插入语句,不能对视图或物化视图执行;
不能对远端表执行多表插入语句;
不能使用表集合表达式;
不能超过999个目标列;
在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时,多表插入语句不能并行执行;
多表插入语句不支持执行计划稳定性;
多表插入语句中的子查询不能使用序列。
二、insert all与insert first 有条件与无条件的区别
all:不考虑先后关系,只要满足条件,就全部插入;
first:考虑先后关系,如果有数据满足第一个when条件又满足第二个when条件,则执行第一个then插入语句,第二个then就不插入第一个then已经插入过的数据了。
其区别也可描述为,all只要满足条件,可能会作重复插入;first首先要满足条件,然后筛选,不做重复插入
同时,insert all可以实现行列转换功能(insert all的旋转功能)
具体示例,如下:
create table edw_int
( agmt_no varchar2(40) not null,
agmt_sub_no varchar2(4) not null,
need_repay_int number(22,2),
curr_period number(4) not null
);
create table edw_int_2 as select * from edw_int;
insert into edw_int select * from edw_int;
select * from edw_int;
--insert all 不带条件
insert all
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
into edw_int_2(agmt_no,agmt_sub_no,curr_period)
values(agmt_no,'1234',curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
select * from edw_int;
select * from edw_int_1;
select * from edw_int_2;
truncate table edw_int_1;
truncate table edw_int_2;
--插入一条测试数据
insert into edw_int values('200012862','2104',1639.04,0);
--insert all 带条件
insert all
when curr_period=2 then
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
else
into edw_int_2(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
commit;
--insert first 带条件
insert first
when curr_period=7 then
into edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
when agmt_sub_no='2104' then
into edw_int_2(agmt_no,agmt_sub_no,need_repay_int,curr_period)
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)
select agmt_no,agmt_sub_no,need_repay_int,curr_period from edw_int;
commit;
----利用insert all 实现行列转换(insert all 的旋转功能)
----建测试表
create table week_bal(id int,w1_bal number,w2_bal number,w3_bal number,w4_bal number,w5_bal number);
insert into week_bal values(1,10.09,12.98,23.89,89.08,1098.01);
commit;
select * from week_bal;
create table week_bal_new(id int,week int,bal number);
select * from week_bal_new;
----实现行列转换
insert all
into week_bal_new(id,week,bal)values(id,1,w1_bal)
into week_bal_new(id,week,bal)values(id,2,w2_bal)
into week_bal_new(id,week,bal)values(id,3,w3_bal)
into week_bal_new(id,week,bal)values(id,4,w4_bal)
select id,w1_bal,w2_bal,w3_bal,w4_bal from week_bal;
select * from week_bal_new;
0 0
- insert all/first 使用与区别简介
- insert all/first 使用与区别简介
- insert all与insert first
- INSERT FIRST和INSERT ALL的区别
- insert first&insert all的区别
- Oracle Insert first & Insert all 的区别
- insert first&insert all的区别
- INSERT ALL和INSERT FIRST的区别
- insert all insert first
- oracle insert all 和insert first 的区别
- oracle数据库insert all 和 insert first用法和区别
- Oracle多表插入insert all/insert first的区别
- ORACLE中的INSERT ALL和INSERT FIRST使用
- INSERT ALL和INSERT FIRST
- INSERT FIRST和INSERT ALL
- insert all/ insert first/ pivoting insert
- insert/insert all/insert first详解
- INSERT ALL和INSERT FIRST语法
- Android之蓝牙开发浅析
- Java字节码重写
- ORA-00060: Deadlock detected
- delphi property 实例(包含数组属性)
- 友盟SDK应用(二)------url分享
- insert all/first 使用与区别简介
- BadTokenException
- Leetcode – 树的遍历总结(java)
- Python刷题笔记(4)- 字符串重组
- jQuery treetable 3.2 + asp.net 应用
- Linux/Unix下的任务管理器-top命令
- assert_param的应用
- Android 接收C环境字符串斜杠零乱码
- Android 热补丁动态修复框架小结