Oracle多表插入insert all/insert first的区别

--新建三张表

CREATE TABLE example
(
	ast_NO            VARCHAR2(40 BYTE) NOT NULL,
	ast_SUB_NO        VARCHAR2(4 BYTE) NOT NULL,
	astD_REPAY_INT     NUMBER(22, 2),
	ast_PERIOD        NUMBER(4) NOT NULL
) ;
   
CREATE TABLE example_1
(
	ast_NO             VARCHAR2(40 BYTE) NOT NULL,
	ast_SUB_NO         VARCHAR2(4 BYTE) NOT NULL,
	astD_REPAY_INT     NUMBER(22, 2),
	ast_PERIOD         NUMBER(4) NOT NULL
) ;
   
CREATE TABLE example_2
(
	ast_NO             VARCHAR2(40 BYTE) NOT NULL,
	ast_SUB_NO         VARCHAR2(4 BYTE) NOT NULL,
	astD_REPAY_INT     NUMBER(22, 2),
	ast_PERIOD         NUMBER(4) NOT NULL
) ;  

--向表中插入数据

INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20003874', '2104', 3126.5, 7);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20003874', '2104', 3290.76, 6);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20003874', '2104', 3454.06, 5);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20003874', '2104', 3616.41, 4);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20017143', '2104', 2350.86, 0);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20017143', '2104', 3566.55, 0);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20018273', '2104', 1639.46, 0);   
INSERT INTO example   
  (ast_no, ast_sub_no, astd_repay_int, ast_period)   
VALUES   
  ('20018273', '2104', 2080.49, 0);   
COMMIT;

insert all:

insert all   
into EXAMPLE_1(ast_no,ast_sub_no,astd_repay_int,ast_period)   
values(ast_no,ast_sub_no,astd_repay_int,ast_period)   
into EXAMPLE_2(ast_no,ast_sub_no,astd_repay_int,ast_period)    
values(ast_no,'1234',ast_period)   
select ast_no,ast_sub_no,astd_repay_int,ast_period from EXAMPLE;   
commit; 

--带条件的insert all语句

insert all   
when ast_period=0 then    
into EXAMPLE_1(ast_no,ast_sub_no,astd_repay_int,ast_period)    
values(ast_no,ast_sub_no,astd_repay_int,ast_period)   
else  
into EXAMPLE_2(ast_no,ast_sub_no,astd_repay_int,ast_period)   
values(ast_no,ast_sub_no,astd_repay_int,ast_period)   
select ast_no,ast_sub_no,astd_repay_int,ast_period from EXAMPLE;   
commit;  

insert first：

INSERT FIRST WHEN AST_PERIOD = 0 THEN INTO EXAMPLE_1
  (AST_NO, AST_SUB_NO, ASTD_REPAY_INT, AST_PERIOD)
VALUES
  (AST_NO, AST_SUB_NO, ASTD_REPAY_INT, AST_PERIOD) WHEN AST_SUB_NO = '2104' THEN INTO EXAMPLE_2
  (AST_NO, AST_SUB_NO, ASTD_REPAY_INT, AST_PERIOD)
VALUES
  (AST_NO, AST_SUB_NO, ASTD_REPAY_INT, AST_PERIOD)
  SELECT AST_NO, AST_SUB_NO, ASTD_REPAY_INT, AST_PERIOD FROM EXAMPLE;

COMMIT;

insert all和insert first的差别就在于：

        all与first的区别

        all:不考虑先后关系，只要满足条件，就全部插入；

        first:考虑先后关系，如果有数据满足第一个when条件又满足第二个when条件，则执行第一个then插入语句，第二个then就不插入第一个then已经插入过的数据了。

        其区别也可描述为，all只要满足条件，可能会作重复插入；first首先要满足条件，然后筛选，不做重复插入。