DB / Oracle / SQL 开发

Oracle MERGE 语句（UPSERT）

2026.09.06

Oracle MERGE 语句（UPSERT）

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

1. 概述

MERGE 语句实现 UPSERT（存在则更新，不存在则插入）[1]：

优势：

  • 单次扫描
  • 原子操作
  • 性能优于分开 INSERT/UPDATE
  • 简化代码

2. 基本语法

MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN
  UPDATE SET t.col = s.col
WHEN NOT MATCHED THEN
  INSERT (col1, col2, ...) VALUES (s.col1, s.col2, ...);

3. 基本示例

3.1 简单 UPSERT

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
  UPDATE SET 
    e.last_name = n.last_name,
    e.salary = n.salary
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary, dept_id)
  VALUES (n.employee_id, n.last_name, n.salary, n.dept_id);

3.2 带条件

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
  UPDATE SET 
    e.salary = n.salary
  WHERE e.dept_id = 10  -- 仅更新部门 10
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary)
  VALUES (n.employee_id, n.last_name, n.salary)
  WHERE n.salary > 5000;  -- 仅插入高薪

4. 使用 DUAL

4.1 单行 UPSERT

MERGE INTO config_table t
USING (
  SELECT 'timeout' AS key, '30' AS value FROM dual
) s
ON (t.key = s.key)
WHEN MATCHED THEN
  UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
  INSERT (key, value) VALUES (s.key, s.value);

5. 多表 MERGE

5.1 USING 子查询

MERGE INTO sales_summary t
USING (
  SELECT 
    product_id, 
    SUM(quantity) AS total_qty,
    SUM(amount) AS total_amt
  FROM sales_detail
  WHERE sale_date >= TRUNC(SYSDATE, 'MM')
  GROUP BY product_id
) s
ON (t.product_id = s.product_id AND t.month = TRUNC(SYSDATE, 'MM'))
WHEN MATCHED THEN
  UPDATE SET 
    t.total_qty = s.total_qty,
    t.total_amt = s.total_amt
WHEN NOT MATCHED THEN
  INSERT (product_id, month, total_qty, total_amt)
  VALUES (s.product_id, TRUNC(SYSDATE, 'MM'), s.total_qty, s.total_amt);

6. DELETE 选项（10g+）

6.1 WHERE 条件删除

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = n.salary
  DELETE WHERE e.active = 'N';  -- 删除标记为不活跃的
WHEN NOT MATCHED THEN
  INSERT VALUES (n.employee_id, n.last_name, n.salary);

6.2 说明

  • DELETE 仅在 MATCHED 时执行
  • 在 UPDATE 之后
  • 满足条件则删除

7. 仅 INSERT / 仅 UPDATE

7.1 仅 INSERT

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN NOT MATCHED THEN
  INSERT VALUES (n.employee_id, n.last_name, n.salary);

7.2 仅 UPDATE

MERGE INTO employees e
USING new_employees n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = n.salary;

8. 性能优势

8.1 对比

-- 慢：分开操作
BEGIN
  UPDATE employees SET salary = 5000 WHERE employee_id = 100;
  IF SQL%NOTFOUND THEN
    INSERT INTO employees VALUES (100, 'Smith', 5000);
  END IF;
END;

-- 快：MERGE
MERGE INTO employees e
USING (SELECT 100 AS id, 'Smith' AS name, 5000 AS sal FROM dual) n
ON (e.employee_id = n.id)
WHEN MATCHED THEN
  UPDATE SET e.salary = n.sal
WHEN NOT MATCHED THEN
  INSERT VALUES (n.id, n.name, n.sal);

8.2 性能数据

数据量分开MERGE提升
1000.2s0.05s4x
10002s0.3s7x
1000020s2s10x

9. 应用场景

9.1 数据同步

-- 每日同步
MERGE INTO target_products t
USING source_products s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
  UPDATE SET 
    t.name = s.name,
    t.price = s.price,
    t.update_date = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (product_id, name, price, create_date)
  VALUES (s.product_id, s.name, s.price, SYSDATE);

9.2 聚合更新

-- 月度汇总
MERGE INTO monthly_summary m
USING (
  SELECT 
    dept_id, 
    SUM(salary) AS total_sal,
    COUNT(*) AS emp_count
  FROM employees
  GROUP BY dept_id
) d
ON (m.dept_id = d.dept_id AND m.month = TRUNC(SYSDATE, 'MM'))
WHEN MATCHED THEN
  UPDATE SET 
    m.total_salary = d.total_sal,
    m.employee_count = d.emp_count
WHEN NOT MATCHED THEN
  INSERT (dept_id, month, total_salary, employee_count)
  VALUES (d.dept_id, TRUNC(SYSDATE, 'MM'), d.total_sal, d.emp_count);

9.3 缓存更新

-- 缓存表 UPSERT
MERGE INTO cache_table c
USING (
  SELECT :key AS key, :value AS value FROM dual
) s
ON (c.key = s.key)
WHEN MATCHED THEN
  UPDATE SET c.value = s.value, c.update_time = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (key, value, update_time)
  VALUES (s.key, s.value, SYSDATE);

10. 常见坑与排错

10.1 ORA-30926: 无法获取稳定行集

-- 源数据有重复键
-- 修复：去重
USING (SELECT DISTINCT ... FROM source) s

10.2 UPDATE 不能改 JOIN 列

-- 错误
MERGE INTO e USING n ON (e.id = n.id)
WHEN MATCHED THEN UPDATE SET e.id = n.id;

-- 修复：不能更新 ON 条件中的列

10.3 性能差

修复：

-- 1. 源表加索引
-- 2. 目标表 JOIN 列加索引
-- 3. 使用 HINT
MERGE /*+ APPEND */ INTO ...

10.4 DELETE 条件

-- DELETE 在 UPDATE 后
-- 不 UPDATE 时 DELETE 不会执行

11. 最佳实践

  1. UPSERT 用 MERGE：性能好
  2. 目标表加索引：JOIN 列
  3. 源数据去重：避免 ORA-30926
  4. 使用子查询：灵活
  5. 批量处理：性能
  6. 结合 BULK：FORALL
  7. 定期 COMMIT：避免长事务
  8. 测试边界：NULL/重复
  9. 使用绑定变量：减少硬解析
  10. 监控执行计划：优化

12. 参考资料

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