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

2019/05/29 15:20
阅读数 83
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
)
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部