一条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 并发问题也需要注意。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值