Oracle MERGE 语句详解

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

MERGE 是 UPSERT 操作[1]：

用途：

数据同步
存在则更新，不存在则插入
高效

2. 基本语法

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

3. 示例

3.1 简单

MERGE INTO employees t
USING new_employees s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary, dept_id) 
  VALUES (s.id, s.name, s.salary, s.dept_id);

3.2 条件更新

MERGE INTO employees t
USING new_employees s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary
  WHERE t.salary != s.salary  -- 仅更新不同
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

3.3 条件插入

MERGE INTO employees t
USING new_employees s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary)
  WHERE s.active = 'Y';  -- 仅插入活跃

4. DELETE

4.1 12c+

MERGE INTO employees t
USING new_employees s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary
  DELETE WHERE s.status = 'INACTIVE';  -- 更新后删除
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

4.2 注意

DELETE 仅在 WHEN MATCHED
DELETE WHERE 在 UPDATE 之后

5. 单表 MERGE

5.1 DUAL

MERGE INTO employees t
USING (
  SELECT 100 AS id, 'Alice' AS name, 5000 AS salary FROM dual
) s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

6. 多表 MERGE

6.1 子查询

MERGE INTO emp_summary t
USING (
  SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
) s
ON (t.dept_id = s.dept_id)
WHEN MATCHED THEN
  UPDATE SET t.emp_count = s.cnt, t.avg_salary = s.avg_sal
WHEN NOT MATCHED THEN
  INSERT (dept_id, emp_count, avg_salary) 
  VALUES (s.dept_id, s.cnt, s.avg_sal);

7. 性能

7.1 索引

-- ON 条件列索引
CREATE INDEX idx_target_id ON target_table(id);
CREATE INDEX idx_source_id ON source_table(id);

7.2 并行

MERGE /*+ PARALLEL(t 4) PARALLEL(s 4) */ INTO ...

7.3 批量

- MERGE 比单独 INSERT/UPDATE 快
- 减少 SQL 调用
- 单次扫描

8. 对比传统方式

8.1 传统

-- 更新
UPDATE employees t SET (name, salary) = (
  SELECT name, salary FROM new_employees s WHERE s.id = t.id
)
WHERE EXISTS (SELECT 1 FROM new_employees s WHERE s.id = t.id);

-- 插入
INSERT INTO employees
SELECT * FROM new_employees s
WHERE NOT EXISTS (SELECT 1 FROM employees t WHERE t.id = s.id);

8.2 MERGE

MERGE INTO employees t
USING new_employees s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

优势：

一次扫描
原子
简洁

9. 应用场景

9.1 数据同步

-- 源 → 目标同步
MERGE INTO target t USING source s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

9.2 ETL

-- 维度表加载
MERGE INTO dim_customer t
USING stage_customer s
ON (t.customer_id = s.customer_id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

9.3 缓存更新

-- 缓存表更新
MERGE INTO cache_emp t
USING (SELECT * FROM employees WHERE updated_at > SYSDATE - 1) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

10. 12c 增强

10.1 DELETE

MERGE ... WHEN MATCHED THEN 
  UPDATE SET ...
  DELETE WHERE ...

10.2 多个条件

-- 单一 ON
-- 但可多个 WHEN MATCHED/NOT MATCHED（限制）

11. 错误处理

11.1 唯一约束

- ORA-00001
- 检查源数据

11.2 类型不匹配

- ORA-00932
- 检查类型

12. 常见坑与排错

12.1 ORA-30926

- 源数据一对多
- 源唯一

12.2 性能差

- 索引
- 并行
- 统计

12.3 DELETE 误解

- DELETE 在 UPDATE 之后
- 仅 WHEN MATCHED

13. 最佳实践

MERGE 优于 INSERT+UPDATE：高效
ON 索引：性能
WHERE 条件：减少
并行：大数据
DELETE 12c+：增强
DUAL 单行：灵活
子查询聚合：ETL
批量同步：场景
测试：正确
文档化：使用

14. 参考资料

[1] Oracle Database SQL Language Reference 19c, “MERGE” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html