現在出現了一個很靈異的情況....在資料庫中使用這條SQL陳述句, 無論執行多少次都不會出現重復資料, 但是在實際的專案中, 回圈讀取資料,執行SQL, 進行插入或者是更新時, 會出現一模一樣的重復資料, 宣告下,建表的SQL沒有加任何約束,可以重新建表,加個主鍵約束等, 很藍瘦~ 求助下這是什么問題, 需要加什么約束嗎?
merge into AA_Temp_gj t1 using dual on(
(select count(*) from AA_Temp_gj h where h.ID=101)>0)
when matched then
update set ID=101,dcreatesystime=to_date('2020-06-23 16:36:11', 'yyyy-mm-dd hh24:mi:ss'), cOperator='0222', cCheckMan='0222', iAmount_f=200000.0000,
dverifydate=to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), iAmount=200000.0000, cDeptCode='01',cPerson='0102', cCusVen='230007', cSSCode='7', cBankAccount='0406000200035158',
dVouchDate=to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), cDigest='測驗資料', cDepCode='null', cPersonCode=2010731, iAmt=200000.0000, iAmt_f=200000.0000, cBank='null',
cNatBankAccount='12345678901234567', cOrderID='null', cNoteNo='null',cVouchID='0000109' where id=101
when not matched then
insert(ID,dcreatesystime,cOperator,cCheckMan,iAmount_f,dverifydate,iAmount,cDeptCode,cPerson,cCusVen,cSSCode,cBankAccount,dVouchDate,cDigest,cDepCode,cPersonCode,iAmt,
iAmt_f,cBank,cNatBankAccount,cOrderID,cNoteNo,cVouchID) values(101,to_date('2020-06-23 16:36:11', 'yyyy-mm-dd hh24:mi:ss'),'0222','0222',200000.0000,
to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),200000.0000,'01','0102','230007','7','0406000200035158',to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'測驗資料',
null,2010731,200000.0000,200000.0000,'null','12345678901234567',null,null,'0000109')
uj5u.com熱心網友回復：
1、從來沒見merge和dual這么用的，長見識了。2、應該是有并發，且并發或同一會話的不同回圈有相同ID值的緣故。
uj5u.com熱心網友回復：
好的，謝謝。我也是第一次用merge
，確實不太會用。
uj5u.com熱心網友回復：
CREATE UNIQUE INDEX idx_aa_temp_gj_u1 ON AA_Temp_gj(ID);
MERGE INTO AA_Temp_gj t1
USING dual
ON (t1.ID = 101)
WHEN MATCHED THEN
UPDATE
SET dcreatesystime = to_date('2020-06-23 16:36:11', 'yyyy-mm-dd hh24:mi:ss')
,cOperator = '0222'
,cCheckMan = '0222'
,iAmount_f = 200000.0000
,dverifydate = to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
,iAmount = 200000.0000
,cDeptCode = '01'
,cPerson = '0102'
,cCusVen = '230007'
,cSSCode = '7'
,cBankAccount = '0406000200035158'
,dVouchDate = to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
,cDigest = '測驗資料'
,cDepCode = 'null'
,cPersonCode = 2010731
,iAmt = 200000.0000
,iAmt_f = 200000.0000
,cBank = 'null'
,cNatBankAccount = '12345678901234567'
,cOrderID = 'null'
,cNoteNo = 'null'
,cVouchID = '0000109'
WHERE id = 101
WHEN NOT MATCHED THEN
INSERT
(ID
,dcreatesystime
,cOperator
,cCheckMan
,iAmount_f
,dverifydate
,iAmount
,cDeptCode
,cPerson
,cCusVen
,cSSCode
,cBankAccount
,dVouchDate
,cDigest
,cDepCode
,cPersonCode
,iAmt
,iAmt_f
,cBank
,cNatBankAccount
,cOrderID
,cNoteNo
,cVouchID)
VALUES
(101
,to_date('2020-06-23 16:36:11', 'yyyy-mm-dd hh24:mi:ss')
,'0222'
,'0222'
,200000.0000
,to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
,200000.0000
,'01'
,'0102'
,'230007'
,'7'
,'0406000200035158'
,to_date('2020-06-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
,'測驗資料'
,NULL
,2010731
,200000.0000
,200000.0000
,'null'
,'12345678901234567'
,NULL
,NULL
,'0000109');
uj5u.com熱心網友回復：
夠直接，我喜歡
uj5u.com熱心網友回復：
merge就是會有重復的，還是要加唯一約束uj5u.com熱心網友回復：
嗯嗯，謝謝，第一次使用merge，不知道會出現重復的情況。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8487.html
標籤：開發
下一篇：857