oracle 自增主键实现批量更新和增加sql

增加：

方案1

<!-- 使用sql 实现 -->
    <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false" >
        INSERT INTO WF_TASKTEMP_DEALSTAFF (DEAL_ID, TEMP_ID, STAFF_ID,DEAL_TYPE, STATUS)
        SELECT WF_TASKTEMP_DEALSTAFF$SEQ.NEXTVAL,a.* FROM (
        <foreach collection="list" item="item" separator="union all">
            SELECT
            #{item.tempId,jdbcType=VARCHAR},
            #{item.staffId,jdbcType=VARCHAR},
            #{item.dealType,jdbcType=VARCHAR},
            #{item.status,jdbcType=CHAR}
            FROM dual
        </foreach>
        ) a
    </insert>

方案二

<!-- 使用存储过程实现 -->       
 <insert id="insertBatch1" parameterType="java.util.List">
            begin
            <foreach collection="list" item="item" index="index">
                insert into WF_TASKTEMP_DEALSTAFF
                (
                  DEAL_ID, TEMP_ID, STAFF_ID,DEAL_TYPE, STATUS
                )
                values(WF_TASKTEMP_DEALSTAFF$SEQ.nextval,
                    #{item.tempId,jdbcType=VARCHAR},
                    #{item.staffId,jdbcType=VARCHAR},
                    #{item.dealType,jdbcType=VARCHAR},
                    #{item.status,jdbcType=CHAR}
                );
            </foreach>
            end;
        </insert>

修改

oracle

<update id="batchUpdateSplitSinglePickCurrency" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            UPDATE ZC_TR_MULTI_ORDER_CURRENCY
            <set>
                <if test="item.sysCorderCode != null">
                    SYS_CORDER_CODE = #{item.sysCorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.sysPorderCode != null">
                    SYS_PORDER_CODE = #{item.sysPorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.bizPorderCode != null">
                    BIZ_PORDER_CODE = #{item.bizPorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.originalOrderCode != null">
                    ORIGINAL_ORDER_CODE = #{item.originalOrderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.splitUserId != null">
                    SPLIT_USER_ID = #{item.splitUserId,jdbcType=VARCHAR},
                </if>

                <if test="item.createDate != null">
                    CREATE_DATE = #{item.createDate},
                </if>

                <if test="item.updateDate != null">
                    UPDATE_DATE = #{item.updateDate},
                </if>
            </set>
            where id = #{item.id,jdbcType=VARCHAR}
        </foreach>
    </update>

java接口

    int batchUpdateSplitSinglePickCurrency(@Param(value = "list") List<MultiOrderCurrency> list) throws Exception;

附赠mysql

<update id="batchUpdateSplitSinglePickCurrency" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            UPDATE ZC_TR_MULTI_ORDER_CURRENCY
            <set>
                <if test="item.sysCorderCode != null">
                    SYS_CORDER_CODE = #{item.sysCorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.sysPorderCode != null">
                    SYS_PORDER_CODE = #{item.sysPorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.bizPorderCode != null">
                    BIZ_PORDER_CODE = #{item.bizPorderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.originalOrderCode != null">
                    ORIGINAL_ORDER_CODE = #{item.originalOrderCode,jdbcType=VARCHAR},
                </if>

                <if test="item.splitUserId != null">
                    SPLIT_USER_ID = #{item.splitUserId,jdbcType=VARCHAR},
                </if>

                <if test="item.createDate != null">
                    CREATE_DATE = #{item.createDate},
                </if>

                <if test="item.updateDate != null">
                    UPDATE_DATE = #{item.updateDate},
                </if>
            </set>
            where id = #{item.id,jdbcType=VARCHAR}
        </foreach>
    </update>

java接口

    void updateMatchs(@Param(value = "matchs")List<MatchBasic> matchs);