ORACLE MERGE介绍

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。 通过MERGE语句，根据一张表或多表联合查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。通过这个MERGE你能够在一个SQL语句中对一个表同时执行INSERT和UPDATE操作. 在 Oracle 10g中MERGE有一些新特性，后面我会介绍这些新特征。先看看MERGE语法如下：
MERGE INTO DM.TM_WGG_SYSVLRUSER_HR DM USING                    
(                                                              
        SELECT DATE_CD,                                        
               HR_CD,                                          
               DATE_HR,
               DECODE(GROUPING(CITY_ID), 1, 9999, CITY_ID) AS CITY_ID,
               DECODE(GROUPING(SYSTEM_ID), 1, -9999, SYSTEM_ID) AS SYSTEM_ID,
               SUM(GSM_REG_USERCNT) AS GSM_REG_USERCNT,
               SUM(TD_REG_USERCNT) AS TD_REG_USERCNT,
               SUM(TD_REG_USERRAT) AS TD_REG_USERRAT,
               SUM(GSM_POWERON_USERCNT) AS GSM_POWERON_USERCNT,
               SUM(TD_POWERON_USERCNT) AS TD_POWERON_USERCNT,
               SUM(TD_POWERON_USERRAT) AS TD_POWERON_USERRAT
        FROM   DM.TM_WGG_SYSVLRUSER_HR
         GROUP BY DATE_HR, DATE_CD, HR_CD, ROLLUP(SYSTEM_ID),ROLLUP(CITY_ID)
) TMP
ON
(
                    DM.DATE_CD   = TMP.DATE_CD
            AND DM.HR_CD        = TMP.HR_CD
            AND DM.CITY_ID   = TMP.CITY_ID
            AND DM.SYSTEM_ID = TMP.SYSTEM_ID
)
WHEN MATCHED THEN  UPDATE  SET
      DM.GSM_REG_USERCNT = TMP.GSM_REG_USERCNT,
      DM.TD_REG_USERCNT = TMP.TD_REG_USERCNT,
      DM.TD_REG_USERRAT = TMP.TD_REG_USERRAT,
      DM.GSM_POWERON_USERCNT  = TMP.GSM_POWERON_USERCNT,
      DM.TD_POWERON_USERCNT = TMP.TD_POWERON_USERCNT,
      DM.TD_POWERON_USERRAT = TMP.TD_POWERON_USERRAT,
      DM.DATE_HR =