Oracle基础教程之Merge into

[[197117]]

Merge into语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。

通过MERGE语句，根据一张表或多表联合查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。通过这个MERGE你能够在一个SQL语句中对一个表同时执行INSERT和UPDATE操作. 在 Oracle 10g中MERGE有一些新特性，后面我会介绍这些新特征。先看看MERGE语法如下：

MERGE INTO TEST_NEW 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  TEST_OLD 
        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）； 

WHEN MATCHED THEN UPDATE SET 表示当on里面的关键字匹配上的时候，就进行修改操作。

但是值得注意的是，在做修改操作的时候，不可以修改on里面关键字的值。

WHEN NOT MATCHED THEN INSERT 表示当on里面的关键字匹配不上的时候，也就是说没有这样一条记录存在TEST_NEW表中时，就进行新增操作。

这时，做新增操作，就可以将on里面的字段进行设置值。

在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<br>）； 

从上可以看出，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'；