oracle一次插入多条数据(insert all)

Dear 丶 2022-05-27 13:19 247阅读 0赞

> 分享一个零基础，通俗易懂，而且非常风趣幽默的[人工智能教程(如不能直接点击访问，请以“右键”->“在新标签页中打开链接”方式打开)][-]网站，网址：https://www.cbedai.net/gnailoug/

# 问题 #

公司的项目，有个功能每次使用需要向数据库插入很多数据，导致页面等待很长时间才有结果。  
数据库：oracle11g  
id：采用sequence自增  
每次循环，都会查询一次sequence，然后insert一条数据，性能非常低。

# 改进 #

改成一次插入多条数据，id通过触发器自动设置，不再每次先查询sequence，效率提高非常多。

# oracle一次插入多条的方法 #

在oracle里面，不支持像mysql那样直接在后面拼多个记录。oracle中有两种方法达到批量插入的效果：

## 方法一：采用union all拼接查询方式 ##

本文不做详细介绍，可在网上查看相关资料。

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
              select 8000,0,'Multi 8000',1 from dual
    union all select 8001,0,'Multi 8001',1 from dual

\#\#方法二：采用insert all的方式  
由于insert all方式插入多条时，通过sequence获取的值是同一个，不会自动获取多个，所以id需要通过其他方式设置，(我这里采用触发器方式自动设置id)

1、创建测试表：

create table test_insert(
           data_id number(10) primary key,
           user_name varchar2(30),
           address varchar2(50)
    )

data\_id为主键，通过sequence产生主键值。

2、创建序列：

create sequence seq_test_insert 
    minvalue 1
    maxvalue 999999999999999999999999
    start with 1
    increment by 1
    cache 20;

3、创建触发器  
通过触发器自动给insert语句设置id值

create or replace trigger tr_test_insert
    before insert on test_insert
    for each row
    begin
      select seq_test_insert.nextval into :new.data_id from dual;
    end;

4、插入测试数据：

insert all 
    into test_insert(user_name,address) values('aaa','henan')
    into test_insert(user_name,address) values('bbb','shanghai')
    into test_insert(user_name,address) values('ccc','beijing')
    select * from dual;

相当于下面三个insert into语句，但性能比单条高多了。

insert into test_insert(user_name,address) values('aaa','henan');
    insert into test_insert(user_name,address) values('bbb','shanghai');
    insert into test_insert(user_name,address) values('ccc','beijing');

需要注意的是，在insert all语句里不能直接使用`seq_test_insert.nextval`，因为即便每个into语句里都加上`seq_test_insert.nextval`也不会获得多个值。

5、查看测试数据

select * from test_insert;

结果如下图：  
![这里写图片描述][70]

另外，insert all还支持往不同的表里插入数据，如：

insert all 
    into table1(filed1,filed2)values('value1','value2')
    into table2(字段1，字段2，字段3) values(值1，值2，值3)
    select * from dual;

[-]: https://www.cbedai.net/gnailoug/
[70]: /images/20220527/ae47f95fe1a14397aa5342b3116a4fbb.png