Oracle SQL合并语句只有1个表和一堆值

我正在使用 Spring JDBC和oracle SQL.

使用SpringJDBC类MapSqlParameterSource,我已经映射了我想要合并的数据.

现在我想使用merge语句来更新/插入数据库表.我只有一个表和一堆我想要合并到它的参数.

merge into proj.person_registry pr
using ( ! parameters should go here somehow? )
on (pr.id = :id or pr.code = :code)
when matched then 
update set pr.code             = :code,pr.name                 = :name,pr.firstname            = :firstname,pr.cl_gender            = :cl_gender,pr.cl_status            = :cl_status,pr.aadress              = :aadress,pr.aadress_date         = :aadress_date 
when not matched then
insert values (:code,:name,:firstname,:cl_gender,:cl_status,;aadress,:aadress_date);

我是否必须以某种方式为using关键字创建临时表,还是有另一种方式？我怎么会这样合并呢？

pr.id和pr.code也有两个唯一的键.有时参数：id为null,当发生这种情况时,我想根据pr.code匹配到：code来到达update语句.如果我的更新包含该行,它仍然可以工作：

update set pr.code             = :code,

解决方法

merge into proj.person_registry pr
using ( 
  select 42 as id
         'xyz' as code,'Dent' as name,'Arthur' as firstname,'male' as cl_gender
         'closed' as cl_status,'Somewher' as aaddress,current_date as aaddress_date
   from dual
) t on (pr.id = t.id or pr.code = t.code)
when matched then 
update set pr.code             = t.code,pr.name                 = t.name,pr.firstname            = t.firstname,pr.cl_gender            = t.cl_gender,pr.cl_status            = t.cl_status,pr.aadress              = t.aadress,pr.aadress_date         = t.aadress_date 
when not matched then
insert values (t.code,t.name,t.firstname,t.cl_gender,t.cl_status,t.aadress_date);

我不熟悉Spring的JDBC模板,但是通过参数占位符替换select … from dual query中的实际值应该可行.

（编辑：淮北站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!