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