ITPub博客

首页 > 数据库 > Oracle > Oracle中的insert/insert all/insert first

Oracle中的insert/insert all/insert first

原创 Oracle 作者:lhrbest 时间: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1352
  • 访问量
    8133545