1、建测试表

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

);

2、插入测试数据

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;

3、insert all/first语句示例

(1)、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;

(2)、insert all-带条件

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;

(3)、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;

4、数据结果查看

truncate table edw_int_1;

truncate table edw_int_2;

select * from edw_int;

select * from edw_int_1;

select * from edw_int_2;

5、all与first的区别

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

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

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

ITPub博客

insert all用法讲解与实例

最新文章