当前位置: 首页 > news >正文

oracle插入多表(insert all/first)

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;
http://www.lryc.cn/news/119290.html

相关文章:

  • 工业以太网交换机-SCALANCE X200 环网组态
  • 利用 Splashtop Enterprise 改善公司的网络安全
  • mqbroker.cmd闪退(mqnamesrv.cmd能正常启动)
  • LeetCode--HOT100题(26)
  • HTTP 请求方法详解
  • 孤立随机森林(Isolation Forest)(Python实现)
  • 小程序如何自定义分享内容
  • SpringBoot整合WebSocket详解
  • 伪原创神码ai怎么样【php源码】
  • Air001基于Keil环境开发,使用airisp串口命令行烧录
  • kubernetes 中的事件(event)简介以及如何收集event和基于event告警
  • C++小游戏贪吃蛇源码
  • 【密码学】穴居人密码
  • neo4j的CQL命令实例演示
  • vue3+ts使用antv/x6
  • wsl1 ubuntu通过宿主机代理连接外网
  • ubuntu20.04 opencv4.2 安装笔记
  • ubuntu安装nginx以及php的部署
  • IntelliJ IDEA 2021/2022关闭双击shift全局搜索
  • HTML 元素中的name 属性
  • 快速上手React:从概述到组件与事件处理
  • K8S系列文章之 离线安装自动化工具Ansible
  • mysql8.0.3集群搭建
  • vue中router路由的原理?两种路由模式如何实现?(vue2) -(上)
  • 消息队列(3) -封装数据库的操作
  • PostgreSQL中根据时间段范围查询数据,如19:29:10到20:29:10范围内的数据,排除年月日
  • 【二分+贪心】CF1665 C
  • 【Wamp】安装 | 局域网内设备访问
  • 【golang】类型推断和变量重声明
  • “算法详解”系列第3卷贪心算法和动态规划出版