一条merge实现插入或更新

    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.databene</groupId>
      <artifactId>contiperf</artifactId>
      <version>2.3.4</version>
      <scope>test</scope>
    </dependency>

merge 语句配置

    <update 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>
        <if test="remark != null and remark != ''">
            REMARK =#{remark}
        </if>
        WHEN NOT MATCHED THEN INSERT (a.id, a.name, a.remark)
        values (#{id}, #{name}, #{remark})
    </update>

测试类：

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<String, Object> 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