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 THEN
	merge_update
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9

举例

MERGE INTO USER a
USING (
	select * from USER
) b
ON (condition) 
WHEN MATCHED THEN
	merge_update
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9

• 注意

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 THEN
	UPDATE SET USER 
		USER_ID = b.id
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11
12

正确✔：

MERGE INTO USER a
USING (
	select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THEN
	UPDATE SET USER 
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11

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 THEN
	UPDATE SET USER 
		USER_ID = b.userid
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11
12

正确✔：

MERGE INTO USER a
USING (
	select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THEN
	UPDATE SET USER 
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11

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 THEN
	UPDATE SET USER 
		USER_ID = b.userid
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11
12

正确✔：

MERGE INTO USER a
USING (
	select #{user.userid} as id from dual
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THEN
	UPDATE SET USER 
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	merge_insert
1
2
3
4
5
6
7
8
9
10
11

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>
1
2
3
4
5
6
7
8
9
10

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 username
			FROM DUAL
		)
	</foreach>
) b
ON (a.USER_ID = b.userid) 
WHEN MATCHED THEN
	UPDATE SET USER 
		USER_NAM = b.username
	WHERE USER_ID = b.userid
WHEN NOT MATCHED THEN
	INSERT(
		USER_ID,USER_NAM
	)VALUES(
		b.userid，b.username
	）
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意

• 采取批量入库的时候，同一批的数据无法进行存在更新，不存在插入。因为同一批的数据不在比较范围内。
• 解决方法：目前我还没有找到怎么在数据库语句中进行优化解决，但是我们可以在代码逻辑中，对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 a
   USING 
   	(SELECT
   		#{item.userid,jdbcType=VARCHAR} as userid,
   		#{item.username,jdbcType=CLOB} as username
   		FROM DUAL
   	)b
   ON (a.USER_ID = b.userid) 
   WHEN MATCHED THEN
   	UPDATE SET USER 
   		USER_NAM = b.username
   	WHERE USER_ID = b.userid
   WHEN NOT MATCHED THEN
   	INSERT(
   		USER_ID,USER_NAM
   	)VALUES(
   		b.userid，b.username
   	);
   </foreach>
end; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

begin
   <foreach collection="list" item="item" index="index">
   MERGE INTO USER a
   USING 
   	(SELECT
   		#{item.userid,jdbcType=VARCHAR} as userid,
   		empty_clob() as username
   		FROM DUAL
   	)b
   ON (a.USER_ID = b.userid) 
   WHEN MATCHED THEN
   	UPDATE SET USER 
   		USER_NAM = b.username
   	WHERE USER_ID = b.userid
   WHEN NOT MATCHED THEN
   	INSERT(
   		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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25