批量插入（Oracle，PLSQL）

批量插入

Oracle 批量插入

		INSERT all
		<foreach collection ="list" item="item" index= "index">
		 INTO tablename
			(
			<if test="item.column2 != null">column2,</if>
			<if test="item.column3 != null">column3,</if>
			<if test="item.column4 != null">column4,</if>
			INSERTTIMEFORHIS
			)
			VALUES
			(
			<if test="item.column2 != null and item.column2 != ''"> #{item.column2,jdbcType=VARCHAR},</if>
			<if test="item.column3 != null">#{item.column3,jdbcType=VARCHAR},</if>
			<if test="item.column4 != null">#{item.column4,jdbcType=VARCHAR},</if>
			sysdate
			)
		</foreach>
		select 1 from dual

PLSQL批量插入

		INSERT INTO tablename (
			column1,
			column2,
			column3,
			column4
		)
		VALUES
		<foreach collection ="list" item="one" separator =",">
			(
			#{one.column1},
			#{one.column2},
			#{one.column3},
			#{one.column4}
			)
		</foreach>

批量更新

    <update id="importExcelService">
        <foreach collection="list" item="item" separator=";">
            update tablename set
                column1= #{item.column1},
                column2 = #{item.column2},
                column3= #{item.column3},
                column4= #{item.column4},
                suretime= CURRENT_TIMESTAMP
            where column5 = #{item.column5}
        </foreach>
    </update>