oracle插入多表(insert all/first)
来源:互联网 发布:json打印出来 编辑:程序博客网 时间:2024/04/29 16:25
1、建测试表
[c-sharp]view plaincopyprint?
- 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、插入测试数据
[c-sharp]view plaincopyprint?
- 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-不带条件
[c-sharp]view plaincopyprint?
- 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-带条件
[c-sharp]view plaincopyprint?
- 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-带条件
[c-sharp]view plaincopyprint?
- 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、数据结果查看
[c-sharp]view plaincopyprint?
- 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的旋转功能)
[c-sharp]view plaincopyprint?
- 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;
整理自网络
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tanzuai
IT群:69254049;
------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tanzuai
IT群:69254049;
0 0
- oracle插入多表(insert all/first)
- Oracle插入多表(insert all/first)
- Oracle插入多表(insert all/first)
- oracle插入多表(insert all/first)
- Oracle插入多表(insert all/first)
- Oracle多表插入insert all/insert first的区别
- Oracle插入之 insert all、insert first
- Oracle实现多表插入-INSERT ALL
- INSERT FIRST && INSERT ALL(按条件进行多表插入)
- oracle 多行插入 insert all
- Oracle 关于INSERT FIRST和INSERT ALL
- Oracle Insert first & Insert all 的区别
- oracle:INSERT ALL和INSERT FIRST
- oracle:INSERT ALL和INSERT FIRST
- Oracle 旋转insert(insert all/first)
- insert all insert first
- ORACLE中的INSERT ALL和INSERT FIRST使用
- oracle insert all 和insert first 的区别
- java 23种常用设计模式之桥接模式(Bridge)
- 作为码农, 如何快速掌握一门技术
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (
- 设置谷歌host地址
- Limbo- 简单访问远程数据库
- oracle插入多表(insert all/first)
- Spring+Hibernate大批量处理数据
- 黑马程序员-IO-Encoding
- 环融科技携手梅花网,与你一起讨论快消品企业如何管理好营销费用
- JUnit + Mockito 单元测试(二)
- Linux dd和cpio命令
- java 中 重写equals方法的用处
- linux 下 python多版本安装
- win8.1 DOS开启wifii共享