Oracle的批量导出和MySQL不一样,MySQL是在values后面直接可以加多个括号,Oracle则不是;
下面就是Oracle批量插入语法:
1. 不带序列号(自增id)的批量插入
-- 不带序列号的批量插入
insert all
into tableName(...)values(...)
into tableName(...)values(...)
into tableName(...)values(...)
...
select 1 from dual;
后面一点要加select语句,不然会报错;
2.带自增序列的批量插入:
-- 带序列号的批量插入
# 方式一
begin
insert into tablename(...) values (sequence.nextval,...);
insert into tablename(...) values (sequence.nextval,...);
insert into tablename(...) values (sequence.nextval,...);
insert into tablename(...) values (sequence.nextval,...);
...
end;
# 方式二:注意:序列号不能和union all/union放在同一查询级别
insert into tablename(...)
select sequence.nextval,...(
select ... from dual
union all/union
select ... from dual
union all/union
select ... from dual
....
);
例如:insert into student_info(sno, sname,sex,create_date)
select stu_no_sequence.nextval sno, sname,sex,create_date from(
select '周九' as sname,'女' as sex,sysdate as create_date from dual
union all
select '龙十' as sname,'女' as sex,sysdate as create_date from dual
union all
select '喜喜' as sname,'女' as sex,sysdate as create_date from dual
);