merge into
table_a a
using
table_b b
on(a.id=b.id)
when
not matched
then
insert(a.id,a.col,a.col2.....) values(b.id,b.col,b.col2.......)
列子:(mybatis中的xml)
入参:对象
表b根据入参创建,然后判断表b跟表a的主键是否冲突,不冲突则插入
<insert id="ignoreSelective" databaseId="oracle" parameterType="com.synjones.cloudcard.attence.infra.entity.AttenceTempWorkEntity" >
merge into
attence_temp_work a
using
(select
#{custMemberId,jdbcType=CHAR} CUST_MEMBER_ID,
#{date,jdbcType=CHAR} "DATE",
#{custId,jdbcType=CHAR} CUST_ID,
#{workTimeId,jdbcType=INTEGER} WORK_TIME_ID,
#{workNature,jdbcType=CHAR} WORK_NATURE,
#{overWorkFlag,jdbcType=CHAR} OVER_WORK_FLAG,
#{overWorkTime,jdbcType=INTEGER} OVER_WORK_TIME,
#{createdOperCode,jdbcType=CHAR} CREATED_OPER_CODE,
#{createdStamp,jdbcType=TIMESTAMP} CREATED_STAMP,
#{lastUpdatedStamp,jdbcType=TIMESTAMP} LAST_UPDATED_STAMP,
#{lastUpdatedOperCode,jdbcType=CHAR} LAST_UPDATED_OPER_CODE,
#{workAroundId,jdbcType=INTEGER} WORK_AROUND_ID
from dual
) b
on(a.CUST_MEMBER_ID=b.CUST_MEMBER_ID and a."DATE"=b."DATE")
when not matched
then
insert
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="custMemberId != null" >
a.cust_member_id,
</if>
<if test="date != null" >
a."DATE",
</if>
<if test="custId != null" >
a.cust_id,
</if>
<if test="workTimeId != null" >
a.work_time_id,
</if>
<if test="workNature != null" >
a.work_nature,
</if>
<if test="overWorkFlag != null" >
a.over_work_flag,
</if>
<if test="overWorkTime != null" >
a.over_work_time,
</if>
<if test="createdOperCode != null" >
a.created_oper_code,
</if>
<if test="createdStamp != null" >
a.created_stamp,
</if>
<if test="lastUpdatedStamp != null" >
a.last_updated_stamp,
</if>
<if test="lastUpdatedOperCode != null" >
a.last_updated_oper_code,
</if>
<if test="workAroundId != null" >
a.work_around_id,
</if>
</trim>
<trim prefix="VALUES (" suffix=")" suffixOverrides="," >
<if test="custMemberId != null" >
#{custMemberId,jdbcType=CHAR},
</if>
<if test="date != null" >
#{date,jdbcType=CHAR},
</if>
<if test="custId != null" >
#{custId,jdbcType=CHAR},
</if>
<if test="workTimeId != null" >
#{workTimeId,jdbcType=INTEGER},
</if>
<if test="workNature != null" >
#{workNature,jdbcType=CHAR},
</if>
<if test="overWorkFlag != null" >
#{overWorkFlag,jdbcType=CHAR},
</if>
<if test="overWorkTime != null" >
#{overWorkTime,jdbcType=INTEGER},
</if>
<if test="createdOperCode != null" >
#{createdOperCode,jdbcType=CHAR},
</if>
<if test="createdStamp != null" >
#{createdStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedStamp != null" >
#{lastUpdatedStamp,jdbcType=TIMESTAMP},
</if>
<if test="lastUpdatedOperCode != null" >
#{lastUpdatedOperCode,jdbcType=CHAR},
</if>
<if test="workAroundId != null" >
#{workAroundId,jdbcType=INTEGER},
</if>
</trim>
</insert>