mybatis中oracle的merge语句示例

merge into 在mybatis mapper.xml中的用法

示例

<update id="mergeTask" parameterType="java.util.List">
        MERGE INTO DM_TASK a
        USING (
        <foreach collection="list" index="index" item="item" open="" close="" separator="union all">
            SELECT
                #{item.ROW_ID, jdbcType=BIGINT} AS ROW_ID,
                CASE WHEN #{item.CREATED_BY, jdbcType=BIGINT} IS NULL THEN -1
                ELSE #{item.CREATED_BY, jdbcType=BIGINT}
                END CREATED_BY,
                CASE WHEN #{item.CREATED_TIME, jdbcType=TIMESTAMP} IS NULL THEN CURRENT_TIMESTAMP
                ELSE TO_TIMESTAMP('${item.CREATED_TIME}', 'syyyy-mm-dd hh24:mi:ss.ff')
                END CREATED_TIME,
                CASE WHEN #{item.LAST_UPD_BY, jdbcType=BIGINT} IS NULL THEN -1
                ELSE #{item.LAST_UPD_BY, jdbcType=BIGINT}
                END LAST_UPD_BY,
                CASE WHEN #{item.LAST_UPD_TIME, jdbcType=TIMESTAMP} IS NULL THEN TO_TIMESTAMP('${item.CREATED_TIME}', 'syyyy-mm-dd hh24:mi:ss.ff')
                ELSE TO_TIMESTAMP('${item.LAST_UPD_TIME}', 'syyyy-mm-dd hh24:mi:ss.ff')
                END LAST_UPD_TIME,
                CASE WHEN #{item.MODIFICATION_NUM, jdbcType=BIGINT} IS NULL THEN 0
                ELSE #{item.MODIFICATION_NUM, jdbcType=BIGINT}
                END MODIFICATION_NUM,
                CASE WHEN #{item.TASK_NO, jdbcType=VARCHAR} IS NULL THEN '0'
                ELSE #{item.TASK_NO, jdbcType=VARCHAR}
                END TASK_NO,
                CASE WHEN #{item.TASK_TYPE, jdbcType=CHAR} IS NULL THEN '0'
                ELSE #{item.TASK_TYPE, jdbcType=VARCHAR}
                END TASK_TYPE,
                #{item.SP_BATCH_NO, jdbcType=VARCHAR} AS SP_BATCH_NO,
                CASE WHEN #{item.ORDER_DATE, jdbcType=DATE} IS NULL THEN NULL
                ELSE TO_TIMESTAMP('${item.ORDER_DATE}', 'syyyy-mm-dd hh24:mi:ss.ff')
                END ORDER_DATE,
                CASE WHEN #{item.DISTRIB_DATE, jdbcType=DATE} IS NULL THEN TO_TIMESTAMP('${item.CREATED_TIME}', 'syyyy-mm-dd hh24:mi:ss.ff')
                ELSE TO_TIMESTAMP('${item.DISTRIB_DATE}', 'syyyy-mm-dd hh24:mi:ss.ff')
                END DISTRIB_DATE,
                #{item.PARENT_TASK_NO, jdbcType=VARCHAR} AS PARENT_TASK_NO,
                #{item.FROM_STATION_ID, jdbcType=BIGINT} AS FROM_STATION_ID,
                #{item.ROUTE_ID, jdbcType=BIGINT} AS ROUTE_ID,
                #{item.ROUTE_CODE, jdbcType=VARCHAR} AS ROUTE_CODE,
                #{item.ROUTE_NAME, jdbcType=VARCHAR} AS ROUTE_NAME,
                #{item.VEHICLE_ID, jdbcType=BIGINT} AS VEHICLE_ID,
                #{item.VEHICLE_SIGN, jdbcType=VARCHAR} AS VEHICLE_SIGN,
                #{item.DRIVER_ID, jdbcType=BIGINT} AS DRIVER_ID,
                #{item.DRIVER_NAME, jdbcType=VARCHAR} AS DRIVER_NAME
            FROM DUAL
        </foreach>
        ) b
        ON (a.TASK_NO = b.TASK_NO)
        WHEN MATCHED THEN UPDATE SET
            a.ROUTE_ID = b.ROUTE_ID,
            a.ROUTE_CODE = b.ROUTE_CODE,
            a.ROUTE_NAME = b.ROUTE_NAME,
            a.VEHICLE_ID = b.VEHICLE_ID,
            a.VEHICLE_SIGN = b.VEHICLE_SIGN,
            a.DRIVER_ID = b.DRIVER_ID,
            a.DRIVER_NAME = b.DRIVER_NAME
        WHEN NOT MATCHED THEN INSERT
            (ROW_ID, CREATED_BY, CREATED_TIME, LAST_UPD_BY, LAST_UPD_TIME, MODIFICATION_NUM,
             TASK_NO, TASK_TYPE, SP_BATCH_NO, ORDER_DATE, DISTRIB_DATE, PARENT_TASK_NO,                          FROM_STATION_ID, ROUTE_ID, ROUTE_CODE, ROUTE_NAME, VEHICLE_ID, VEHICLE_SIGN,                        DRIVER_ID, DRIVER_NAME
            )
        VALUES
            (b.ROW_ID, b.CREATED_BY, b.CREATED_TIME, b.LAST_UPD_BY, b.LAST_UPD_TIME,                            b.MODIFICATION_NUM, b.TASK_NO, b.TASK_TYPE, b.SP_BATCH_NO, b.ORDER_DATE,                            b.DISTRIB_DATE, b.PARENT_TASK_NO, b.FROM_STATION_ID, b.ROUTE_ID, b.ROUTE_CODE,                      b.ROUTE_NAME, b.VEHICLE_ID, b.VEHICLE_SIGN, b.DRIVER_ID, b.DRIVER_NAME
           )
    </update>

1.语法：

merge into 数据库表名 a 
using( 传过来的数据集（此数据集要与数据库表的字段映射上） ) b 
on (a.字段名 = b.字段名 ... 还可以再加条件)
when matched then update set ( a.字段名 = b.字段名, ...... )
when not matched then insert (字段名，......) values ( b.字段名, ...... )

2.说明

（1）示例中传过来的参数是list<map<string,object>>

（2）用foreach循环，循环内用select 查询每一项，用union all连接。

       select #{item.名字，jdbcType=VARCHAR} as 数据库对应的字段名称
              .......
              .......
       from dual