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_PRODUCT
create 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_PRODUCT
create 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_PRODUCT
create 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_PRODUCT
select t.*,sysdate-1
from 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_product
select * from apps_product
where last_updated_date>=trunc(sysdate);

从上面的表可以看出：

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

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

MERGE INTO DWH_PRODUCT dp
USING ODS_PRODUCT op
ON (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=sysdate
WHEN 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的最基本用法，关于其他用法，以后会更新！

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