oracle中insert all的用法

zxrhhm

已于 2024-05-16 09:54:13 修改

阅读量490

点赞数 10

文章标签： oracle 数据库

于 2024-05-16 08:43:12 首次发布

本文链接：https://blog.csdn.net/zxrhhm/article/details/138941376

版权

1、简述

使用insert into语句进行表数据行的插入，但是oracle中有一个更好的实现方式：使用insert all语句。

insert all语句是oracle中用于批量写数据的。insert all分又为

无判断条件插入
有判断条件插入
有判断条件插入分为

Insert all when... 子句
insert first when... 子句

2、表和数据准备

--创建表
sqlplus hr/hr@192.168.0.207:1521/PROD01PDB
CREATE TABLE t_students(
   ID   NUMBER(4) primary key,
   NAME VARCHAR2(32),
   sex  VARCHAR2(3)
);
 
--删除表
drop table t_students;
drop table t_students_01;
drop table st_student_02;
 
--向t_students表中插入数据
INSERT INTO t_students(ID, NAME, sex) VALUES(881001, '成都', '女');
INSERT INTO t_students(ID, NAME, sex) VALUES(881002, '深圳', '男');
INSERT INTO t_students(ID, NAME, sex) VALUES(881003, '上海', '女');
commit;
 
HR@192.168.0.207:1521/PROD01PDB> col NAME format a20
HR@192.168.0.207:1521/PROD01PDB> select * from t_students;
        ID   NAME                 SEX
---------- -------------------- ---------
    881001   成都                 女
    881002   深圳                 男
    881003   上海                 女

--复制表结构创建表t_students_01，t_students_02
CREATE TABLE t_students_01 AS SELECT * FROM t_students WHERE 1 = 2;
CREATE TABLE t_students_02 AS SELECT * FROM t_students WHERE 1 = 2;

--查询表
select * from t_students;
select * from t_students_01;
select * from t_students_02;

3、insert all无判断条件插入

将t_students表中的数据插入t_students_01，t_students_02表中可以这样写

insert all
  into t_students_01 values(id,name,sex)
  into t_students_02 values(id,name,sex)
select id,name,sex from t_students;

4、insert all有判断条件插入

有判断条件插入又分为两种：insert all when... 子句和insert first when... 子句

-- insert t_students
INSERT INTO t_students(ID, NAME, sex) VALUES(1004, '广州', '女');
INSERT INTO t_students(ID, NAME, sex) VALUES(1005, '苏州', '男');
commit;

-- insert first
insert first 
  when id>=1004 then
  into t_students_01 values(id,name,sex)
  when id=1005 then
  into t_students_02 values(id,name,sex)
select id,name,sex from t_students;
commit;

-- insert all
insert all 
  when id>=1004 then
  into t_students_01 values(id,name,sex)
  when id=1005 then
  into t_students_02 values(id,name,sex)
select id,name,sex from t_students;
commit;

5、insert all 、insert first 子句的区别

insert first是依据是否满足判断条件来确定先后关系的，当数据满足第一个when判断条件又满足第二个when判断条件，则执行第一个then插入语句，第二个then就不插入。

反之有数据不满足第一个when判断条件且满足第二个when判断条件,则数据会插入第二个条件下对应的表中，这也正是insert first与inset all的区别。

insert all 只要满足条件，就会插入，这个会造成重复插入；

insert first 只要有一个满足条件，后面的条件不再判断，不会造成重复插入。

注意：insert all 不支持序列插入，会导致两边不一致

6、Oracle19c OCP 考试点题目

自己在HR schmea下

select employee_id  emp_id,manger_id MGR,salary SAL from EMPLOYEES;

create table special_sal 
as 
select employee_id  emp_id,salary SAL 
from HR.EMPLOYEES 
where 1=0;

CREATE table sal_history as select employee_id  emp_id,hire_date hiredate,salary SAL 
from EMPLOYEES 
where 1=0;

CREATE table mgr_history as select employee_id  emp_id,manager_id MGR,salary SAL 
from EMPLOYEES 
where 1=0;

insert all 
when SAL > 20000 then
    into special_sal VALUES(emp_id,SAL)
else 
    into sal_history VALUES(emp_id,hiredate,sal) 
    into mgr_history values(emp_id,mgr,sal)
select employee_id  emp_id,hire_date hiredate,salary SAL,manager_id MGR
from EMPLOYEES 
where EMPLOYEE_ID<125;

HR@192.168.0.207:1521/PROD01PDB> select * from special_sal;

    EMP_ID        SAL
---------- ----------
       100      24000

HR@192.168.0.207:1521/PROD01PDB> select * from sal_history;

    EMP_ID HIREDATE                   SAL
---------- ------------------- ----------
       101 2005-09-21 00:00:00      17000
       102 2001-01-13 00:00:00      17000
       103 2006-01-03 00:00:00       9000
       104 2007-05-21 00:00:00       6000
       105 2005-06-25 00:00:00       4800
       106 2006-02-05 00:00:00       4800
       107 2007-02-07 00:00:00       4200
       108 2002-08-17 00:00:00      12008
       109 2002-08-16 00:00:00       9000
       110 2005-09-28 00:00:00       8200
       111 2005-09-30 00:00:00       7700

    EMP_ID HIREDATE                   SAL
---------- ------------------- ----------
       112 2006-03-07 00:00:00       7800
       113 2007-12-07 00:00:00       6900
       114 2002-12-07 00:00:00      11000
       115 2003-05-18 00:00:00       3100
       116 2005-12-24 00:00:00       2900
       117 2005-07-24 00:00:00       2800
       118 2006-11-15 00:00:00       2600
       119 2007-08-10 00:00:00       2500
       120 2004-07-18 00:00:00       8000
       121 2005-04-10 00:00:00       8200
       122 2003-05-01 00:00:00       7900

    EMP_ID HIREDATE                   SAL
---------- ------------------- ----------
       123 2005-10-10 00:00:00       6500
       124 2007-11-16 00:00:00       5800

24 rows selected.

HR@192.168.0.207:1521/PROD01PDB> select * from mgr_history;

    EMP_ID        MGR        SAL
---------- ---------- ----------
       101        100      17000
       102        100      17000
       103        102       9000
       104        103       6000
       105        103       4800
       106        103       4800
       107        103       4200
       108        101      12008
       109        108       9000
       110        108       8200
       111        108       7700

    EMP_ID        MGR        SAL
---------- ---------- ----------
       112        108       7800
       113        108       6900
       114        100      11000
       115        114       3100
       116        114       2900
       117        114       2800
       118        114       2600
       119        114       2500
       120        100       8000
       121        100       8200
       122        100       7900

    EMP_ID        MGR        SAL
---------- ---------- ----------
       123        100       6500
       124        100       5800

24 rows selected.