oracle批量导入sql语句

https://blog.csdn.net/gnail_oug/article/details/80005957

由于要将mysql数据库转为oracle，mybatis的映射文件里面的批量插入用的mysql的多个values的形式，但是换成oracle库之后sql报错。所以.oracle并不支持这种一个insert into 多个values的方法

mysql:INSERT INTO users(name, age) VALUES(‘ccc’, 333), (‘aaa’, 222), (‘bbb’, 111);

事实证明mysql的一条sql插入多条数据在oracle中不适用。

insert into bill.TB_1174253740908220416(BILL_ID,ACCOUNT_BODY)
select SEQ_DEAL_FLOW.nextval,A.* from ( 
  SELECT  '李春英1' from dual  
  UNION
  SELECT  '李春英2' from dual 
  UNION
  SELECT  '李春英3' from dual 
) A

mybatis的写法（有序列的情况）

<!-- 批量插入 --> 
   <insert id="inserts" parameterType="java.util.List"> 
      insert into PRESON 
       select SEQ_PRESON_ID.NEXTVAL,A.* from( 
       <foreach collection="list" item="item" index="index" 
           separator="UNION"> 
           SELECT 
           #{item.presonName}, 
           #{item.presonTel}, 
           #{item.presonEmail}, 
           #{item.presonAge} 
           from dual 
            </foreach> 
       ) A 
   </insert>

没有序列的情况

https://www.jianshu.com/p/fdba0e023db8

<insert id="insertuser" parameterType="java.util.List" useGeneratedKeys="false" >
    insert into user
    (
        username,
        sex,
        age
    )
    <foreach close=")" collection="list" item="item" index="index" open="(" separator="union all">
        select
            #{item.username},
            #{item.sex},
            #{item.age}
        from dual 
    </foreach>
</insert>

第一种插入

insert into bill.TB_1174253740908220416(BILL_ID,ACCOUNT_BODY)
select 1,'李春英1' from dual
union
select 2,'李春英2' from dual

第二种插入语句

INSERT all 
INTO bill.TB_1174253740908220416 (BILL_ID,ACCOUNT_BODY) VALUES(1,'李春英') 
INTO bill.TB_1174253740908220416 (BILL_ID,ACCOUNT_BODY) VALUES(2,'李春英')
SELECT 1 FROM dual;