ORACLE 批量插入

Oracle批量插入语句与其他数据库不同，下面列出不同业务需求的插入

假设有一张表Student

-- 学生表

create table Student(

id Varchar2(11) primar ykey,

name varchar2(32) not null,

sex varchar2(3) not null,

age smallint,

tel varchar(16)

)

其中[]中代表可选;<>代表必须;table_column的数量必须和column_value一致，并且数据类型要相匹配

1. 单条自定义记录插入

　　命令格式：

insert into table <tableName>[(<table_column1>,<table_column2>...)] values([<column_value1>,<column_value2>...])

　　示例：insert into Student(id, name, sex, age, tel) values ('13', 'jack', '男', 13, '13345674567')

2.多条自定义记录插入

　　命令格式1：

insert all into <tableName> [(<table_column1>,<table_column2>...)]values([<column_value1>,<column_value2>...])

[ into <tableName>[(<table_column1>,<table_column2>...)]values([<column_value1>,<column_value2>...])]...

select<table_value1>[,<table_value2>...]from dual;

　　示例：

insert all into Student(id, name, sex, age, tel)

into Student(id, name, sex, age, tel) values ('12', 'jack1', '男', 12, '13345674567' )

into Student(id, name, sex, age, tel) values ('13', 'jack2', '男', 13, '13345674567')

select '14', 'jack', '男', 13, '13345674567' from dual;

　　Note: 我也不知道为什么要加select <values> from dual语句，反正不加就报错

　　命令格式2：

insert into<tableName>[(<table_column1>,<table_column2>...)] select[<column_value1>,<column_value2>...] from dual

[ union select [<column_value1>,<column_value2>...]from dual ]...

　　示例：

insert into Student(id, name, sex, age, tel)

select '24', 'jack', '男', 22, '13345674567' from dual

union select '25', 'jack', '男', 22, '13345674567' from dual

union select '26', 'jack', '男', 32, '13345674567' from dual

　3. 数据库记录插入

　　命令格式：

insert into<tableName1>[(<table_column1>,<table_column2>...)]select[<column_value1>,<column_value2>...]from<tableName2>[where [...]]

union[ select [<column_value1>,<column_value2>...]from<tableName2>[where [...] ]]

　　示例：

insert into student(id, name, sex, age, tel)

select (id-1)||'' as id, name, sex, age, tel from Student where id='11'

union select id||'1' as id, name, sex, age, tel from Student where id like '1%'

union select id||'2' as id, name, sex, age, tel from Student where id like '%1' and id/3!=0

Note:不推荐插入语句不写名字段，比如

insert into student select * from student2；

into Student values ('12', 'jack', '男', 12, '13345674567' )

转载自：https://www.cnblogs.com/hjm0928/p/10254894.html