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首先要满足条件,然后筛选,不做重复插入。