orace快速导入mysql_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

)

---------------------

作者：shangguanjiyan

来源：CSDN

原文：https://blog.csdn.net/shangguanjiyan/article/details/84106023

版权声明：本文为博主原创文章，转载请附上博文链接！