说明
1、数据导入功能,存在全量更新/增量更新的问题,简单记录使用 oracel 的 merge into 函数。
2、全量更新(覆盖模式):数据库存在该条数据时,做更新操作。不存在时,做新增操作
3、增量更新(跳过模式):数据库存在该条数据时,不做任何操作,不存在时,做新增操作。
4、merge into 对千万级以上的数据更新,速度比较慢。(没有尝试过)
merge into 语法
merge into [target-table] A
using [source-table sql] B
on ([conditional expression] and [...]...)
when matched then -- 当on中的条件匹配时
[update sql] -- 执行操作 更新或删除等
when not matched then -- 当on中的条件不匹配时
[insert sql] -- 执行操作 新增
Oracle 示例脚本(单条)
/************** 全量更新(覆盖模式)*****************/
merge into im_supply_function v
using (select '25608A5CCC974883991DD6B6B58D25BF' supply_service_code,
'BDD204089A96497985972B15F160F986' function_code,
'功能名称' function_name,
'www.baidu.com' target,
'2019-03-01 14:03:35' create_time,
'创建人' create_user,
'备注' remarks
from dual) d
on (v.function_code = d.function_code) -- 这里通过主键判断,数据是否存在
when matched then
update set
v.supply_service_code = d.supply_service_code,
v.function_name = d.function_name,
v.target = d.target,
v.create_time = to_date(d.create_time, 'yyyy-mm-dd hh24:mi:ss'),
v.create_user = d.create_user,
v.remarks = d.remarks
when not matched then
insert (
v.supply_service_code,
v.function_code,
v.function_name,
v.target,
v.create_time,
v.create_user,
v.remarks)
values (
d.supply_service_code,
d.function_code,
d.function_name,
d.target,
to_date(d.create_time, 'yyyy-mm-dd hh24:mi:ss'),
d.create_user,
d.remarks
) ;
/************** 增量更新(跳过模式)*****************/
merge into im_supply_function v
using (select '25608A5CCC974883991DD6B6B58D25BF' supply_service_code,
'BDD204089A96497985972B15F160F986' function_code,
'功能名称' function_name,
'www.baidu.com' target,
'2019-03-01 14:03:35' create_time,
'创建人' create_user,
'备注' remarks
from dual) d
on (v.function_code = d.function_code) -- 这里通过主键判断,数据是否存在
when not matched then
insert (
v.supply_service_code,
v.function_code,
v.function_name,
v.target,
v.create_time,
v.create_user,
v.remarks)
values (
d.supply_service_code,
d.function_code,
d.function_name,
d.target,
to_date(d.create_time, 'yyyy-mm-dd hh24:mi:ss'),
d.create_user,
d.remarks
) ;
Oracle 示例脚本(批量- 覆盖模式)
merge into im_supply_function v
using (<foreach collection="list" index="index" item="item"
open="(" close=")" separator="union">
SELECT
#{item.supply_service_code,jdbcType=VARCHAR} as supply_service_code,
#{item.function_code,jdbcType=VARCHAR} as function_code,
#{item.function_name,jdbcType=VARCHAR} as function_name,
#{item.target,jdbcType=VARCHAR} as target,
#{item.create_time,jdbcType=TIMESTAMP} as create_time,
#{item.create_user,jdbcType=VARCHAR} as create_user,
#{item.remarks,jdbcType=VARCHAR} as remarks
FROM dual
</foreach>) d
on (v.function_code = d.function_code) -- 这里通过主键判断,数据是否存在
when matched then
update set
v.supply_service_code = d.supply_service_code,
v.function_name = d.function_name,
v.target = d.target,
v.create_time = to_date(d.create_time, 'yyyy-mm-dd hh24:mi:ss'),
v.create_user = d.create_user,
v.remarks = d.remarks
when not matched then
insert (
v.supply_service_code,
v.function_code,
v.function_name,
v.target,
v.create_time,
v.create_user,
v.remarks)
values (
d.supply_service_code,
d.function_code,
d.function_name,
d.target,
to_date(d.create_time, 'yyyy-mm-dd hh24:mi:ss'),
d.create_user,
d.remarks
) ;
值得注意的是:覆盖模式下,on()中判断数据是否存在的字段,在update set的时候不能给该字段set值,否则会报错。
list为接口入参List<T>