在Oracle中Merge Into Using语句的使用示例

时间:2019-5-24 作者:WilliamChan

今天早上,在用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);

至此,执行结果终于达到预期,效率也提升了一个数量级(几分钟降到十来秒)。 这样就很好的解决了我的问题。 注:以上示例代码经过修改,无法正常执行,但思路和解决问的过程都写清楚了。

声明:若您对本文内容有任何意见,欢迎发送邮件至:admin@zhidaobu.com,本站会尽快回应并做出相应处理。