MERGE的使用一些注意事项

官方文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

 

MERGE

Purpose

 

Use the MERGE statement to select rows from one ormore sources for update or insertion into a table or view. You canspecify conditions to determine whether to update or insert intothe target table or view.

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

MERGE is a deterministic statement. That is, youcannot update the same row of the target table multiple times inthe same MERGE statement.

Note:

Oracle Database does not implement fine-grained access controlduring MERGE statements. If you are using thefine-grained access control feature on the target table or tables,use equivalent INSERT and UPDATEstatements instead of MERGE to avoid error messagesand to ensure correct access control.

Prerequisites

You must have the INSERT and UPDATEobject privileges on the target table and the SELECTobject privilege on the source table. To specify theDELETE clause of the merge_update_clause, you mustalso have the DELETE object privilege on the targettable.

 

 

merge_update_clause

 

The merge_update_clause specifiesthe new column values of the target table. Oracle performs thisupdate if the condition of the ON clause is true. Ifthe update clause is executed, then all update triggers defined onthe target table are activated.

Specify the where_clause if you want thedatabase to execute the update operation only if the specifiedcondition is true. The condition can refer to either the datasource or the target table. If the condition is not true, then thedatabase skips the update operation when merging the row into thetable.

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

You can specify this clause by itself or with themerge_insert_clause. If youspecify both, then they can be in either order.

Restrictions on themerge_update_clause This clause is subject tothe following restrictions: --在update字句中的限制

• You cannot update a column that is referenced in theON condition clause.

• --你不能更新在ONcondition字句中被引用的一个列。

• You cannot specify DEFAULT when updating aview.

• 当更新一个试图时候你不能够指定默认