您当前的位置： 首页 >  oracle

Oracle中的insert/insert all/insert first

发布时间：2019-09-03 17:19:26 ，浏览量：0

Oracle中的insert/insert all/insert first

1、概念

Oracle 中insert all 是指把 同一批 数据插入到 不同的表 中。

假如，现在有个需求，把表 t 的中数据分别插入到 t1、t2，如果你不知道 insert all， 你可能会使用 insert 插入 2 次，例如：

INSERT INTO t1(object_id, object_name) SELECT * FROM t;

INSERT INTO t2(object_id, object_name) SELECT * FROM t;

COMMIT;

其实，以上这样的写法，不一定正确。在 两次 insert 过程中， 有可能 t 表的数据发生了改变，从而导致 t1、t2 表得到的数据不一样，正确的写法是用 insert all

INSERT ALL

   INTO t1(object_id, object_name)

   INTO t2(object_id, object_name)

SELECT * FROM t;

COMMIT;

1.1 思维导图

在这里插入图片描述

1.2 基础数据

DROP TABLE stu; -- if exists

CREATE TABLE stu (

   s_id NUMBER,

   s_xm VARCHAR2(30)

);

INSERT INTO stu(s_id, s_xm) VALUES (1, '小游子');

INSERT INTO stu(s_id, s_xm) VALUES (2, '小优子');

INSERT INTO stu(s_id, s_xm) VALUES (3, '小倩子');

COMMIT;

2、实例分析

2.1 无条件插入

CREATE TABLE stu1 AS SELECT * from stu WHERE 1 = 2;

CREATE TABLE stu2 AS SELECT * from stu WHERE 1 = 2;

INSERT ALL

   INTO stu1(s_id, s_xm)

   INTO stu2(s_id, s_xm)

SELECT * FROM stu;

COMMIT;

SELECT * FROM stu1;

SELECT * FROM stu2;

在这里插入图片描述

2.2 有条件插入

2.2.1 insert first

对于每一行数据，只插入到 第一个when 条件成立的表，不继续检查其他条件。

DROP TABLE stu1; -- if exists

DROP TABLE stu2; -- if exists

CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;

CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;

INSERT FIRST 

  WHEN s_id <= 2 THEN 

     INTO stu1 (s_id, s_xm) 

  WHEN s_id >= 2 THEN -- 注意 2 是重复的哦

     INTO stu2 (s_id, s_xm)

SELECT * FROM stu;

SELECT * FROM stu1;

SELECT * FROM stu2;

测试结果：

在这里插入图片描述

2.2.2 insert all

对于每一行数据，对 每一个when 条件都进行检查，如果满足条件就执行插入操作。

-- 仅将上述 FIRST 改为 ALL

DROP TABLE stu1; -- if exists

DROP TABLE stu2; -- if exists

CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;

CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;

INSERT ALL

  WHEN s_id <= 2 THEN 

     INTO stu1 (s_id, s_xm) 

  WHEN s_id >= 2 THEN -- 注意 2 是重复的哦

     INTO stu2 (s_id, s_xm)

SELECT * FROM stu;

SELECT * FROM stu1;

SELECT * FROM stu2;

测试结果：

在这里插入图片描述

3、其他操作

3.1 行转列插入

DROP TABLE stu1; -- if exists

DROP TABLE stu2; -- if exists

CREATE TABLE stu1 (

  s_id NUMBER,

  s_xm VARCHAR(30) -- 数据类型要一致哦

);

CREATE TABLE stu2 (

  s_id  NUMBER,

  s_xm1 VARCHAR2(30),

  s_xm2 VARCHAR2(30),

  s_xm3 VARCHAR2(30)

);

INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (1, 'a1', 'b1', 'c1');

INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (2, 'a2', 'b2', 'c2');

INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (3, 'a3', 'b3', 'c3');

INSERT ALL

  INTO stu1 VALUES (s_id, s_xm1) 

  INTO stu1 VALUES (s_id, s_xm2)

  INTO stu1 VALUES (s_id, s_xm3)

SELECT * FROM stu2;

SELECT * FROM stu1;

测试结果：

在这里插入图片描述

————————————————

insert all官方文档解释：

/*

多表插入语句的限制条件:

 1. 只能对表执行多表插入语句，不能对视图或物化视图执行；

 2. 不能对远端表执行多表插入语句；

 3. 不能使用表集合表达式；

 4. 不能超过999个目标列；

 5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；

 6. 多表插入语句不支持执行计划稳定性；

 7. 多表插入语句中的子查询不能使用序列。

*/

第一步：准备数据

CREATE TABLE t1(product_id NUMBER, product_name VARCHAR2(80),MONTH NUMBER);  

INSERT INTO t1 VALUES(111, '苹果',1);  

INSERT INTO t1 VALUES(222, '橘子',1);  

INSERT INTO t1 VALUES(333, '香蕉',1);  

第二部：insert all

INSERT ALL  

    INTO t2   

    VALUES (product_id, product_name,MONTH)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+1)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+2)  

    INTO t2   

    VALUES (product_id, product_name,MONTH+3)   

SELECT product_id, product_name, MONTH  

FROM t1;  

这里的表t2的数据下面会用到

---------------------------------------------------------------------------

第三步：有条件的insert all

insert all 

when month=1 then

into t3

when month=2 then 

into t4

else 

into t5

select product_id,product_name,month from t2;

commit;

-------------------------------------------------------------------------

第四部：insert first （需要讲解下）

insert first 

when month=1 then

into t3 VALUES(product_id,product_name,month)  

when product_id=111 then 

into t4 VALUES(product_id,product_name,month)  

else 

into t5 VALUES(product_id,product_name,month)  

select product_id,product_name,month from t2;

commit;

--------------------------------------------------------------------------

根据第二部，t2表的数据做测试。

查询t4表发现:少了一条 111 苹果 1

111 苹果2

111 苹果 3

111 苹果 4

总结：insert first 当数据满足第一when 条件的时候，不会作用在下面的when条件了，

简而言之:当一条数据满足条件后，即使满足后面的条件也不会插入数据库。

正常情况：如果数据满足所有的when条件，每个表都会插入该条数据，insert first 数据只会插入一个表。

无条件的插入

Oracle中的insert all是指把同一批数据插入到不同的表中，假如如今有个需求：把t表中的数据分别插入t1,t2，假设你不知道insert all，你可能会使用insert插入2次，例如以下所看到的： 

insert  into t1(object_name,object_id) select * from t;
insert  into t2(object_name,object_id) select * from t;
commit;

其实，以上这样的写法是错误的，由于在两次insert的过程中，t表的数据有可能已经发生了变化，也就是说，t1,t2表得到的数据有可能不一样，正确的写法应该是採用insert all： 

insert all
into t1(object_name,object_id)
into t2(object_name,object_id)
select * from t;
commit;

有条件的插入
insert first/all 是对每一行来进行推断 两者差别： insert first：对于每一行数据，仅仅插入到第一个when条件成立的表，不继续检查其它条件。 insert all ： 对于每一行数据，对每个when条件都进行检查，假设满足条件就运行插入操作。 

看以下的样例： 

--insert first
--前面等于1的条件被<=5含在内，FIRST就表示前面插入了，后面不会再插入了。
insert first
when object_id = 1 then
into t1(object_name,object_id)
when object_id <=5 then                      
into t2(object_name,object_id)
select * from t;
commit;
select * from t1;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
select * from t2;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
I_USER1                            2
CON$                               3
UNDO$                              4
C_COBJ#                            5
--insert all
insert all
when object_id = 1 then
into t1(object_name,object_id)
when object_id <=5 then                      
into t2(object_name,object_id)
select * from t;
commit;
SQL> select * from t1;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
SQL> select * from t2;
OBJECT_NAME                OBJECT_ID
--------------------------------- ---
ICOL$                              1
I_USER1                            2
CON$                               3
UNDO$                              4
C_COBJ#                            5

行转列插入
insert all还能够实现行转列插入： 
select * from sales_source_data;
EMPLOYEE_ID    WEEK_ID  SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI
----------- ---------- ---------- ---------- ---------- ---------- ----------
        176          6       2000       3000       4000       5000       6000
insert all
into sales_info values(employee_id,week_id,sales_mon)
into sales_info values(employee_id,week_id,sales_tue)
into sales_info values(employee_id,week_id,sales_wed)
into sales_info values(employee_id,week_id,sales_thur)
into sales_info values(employee_id,week_id,sales_fri)
select employee_id,week_id,sales_mon,sales_tue,
sales_wed,sales_thur,sales_fri
from sales_source_data;
select * from sales_info;
EMPLOYEE_ID       WEEK      SALES
----------- ---------- ----------
        176          6       2000
        176          6       3000
        176          6       4000
        176          6       5000
        176          6       6000
多表插入语句的限制条件  1. 仅仅能对表运行多表插入语句，不能对视图或物化视图运行；  2. 不能对远端表运行多表插入语句；  3. 不能使用表集合表达式；  4. 不能超过999个目标列；  5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行运行；  6. 多表插入语句不支持运行计划稳定性；  7. 多表插入语句中的子查询不能使用序列。

About Me

........................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号（ xiaomaimiaolhr）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文CSDN地址： https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号： 230161599 、618766405

● 微 信群：可加我微 信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友 （ 646634621 ），注明添加缘由

● 于 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

● 最新修改时间：2019-09-01 06:00 ~ 2019-09-31 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

........................................................................................................................

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ xiaomaimiaolhr）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2655888/，如需转载，请注明出处，否则将追究法律责任。

关注
打赏
1688896170
查看更多评论

暂无认证

  • 0浏览

    0关注

    108697博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文

[ 申请 ]友情链接：

快连vpn 快连 快连vpn 搜外友链 笔趣阁 爱思助手 ClashX教程 绘画宝宝 配音宝宝
立即登录/注册

微信扫码登录

0.0513s