Oracle插入之 insert all、insert first，oracleinsert

Oracle插入之 insert all、insert first，oracleinsert

利用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 first

insert 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;