mybatis利用xml做批量插入和批量更新(oracle数据库)

数据库:oracle

批量插入

第一种：

<insert id="insertBatch">
    insert into PREEXP_BOX_CONTROL(ROW_ID,ORD_NO, ITEM_NO, PLANT_CODE, WARE_CODE, MAT_CODE, MAT_NAME, LOT_NO,
    STORAGE_BOX_ID, BOX_CODE, CELL_CODE, QTY, CTRL_REMARK, MFG_DATE, EX_DATE, DATE_CODE, EX_STATUS, FLAG, STATUS,
    DELIV_DATE, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, DEL_FLAG)
    (
    <foreach collection="entities" item="entity" index="" separator="union all">
        select
        #{entity.rowId},#{entity.ordNo}, #{entity.itemNo}, #{entity.plantCode}, #{entity.wareCode}, #{entity.matCode},
        #{entity.matName}, #{entity.lotNo}, #{entity.storageBoxId}, #{entity.boxCode}, #{entity.cellCode},
        #{entity.qty}, #{entity.ctrlRemark}, #{entity.mfgDate}, #{entity.exDate}, #{entity.dateCode},
        #{entity.exStatus}, #{entity.flag}, #{entity.status}, #{entity.delivDate}, #{entity.createBy},
        #{entity.createDate}, #{entity.updateBy}, #{entity.updateDate}, #{entity.delFlag}
        from
        dual
    </foreach>
    )
</insert>

第二种：

<insert id="batchInsertUserWare" >
    insert all
    <foreach collection="list" item="item" >
        into
        sys_user_ware
        (
            id,
            user_id,
            ware_id,
            del_flag,
            create_by,
            create_date,
            update_by,
            update_date
        )
        values
        (
            #{item.id},
            #{item.userId},
            #{item.wareId},
            '0',
            #{item.createBy},
            current_timestamp,
            #{item.updateBy},
            current_timestamp
        )
    </foreach>
    select 1 from dual
</insert>

批量更新：

<update id="batchUpdateInventoryOnHold">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator="; ">
        UPDATE TR_INVENTORY
        SET
        <if test="updateBy != null and updateBy != ''">
            update_by = #{updateBy},
        </if>
        HOLD_ALLOCATED_QTY = HOLD_ALLOCATED_QTY - #{item.qty},
        ONHAND_QTY = ONHAND_QTY - #{item.qty},
        ONHOLD_QTY=ONHOLD_QTY - #{item.qty}
        WHERE
        DEL_FLAG='0'
        AND
        WARE_CODE=#{item.wareCode}
        AND
        MAT_CODE=#{item.matCode}
        <if test="item.lotNo != null and item.lotNo != ''">
            AND
            LOT_NO=#{item.lotNo}
        </if>
    </foreach>
</update>

 <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            update
            TR_STORAGE_BOX
            set

            FLAG =
            CASE WHEN HOLD_TYPE = '00'
            THEN
            'AVAILABLE'
            ELSE
            'ON_HOLD'
            END,

            HOLD_REASONS =
                CASE WHEN HOLD_TYPE = '00'
            THEN
            null
            ELSE
            replace(HOLD_REASONS,',出库卡控系统自动冻结','')
            END,

            HOLDTYPE_DESP =
                CASE WHEN HOLD_TYPE = '00'
            THEN
            null
            ELSE
            replace(HOLDTYPE_DESP,',出库卡控系统自动冻结','')
            END,

            HOLD_TYPE =
                CASE WHEN HOLD_TYPE = '00'
            THEN
            null
            ELSE
            replace(HOLD_TYPE,',00','')
            END
            where
            id in(
            select distinct  t1.id
            from TR_STORAGE_BOX t1
            left join tr_storage_box_detail t2 on t1.ID=t2.STORAGE_BOX_ID
            where t1.DEL_FLAG='0'
            and t2.DEL_FLAG='0'
            and t1.WARE_ID = #{item.wareId}
            and t2.MAT_ID = #{item.matId}
            and t2.C_STREAM_CODE = #{item.cLotNo}
            and t1.STATUS IN ('PUTAWAY')
            and t1.FLAG IN ('ON_HOLD')
            )
        </foreach>