Oracle基礎之Merge into

標籤：tom   select   class   查詢   blog   pow   介紹   custom   需求   

　　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)) TMPON(                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_HRWHEN 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‘ ) OON(            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) OON(            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