Oracle插入之 insert all、insert first介绍
利用insert first/all使得INSERT语句可以同时插入多张表,还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。
insert first:对于每一行数据,只插入到第一个when条件成立的表,不继续检查其他条件。
insert all:对于每一行数据,对每一个when条件都进行检查,如果满足条件就执行插入操作。create table edw_int (
agmt_no varchar2(40 byte) not null,
agmt_sub_no varchar2(4 byte) not null,
need_repay_int number(22,2),
curr_period number(4) not null
);
create table edw_int_1 (
agmt_no varchar2(40 byte) not null,
agmt_sub_no varchar2(4 byte) not null,
need_repay_int number(22,2),
curr_period number(4) not null
);
create table edw_int_2 (
agmt_no varchar2(40 byte) not null,
agmt_sub_no varchar2(4 byte) not null,
need_repay_int number(22,2),
curr_period number(4) not null
);
-- 插入数据
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3126.5, 7);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3290.76, 6);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3454.06, 5);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20003874', '2104', 3616.41, 4);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20017143', '2104', 2350.86, 0);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20017143', '2104', 3566.55, 0);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20018273', '2104', 1639.46, 0);
insert into edw_int (agmt_no, agmt_sub_no, need_repay_int, curr_period) values ('20018273', '2104', 2080.49, 0);
COMMIT;
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;
commit;
删除完数据继续测试 加上条件when then else
truncate table edw_int_1;
truncate table edw_int_2;insert all
when curr_period = 0 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 firstinsert first
when curr_period = 0 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;