oralce 使用foreach实现批量插入数据到数据库时，报错 ORA-00933: SQL 命令未正确结束

在修改代码评审指出的问题时，由于循环操作数据库，需要写一个批量插入的sql，萌新的我自然想到了之前使用的foreach标签，拼接一个sql ，从而执行批量插入，解决问题。

foreach拼接sql可以参照这里，于是写了一串这个文件：

    <insert id="batchInsertMessageInfo">
        insert into XXX values 
        <foreach collection="list" index="index" item="item" separator=",">
            (#{XXX.xxx},#{XXX.xxx},#{XXX.xxx},#{XXX.xxx})
        </foreach>
    </insert>

简单介绍一下，就是将sql拼接为以下的格式

insert into tablename values
(xxx,xxxx,xxxx),(xxx,xxxx,xxx),(xxx,xxxx,xxx)

执行的时候竟然报错了，提示sql命令未正确结束：

java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

大致报错信息没有截图，就不贴了，后面百度之后才发现，这个格式的批量插入无法被oracle识别，mysql则不受影响，只能又长见识了。

需要借助union all 实现

于是对xml文件进行修改：

<insert id="insertOperation">
        insert into XXX.xxx(xxx,xxx,xxx,xxx) values
            <foreach collection="list" item="item" separator="union all">
                SELECT #{item.xxx},#{item.xxx},#{item.xxx},#{item.xxx} FROM dual
            </foreach>
    </insert>

这样拼接好的sql如下所示：

insert into XXX.xxx(xxx,xxx,xxx,xxx) values SELECT #{item.xxx},#{item.xxx},#{item.xxx},#{item.xxx} FROM dual union all
SELECT #{item.xxx},#{item.xxx},#{item.xxx},#{item.xxx} FROM dual

满怀信心的去执行，结果又报错了，这回提示的错误如下：

Cause: java.sql.SQLException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

检查之后发现，本来对应的CLOB类型的数据，插入时数据库反而认为是LONG类型，继续修改sql语句：

< insert id = "insertBatch"  >
           insert all
            < foreach collection = "list" item = "log" index = "index" >
                into T_OPERATION_LOG    -- 表名
                (ID)     --插入对应的字段
                values
                (#{log.id})   -- 插入的值
            </ foreach >
           select 1 from dual
</ insert >

改成这样的就能够成功批量插入了，只能说oracle的批量插入确实有点坑。

最后把拼接好的sql写出来

insert all
into xxxx(xxx,xxxx) values (xxx) union all 
into xxxx(xxx,xxxx) values (xxx)
select 1from dual

其实是借用insert all 这个在多表中插入同一条数据的命令绕了一下，实现了在一张表中插入多条数据。

具体可以参照以下博客：

https://blog.csdn.net/fukaiit/article/details/80691665

 

https://www.cnblogs.com/ninicwang/p/7470745.html

https://blog.csdn.net/csdn_ss1991/article/details/80439777