Oracle插入之 insert all、insert first

最新推荐文章于 2025-06-18 21:05:10 发布

X-rapido

最新推荐文章于 2025-06-18 21:05:10 发布

阅读量3.9k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： Oracle

本文链接：https://blog.youkuaiyun.com/xiaokui_wingfly/article/details/45115299

数据库专栏收录该内容

44 篇文章

订阅专栏

本文介绍了如何使用insertall和insertfirst语句在Oracle数据库中同时向多个表插入数据，并通过示例展示了如何根据条件选择性地插入数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查看原文：http://www.ibloger.net/article/245.html

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