oracle批量新增数据，如果有指定字段相同的数据，则更新
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
<insert id="batchAddStorage">
MERGE INTO COMPUTER_GOODS_STORAGE t1
USING (
<foreach collection="list" item="item" index="index" separator="union" >
select
#{item.id} id,
#{item.selfNo} selfNo,
#{item.commodityName} commodityName,
#{item.goodsName} goodsName,
#{item.modelSpec} modelSpec,
#{item.mainConfiguration} mainConfiguration,
#{item.unit} unit,
#{item.num} num,
#{item.baseNum} baseNum,
#{item.purchaseTotalPrice} purchaseTotalPrice
from dual
</foreach>) t2
ON (
t1.SELF_NO = t2.selfNo
)
WHEN MATCHED THEN
UPDATE SET t1.NUM = t2.num+t1.NUM,t1.PURCHASE_TOTAL_PRICE = t2.purchaseTotalPrice+t1.PURCHASE_TOTAL_PRICE,
t1.UNIT = t2.unit,t1.MAIN_CONFIGURATION = t2.mainConfiguration,t1.COMMODITY_NAME = t2.commodityName
WHEN NOT MATCHED THEN
INSERT
(ID,SELF_NO, COMMODITY_NAME, GOODS_NAME, MODEL_SPEC, MAIN_CONFIGURATION, UNIT,NUM,BASE_NUM,PURCHASE_TOTAL_PRICE)
VALUES
(t2.id,t2.selfNo, t2.commodityName, t2.goodsName, t2.modelSpec, t2.mainConfiguration, t2.unit,t2.num,t2.baseNum,t2.purchaseTotalPrice)
</insert>
说明如果传入的字段值 t2.selfNo 的值在表中已存在相同的值（t1.SELF_NO = t2.selfNo则批量更新指定字段（UPDATE SET t1.NUM = t2.num+t1.NUM,t1.PURCHASE_TOTAL_PRICE = t2.purchaseTotalPrice+t1.PURCHASE_TOTAL_PRICE,
t1.UNIT = t2.unit,t1.MAIN_CONFIGURATION = t2.mainConfiguration,t1.COMMODITY_NAME = t2.commodityName否则则批量插入数据（INSERT
(ID,SELF_NO, COMMODITY_NAME, GOODS_NAME, MODEL_SPEC, MAIN_CONFIGURATION, UNIT,NUM,BASE_NUM,PURCHASE_TOTAL_PRICE)
VALUES
(t2.id,t2.selfNo, t2.commodityName, t2.goodsName, t2.modelSpec, t2.mainConfiguration, t2.unit,t2.num,t2.baseNum,t2.purchaseTotalPrice)