2008-05-29: Merge_must have `insert` and `update` privileges on the -优快云博客

本文详细介绍了 Oracle 数据库中 SQL 的 MERGE 语句用法，包括如何使用该语句进行数据更新和插入操作，以及如何通过条件判断决定更新或插入目标表。此外还提供了实际应用案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MERGE

Purpose

Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables. You can specify conditions to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

Prerequisites

You must have the INSERT and UPDATE object privileges on the target table and the SELECT object privilege on the source table. To specify the DELETE clause of the merge_update_clause, you must also have the DELETE object privilege on the target table.

Syntax

merge::=
Description of merge.gif follows

Description of the illustration merge.gif

merge_update_clause ::=
Description of merge_update_clause.gif follows

Description of the illustration merge_update_clause.gif

merge_insert_clause ::=
Description of merge_insert_clause.gif follows

Description of the illustration merge_insert_clause.gif

where_clause::=
Description of where_clause.gif follows

Description of the illustration where_clause.gif

Semantics

INTO Clause

Use the INTO clause to specify the target table you are updating or inserting into.

USING Clause

Use the USING clause to specify the source of the data to be updated or inserted. The source can be a table, view, or the result of a subquery.

ON Clause

Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.

merge_update_clause

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

You can specify this clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order.

Restrictions on the merge_update_clause

You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.

merge_insert_clause

The merge_insert_clause specifies values to insert into the column of the target table if the condition of the ON clause is false. If the insert clause is executed, then all insert triggers defined on the target table are activated. If you omit the column list after the INSERT keyword, then the number of columns in the target table must match the number of values in the VALUES clause.

To insert all of the source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is ON (0=1). Oracle Database recognizes such a predicate and makes an unconditional insert of all source rows into the table. This approach is different from omitting the merge_update_clause. In that case, the database still must perform a join. With constant filter predicate, no join is performed.

Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Oracle Database skips the insert operation for all rows for which the condition is not true.

You can specify this clause by itself or with the merge_update_clause. If you specify both, then they can be in either order.

Restriction on Merging into a View

You cannot specify DEFAULT when updating a view.

Examples

Merging into a Table: Example

The following example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

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;

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*0.1)
     WHERE (S.salary <= 8000);

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740