• Oracle基础之Merge into


      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
    );

      

      从上可以看出,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';
    

      

  • 相关阅读:
    Spring个人冲刺阶段总结
    开学测试代码
    《人月神话》读后感(三)
    《人月神话》读后感(二)
    《人月神话》读后感(一)
    记账本小程序7天开发记录(第七天)
    记账本小程序7天开发记录(第六天)
    记账本小程序7天开发记录(第五天)
    记账本小程序7天开发记录(第四天)
    记账本小程序7天开发记录(第三天)
  • 原文地址:https://www.cnblogs.com/MoreThinking/p/7090256.html
Copyright © 2020-2023  润新知