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