ORACLE MERGE 介绍及示例.docxVIP

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))?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?THENINSERT(?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