ORACLE 批量插入更新删除sql
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.nari.devices.mapper.KeyScheduleUpdateMapper"> <insert id="insertBatch"> INSERT INTO REC_METER_KEY_SCHEDULE_UPDATE (TASK_ID, METER_ID, UPDATE_CYCLE, KEY_TYPE, BEGIN_TIME, STATUS, CREATE_TIME, UPDATE_TIME, SOURCE, CLIENT_TYPE) <foreach collection="keyScheduleUpdateDtoList" item="item" separator=" UNION ALL "> SELECT #{item.taskId}, #{item.meterId}, #{item.updateCycle}, #{item.keyType}, #{item.beginTime}, #{item.status}, SYSDATE, #{item.updateTime}, #{item.source}, #{item.clientType} FROM DUAL </foreach> </insert> <update id="updateBatch" parameterType="java.util.List"> <foreach collection="keyScheduleUpdateDtoList" item="item" index="index" separator=";" open="BEGIN" close=";END;"> UPDATE REC_METER_KEY_SCHEDULE_UPDATE SET UPDATE_CYCLE = #{item.updateCycle,jdbcType=INTEGER}, KEY_TYPE = #{item.keyType,jdbcType=INTEGER}, BEGIN_TIME = #{item.beginTime,jdbcType=DATE}, STATUS = #{item.status,jdbcType=INTEGER}, UPDATE_TIME = #{item.updateTime,jdbcType=DATE}, SOURCE = #{item.source,jdbcType=INTEGER}, CLIENT_TYPE =#{item.clientType,jdbcType=INTEGER} WHERE TASK_ID = #{item.taskId} and METER_ID = #{item.meterId,jdbcType=INTEGER} </foreach> </update> <delete id="deleteBatch" parameterType="java.util.List"> delete from REC_METER_KEY_SCHEDULE_UPDATE where TASK_ID in <foreach item="item" index="index" collection="meterKeys" open="(" separator="," close=")"> #{item} </foreach> </delete> </mapper>
原文地址:https://blog.csdn.net/qq_44981598/article/details/142925019
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!