oracle merge into 小例

今天调试程序查看日志遇到如下日志打印

Preparing: merge into ex_mistakes_tb a using (select ? user_no, ? quest_no from dual) b on (a.user_no = b.user_no and a.quest_no = b.quest_no) when matched then update set mistake_times = mistake_times + 1, last_modi_date = ? when not matched then insert (user_no, quest_no, mistake_type, mistake_times, is_open, last_modi_date) values(?, ?, ?, ?, ?, ?) 
Parameters: jc002(String), 20170719154904504111(String), 20210812100212(String), jc002(String), 20170719154904504111(String), 00(String), 1(Integer), 1(String), 20210812100212(String)
Updates: 1

 

整理后的sql 是这样的

merge into ex_mistakes_tb a
using (select 'jc002' user_no, '20170719154908385290' quest_no from dual) b
on (a.user_no = b.user_no and a.quest_no = b.quest_no)
when matched then
  update
     set mistake_times  = mistake_times + 1,
         last_modi_date = '20210812100211'
when not matched then
  insert
    (user_no,
     quest_no,
     mistake_type,
     mistake_times,
     is_open,
     last_modi_date)
  values
    ('jc002', '20170719154908385290', '00', 1, '1', '20210812100211')

这句sql的意思是说 , 查看表中是否存在 user_no='jc002' 以及quest_no='20170719154904504111' 的记录 , 如果有就更新这条记录 , 如果没有就添加记录

这条语句出现的场景是"将一条记录添加到错题集 , 如果错题集有了就更新一下出错的次数 , 如果没有就添加一条记录"