你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle基础教程之Merge into

[日期:2017-07-14] 来源:Linux社区  作者:MoreThinking [字体: ]

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

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-07/145664.htm

linux
相关资讯       Oracle MERGE INTO 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款