mybatis操作oracle数据库通过入参List实现批量新增和修改

批量新增
注意：由于是oracle 数据库，批量新增的时候和其他数据不一样，批量新增的时候必须遍历查询通过 UNION ALL 连接成临时表再进行批量添加，我这里入参是List<Map<String,Object>>

<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
        insert into phf_budget(pk_id,fk_report_record_id,subject_id,budget_number,unit_price,money_total,creator_user_id)
        select *
        from(
        <foreach collection="list" item="map" index="index" separator="UNION ALL">
            SELECT
                SYS_GUID() as pk_id,
                <choose>
                    <when test="fkReportRecordId != null">
                        #{fkReportRecordId}
                    </when>
                    <otherwise>
                        ''
                    </otherwise>
                </choose>
                as fk_report_record_id,
                <choose>
                    <when test="map.subjectId != null">
                        #{map.subjectId}
                    </when>
                    <otherwise>
                        ''
                    </otherwise>
                </choose>
                as subject_id,
                <choose>
                    <when test="map.budgetNumber != null and map.budgetNumber != ''">
                        #{map.budgetNumber}
                    </when>
                    <otherwise>
                        '0'
                    </otherwise>
                </choose>
                as budget_number,
                <choose>
                    <when test="map.unitPrice != null and map.unitPrice != null">
                        #{map.unitPrice}
                    </when>
                    <otherwise>
                        '0'
                    </otherwise>
                </choose>
                as unit_price,
                <choose>
                    <when test="map.moneyTotal != null and map.moneyTotal != null">
                        #{map.moneyTotal}
                    </when>
                    <otherwise>
                        '0'
                    </otherwise>
                </choose>
                as money_total,
                <choose>
                    <when test="userId != null">
                        #{userId}
                    </when>
                    <otherwise>
                        ''
                    </otherwise>
                </choose>
                as creator_user_id
            from dual
        </foreach>
        )
    </insert>

eg1：

 
INSERT INTO CRD_SUBSIDY_AMOUNT (  
		id,  
		DELETED,  
		CREATED_BY,  
		CREATED_DATE,  
		ORGANIZATION_ID,  
		REMARK,  
		SUBSIDY_GRANT_AMT,  
		MONTH_OF_YEAR,  
		SURPLUS_AMT,  
		USED_AMT,  
		AMOUNT_ID,  
		ALLOW_USED_SUBSIDY_AMT,  
		STAFF_ID,  
		STAFF_NAME   
	)  
SELECT  A.*
FROM(
<foreach collection="list" item="item" index="index" separator="UNION ALL">
 SELECT
			 #{ item.id,jdbcType=VARCHAR } id,  
			 #{ item.del ,jdbcType=NUMBER} DELETED, 
			 #{ item.createdBy,jdbcType=VARCHAR } CREATED_BY,    
			 #{ item.createdDate,jdbcType=TIMESTAMP} CREATED_DATE, 
			 #{ item.organizationId,jdbcType=VARCHAR } ORGANIZATION_ID, 
			 #{ item.remark,jdbcType=VARCHAR } REMARK,  
			 #{ item.subsidyGrantAmt,jdbcType=VARCHAR } SUBSIDY_GRANT_AMT, 
			 #{ item.monthOfYear,jdbcType=VARCHAR } MONTH_OF_YEAR, 
			 #{ item.surplusAmt ,jdbcType=VARCHAR} SURPLUS_AMT, 
			 #{ item.usedAmt,jdbcType=VARCHAR } USED_AMT, 
			 #{ item.amountId ,jdbcType=VARCHAR} AMOUNT_ID,  
			 #{ item.allowUsedSubsidyAmt,jdbcType=VARCHAR } ALLOW_USED_SUBSIDY_AMT,  
			 #{ item.staffId,jdbcType=VARCHAR } STAFF_ID, 
			 #{ item.staffName,jdbcType=VARCHAR } STAFF_NAME  
     FROM dual
   </foreach>
   )A

eg2：

<insert id="insert" parameterType="ResultVo">
        insert all
        <foreach collection="resultList" item="result">
            into RM_PLATE_RESULT (VC_PLATE_CODE,VC_COMPONENT_CODE,VC_COMPONENT_NAME,
            <if test="rmPlateResult.daStartDate !=null and rmPlateResult.daStartDate != ''">
                D_START_DATE,
            </if>
            <if test="rmPlateResult.daEndDate !=null and rmPlateResult.daEndDate != ''">
                D_END_DATE,
            </if>
            VC_PLATE_STATUS,VC_CRATE_USER,D_CREATE)
            values (
            #{rmPlateResult.vcPlateCode},
            #{rmPlateResult.vcComponentCode},
            #{rmPlateResult.vcComponentName},
            <if test="rmPlateResult.daStartDate !=null and rmPlateResult.daStartDate != ''">
                to_date(#{rmPlateResult.daStartDate},'yyyy-MM-dd hh24:mi:ss'),
            </if>
            <if test="rmPlateResult.daEndDate !=null and rmPlateResult.daEndDate != ''">
                to_date(#{rmPlateResult.daEndDate},'yyyy-MM-dd hh24:mi:ss'),
            </if>
            #{rmPlateResult.vcPlateStatus},
            #{rmPlateResult.vcCrateUser},
            sysdate
            )
        </foreach>
        select * from dual
    </insert>

批量更新
orcale 批量更新的时候在标签未必须加 begin — end

<update id="updateBatch" parameterType="java.util.List">
        <if test="list!=null">
            begin
            <foreach collection="list" item="item" index= "index" open="" close="" separator =";">
                update phf_budget
                <set>
                    <if test="item.budgetNumber != null">budget_number = #{item.budgetNumber},</if>
                    <if test="item.unitPrice != null">unit_price = #{item.unitPrice},</if>
                    <if test="item.moneyTotal != null">money_total = #{item.moneyTotal},</if>
                    last_modification_time = SYSDATE,
                    <if test="userId != null">last_modifier_user_id = #{userId},</if>
                </set>
                <where>
                    pk_id=#{item.pkId}
                </where>
            </foreach>
            ;end;
        </if>
    </update>