批量修改 插入 orcale

批量修改

<update id="updatetList" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
            update PMS_RVORDERD
            <set>
                <if test="item.orgCode != null and item.orgCode !='' ">
                    ORG_CODE = #{item.orgCode,jdbcType=VARCHAR},
                </if>
                <if test="item.updateUserName != null and item.updateUserName!=''">
                    UPDATE_USER_NAME = #{item.updateUserName,jdbcType=VARCHAR},
                </if>
            </set>
            where id = #{item.id,jdbcType=DECIMAL}
        </foreach>
    </update>

批量插入

<insert id="insertList" parameterType="list">
        insert into PMS_RVORDERD
        (
        ID, ORG_CODE, RV_ORDER_CODE    
        )
        select SEQ_PMS_RVORDERD.nextval,t.* from
        (
        <foreach item="item" collection="list" separator="union all">
            select
            #{item.orgCode,jdbcType=VARCHAR},#{item.rvOrderCode,jdbcType=VARCHAR}
            from dual
        </foreach>
        ) t
    </insert>

查询与修改

<update id="updateActualArrivalQtyByList">
        update pms_rvorderd r
        set (actual_arrival_qty,arrival_qty,INDICATED__QTY,UNINDICATED__QTY)=
        (
        select t.actual_arrival_qty,t.arrival_qty,t.INDICATED__QTY,t.UNINDICATED__QTY
        from
        (
        <foreach collection="rvorderdList" item="rvorderd" separator="union all">
            select #{rvorderd.id} as id,
            #{rvorderd.actualArrivalQty} as actual_arrival_qty,
            #{rvorderd.arrivalQty} as arrival_qty,
            #{rvorderd.indicatedQty} as INDICATED__QTY,
            #{rvorderd.unindicatedQty} as UNINDICATED__QTY
            from dual
        </foreach>
        )t where t.id=r.id
        )where exists
        (
        select 1 from
        (
        <foreach collection="rvorderdList" item="rvorderd" separator="union all">
            select #{rvorderd.id} as id,
            #{rvorderd.actualArrivalQty} as actual_arrival_qty,
            #{rvorderd.arrivalQty} as arrival_qty,
            #{rvorderd.indicatedQty} as INDICATED__QTY,
            #{rvorderd.unindicatedQty} as UNINDICATED__QTY
            from dual
        </foreach>
        )t1 where t1.id=r.id
        )
</update>