oracle中对insert into on duplicate key 语句的替代

insert into on duplicate key 是mysql中的语句，oracle中没有直接对应。不过采用merge into语句可以起到相同效果。

比如有一个表TTT，我们在no,start_time,code三个字段上建立了唯一索引；希望插入新记录时，如果在唯一索引上有重复的就更新，没有就直接插入。

MERGE INTO TTT
USING 
  (select count(*) num from TTT where no = '7099' and start_time = to_date('20200817204243','yyyyMMddHH24miss') and code = '01060') q 
ON (q.num=1) 
WHEN MATCHED THEN
  update set end_time = to_date('20200817210000','yyyyMMddHH24miss'), state = 1 where  no = '7099' and start_time = to_date('20200817204243','yyyyMMddHH24miss') and code = '01060'
WHEN NOT MATCHED THEN 
  insert (id, no, sec, stor_time, o_time, start_time, end_time, state, frs, code) 
    values('5', '7099', '1', TO_DATE('2020-08-17 20:52:32', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-08-17 20:52:32', 'SYYYY-MM-DD HH24:MI:SS'),
           TO_DATE('2020-08-17 22:31:31', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-08-17 22:31:50', 'SYYYY-MM-DD HH24:MI:SS'),'0','145','01200');

后经人指点，发现可以改成下面这样，效率提高：

MERGE INTO TTT
USING dual ON (no = '7099' and start_time = to_date('20200817204243','yyyyMMddHH24miss') and code = '01060')
WHEN MATCHED THEN
  update set end_time = to_date('20200817210000','yyyyMMddHH24miss'), state = 1 where  no = '7099' and start_time = to_date('20200817204243','yyyyMMddHH24miss') and code = '01060'
WHEN NOT MATCHED THEN 
  insert (id, no, sec, stor_time, o_time, start_time, end_time, state, frs, code) 
    values('5', '7099', '1', TO_DATE('2020-08-17 20:52:32', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-08-17 20:52:32', 'SYYYY-MM-DD HH24:MI:SS'),
           TO_DATE('2020-08-17 22:31:31', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2020-08-17 22:31:50', 'SYYYY-MM-DD HH24:MI:SS'),'0','145','01200');

新语句使用了dual表；避免了count(*)操作。update部分，需要加where条件，否则可能就变成了全表更新；insert部分实际上和条件之间没有什么直接关联，应该由外部程序（比如JDBC中的PreparedStatement设置参数时)来保持一致。