情况:上传文件读取数据,将数据批量插入数据库,SQL如下
INSERT INTO XX_DT_CHANNEL(CHANNEL_NO,CHANNEL_NAME,CHANNEL_TYPE,IMPORT_DATE,VALID,IS_LOCK)
select T.* from(
<foreach collection="channelList" item="item" index="index" close=")" open="(" separator="union all">
select
#{item.channelNo,jdbcType=VARCHAR},
#{item.channelName,jdbcType=VARCHAR},
#{item.channelType,jdbcType=VARCHAR},
sysdate,
'1',
'1'
from DUAL
</foreach>
) T
当执行SQL时,报错:ora-00918:未定义明确列错误
原因:foreach 中存在相同常量,最后两个1,数据库不知道是将数据插入到那个列
解决:存在相同常量时,在后面加上as 别名
INSERT INTO XX_DT_CHANNEL(CHANNEL_NO,CHANNEL_NAME,CHANNEL_TYPE,IMPORT_DATE,VALID,IS_LOCK)
select T.* from(
<foreach collection="channelList" item="item" index="index" close=")" open="(" separator="union all">
select
#{item.channelNo,jdbcType=VARCHAR},
#{item.channelName,jdbcType=VARCHAR},
#{item.channelType,jdbcType=VARCHAR},
sysdate,
'1' as valid,
'1' as isLock
from DUAL
</foreach>
) T