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

巴特西

Oracle基础之Merge into

最新文章

热门文章