ORACLE MERGE INTO UPDATE DELETE 用法
ORACLE MERGE INTO UPDATE DELETE 用法
使用该MERGE语句从一个或多个源中选择行以进行更新或插入表或视图。您可以指定条件以确定是更新还是插入目标表或视图。
此语句是组合多个操作的便捷方式。它可以让你避免多次INSERT，UPDATE和DELETEDML语句。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.7MERGE 示例
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
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;">
