MERGE

功能描述

通过MERGE INTO语句，将目标对象（包括表和视图）和源对象（包括表和视图）中数据针对关联条件进行匹配，若关联条件匹配时对目标对象进行UPDATE，无法匹配时对目标对象执行INSERT。此语法可以很方便地用来合并执行UPDATE和INSERT，避免多次执行。

注意事项

进行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限，以及源表的SELECT权限。
从V2.2 Build 14及以后版本开始支持将视图作为MERGE INTO语法的对象，视图定义可以为任意支持更新的视图。

语法格式

MERGE [/*+ plan_hint */] INTO { table_name | view_name } [ partition_clause ] [ [ AS ] alias ]
USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
ON ( condition )
[
  WHEN MATCHED THEN
  UPDATE SET { column_name = { expression | subquery | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | subquery | DEFAULT } [, ...] ) } [, ...]
  [ WHERE condition ]
]
[
  WHEN NOT MATCHED THEN
  INSERT { DEFAULT VALUES |
  [ ( column_name [, ...] ) ] VALUES ( { expression | subquery | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
];

其中partition_clause可以是：

PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |
SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }

partition_clause仅在CENTRALIZED模式下可用。

UPDATE和INSERT子句中的“subquery”仅在CENTRALIZED模式下可用。

参数说明

plan_hint子句

以/*+ */的形式MERGE关键字后，用于对MERGE对应的语句块生成的计划进行hint调优，详细用法请参见章节使用Plan Hint进行调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。
INTO子句

指定正在更新或插入的目标表。
table_name

目标表的表名。
view_name

目标视图的名字。

本特性从V2.2 Build 14版本开始支持。
alias

目标表的别名。

取值范围：字符串，符合标识符命名规范。
partition_clause

指定分区只适合分区表。
```
PARTITION { ( partition_name ) |
FOR ( partition_value [, ...] ) }
```
如果value子句的值和指定分区不一致，会抛出异常。

示例详见CREATE TABLE PARTITION。
USING子句

指定源表，源表可以为表、视图或子查询。
ON子句

关联条件，用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。
WHEN MATCHED子句

当源表和目标表中数据针对关联条件可以匹配上时，选择WHEN MATCHED子句进行UPDATE操作。

不支持更新系统表、系统列。
WHEN NOT MATCHED子句

当源表和目标表中数据针对关联条件无法匹配时，选择WHEN NOT MATCHED子句进行INSERT操作。

不支持INSERT子句中包含多个VALUES。

WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换，可以缺省其中一个，但不能同时缺省，不支持同时指定两个WHEN MATCHED或WHEN NOT MATCHED子句。
DEFAULT

用对应字段的缺省值填充该字段。

如果没有缺省值，则为NULL。
WHERE condition

UPDATE子句和INSERT子句的条件，只有在条件满足时才进行更新操作，可缺省。不支持WHERE条件中引用系统列。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为bool值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。

示例

示例1 合并目标和源对象为table。

1、创建目标表products和源表newproducts，并插入数据。

CREATE TABLE products
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products VALUES (1600, 'play gym', 'toys');
INSERT INTO products VALUES (1601, 'lamaze', 'toys');
INSERT INTO products VALUES (1666, 'harry potter', 'dvd');

CREATE TABLE newproducts
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');

2、进行MERGE INTO操作。

MERGE INTO products p   
USING newproducts np   
ON (p.product_id = np.product_id)   
WHEN MATCHED THEN  
  UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'  
WHEN NOT MATCHED THEN  
  INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

提交回显结果为：

MERGE 4

3、查询更新后的结果。

SELECT * FROM products ORDER BY product_id;

显示结果为：

product_id |  product_name  | category  
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1502 | olympus camera | electrncs
       1600 | play gym       | toys
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)

示例2 合并源对象为表，目标对象为视图。

1、创建测试表并插入数据。

CREATE TABLE products1
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
CREATE TABLE newproducts1
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO products1 VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products1 VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products1 VALUES (1600, 'play gym', 'toys');
INSERT INTO products1 VALUES (1601, 'lamaze', 'toys');
INSERT INTO products1 VALUES (1666, 'harry potter', 'dvd');
INSERT INTO newproducts1 VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts1 VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts1 VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts1 VALUES (1700, 'wait interface', 'books');

2、创建基于测试表product的视图。

create view v_products1 as select * from products1;

3、执行合并操作。源对象为测试表newproducts1，目标对象为视图v_procucts。

MERGE INTO v_products1 vp
USING newproducts1 np
ON (vp.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE SET vp.product_name = np.product_name, vp.category = np.category WHERE vp.product_name != 'play gym'
WHEN NOT MATCHED THEN
INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

4、查询合并后结果。

select * from v_products1;;
select * from newproducts1;
select * from products1;

查询结果为如下：

 product_id |  product_name  | category
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1600 | play gym       | toys
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)

product_id |  product_name  | category
------------+----------------+-----------
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(4 rows)

 product_id |  product_name  | category
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1600 | play gym       | toys
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)

示例3 合并源对象为视图，目标对象为视图。

1、创建测试表并插入数据。

CREATE TABLE products2
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
CREATE TABLE newproducts2
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);

INSERT INTO products2 VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products2 VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products2 VALUES (1600, 'play gym', 'toys');
INSERT INTO products2 VALUES (1601, 'lamaze', 'toys');
INSERT INTO products2 VALUES (1666, 'harry potter', 'dvd');
INSERT INTO newproducts2 VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts2 VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts2 VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts2 VALUES (1700, 'wait interface', 'books');

2、创建基于测试表的视图。

create view v_products2 as select * from products2;  
create view v_newproducts2 as select * from newproducts2;

3、执行合并操作，将视图v_newproducts2中符合条件的内容合并至视图v_products2。

MERGE INTO v_products2 vp
USING v_newproducts2 vnp
ON (vp.product_id = vnp.product_id)
WHEN MATCHED THEN
UPDATE SET vp.product_name = vnp.product_name, vp.category = vnp.category WHERE vp.product_name != 'play gym'
WHEN NOT MATCHED THEN
INSERT VALUES (vnp.product_id, vnp.product_name, vnp.category) WHERE vnp.category = 'books';

4、查询视图及测试表的数据。

select * from v_products2;
select * from products2;
select * from v_newproducts2;
select * from newproducts2;

查询结果为如下：

 product_id |  product_name  | category
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1600 | play gym       | toys
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)

 product_id |  product_name  | category
------------+----------------+-----------
       1501 | vivitar 35mm   | electrncs
       1600 | play gym       | toys
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(6 rows)

 product_id |  product_name  | category
------------+----------------+-----------
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(4 rows)

 product_id |  product_name  | category
------------+----------------+-----------
       1502 | olympus camera | electrncs
       1601 | lamaze         | toys
       1666 | harry potter   | toys
       1700 | wait interface | books
(4 rows)

VastbaseG100

MERGE

功能描述

注意事项

语法格式

参数说明

示例

相关链接

弹框头部