正文

oracle插入多表(insert all/first)

IP云V管理员/2022-12-01/2 阅读

12/01

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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

6、 利用insert all实现行列转换（insert all的旋转功能）

1、建一行多列表及测试数据插入
create table week_bal(id int,w1_bal number,w2_bal number,w3_bal number,w4_bal number,w5_bal number);
insert into week_bal values(1,10.09,12.98,23.89,89.08,1098.01);
commit;
select * from week_bal;
2、建少列多行表
create table week_bal_new(id int,week int,bal number);
3、利用insert all语句插入
insert all
into week_bal_new(id,week,bal)values(id,1,w1_bal)
into week_bal_new(id,week,bal)values(id,2,w2_bal)
into week_bal_new(id,week,bal)values(id,3,w3_bal)
into week_bal_new(id,week,bal)values(id,4,w4_bal)
into week_bal_new(id,week,bal)values(id,5,w5_bal)
select id,w1_bal,w2_bal,w3_bal,w4_bal,w5_bal from week_bal;
select * from week_bal_new;

相关阅读

目录[+]