二十一、Oracle中insert all用法

目录

21.1. 准备基础数据

21.2. 同时插入多个表

21.3. 同时插入多个表(带条件)

21.4. insert first

---

21.1. 准备基础数据

create table orderinfo

( order_id integer primary key,

 salary number(5) not null)；

 

create table smallorder

( order_id integer primary key,

 salary number(5) not null)；

 

create table largeorder

( order_idinteger primary key,

 salary number(5) not null)；

 

create table specialorder

( order_id integer primary key,

 salary number(5) not null)；

 

insert into orderinfo values(1, 100);

insert into orderinfo values(2, 600);

insert into orderinfo values(3, 700);

insert into orderinfo values(4, 200);

insert into orderinfo values(5, 300);

select * from orderinfo；

---

21.2. 同时插入多个表

insert all

into smallorder values(order_id,salary)

into largeorder values(order_id,salary)

select * from orderinfo;

 

---

21.3. 同时插入多个表(带条件)

insert all   

when salary >= 0 and salary < 300then

 intosmallorder values(order_id,salary)

when salary >= 700 then

 into specialorder values(order_id,salary)

when salary >= 300 then

 intolargeorder values(order_id,salary)

select * from orderinfo;     --700同时插入largeorder表和specialorder表

 

---

21.4. insert first

when salary >= 0 and salary < 300then

 intosmallorder values(order_id,salary)

when salary >= 700 then

 intospecialorder values(order_id,salary)

when salary >= 300 then

 intolargeorder values(order_id,salary)

select * from orderinfo;     --700只插入specialorder表，与条件语句的顺序有关

select * from smallorder;

select * from largeorder;

select * from specialorder