student表结构
字段名称 类型 注释
ID NUMBER 主键
NAME VARCHAR 姓名
AGE NUMBER 年龄
VALID_START_TIME DATE 有效开始时间
VALID_END_TIME DATE 有效结束时间
oracle执行upsert的sql语句
BEGIN
UPDATE STUDENT SET NAME = 'ZHANGSAN'
WHERE ID=200002;
IF
SQL%NOTFOUND THEN
INSERT INTO STUDENT (ID,NAME,AGE) VALUES (200002,'ZHANGSAN',7);
END IF;
END;
mybatis批量执行upsert的sql定义语句
<insert id="batchSetStudent" parameterType="list">
<foreach collection="addList" index="index" item="map" open="BEGIN" close="END;">
UPDATE STUDENT
SET VALID_START_TIME=to_date(#{map.startTime},'yyyy-MM-dd hh24:mi:ss'),VALID_END_TIME=to_date(#{map.expireTime},'yyyy-MM-dd hh24:mi:ss')
WHERE ID=#{map.id};
IF
SQL%NOTFOUND THEN
INSERT INTO STUDENT (ID,NAME,AGE
<if test="map.startTime != null and map.startTime != ''">
,VALID_START_TIME
</if>
<if test="map.expireTime != null and map.expireTime != ''">
,VALID_END_TIME
</if>
)
VALUES (#{map.id},#{map.name},#{map.age}
<if test="map.startTime != null and map.startTime != ''">
,to_date(#{map.startTime},'yyyy-MM-dd hh24:mi:ss')
</if>
<if test="map.expireTime != null and map.expireTime != ''">
,to_date(#{map.expireTime},'yyyy-MM-dd hh24:mi:ss')
</if>
);
END IF;
</foreach>
</insert>