ORACLE MERGE 介绍及示例

MERGE語句是Oracle9i+新增的語法，用來合併UPDATE和INSERT語句。透過MERGE語句，根據一張表或多表聯合查詢的連接條件對另外一張表進行查詢，連接條件匹配上的進行UPDATE，無法匹配的執行INSERT。

MERGE INTO DM.TM_WGG_SYSVLRUSER_HR 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 DM.TM_WGG_SYSVLRUSER_HR

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

在ORACLE 10i中，MERGE有如下一些新特性。 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

在ON條件中使用常量過濾謂詞來insert所有的行到目標表中,不需要連接源表和目標表 UPDATE子句後面可以跟DELETE子句來去除一些不需要的行

[sql] --測試數據

create table table1(id varchar2(100),name varchar2(1000),address varchar2(1000)); insert into table1(id,name,address)values('01001','影子','河北') ; commit; --插入

merge into table1 t1

using (select '01002' id,'影子' name,'河北' address from dual) t2 on (t1.id = t2.id) when matched then

update set t1.name = t2.name, t1.address = t2.address when not matched then

insert values (t2.id, t2.name,t2.address); commit;

--查詢結果 select * from table1 01001 影子 河北

01002 影子2 遼寧 --更新

merge into table1 t1

using (select '01001' id,'不是影子' name,'山西' address from dual) t2 on (t1.id = t2.id) when matched then

update set t1.name = t2.name, t1.address = t2.address when not matched then

insert values (t2.id, t2.name,t2.address); commit;

--查詢結果 select * from table1

01001 不是影子 山西 01002 影子2 遼寧

--刪除測試數據 drop table table1;