mybatis+oracle 批量插入，若数据库中有则做更新操作

1.只批量插入：

insert into WXPAY_ACCOUNT
(
id ,
out_trade_no ,
transaction_id
)
select SEQ_WXPAY_ACCOUNT.nextval id,a.* FROM (

SELECT
#{wxpayAccount.outTradeNo ,jdbcType=VARCHAR},
#{wxpayAccount.transactionId ,jdbcType=VARCHAR}
FROM dual

) a

2.批量插入（存在不插入）

merge into WXPAY_ACCOUNT wa using (

SELECT
#{wxpayAccount.outTradeNo ,jdbcType=VARCHAR} as out_trade_no ,
#{wxpayAccount.transactionId ,jdbcType=VARCHAR} as transaction_id
FROM dual
 ) a ON (
wa.out_trade_no = a.out_trade_no and wa.transaction_id = a.transaction_id
) when NOT MATCHED THEN
INSERT (
id ,
out_trade_no ,
transaction_id
) VALUES (
SEQ_WXPAY_ACCOUNT.nextval    ,
a.out_trade_no ,
a.transaction_id
)

3.批量更新：

update wxpay_account w SET CHECK_STATUS =1
WHERE EXISTS (
SELECT 1 FROM (
SELECT wa.id FROM wxpay_account wa INNER JOIN PAYMENT_ORDER po
ON po.PAYNO = wa.OUT_TRADE_NO AND wa.OUT_REFUND_NO = ‘0’
AND wa.CHECK_STATUS = 0 AND wa.TOTAL_FEE = po.PAYAMOUNT
AND po.PAYTYPE = ‘wxpay’ AND to_char(wa.TRADE_TIME,‘yyyyMMdd’) = #{billDate}
AND po.createtime BETWEEN to_date(#{billDate},‘yyyy-MM-dd’) -1 AND to_date(#{billDate},‘yyyy-MM-dd’) +1
AND substr(wa.OUT_TRADE_NO,1,3) = #{billStart}
UNION ALL
SELECT rwa.id FROM wxpay_account rwa INNER JOIN PAYMENT_ORDER rpo
ON rpo.payno = rwa.OUT_REFUND_NO
AND rwa.CHECK_STATUS = 0 AND rwa.SETTLEMENT_REFUND_FEE = rpo.PAYAMOUNT
AND rpo.PAYTYPE = ‘wxpay’ AND to_char(rwa.TRADE_TIME,‘yyyyMMdd’) = #{billDate}
AND rpo.createtime BETWEEN to_date(#{billDate},‘yyyy-MM-dd’) -1 AND to_date(#{billDate},‘yyyy-MM-dd’) +1
AND substr(rwa.OUT_TRADE_NO,1,3) = #{billStart}
AND substr(rwa.OUT_REFUND_NO,1,3) = #{billStart}
) b where w.id = b.id
)

转载于:https://www.cnblogs.com/superming/p/10944041.html