Oracle 提供了 merge 语句可以通过一条 SQL 语句实现 insert 和 update 操作，但是如果使用不当，会遇到奇怪的问题。为什么明明没有数据，确没有执行 insert 操作，也没有执行 update 操作，好像 merge 语句不工作了。

1. 建表语句

CREATE TABLE TEST_TB1 (
	ID NUMBER(10,0),
	NAME VARCHAR2(100) NOT NULL,
	REMARK VARCHAR2(100),
	CONSTRAINT PK_TEST_TB1 PRIMARY KEY (ID)
);

2. merge 语句1

使用 SQLPlus 连接数据库：

instantclient_11_2/sqlplus.exe test_db/test_db@192.1.1.166:1521/tsdb
 
# 查询表数据
SQL> select * from TEST_TB1;
 
no rows selected

merge语句1： 

MERGE INTO TEST_TB1 a
    USING (SELECT zz.id FROM TEST_TB1 zz WHERE zz.id = 1) b
ON (a.id = b.id)
WHEN MATCHED THEN
    UPDATE SET NAME = 'Tom', REMARK = 'Tom update'
WHEN NOT MATCHED THEN 
    INSERT (a.id, a.name, a.remark)
	values (1, 'Tom', 'Tom insert');

执行结果：

0 rows merged.

很奇怪为什么没有执行 insert 操作。

3. 修复后的 merge 语句2

MERGE INTO TEST_TB1 a
    USING ( SELECT ( SELECT zz.ROWID AS rid FROM TEST_TB1 zz WHERE zz.id = 1) AS rid FROM dual) b
ON (a.ROWID = b.rid)
WHEN MATCHED THEN
    UPDATE SET NAME = 'Tom', REMARK = 'Tom update'
WHEN NOT MATCHED THEN
    INSERT (a.id, a.name, a.remark)
  values (1, 'Tom', 'Tom insert');

执行结果：

1 row merged. 

看看数据吧

 
SQL> select * from TEST_TB1;
 
        ID
----------
NAME
--------------------------------------------------------------------------------
REMARK
--------------------------------------------------------------------------------
         1
Tom
Tom update

执行成功了！

4. 两个 merge 语句 source table 对比 

merge 语句1 中 source table 的查询结果：

SQL> SELECT zz.id FROM TEST_TB1 zz WHERE zz.id = 2;
 
no rows selected

 merge 语句2中 source table 的查询结果：

SQL> SELECT ( SELECT zz.ROWID AS rid FROM TEST_TB1 zz WHERE zz.id = 2) AS rid FROM dual;
 
RID
------------------

5. 并发问题

虽然解决了 merge 语句问题，还是要注意 merge 语句并发问题。

使用 contiperf 进行并发测试：

    <dependency>
      <groupId>org.databenegroupId>
      <artifactId>contiperfartifactId>
      <version>2.3.4version>
      <scope>testscope>
    dependency>

merge 语句配置

    id="insertByMerge" parameterType="com.ybst.note.db.comm.entity.TestTb1">
        MERGE INTO TEST_TB1 a
		USING ( SELECT ( SELECT zz.ROWID AS rid FROM TEST_TB1 zz WHERE zz.id = #{id}) AS rid
	            FROM dual) b
	    ON (a.ROWID = b.rid)
	    WHEN MATCHED THEN
        UPDATE
        SET
        <if test="name != null and name != ''">
            NAME =#{name},
        
        <if test="remark != null and remark != ''">
            REMARK =#{remark}
        
        WHEN NOT MATCHED THEN INSERT (a.id, a.name, a.remark)
        values (#{id}, #{name}, #{remark})
    

测试类：

import com.ybst.note.db.comm.dao.testpj.ITestTb1Dao;
import com.ybst.note.db.comm.entity.testpj.TestTb1;
import com.ybst.note.testpj.test.annotation.TargetDataSource;
import org.databene.contiperf.PerfTest;
import org.databene.contiperf.junit.ContiPerfRule;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import java.util.HashMap;
import java.util.Map;
 
@SpringBootTest
@RunWith(SpringRunner.class)
public class TestTb1Test {
    private static Logger logger = LoggerFactory.getLogger(TestTb1Test.class);
 
    @Rule
    public ContiPerfRule contiPerfRule = new ContiPerfRule();
 
    @Autowired
    private volatile ITestTb1Dao testTb1Dao;
 
    private int timesRecords = 0;
 
    @TargetDataSource(name = "testpj")
    @PerfTest(invocations = 10*10, threads = 10)  // 10个线程， 执行 100次， 每个执行10次
    @Test
    public void testInsertByMerge() {
        TestTb1 testTb1 = new TestTb1();
        testTb1.setId(1L);
        testTb1.setName("zhangsan");
        testTb1.setRemark("zhangsan info");
 
        testTb1Dao.insertByMerge(testTb1);
 
        timesRecords++;
        logger.info("执行第 {} 次", timesRecords);
    }
 
    @Before
    public void setup() {
        Map paramMap = new HashMap<>();
        paramMap.put("id", 1L);
        testTb1Dao.deleteByMap(paramMap);
    }
}

执行单元测试后，抛出的异常信息：

SQL> SELECT zz.id FROM TEST_TB1 zz WHERE zz.id = 2;
 
no rows selected
 
2022-07-29 15:12:26.874  INFO 248984 --- [       Thread-8] c.y.n.testpj.test.dao.TestTb1Test    : 执行第 74 次
samples: 74
max:     91334
average: 8659.378378378378
median:  19
 
org.databene.contiperf.PerfTestExecutionError: org.springframework.dao.DuplicateKeyException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-00001: 违反唯一约束条件 (test_db.PK_TEST_TB1)

6. 小结

• 使用 merge 时 source table 无数据，会导致 merge 无法正常工作；
• merge 并发问题也需要注意。

• merge 官方文档： MERGE
• SQL*Plus 官方下载地址：Instant Client for Microsoft Windows (x64) 64-bit