MERGE

目的

MERGE文を使用すると、1つ以上のソースから行を選択し、表またはビューに対して更新および挿入できます。対象となる表またはビューに対して更新と挿入のどちらを実行するかを決定する条件を指定できます。

この文は、複数の操作を組み合せるときに便利です。DML文INSERT、UPDATEおよびDELETEを複数指定する必要がなくなります。

MERGEは、決定的な文です。対象となる表の同じ行を、同一のMERGE文で何度も更新することはできません。

ノート:

Oracle Databaseの以前のリリースでは、MERGE INTO文を含むアプリケーションでOracle Virtual Private Databaseポリシーを作成すると、Virtual Private Databaseポリシーが存在するため、MERGE INTO文はORA-28132「MERGE INTO構文ではセキュリティ・ポリシーをサポートしていません」エラーにより回避されていました。Oracle Database 11gリリース2(11.2.0.2)以降では、MERGE INTO操作を含むアプリケーションでポリシーを作成できます。そのためには、DBMS_RLS.ADD_POLICY statement_typesパラメータにINSERT、UPDATEおよびDELETE文を含めるか、statement_typesパラメータを完全に省略します。特定のタイプのSQL文にポリシーを適用する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。

前提条件

対象となる表に対するINSERTオブジェクト権限とUPDATEオブジェクト権限、およびソース・オブジェクトに対するSELECTオブジェクト権限が必要です。merge_update_clauseのDELETE句を指定するには、対象となる表またはビューに対するDELETEオブジェクト権限も必要です。

構文

merge::=

図merge.epsの説明

ノート:

少なくともmerge_update_clauseまたはmerge_insert_clause句のどちらかを指定する必要があります。

(merge_update_clause::=、merge_insert_clause::=、error_logging_clause::=)

values_clause::=

図values_clause.epsの説明

merge_update_clause::=

図merge_update_clause.epsの説明

merge_insert_clause::=

図merge_insert_clause.epsの説明

where_clause::=

図where_clause.epsの説明

error_logging_clause::=

図error_logging_clause.epsの説明

セマンティクス

INTO句

INTO句を使用すると、更新または挿入の対象となる表またはビューを指定できます。データをビューにマージする場合、そのビューは更新可能であることが必要です。詳細は、「更新可能なビューのノート」を参照してください。

ターゲット・ビューの制限事項

INSTEAD OFトリガーが定義されたターゲット・ビューは指定できません。

USING句

USING句を使用して、更新または挿入の元となるソースを指定します。

values_clause

values_clauseのセマンティクスについては、SELECT文の「values_clause」のvalues_clauseを参照してください。

ON句

ON句を使用すると、MERGEの更新操作または挿入操作の条件を指定できます。対象となる表の中で検索条件が真となる各行は、ソースの対応するデータに基づいて行が更新されます。どの行も条件が真とならない場合、ソースの対応する行に基づいて対象となる表に行が挿入されます。

merge_update_clause

merge_update_clauseを指定すると、対象となる表またはビューの新しい列値を指定できます。ON句の条件が真となる場合、更新が実行されます。更新が実行されると、対象となる表に定義されているすべての更新トリガーがアクティブになります。

where_clauseを指定すると、指定した条件が真の場合のみに更新操作が実行されるようにできます。条件には、データソースまたは対象となる表を参照できます。条件が真ではない場合、行を表に挿入する際に更新操作がスキップされます。

DELETE where_clauseを指定すると、表の移入中または更新中にその表内のデータをクリーンアップできます。この句によって処理される行は、マージ操作によって更新される対象の表内の行のみです。DELETE WHERE条件は、更新後の値を評価し、UPDATE SET ... WHERE条件によって評価された元の値は評価しません。更新先の表の行がDELETE条件を満たし、ON句によって定義された結合に含まれていない場合、その行は削除されます。更新先の表に定義されている削除トリガーが起動し、各行が削除されます。

この句は、単独で、またはmerge_insert_clauseとともに指定できます。merge_insert_clauseとともに指定する場合は、どちらを先に指定してもかまいません。

merge_update_clauseの制限事項

この句には、次の制限事項があります。

参照する列は、ON condition句で更新できません。
ビューを更新する場合は、DEFAULTを指定できません。

merge_insert_clause

merge_insert_clauseを指定すると、ON句の条件が偽となる場合に対象となる表の列に挿入する値を指定できます。挿入が実行されると、対象となる表に定義されているすべての挿入トリガーがアクティブになります。INSERTキーワードの後に列リストを指定しない場合、対象となる表内の列数は、VALUES句内の値の数と一致している必要があります。

すべてのソース行を表に挿入するには、ON句の条件に定数フィルタ条件を使用します。定数フィルタ条件の一例はON(0=1)です。Oracle Databaseはこのような条件を認識すると、すべてのソース行を無条件に表に挿入します。この方法は、merge_update_clauseを省略することとは異なります。merge_update_clauseを省略しても、結合は実行されます。定数フィルタ条件を設定すると、結合は実行されません。

where_clauseを指定すると、指定した条件が真の場合のみに更新操作が実行されるようにできます。条件には、データソース列のみを参照できます。条件が真ではないすべての行に対する挿入操作はスキップされます。

merge_insert_clauseは、単独で、またはmerge_update_clauseとともに指定できます。merge_insert_clauseとともに指定する場合は、どちらを先に指定してもかまいません。

merge_insert_clauseの制限事項

ビューに挿入する場合は、DEFAULTを指定できません。

error_logging_clause

error_logging_clauseのMERGE文での動作は、INSERT文の場合と同じです。詳細は、「INSERT」文の「error_logging_clause」を参照してください。

関連項目:

エラー・ロギングによる表への挿入: 例

例

表へのマージ: 例

次の例では、サンプル・スキーマoeのデフォルト・ボーナスが100であるbonuses表を使用します。次に、oe.orders表のsales_rep_id列に基づいて、販売実績があったすべての従業員をbonuses表に挿入します。最終的に、人事部門マネージャが、給与が$8000以下の従業員にボーナスを支給することを決定します。販売実績がなかった従業員には、給与の1%がボーナスとして支給されます。販売実績があった従業員には、給与の1%がボーナスに加算されて支給されます。MERGE文は、これらの変更を1行で実装します。

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM hr.employees e, oe.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 hr.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
        155        170
        159        180
        160        175
        161        170
        164         72
        165         68
        166         64
        167         62
        171         74
        172         73
        173         61
        179         62

条件付き挿入と更新: 例

次の例では、MERGE文を使用して、条件に従って表データを挿入し更新します。

次の文は、people_sourceとpeople_targetという名前の2つの表を作成し、名前を移入します。

CREATE TABLE people_source ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

CREATE TABLE people_target ( 
  person_id  INTEGER NOT NULL PRIMARY KEY, 
  first_name VARCHAR2(20) NOT NULL, 
  last_name  VARCHAR2(20) NOT NULL, 
  title      VARCHAR2(10) NOT NULL 
);

INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');

COMMIT;

次の文は、person_id列を使用してpeople_targetとpeople_sourceの内容を比較します。people_source表に一致するものがあると、people_target表の値が更新されます。

MERGE INTO people_target pt 
USING people_source ps 
ON    (pt.person_id = ps.person_id) 
WHEN MATCHED THEN UPDATE 
  SET pt.first_name = ps.first_name, 
      pt.last_name = ps.last_name, 
      pt.title = ps.title;