Oracle Database之MERGE INTO详细介绍

MERGE INTO简介

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。总的原则是“有则更新，无则插入”。

MERGE INTO语法

说明：

至少要包含merge_update_clause或merge_insert_clause中的一个；

merge_update_clause的基本构成： WHEN MATCHED THEN UPDATE SET column1=expression1 [, column2=expression2]... [where_clause] [DELETE [where_clause]]

merge_insert_clause的基本构成：WHEN NOT MATCHED THEN INSERT (column1 [, column2]...) VALUES (expression1 [, expression2]..) [where_clause]

示例场景设计

假设有四张表，关系如下：

APPS_PRODUCT包含字段： {product_id, product_name, product_type, unit, unit_price, quantity, creation, created_by, last_updated_date, last_updated_by}

ODS_PRODUCT包含字段： {product_id, product_name, product_type, unit, unit_price, quantity, creation, created_by, last_updated_date, last_updated_by}

DW_PRODUCT包含字段： {product_id, product_name, product_type, unit, unit_price, quantity, creation, created_by, last_updated_date, last_updated_by,dw_load_date}

其中：

APPS_PRODUCT表的数据由前端APP录入、修改或删除；

ODS_PRODUCT表存储表APPS_PRODUCT新增或更改的最新数据；

DWH_PRODUCT表用来同步APPS_PRODUCT表；

创建表的脚本并插入初始测试数据：

--创建表APPS_PRODUCTcreate table apps_product(product_id number,product_name varchar2(50),product_type varchar2(100),unit varchar2(50),unit_price number(22,2),quantity number(22,2),creation date,created_by varchar2(50),last_updated_date date,last_updated_by varchar2(50));insert into apps_product values(1,'Dell Laptop','Computer','Set',5600,20,sysdate-1,'system',sysdate-1,'system');insert into apps_product values(1,'Lenovo Laptop','Computer','Set',9600,10,sysdate-1,'system',sysdate-1,'system');insert into apps_product values(1,'Sony Laptop','Computer','Set',8600,30,sysdate-1,'system',sysdate-1,'system');insert into apps_product values(1,'Huawei Laptop','Computer','Set',6600,2,sysdate-1,'system',sysdate-1,'system');insert into apps_product values(1,'Apple Mackbook Pro','Computer','Set',7600,14,sysdate-1,'system',sysdate-1,'system');--创建表ODS_PRODUCTcreate table ODS_PRODUCT(product_id number,product_name varchar2(50),product_type varchar2(100),unit varchar2(50),unit_price number(22,2),quantity number(22,2),creation date,created_by varchar2(50),last_updated_date date,last_updated_by varchar2(50));insert into ODS_PRODUCT select * from apps_product;select * from ods_product;--创建表DWH_PRODUCTcreate table DWH_PRODUCT(product_id number,product_name varchar2(50),product_type varchar2(100),unit varchar2(50),unit_price number(22,2),quantity number(22,2),creation date,created_by varchar2(50),last_updated_date date,last_updated_by varchar2(50),dw_load_date date);insert into DWH_PRODUCTselect t.*,sysdate-1from ods_product t;

此时，如果前端程序对apps_product表做了以下操作：

insert into apps_product values(6,'LG Laptop','Computer','Set',10000,14,sysdate,'system',sysdate,'system');insert into apps_product values(7,'Panda TV','Television','Set',80,14,sysdate,'system',sysdate,'system');update apps_product set product_name='DELL Computer',last_updated_date=sysdate where product_id=1;

那我们如何通过MERGE INTO来把APPS_PRODUCT表的改动更新到DWH_PRODUCT表呢， 请看下面详细步骤：

--新增两条记录insert into apps_product values(6,'LG Laptop','Computer','Set',10000,14,sysdate,'system',sysdate,'system');insert into apps_product values(7,'Panda TV','Television','Set',80,14,sysdate,'system',sysdate,'system');--更新一条记录update apps_product set product_name='DELL Computer',last_updated_date=sysdate where product_id=1;

--删除表ods_product的数据，该表之保存增量的数据truncate table ods_product;insert into ods_productselect * from apps_productwhere last_updated_date>=trunc(sysdate);

从上面的表可以看出：

• product_id=1的product_name被更新过；
• product_id=6,7是新增的记录；

下面通过MERGE INTO，利用ods_product表来更新修改的记录，新增新加的记录的记录：

MERGE INTO DWH_PRODUCT dpUSING ODS_PRODUCT opON (dp.product_id=op.product_id)WHEN MATCHED THEN   UPDATE SET      dp.product_name=op.product_name,      dp.product_type=op.product_type,      dp.unit=op.unit,      dp.unit_price=op.unit_price,      dp.quantity=op.quantity,      dp.creation=op.creation,      dp.created_by=op.created_by,      dp.last_updated_date=op.last_updated_date,      dp.last_updated_by=op.last_updated_by,      dp.dw_load_date=sysdateWHEN NOT MATCHED THEN      INSERT         VALUES         (           op.product_id,            op.product_name,            op.product_type,op.unit,            op.unit_price,           op.quantity,           op.creation,           op.created_by,            op.last_updated_date,           op.last_updated_by,           sysdate);

本文只是介绍MERGE INTO的最基本用法，关于其他用法，以后会更新！

如果您在尝试中遇到任何问题，敬请指正！