oracle一次插入多条的方法
1.采用union all拼接查询方式
insert into table_name(ID,NAME,AME)
select 001,'张三',21 from dual
union all select 002,'Multi',31 from dual
2.采用insert all的方法
由于insert all方式插入多条时,通过sequence获取的值是同一个,不会自动获取多个,所以id需要通过其他方式设置,(我这里采用触发器方式自动设置id)
(1).创建表
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;
(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 all还支持往不同的表里插入数据,如:
insert all
into table1(filed1,filed2)values('value1','value2')
into table2(字段1,字段2,字段3) values(值1,值2,值3)
select * from dual;
3.ibatis结合oracle批量插入数据
<insert id="insert_table" parameterClass="java.util.List">
insert into sj_test( col1 , col2 , col3 ) values select
col1 , col2 , col3
from (
<iterate conjunction=" union all ">
select
#test[].col1# as col1 , #test []. col2# as col2, # test[].col3# as col3 from dual
</iterate>
)
</insert>
使用这种方法需要向dao层传递一个List集合