今天早上,在用oracle写一个存储过程的时候,要更新和插入大数据量的表,发现直接使用update和Insert,速度非常慢,效率让人想崩溃。于是想起了Merge Into Using语句,使用该语句可能会提高效率,于是赶紧动手改码:
原代码:
--示例代码已经做更改,大概是这意思
FOR CUR IN (SELECT ROWID, ITEMID, TASKID, UNITID FROM TMP) LOOP
UPDATE TMP t
SET (DUENUM, DUESUM, DUESUMRMB, DUESUMTAX, DUESUMTAXRMB, ACCDUESUMRMB) =
(SELECT DUENUM,
DUESUM,
DUESUMRMB,
DUESUMTAX,
DUESUMTAXRMB,
ACCDUESUMRMB
FROM BILL A
WHERE A.ITEMID = T.ITEMID
AND A.TASKID = T.TASKID
AND A.UNITID = T.UNITID);
RETURNINT := RETURNINT + 1;
IF RETURNINT > 500 THEN
COMMIT;
RETURNINT := 0;
END IF;
END LOOP;
INSERT INTO TMP
(DUENUM, DUESUM, DUESUMRMB, DUESUMTAX, DUESUMTAXRMB, ACCDUESUMRMB)
SELECT DUENUM, DUESUM, DUESUMRMB, DUESUMTAX, DUESUMTAXRMB, ACCDUESUMRMB
FROM BILL A
WHERE NOT EXISTS(TMP t WHERE A.ITEMID = T.ITEMID
AND A.TASKID = T.TASKID
AND A.UNITID = T.UNITID);
改成Merge Into Using语句:
--示例代码已经做更改,大概是这意思
MERGE INTO TMP T
USING (SELECT A.ROWID as RID,
B.ID,
B.TASKID,
B.ITEMID,
B.UNITID,
B.OCCAIRDCODE,
B.DUENUM,
B.DUESUM,
B.DUESUMRMB,
B.DUESUMTAX,
B.DUESUMTAXRMB,
B.ACCDUESUMRMB
FROM B,A
where A.ITEMID = B.ITEMID AND A.TASKID = B.TASKID AND A.UNITID = B.UNITID) CUR
ON (T.ROWID = CUR.RID)
WHEN MATCHED THEN
UPDATE
SET T.ID = CUR.ID,
T.DID = CUR.DID,
T.DUENUM = CUR.DUENUM,
T.DUESUM = CUR.DUESUM,
T.DUESUMRMB = CUR.DUESUMRMB,
T.DUESUMTAX = CUR.DUESUMTAX,
T.DUESUMTAXRMB = CUR.DUESUMTAXRMB,
T.ACCDUESUMRMB = CUR.ACCDUESUMRMB
WHEN NOT MATCHED THEN
INSERT
(BILLID,
TASKID,
ITEMID,
UNITID,
BILLDUENUM,
BILLDUESUM,
BILLDUESUMRMB,
BILLDUESUMTAX,
BILLDUESUMTAXRMB,
BILLACCDUESUMRMB)
VALUES
(CUR.ID,
CUR.TASKID,
CUR.ITEMID,
CUR.UNITID,
CUR.OCCAIRDCODE,
CUR.DUENUM,
CUR.DUESUM,
CUR.DUESUMRMB,
CUR.DUESUMTAX,
CUR.DUESUMTAXRMB,
CUR.ACCDUESUMRMB);
执行修改后的代码,发现有update但是没有insert。根据merge into Using语句原理,肯定是可以实现‘存在时 – 更新,不存在时,插入’的功能的。原语法是这样的:
MERGE INTO 目标表 a
USING 源表 b
ON (a.字段1 = b.字段2 and a.字段n = b.字段n)
WHEN MATCHED THEN
UPDATE SET a.新字段 = b.字段
WHERE 限制条件
WHEN NOT MATCHED THEN
INSERT (a.字段名1,a.字段名n) VALUES(b.字段值1, b.字段值n)
WHERE 限制条件
不同之处是,我的语法在UPDATE和INSERT中没有使用WHERE的限制条件。 仔细查看后发现,在一个同时存在Insert和Update语法的Merge into Using语句中,总共Insert/Update的记录数,就是Using语句中的记录数。我的Using语句查出来的全部是匹配后面的ON (T.ROWID = CUR.RID)语句的(也就是只适合UPDATE的),并没有查出需要新增的。因此需要修改Using语句,将需要新增的也查出来才行。 因此,关键部分的代码:
FROM B,A
where A.ITEMID = B.ITEMID AND A.TASKID = B.TASKID AND A.UNITID = B.UNITID
改成
FROM B LEFT JOIN A
on (A.ITEMID = B.ITEMID AND A.TASKID = B.TASKID AND A.UNITID = B.UNITID)
完整代码差不多是这样:
--示例代码已经做更改,大概是这意思
MERGE INTO TMP T
USING (SELECT A.ROWID as RID,
B.ID,
B.TASKID,
B.ITEMID,
B.UNITID,
B.OCCAIRDCODE,
B.DUENUM,
B.DUESUM,
B.DUESUMRMB,
B.DUESUMTAX,
B.DUESUMTAXRMB,
B.ACCDUESUMRMB
FROM B LEFT JOIN A
on (A.ITEMID = B.ITEMID AND A.TASKID = B.TASKID AND A.UNITID = B.UNITID)) CUR
ON (T.ROWID = CUR.RID)
WHEN MATCHED THEN
UPDATE
SET T.ID = CUR.ID,
T.DID = CUR.DID,
T.DUENUM = CUR.DUENUM,
T.DUESUM = CUR.DUESUM,
T.DUESUMRMB = CUR.DUESUMRMB,
T.DUESUMTAX = CUR.DUESUMTAX,
T.DUESUMTAXRMB = CUR.DUESUMTAXRMB,
T.ACCDUESUMRMB = CUR.ACCDUESUMRMB
WHEN NOT MATCHED THEN
INSERT
(BILLID,
TASKID,
ITEMID,
UNITID,
BILLDUENUM,
BILLDUESUM,
BILLDUESUMRMB,
BILLDUESUMTAX,
BILLDUESUMTAXRMB,
BILLACCDUESUMRMB)
VALUES
(CUR.ID,
CUR.TASKID,
CUR.ITEMID,
CUR.UNITID,
CUR.OCCAIRDCODE,
CUR.DUENUM,
CUR.DUESUM,
CUR.DUESUMRMB,
CUR.DUESUMTAX,
CUR.DUESUMTAXRMB,
CUR.ACCDUESUMRMB);
至此,执行结果终于达到预期,效率也提升了一个数量级(几分钟降到十来秒)。 这样就很好的解决了我的问题。 注:以上示例代码经过修改,无法正常执行,但思路和解决问的过程都写清楚了。