Mybatis XML 数据源为 Oracle 之批量插入或更新 Merge Into 的具体介绍与使用

声明

1. 原文地址：https://blog.csdn.net/qq_42142477/article/details/115198285

一、前言

• 由于近期在所开发的项目中，对于数据入库，有 存在即更新，不存在则插入 的需求，因此发现了 Oracle中的 MergeInto 命令。本文将对MergeInto的用法进行介绍并将 MergeInto 和批量插入进行结合，同时还会对在 MergeInto 开发中遇到的问题进行总结。

1、MergeInto简介

• MergeInto 命令是 Oracle9i 中新增的命令，有了 MergeInto 语句，我们对数据能够不仅仅只做单一的插入或单一的更新，而是可以将更新与插入一起操作。

• MergeInto通过对两张表进行连接比较，如果匹配则 UPDATE，否则 INSERT

语法

MERGE INTO table_name a
USING ((table|view|sub_query)
) b
ON (condition) 
WHEN MATCHED THENmerge_update
WHEN NOT MATCHED THENmerge_insert

举例

MERGE INTO USER a
USING (select * from USER
) b
ON (condition) 
WHEN MATCHED THENmerge_update
WHEN NOT MATCHED THENmerge_insert

• 注意

1. 此处的update语句与平时我们写的update语句不同，update和set之间不能有表名，否则会报缺少SET关键字。
2. update语句中不能存在在ON中进行匹配后的字段，否则会报无法更新 ON 子句中引用的列A.USERID。
   错误❌：

MERGE INTO USER a
USING (select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.id) 
WHEN MATCHED THENUPDATE SET USER USER_ID = b.idUSER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

正确✔：

MERGE INTO USER a
USING (select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

3. update语句中不能在update中使用ON连接条件中的命名，否则会报B.USERID标识符无效。
   错误❌：

MERGE INTO USER a
USING (select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_ID = b.useridUSER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

正确✔：

MERGE INTO USER a
USING (select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

4. 关于MergeInto只能更新不能插入（Updates:0）
   merge into a using on b，a、b表进行比较

错误❌：如果是第一次入库，b必然为null

MERGE INTO USER a
USING (select #{user.userid} as id from USER
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_ID = b.useridUSER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

正确✔：

MERGE INTO USER a
USING (select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENmerge_insert

2、MergeInto批量插入更新

• 我们已经知道批量入库是将一个list作为整体进行入库操作，那么MergeInto和批量入库结合起来应该怎么写呢？又会有什么问题？

传统的批量入库

insert into USER(
USER_ID,USER_NAM
)
<foreach collection="list" item="item" index="index" separator="union all">(SELECT#{item.userid},#{item.username}FROM DUAL)
</foreach>

MergeInto批量

MERGE INTO USER a
USING (<foreach collection="list" item="item" index="index" separator="union all">(SELECT#{item.userid,jdbcType=VARCHAR} as userid,#{item.username,jdbcType=VARCHAR} as usernameFROM DUAL)</foreach>
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.userid
WHEN NOT MATCHED THENINSERT(USER_ID,USER_NAM)VALUES(b.userid，b.username）
)

注意

• 采取批量入库的时候，同一批的数据无法进行存在更新，不存在插入。因为同一批的数据不在比较范围内。
• 解决方法：目前我还没有找到怎么在数据库语句中进行优化解决，但是我们可以在代码逻辑中，对list进行一个判断，如果已存在，则不执行list.add()。通过代码和数据库结合的方法，就可以避免上述情况了。

3、带有CLOB类型字段的MergeInto

• 如果说我们将带有CLOB类型字段的sql按照上述语句去写，代码执行过程中会报错：java.sql.SQLSyntaxErrorException:ORA-01790:表达式必须具有对应表达式相同的数据类型。

• 因此，针对带有CLOB类型字段的sql有两种写法：

• 写法1

begin<foreach collection="list" item="item" index="index">MERGE INTO USER aUSING (SELECT#{item.userid,jdbcType=VARCHAR} as userid,#{item.username,jdbcType=CLOB} as usernameFROM DUAL)bON (a.USER_ID = b.userid) WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.useridWHEN NOT MATCHED THENINSERT(USER_ID,USER_NAM)VALUES(b.userid，b.username);</foreach>
end; 

• 写法2
  • 将empty_clob()代替要插入的clob数据，再用真正的数据去替换empty_clob()
  • 注意： 要对clob字段进行非空判断，不然当clob为空时会报错

begin<foreach collection="list" item="item" index="index">MERGE INTO USER aUSING (SELECT#{item.userid,jdbcType=VARCHAR} as userid,empty_clob() as usernameFROM DUAL)bON (a.USER_ID = b.userid) WHEN MATCHED THENUPDATE SET USER USER_NAM = b.usernameWHERE USER_ID = b.useridWHEN NOT MATCHED THENINSERT(USER_ID,USER_NAM)VALUES(b.userid，b.username);<if test="item.vluResume != null">update USER set USER_NAM = #{item.username,jdbcType=CLOB} where USER_ID = #{item.userid,jdbcType=VARCHAR} ;</if></foreach>
end;