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

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

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