.Net程序员学用Oracle系列(13)：合并语句(MERGE)

• 1、语法说明
  • 1.1、UPDATE 和 INSERT 可以只出现一个
  • 1.2、UPDATE 后面还可以再跟 WHERE
  • 1.3、UPDATE 和 INSERT 同时出现
  • 1.4、UPDATE 之后还可以再删除行
  • 1.5、将两个列的部分行值互换
• 2、常见雷区
  • 2.1、ORA-30926: 无法在源表中获得一组稳定的行
  • 2.2、USING 了空行导致 UPDATE 或 INSERT 失败
• 3、总结

在开发工作中时常会遇到这样一个问题：要判断一张表中是否存在某条记录，如果不存在则添加该记录，如果存在则修改该记录。按照一般的思路，至少需要 SELECT、INSERT、UPDATE 三条 SQL 语句才能搞定。无论是用程序代码还是存储过程，尽管逻辑不算复杂，但代码会比较长、可维护性也不好，而且经验不足的开发人员往往不会考虑事务，写出来的代码会有逻辑漏洞。

我本人是在一次发现旧代码中存在逻辑漏洞后，不想写太长的事务控制代码，又想不出更简洁的方案时，带着“要是能一条语句同时搞定添加和修改就好了”的幻想开始了漫无边际的百度之旅，终于功夫不负有心人，合并语句 MERGE 就这样被我找到了。于是乎我开始在工作中大量使用 MERGE，由于经验不足，曾多次入坑，我将通过本文把我的使用心得分享给大家。

1、语法说明

MERGE 语句具有按条件获取要更新或插入到表中的数据行，然后从一个或多个源头对表进行更新或者向表中插入行两方面的能力。它最经常被用在数据仓库中来移动大量的数据，但它的应用不仅限于数据仓库环境下。这个语句提供的一个很大的附加值在于你可以很方便地把多个操作结合成一个。如果你避免去做那些不是必须做的事情，响应的时间可能得到相应的改善。

简单的说 MERGE 语句就是用来合并 UPDATE 语句和 INSERT 语句的。通过 MERGE 语句，可根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上了就执行 UPDATE，无法匹配就执行 INSERT。这个语句仅需要一次全表扫描就完成了全部工作，执行效率要高于 INSERT + UPDATE。语法：

MERGE INTO target_table
USING {table/view/subquery}
ON(condition)
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

1.1、UPDATE 和 INSERT 可以只出现一个

只出现 INSERT 的示例：

-- 如果课程表中没有“计算机”这门课程则插入这门课程
MERGE INTO demo.t_course t1
USING(SELECT 1 cid,'计算机' cname FROM DUAL) t2
ON(t1.course_id=t2.cid)
WHEN NOT MATCHED THEN
INSERT(t1.course_id,t1.course_name) VALUES(t2.cid,t2.cname);

只出现 UPDATE 的示例：

-- 如果课程表中有“计算机”这门课程则将这门课程的备注改为“工科”
MERGE INTO demo.t_course t1
USING(SELECT 1 cid,'计算机' cname FROM DUAL) t2
ON(t1.course_name=t2.cname)
WHEN MATCHED THEN
UPDATE SET t1.course_desc='工科';

1.2、UPDATE 后面还可以再跟 WHERE

如果课程表中有多个名为“计算机”的课程，而实际上只需要把课程 ID 为 1 的课程备注改为“工科”，上文中只出现 UPDATE 的示例就有逻辑错误了。这种情况的正确示例：

MERGE INTO demo.t_course t1
USING(SELECT 1 cid,'计算机' cname FROM DUAL) t2
ON(t1.course_name=t2.cname)
WHEN MATCHED THEN
UPDATE SET t1.course_desc='工科' WHERE t1.course_id=1;

1.3、UPDATE 和 INSERT 同时出现

如果研发一部有叫“大国”的人，就把他的岗位工资加 200，如果没有就把他添加到研发一部。示例：

MERGE INTO demo.t_staff t1
USING(SELECT '010101' dept_code,'大国' staff_name FROM DUAL) t2
ON(t1.dept_code=t2.dept_code AND t1.staff_name=t2.staff_name)
WHEN MATCHED THEN
  UPDATE SET t1.post_salary=t1.post_salary+200
WHEN NOT MATCHED THEN
  INSERT(staff_name, dept_code, gender, birthday, edu_bg, base_salary, post_salary, post_code)
  VALUES('大国','010101',1,TO_DATE('1992-01-15','yyyy-mm-dd'),2,2500,4000,'P50');

假如说员工中没有叫“大国”的，所以第一次执行上面这条语句时，大国会被插入到研发一部；这时候研发一部就已经有“大国”了，如果再执行第二次，“大国”的岗位工资就会被修改成 4200 了。

1.4、UPDATE 之后还可以再删除行

如要把低工资的员工固定薪资上调 500，然后把工资依然低于 5000 的员工数据删除，示例：

MERGE INTO demo.t_staff_salary t1
USING demo.t_staff_low t2
ON(t1.staff_id=t2.staff_id)
WHEN MATCHED THEN
UPDATE SET t1.fixed_salary=t1.fixed_salary+500
DELETE WHERE t1.fixed_salary<5000;

这种语法有三个注意点，如下：

• DELETE 必须和 UPDATE 同时出现，且 DELETE WHERE 必须在 UPDATE [WHERE] 之后。
• DELETE 只能删除目标表数据，无法删除源表数据。如上例中只能删除 t1 表的数据，无法删除 t2 表的数据。
• 只有同时满足 ON、UPDATE 的 WHERE 以及 DELETE 的 WHERE 三个条件的记录会被删除。也就是说 DELETE 的作用范围仅为被 UPDATE 过的那些行。

1.5、将两个列的部分行值互换

如果要求将两个列的值互换，那自然是简单，譬如要把 t 表中的 a 列与 b 列值互换，示例：

UPDATE t SET a=b,b=a;

但如果要求将两个列的部分行值互换，譬如要把小强的岗位工资和小明的岗位工资调换一下。假如先把小强的岗位工资更新成小明的岗位工资，那么再更新小明的岗位工资时就不知道小强的岗位工资了。要是在程序中倒是好办，可以先把小强的工资存储起来，那是不是仅用 SQL 语句就无法解决这个问题了呢？答案是否定的！示例：

MERGE INTO demo.t_staff t1
USING(
  SELECT 1 staff_id,(SELECT n1.post_salary FROM t_staff n1 WHERE n1.staff_id=2) post_salary FROM DUAL
  UNION ALL
  SELECT 2 staff_id,(SELECT n2.post_salary FROM t_staff n2 WHERE n2.staff_id=1) post_salary FROM DUAL
) t2
ON(t1.staff_id=t2.staff_id)
WHEN MATCHED THEN
UPDATE SET t1.post_salary=t2.post_salary;

2、常见雷区

ORA-30926: 无法在源表中获得一组稳定的行

这可能是用 MERGE 语句最常见的报错！先来创建两张测试表，语句如下：

CREATE TABLE t1(cid NUMBER(4),cname VARCHAR2(20));
INSERT ALL
  INTO t1 VALUES(1,'A')
  INTO t1 VALUES(2,'B')
  INTO t1 VALUES(3,'C')
SELECT * FROM DUAL;
COMMIT;

CREATE TABLE t2(cid NUMBER(4),cname VARCHAR2(20));
INSERT ALL
  INTO t2 VALUES(1,'甲')
  INTO t2 VALUES(2,'乙')
  INTO t2 VALUES(2,'乙')
SELECT * FROM DUAL;
COMMIT;

要求把 t1 表中 cid 与 t2 表中 cid 相同的行的 cname 列的值更新为 t2 表中的 cname 列的值，简单的说就是把 t1 表中的 A/B/C 翻译成 t2 表中的甲/乙/丙。按照一般的思路是行不通的，错误示例：

MERGE INTO t1
USING t2
ON(t1.cid=t2.cid)
WHEN MATCHED THEN
UPDATE SET t1.cname=t2.cname;

很遗憾！它会报：“ORA-30926: 无法在源表中获得一组稳定的行”的错。原因是 MERGE 语句要求保证 ON 中条件的唯一性，也就是说目标表中的一条记录不能对应源表中的多条记录。但反过来源表中的一条记录对应目标表中的多条记录却是可以的，示例：

MERGE INTO t2
USING t1
ON(t1.cid=t2.cid)
WHEN MATCHED THEN
UPDATE SET t2.cname=t1.cname;

那是不是说目标表中的一条记录对应源表中的多条记录时就无法通过 MERGE 更新了呢？也不完全是！就拿本例来说，可以通过对源表做聚合，将多行归并成单行，然后再更新。示例：

MERGE INTO t1
USING(SELECT t2.cid,MAX(t2.cname) cname FROM t2 GROUP BY t2.cid) t2
ON(t1.cid=t2.cid)
WHEN MATCHED THEN
UPDATE SET t1.cname=t2.cname;

2.2、USING 了空行导致 UPDATE 或 INSERT 失败

假如要判断上例中的 t2 表里是否有 cid 为 3 的记录，如果有就把它的 cname 改为丙，如果没有就插入cid 为 3，cname 为丙的记录。错误示例：

MERGE INTO t2
USING(SELECT * FROM t2 WHERE t2.cid=3) t
ON(t2.cid=t.cid)
WHEN MATCHED THEN
UPDATE SET t2.cname='丙'
WHEN NOT MATCHED THEN
INSERT VALUES(3,'丙');

上面这个语句是可以执行成功的，但遗憾的是，表中却没有增加记录。原因是 MERGE 语句要求 USING 后面必须包含要更新或插入的行，而上例中 t2.cid=3 的行是不存在的。下面我来介绍两种正确写法。

写法一，示例：

MERGE INTO t2
USING(SELECT 3 cid,'丙' cname FROM DUAL) t
ON(t2.cid=t.cid)
WHEN MATCHED THEN
UPDATE SET t2.cname='丙'
WHEN NOT MATCHED THEN
INSERT VALUES(3,'丙');

写法二，示例：

USING(SELECT COUNT(1) cnt FROM t2 WHERE t2.cid=3) t
ON(t.cnt>0)
WHEN MATCHED THEN
UPDATE SET t2.cname='丙'
WHEN NOT MATCHED THEN
INSERT VALUES(3,'丙');

推荐写法一，写法二相对没有写法一那么好理解，一旦理解反了，后果可能很严重。譬如上例中如果写成 t.cnt<1 会把整张表里的数据都更新成丙。

3、总结

MERGE 是 SQL 中除 INSERT、DELETE、UPDATE、SELECT 之外又一重要基本语句，不仅 Oracle 中有，MS SQL Server 和 MySQL 等关系型数据库中也都有，所以非常有必要熟练掌握 MERGE 语句。若能在开发过程中合理的运用 MERGE 语句，可有效提高开发效率，增强代码的可维护性和健壮性，最显而易见的好处就是——不用写那么长的代码了。

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-merge.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！