批量保存或者更新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.zto.cloudcall.mapper.ZtoSupplierEquipmentBillMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.zto.cloudcall.model.ZtoSupplierEquipmentBill">
        <id column="ID" property="id"/>
        <result column="MONTH" property="month"/>
        <result column="SITE_NAME" property="siteName"/>
        <result column="SITE_CODE" property="siteCode"/>
        <result column="SITE_ID" property="siteId"/>
        <result column="BILLING_THEME" property="billingTheme"/>
        <result column="COUNT" property="count"/>
        <result column="STATUS" property="status"/>
        <result column="OPEN_TIME" property="openTime"/>
        <result column="FORBIDDEN_TIME" property="forbiddenTime"/>
        <result column="SUPPLIER_ID" property="supplierId"/>
        <result column="PURCHASE_PATTERN" property="purchasePattern"/>
        <result column="TOTAL_COST" property="totalCost"/>
        <result column="BILL_NUMBER" property="billNumber"/>
        <result column="BILL_NAME" property="billName"/>
        <result column="CREATOR" property="creator"/>
        <result column="MODIFIER" property="modifier"/>
        <result column="GMT_CREATE" property="gmtCreate"/>
        <result column="GMT_MODIFIED" property="gmtModified"/>
        <result column="DELETED" property="deleted"/>
        <result column="VERSION" property="version"/>
        <result column="SITE_EQUIPMENT_ID" property="siteEquipmentId"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID, MONTH, SITE_NAME, SITE_CODE, SITE_ID, BILLING_THEME, COUNT, STATUS, OPEN_TIME, FORBIDDEN_TIME, SUPPLIER_ID, PURCHASE_PATTERN, TOTAL_COST, BILL_NUMBER, BILL_NAME, CREATOR, MODIFIER, GMT_CREATE, GMT_MODIFIED, DELETED, VERSION,SITE_EQUIPMENT_ID
    </sql>

    <sql id="Base_Batch_Column_List">
        ID, MONTH, SITE_NAME, SITE_CODE, SITE_ID,SUPPLIER_ID,CREATOR,GMT_CREATE,MODIFIER, GMT_MODIFIED, VERSION, BILLING_THEME,  PURCHASE_PATTERN,SITE_EQUIPMENT_ID,COUNT, TOTAL_COST, OPEN_TIME, FORBIDDEN_TIME
    </sql>

    <!--批量保存或者更新供应商设备账单-->
    <insert id="saveOrUpdateBatchSupplierEquipmentBill" parameterType="java.util.List" useGeneratedKeys="false">
        MERGE INTO ZTO_SUPPLIER_EQUIPMENT_BILL t1
        USING (
        <foreach collection="list" item="item" index="index" separator="union all">
            select
            #{item.id,jdbcType=VARCHAR} ID,
            #{item.month,jdbcType=VARCHAR} MONTH ,
            #{item.siteName,jdbcType=VARCHAR} SITE_NAME,
            #{item.siteCode,jdbcType=VARCHAR} SITE_CODE,
            #{item.siteId,jdbcType=VARCHAR} SITE_ID,
            #{item.supplierId,jdbcType=VARCHAR} SUPPLIER_ID,
            #{item.creator,jdbcType=VARCHAR} CREATOR,
            #{item.gmtCreate,jdbcType=TIMESTAMP} GMT_CREATE,
            #{item.modifier,jdbcType=VARCHAR} MODIFIER,
            #{item.gmtModified,jdbcType=TIMESTAMP} GMT_MODIFIED,
            #{item.version,jdbcType=NUMERIC} VERSION,
            #{item.billingTheme,jdbcType=VARCHAR} BILLING_THEME,
            #{item.purchasePattern,jdbcType=VARCHAR} PURCHASE_PATTERN,
            #{item.siteEquipmentId,jdbcType=VARCHAR} SITE_EQUIPMENT_ID,
            #{item.count,jdbcType=NUMERIC} COUNT,
            #{item.totalCost,jdbcType=DECIMAL} TOTAL_COST,
            #{item.openTime,jdbcType=TIMESTAMP} OPEN_TIME,
            #{item.forbiddenTime,jdbcType=TIMESTAMP} FORBIDDEN_TIME
            from dual
        </foreach>) t2
        ON (
        t1.ID = t2.ID
        )
        WHEN MATCHED THEN
        UPDATE SET
        t1.MONTH = t2.MONTH,
        t1.SITE_NAME=t2.SITE_NAME,t1.SITE_CODE=t2.SITE_CODE,t1.SITE_ID=t2.SITE_ID,t1.SUPPLIER_ID=t2.SUPPLIER_ID,t1.VERSION=t2.VERSION,t1.GMT_MODIFIED=t2.GMT_MODIFIED,t1.MODIFIER=t2.MODIFIER,t1.BILLING_THEME=t2.BILLING_THEME,
        t1.PURCHASE_PATTERN=t2.PURCHASE_PATTERN,t1.COUNT=t2.COUNT,t1.TOTAL_COST=t2.TOTAL_COST,
        t1.OPEN_TIME=t2.OPEN_TIME,t1.FORBIDDEN_TIME=t2.FORBIDDEN_TIME
        WHEN NOT MATCHED THEN
        INSERT
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <include refid="Base_Batch_Column_List"/>
        </trim>
        VALUES
        ( SYS_GUID(), t2.MONTH, t2.SITE_NAME, t2.SITE_CODE, t2.SITE_ID,
        t2.SUPPLIER_ID,t2.CREATOR,t2.GMT_CREATE,t2.MODIFIER,t2.GMT_MODIFIED,t2.VERSION,t2.BILLING_THEME,t2.PURCHASE_PATTERN,t2.SITE_EQUIPMENT_ID,t2.COUNT,t2.TOTAL_COST,t2.OPEN_TIME,t2.FORBIDDEN_TIME)
    </insert>
    <select id="getByBillNumberAndStatus" resultType="com.zto.cloudcall.model.ZtoSupplierEquipmentBill">
        select * from ZTO_SUPPLIER_EQUIPMENT_BILL
        where BILL_NUMBER = #{billNumber}
        and deleted = 0
        and status = #{status}
    </select>
    <select id="getByBillNoAndSiteCode" resultType="com.zto.cloudcall.model.ZtoSupplierEquipmentBill">
        select * from ZTO_SUPPLIER_EQUIPMENT_BILL
         where BILL_NUMBER = #{billNumber}
        and site_code = #{siteCode}
    </select>


</mapper>