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  =  TMP.DATE_HR
WHEN   NOT  MATCHED  THEN    
INSERT
(
      DM.DATE_CD,
      DM.HR_CD,
      DM.DATE_HR,
      DM.CITY_ID,
      DM.SYSTEM_ID,
      DM.GSM_REG_USERCNT,
      DM.TD_REG_USERCNT,
      DM.TD_REG_USERRAT,
      DM.GSM_POWERON_USERCNT,
      DM.TD_POWERON_USERCNT,
      DM.TD_POWERON_USERRAT
)
VALUES
( 
      TMP.DATE_CD,
      TMP.HR_CD,
      TMP.DATE_HR,
      TMP.CITY_ID,
      TMP.SYSTEM_ID,
      TMP.GSM_REG_USERCNT,
      TMP.TD_REG_USERCNT,
      TMP.TD_REG_USERRAT,
      TMP.GSM_POWERON_USERCNT,
      TMP.TD_POWERON_USERCNT,
      TMP.TD_POWERON_USERRAT

);

 

 在ORACLE 10i中，MERGE有如下一些新特性。

1、UPDATE或INSERT子句是可选的     

假如某个系统中，有个订单表，现在要求新增订单的记录都要反应到订单历史表ORDER_HISTORY中，我们可以如下写脚本

MERGE  INTO  ORDER_HISTORY H USING
(
       SELECT  ORDER_ID               , -- 订单编号
             CUSTOMER_ID            , -- 客户编号
             EMPLOYEE_ID            , -- 员工编号
             ORDER_DATE             , -- 订购日期；
             REQUIRED_DATE          , -- 预计到达日期
             SHIPPED_DATE           , -- 发货日期
             SHIPPER                , -- 运货商
             FREIGHT                , -- 运费
             SHIP_NAM               , -- 货主姓名；
             SHIP_ADDRESS           , -- 货主地址
             SHIP_CITY              , -- 货主所在城市；
             SHIP_REGION            , -- 货主所在地区；
             SHIP_POSTALCODE        , -- 货主邮编
             SHIP_COUNTRY             -- 货主所在国家
       FROM   ORDER_DTL
       WHERE  TO_CHAR(ODER_DATE,  ' YYYY-MM-DD ' )  =   ' 20110530 '  
) O
ON
(
            O.ORDER_ID  =  H.ORDER_ID
)  
WHEN   NOT  MATCHED  THEN   INSERT
(
             H.ORDER_ID               ,
             H.CUSTOMER_ID            ,
             H.EMPLOYEE_ID            ,
             H.ORDER_DATE             ,
             H.REQUIRED_DATE          ,
             H.SHIPPED_DATE           ,
             H.SHIPPER                ,
             H.FREIGHT                ,
             H.SHIP_NAM               ,
             H.SHIP_ADDRESS           ,
             H.SHIP_CITY              ,
             H.SHIP_REGION            ,
             H.SHIP_POSTALCODE        ,
             H.SHIP_COUNTRY           
)
VALUES
(           
            O.ORDER_ID                ,
            O.CUSTOMER_ID             ,
            O.EMPLOYEE_ID             ,
            O.ORDER_DATE              ,
            O.REQUIRED_DATE           ,
            O.SHIPPED_DATE            ,
            O.SHIPPER                 ,
            O.FREIGHT                 ,
            O.SHIP_NAM                ,
            O.SHIP_ADDRESS            ,
            O.SHIP_CITY               ,
            O.SHIP_REGION             ,
            O.SHIP_POSTALCODE         ,
            O.SHIP_COUNTRY            

); 

从上可以看出，MATCHED 或NOT MATCHED是可选的。不必非得

WHEN NOT MATCHED THEN UPDATE SET

.....

WHEN MATCHED THEN INSERT

 

2、UPDATE和INSERT子句可以加WHERE子句                                           

现在由于需求改变，我们仅仅需要把员工1001的订单数据同步到订单历史记录表  

MERGE  INTO  ORDER_HISTORY H USING
(
       SELECT  ORDER_ID               , -- 订单编号
             CUSTOMER_ID            , -- 客户编号
             EMPLOYEE_ID            , -- 员工编号
             ORDER_DATE             , -- 订购日期；
             REQUIRED_DATE          , -- 预计到达日期
             SHIPPED_DATE           , -- 发货日期
             SHIPPER                , -- 运货商
             FREIGHT                , -- 运费
             SHIP_NAM               , -- 货主姓名；
             SHIP_ADDRESS           , -- 货主地址
             SHIP_CITY              , -- 货主所在城市；
             SHIP_REGION            , -- 货主所在地区；
             SHIP_POSTALCODE        , -- 货主邮编
             SHIP_COUNTRY             -- 货主所在国家
       FROM    ORDER_DTL
) O
ON
(
            O.ORDER_ID  =  H.ORDER_ID
)
WHEN  MATCHED  THEN   UPDATE      SET              
             H.CUSTOMER_ID          =      O.CUSTOMER_ID       ,
             H.EMPLOYEE_ID          =      O.EMPLOYEE_ID       ,
             H.ORDER_DATE           =      O.ORDER_DATE        ,
             H.REQUIRED_DATE        =      O.REQUIRED_DATE     ,
             H.SHIPPED_DATE         =      O.SHIPPED_DATE      ,
             H.SHIPPER              =      O.SHIPPER           ,
             H.FREIGHT              =      O.FREIGHT           ,
             H.SHIP_NAM             =      O.SHIP_NAM          ,
             H.SHIP_ADDRESS         =      O.SHIP_ADDRESS      ,
             H.SHIP_CITY            =      O.SHIP_CITY         ,
             H.SHIP_REGION         =      O.SHIP_REGION       ,
             H.SHIP_POSTALCODE      =      O.SHIP_POSTALCODE   ,
             H.SHIP_COUNTRY         =      O.SHIP_COUNTRY      
        WHERE  O.EMPLOYEE_ID  =   1001
WHEN   NOT  MATCHED  THEN   INSERT
(
             H.ORDER_ID               ,
             H.CUSTOMER_ID            ,
             H.EMPLOYEE_ID            ,
             H.ORDER_DATE             ,
             H.REQUIRED_DATE          ,
             H.SHIPPED_DATE           ,
             H.SHIPPER                ,
             H.FREIGHT                ,
             H.SHIP_NAM               ,
             H.SHIP_ADDRESS           ,
             H.SHIP_CITY              ,
             H.SHIP_REGION            ,
             H.SHIP_POSTALCODE        ,
             H.SHIP_COUNTRY           
)
VALUES
(           
            O.ORDER_ID                ,
            O.CUSTOMER_ID             ,
            O.EMPLOYEE_ID             ,
            O.ORDER_DATE              ,
            O.REQUIRED_DATE           ,
            O.SHIPPED_DATE            ,
            O.SHIPPER                 ,
            O.FREIGHT                 ,
            O.SHIP_NAM                ,
            O.SHIP_ADDRESS            ,
            O.SHIP_CITY               ,
            O.SHIP_REGION             ,
            O.SHIP_POSTALCODE         ,
            O.SHIP_COUNTRY            
)   

WHERE O.EMPLOYEE_ID = 1001  

 3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

 

 4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行