merge into mybatis批量插入或更新，有则更新，无则插入

merge into 语法

MERGE INTO [target-table] T USING [source-table sql] S 
ON([conditional expression] and [...]...)
WHEN MATCHED
THEN [UPDATE sql]
WHEN NOT MATCHED
THEN [INSERT sql]

实际实现功能，导入接口清单表，根据接口编号判断，存在即更新，不存在即插入，separator="union all" 和 from dual的作用是将每行数据合并成一张虚拟临时表数据

<update id="saveOrUpdateInterfaceList">
        MERGE INTO INTERFACE_LIST a
        USING(
            <foreach collection="list" index="index" item="interfaceList"     
                   separator="union all">
                SELECT
                #{interfaceList.id,jdbcType=VARCHAR} id,
                #{interfaceList.projectId,jdbcType=VARCHAR} projectId,
                #{interfaceList.interfaceCode,jdbcType=VARCHAR} interfaceCode,
                #{interfaceList.requestSys,jdbcType=VARCHAR} requestSys,
                #{interfaceList.responseSys,jdbcType=VARCHAR} responseSys,
                #{interfaceList.interfaceMode,jdbcType=VARCHAR} interfaceMode,
                #{interfaceList.description,jdbcType=VARCHAR} description,
                #{interfaceList.dataFlow,jdbcType=VARCHAR} dataFlow,
                #{interfaceList.protocol,jdbcType=VARCHAR} protocol,
                #{interfaceList.interfaceStatus,jdbcType=VARCHAR} interfaceStatus,
                #{interfaceList.prdTime,jdbcType=VARCHAR} prdTime,
                #{interfaceList.remarks,jdbcType=VARCHAR} remarks
                from dual
            </foreach>
        )b
        ON (a.INTERFACE_CODE = b.interfaceCode)
        when matched then
        UPDATE SET a.PROJECT_ID = b.projectId,a.REQUEST_SYS = b.requestSys,
                   a.RESPONSE_SYS = b.responseSys,
                   a.INTERFACE_MODE = b.interfaceMode,
                   a.DESCRIPTION = 
                   b.description,a.DATA_FLOW = b.dataFlow,
                   a.PROTOCOL = b.protocol,
                   a.INTERFACE_STATUS = b.interfaceStatus,
                   a.PRD_TIME = b.prdTime,
                   a.REMARKS = b.remarks
               where a.PROJECT_ID = B.projectId
        when not matched then
        INSERT(a.ID,a.PROJECT_ID,a.INTERFACE_CODE,a.REQUEST_SYS,
               a.RESPONSE_SYS,a.INTERFACE_MODE,
               a.DESCRIPTION,a.DATA_FLOW,a.PROTOCOL,
               a.INTERFACE_STATUS,a.PRD_TIME,a.REMARKS)
        VALUES (b.id,b.projectId,b.interfaceCode,b.requestSys,
                b.responseSys,b.interfaceMode,
                b.description,b.dataFlow,b.protocol,
                b.interfaceStatus,
                b.prdTime,b.remarks)
</update>

注意，update 和 insert的数据值需要在using中取