今天遇到一个奇葩的需求：系统A是主系统，终端PDA会上传数据到系统A。当有单据id在系统A的id能查到，则update状态；当单据id在系统A中查不到则insert。下面来做一个简单的测试。

drop table test;
create table test(id number primary key, name varchar2(10),state number(1));
insert into test values(1,'单据1',1);
insert into test values(2,'单据2',1);
insert into test values(3,'单据3',1);
commit;

1.当一看到这个需求，我就想到用merge into，于是写出下列SQL:

merge into test t1
using (select id from test where name = ?) t2
on(t1.id = t2.id)
when matched then
update set state = ?
when not matched then
insert (id, name, state) values (?, ?, ?);

这些写的问题,当select id from test where name = ?有值可以update成功，当没有值不会insert。

2. 改进一下

merge into test t1
using (select id from test where name = '单据1'
union select -1 from dual) t2
on(t1.id = t2.id)
when matched then
update set state = 2
when not matched then
insert (id, name, state) values (1, '单据1', 1);

这些写的也有问题,当select id from test where name = ?，select id from test where name = '单据1'不能查出来数据的时候
是ok的，当能查出来数据的时候会报主键冲突
merge into test t1
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SYS_C0065462)

3.改进一下,保证using里面的内容查出一条记录，不管是否有值
select * from test;
ID NAME STATE
---------- ---------- ----------
1 单据1 1
2 单据2 1
3 单据3 1
--PDA的单据在系统A中有

merge into test t1
using (select id from test where name = '单据1'
union all
select 1 id
from dual
where not exists (select id from test where name = '单据1')) t2
on (t1.id = t2.id)
when matched then
update set state = 2
when not matched then
insert (id, name, state) values (t2.id, '单据1', 1);

--PDA的单据在系统A中没有

merge into test t1
using (select id from test where name = '单据10'
union all
select 10 id
from dual
where not exists (select id from test where name = '单据10')) t2
on (t1.id = t2.id)
when matched then
update set state = 2
when not matched then
insert (id, name, state) values (t2.id, '单据1', 1);

select * from test;
ID NAME STATE

--------- ---------- ----------
1 单据1 2
2 单据2 1
3 单据3 1
10 单据1 1

4.如果id能够确认，那最好了，如下就变得更简洁了

--PDA的单据在系统A中有

merge into test t1
using (select 1 id from dual) t2
on (t1.id = t2.id)
when matched then
update set state = 2
when not matched then
insert (id, name, state) values (t2.id, '单据1', 1);

--PDA的单据在系统A中没有
?

merge into test t1
using (select 10 id from dual) t2
on (t1.id = t2.id)
when matched then
update set state = 2
when not matched then
insert (id, name, state) values (t2.id, '单据10', 1);

?