ORACLE MERGE INTO UPDATE DELETE 用法

使用该 MERGE 语句从一个或多个源中选择行以进行更新或插入表或视图。您可以指定条件以确定是更新还是插入目标表或视图。

此语句是组合多个操作的便捷方式。它可以让你避免多次 INSERT ， UPDATE 和 DELETE DML 语句。 MERGE 是一个确定性的陈述。您无法在同一 MERGE 语句中多次更新目标表的同一行。

1.1 先决条件

您必须具有 INSERT 与 UPDATE 目标表和对象的权限 READ 或 SELECT 源表上的对象权限。要指定 DELETE 子句 merge_update_clause ，还必须 DELETE 在目标表上具有对象特权。

1.2 句法

merge :: =

注意： 您必须至少指定其中一个条款 merge_update_clause 或 merge_insert_clause 。

merge_update_clause :: =

merge_insert_clause :: =

where_clause :: =

error_logging_clause :: =

1.3 关于 INTO 说明

使用该 INTO 子句指定要更新或插入的目标表或视图。为了将数据合并到视图中，视图必须是可更新的。

对目标视图的限制

您无法指定 INSTEAD OF 已定义触发器的目标视图。

1.4 关于 ON 说明

使用该 ON 子句指定 MERGE 操作更新或插入的条件。对于搜索条件为 true 的目标表中的每一行， Oracle 数据库使用源表中的相应数据更新该行。如果任何行的条件不为真，则数据库将根据相应的源表行插入目标表。

对 ON 条款的限制

在以前版本的 Oracle 数据库中，当您在包含该 MERGE INTO 语句的应用程序上创建 Oracle 虚拟专用数据库策略时，由于存在虚拟专用数据库策略，该 MERGE INTO 语句将被阻止并出现 ORA-28132: Merge into syntax does not support security policies 错误。从 Oracle Database 11g 第 2 版 （ 11.2.0.2 ）开始，您可以在包含 MERGE INTO 操作的应用程序上创建策略。要做到这一点，在 DBMS_RLS 。 ADD_POLICY statement_types 参数，包括 INSERT ， UPDATE ，和 DELETE 语句，或只是省略 statement_types 参数完全。有关对特定 SQL 语句类型实施策略的详细信息，请参阅 “ Oracle 数据库安全指南 ” 。

1.5 merge_update_clause

在 merge_update_clause 指定目标表的新列值。如果 ON 子句的条件为真， Oracle 将执行此更新。如果执行 update 子句，则激活目标表上定义的所有更新触发器。

指定 where_clause ，如果你想在数据库中执行仅当指定条件为真更新操作。条件可以指数据源或目标表。如果条件不为 true ，则数据库会在将行合并到表中时跳过更新操作。

指定 DELETE where_clause 在 填充或更新表 时清理表中的数据。受此子句影响的唯一行是目标表中由合并操作更新的那些行。该 DELETE WHERE 条件对 更新后的值 ，而不是由评估原值 UPDATE SET ... WHERE 条件。如果目标表的一行符合 DELETE 条件但未包含在 ON 子句定义的连接中，则不会删除它。对于每行删除，将激活目标表上定义的任何删除触发器。

您可以 单独指定此子句 ，也可以使用 merge_insert_clause 。如果 同时指定 两者，则它们可以按 任意顺序排列 。

即 : delele 只能跟 update 一起使用，同时 where 只能出现一次，如果 update 使用了 where ， delete 后面的 where 就无效了。

merge_update_clause 的 限制

本条款受以下限制：

您无法更新 ON condition 子句中引用的列。

您无法在更新视图时指定 DEFAULT 。

1.6 merge_insert_clause

merge_insert_clause 指定的值以插入到目标表的列，如果条件 ON 子句是假的。如果执行 insert 子句，则激活目标表上定义的所有插入触发器。如果省略 INSERT 关键字后面的列列表，则目标表中的列数必须与 VALUES 子句中的值数相匹配。

要将所有源行插入表中，可以在子句条件中使用 常量过滤谓词 ON 。常量过滤谓词的一个例子是 ON （ 0=1 ）。 Oracle 数据库识别这样的谓词，并将所有源行无条件地插入到表中。这种方法不同于省略 merge_update_clause 。在这种情况下，数据库仍然必须执行连接。使用常量过滤器谓词，不执行任何连接。

指定 where_clause ，如果你想 Oracle 数据库执行插入操作仅当指定条件为真。条件只能引用数据源表。 Oracle 数据库会跳过条件不为真的所有行的插入操作。

您可以单独指定此子句，也可以使用 merge_update_clause 。如果同时指定两者，则它们可以按任意顺序排列。

merge_insert_clause 的 限制

DEFAULT 插入视图时无法指定。

1.7 MERGE 示例

1.7.1 示例一

以下示例使用 bonuses 示例模式中的表， oe 默认奖励为 100. 然后 bonuses ，根据表的 sales_rep_id 列，将所有进行销售的员工插入到 oe.orders 表中。最后，人力资源经理决定薪水为 8000 美元或更低的员工应该获得奖金。那些没有进行销售的人可以获得 1 ％的工资奖金。那些已经进行销售的人的奖金增加相当于他们工资的 1 ％。该 MERGE 语句只需一步即可实现这些更改：

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)

(SELECT e.employee_id FROM employees e, orders o

WHERE e.employee_id = o.sales_rep_id

GROUP BY e.employee_id);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS

----------- ----------

153 100

154 100

155 100

156 100

158 100

159 100

160 100

161 100

163 100

MERGE INTO bonuses D

USING (SELECT employee_id, salary, department_id FROM employees

WHERE department_id = 80) S

ON (D.employee_id = S.employee_id)

WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

DELETE WHERE (S.salary > 8000)

WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

VALUES (S.employee_id, S.salary*.01)

WHERE (S.salary <= 8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS

----------- ----------

153 180

154 175

155170

159 180

160 175

161 170

164 72

165 68

166 64

167 62

171 74

172 73

173 61

179 62

参考： https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F

1.7.2 示例二

目标：从 T_TAR 表中删除 ID 在 T_ORG 表中存在的记录，将 ID 在 T_ORG 中存在，而在 T_TAR 中不存在，且 TPYE 类型为 TABLE 的记录插入到 T_TAR 表中。

环境建立：

Create table T_ORG (ID NUMBER ,NAME VARCHAR2(30), TYPE VARCHAR2(19));

Create table T_TAR (ID NUMBER ,NAME VARCHAR2(30), TYPE VARCHAR2(19));

INSERT INTO T_ORG SELECT ROWNUM, OBJECT_NAME ,OBJECT_TYPE FROM ALL_OBJECTS;

INSERT INTO T_TAR SELECT * FROM T_ORG WHERE TYPE=’INDEX’;

COMMIT;

Set timing on

Set autot on

Set linesize 160

alter system flush buffer_cache;

alter system flush shared_pool;

truncate table T_TAR;

truncate table T_ORG;

TRUNCATE TABLE T_ORG;

TRUNCATE TABLE T_TAR ;

INSERT INTO T_ORG SELECT ROWNUM, OBJECT_NAME ,OBJECT_TYPE FROM ALL_OBJECTS;

INSERT INTO T_TAR SELECT * FROM T_ORG WHERE TYPE='INDEX';

commit ;

MERGE INTO T_TAR t

USING T_ORG O

ON (t.id=o.id)

WHEN MATCHED THEN

updateset t.name = t.name

DELETE WHERE (t.id=o.id)

WHEN NOT MATCHED THEN

INSERTvalues (o.id,o.name,o.type) WHERE (o.type='TABLE') ;

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">