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.gif」の説明

注意:

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

(merge_update_clause ::=、merge_insert_clause ::=、error_logging_clause ::=を参照)

merge_update_clause::=

図「merge_update_clause.gif」の説明

merge_insert_clause::=

図「merge_insert_clause.gif」の説明

where_clause::=

図「where_clause.gif」の説明

error_logging_clause::=

図「error_logging_clause.gif」の説明

セマンティクス

INTO句

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

ターゲット・ビューの制限事項: INSTEAD OFトリガーが定義されたターゲット・ビューを指定することはできません。

USING句

USING句を使用すると、更新または挿入の対象となるデータのソースを指定できます。ソースには、表、ビューまたは副問合せの結果を指定できます。

ON句

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

ON句の制限事項:

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_update_clauseとともに指定できます。merge_insert_clauseとともに指定する場合は、どちらを先に指定してもかまいません。

merge_insert_clauseの制限事項: ビューへの挿入時にはDEFAULTを指定できません。

error_logging_clause

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

関連項目:

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

例

表へのマージ例次の例では、oeサンプル・スキーマ内のbonuses表(ボーナスのデフォルトは100)を使用します。次に、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 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
        155        170
        159        180
        160        175
        161        170
        164         72
        165         68
        166         64
        167         62
        171         74
        172         73
        173         61
        179         62