[oracle]常用函数merge-Oracle

Oracle 21NN 4个月前 (06-27) 50次浏览 已收录 0个评论 扫描二维码

[oracle]常用函数merge。MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。 通过MERGE语句,根据一张表或多表联合查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。通过这个MERGE你能够在一个SQL语句中对一个表同时执行INSERT和UPDATE操作. 在 Oracle 10g中MERGE有一些新特性,后面我会介绍这些新特征。先看看MERGE语法如下:

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有如下一些新特性。

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

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行


本文:[oracle]常用函数merge-Oracle 本文链接:https://www.21nn.cn/shujuku/oracle/49242.html 本站所以图片、文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如有侵权请邮件与我们联系处理。i@ki4.cn
喜欢 (0)
[1353713598@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址