使用merge 需要注意的问题
场景一：
先创建两张测试用的表。
SQL> create table merge_01 as select employee_id,first_name,last_name,salary
2 from hr.employees
3 /
Table created.
SQL> create table merge_02 as select employee_id,first_name,last_name,salary
2 from hr.employees
3 where employee_id < 170;
Table created.
通过employee_id 列来匹配，如果匹配到，则update 目标表的salary 列，如果没有匹配到，则insert 源表中的记录。这种场景下merge 语句可以正确的完成我们的需求。
SQL> edit
Wrote file afiedt.buf
1 merge into merge_02
2 using merge_01
3 on (merge_02.employee_id = merge_01.employee_id)
4 when matched then
5 update set merge_02.salary = merge_02.salary * 1.1
6 when not matched then
7* insert values(merge_01.employee_id,merge_01.first_name,merge_01.last_name,merge_01.salary)
SQL> /
107 rows merged.
SQL> rollback;
Rollback complete.
场景二：
上面的语句可以正常的执行，把merge 语句所做的修改rollback掉，换一个场景进行测试。往merge_01 表中插入重复的数据，使得其中的每一条记录都存在一个副本。
SQL> insert into merge_01 select employee_id,first_name,last_name,salary
2 from hr.employees;
107 rows created.
SQL> select count(*) from merge_01
2 where employee_id = 100;
COUNT(*)
----------
2
再使用上面的merge 语句来操作merge_01,merge_02 表。出现ORA-30926 的错误，因为在源表merge_01 中存在两条记录匹配目标表merge_02 中的每一条记录。这情况下可以考虑添加主键约束来解决，使得源表中的记录不重复。
SQL> edit
Wrote file afiedt.buf
1 merge into merge_02
2 using merge_01
3 on (merge_02.employee_id = merge_01.employee_id)
4 when matched then
5 update set merge_02.salary = merge_02.salary * 1.1
6 when not matched then
7* insert values(merge_01.employee_id,merge_01.first_name,merge_01.last_name,merge_01.salary
SQL> /
merge into merge_02
*
ERROR at line 1:
ORA-30926: 无法在源表中获得一组稳定的行
如果把目标表truncate 掉，这样源表merge_01 就不存在多条记录，匹配目标表中的每条记录了。这时候下面的merge 语句可以“正常”的执行，但是不一定是正确的执行，因为这可能是你不想要的结果，这一点需要注意。在这种情况下，相当于
insert into merge_02 select * from merge_01;
SQL> truncate table merge_02;
Table truncated.
SQL> merge into merge_02
2 using merge_01
3 on (merge_02.employee_id = merge_01.employee_id)
4 when matched then
5 update set merge_02.salary = merge_02.salary * 1.1
6 when not matched then
7 insert values(merge_01.employee_id,merge_01.first_name,merge_01.last_name,merge_01.salary)
8 /
SQL> select * from merge_02
2 where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- ---------------- ---------------- ----------
100 Steven King 24000
100 Steven King 24000
场景三：
如果merge 语句中的目标表中存在多条记录匹配源表中的记录会是怎么样？下面我们来测试一下。
SQL> truncate table merge_01;
Table truncated.
SQL> insert into merge_01 select employee_id,first_name,last_name,salary
2 from hr.employees;
107 rows created.
SQL> commit;
Commit complete.
现在在merge_02 中存在多条记录匹配merge_01 中的每一条记录，再来执行下面的merge 语句。下面查询下employee_id = 100 的用户的salary。
SQL> select * from merge_02
2 where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- ---------------- ---------------- ----------
100 Steven King 24000
100 Steven King 24000
SQL> merge into merge_02
2 using merge_01
3 on (merge_02.employee_id = merge_01.employee_id)
4 when matched then
5 update set merge_02.salary = merge_02.salary * 1.1
6 when not matched then
7 insert values(merge_01.employee_id,merge_01.first_name,merge_01.last_name,merge_01.salary)
8 /
214 rows merged.
目标表中在源表中匹配到的每一条记录都会被更新，在中场景下merge 虽然也是"正常"执行的，但是却并一定正确，这一点需要注意。
SQL> select * from merge_02
2 where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- ---------------- ---------------- ----------
100 Steven King 26400
100 Steven King 26400
小结：merge 语句的“正常”执行，不一定意味着正确。使用merge 语句前需要对源表和目标表中的数据的状态有所了解。以便确定merge 语句做了正确的事情。