MERGE

目的

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

この文は、複数の操作を組み合せるときに便利です。DML文INSERTUPDATEおよび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パラメータにINSERTUPDATEおよびDELETE文を含めるか、statement_typesパラメータを完全に省略します。特定のタイプのSQL文にポリシーを適用する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。

前提条件

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

構文

merge::=

注意:

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

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

merge_update_clause::=

merge_insert_clause::=

error_logging_clause::=

セマンティクス

INTO句

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

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

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

ON句

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

ON句の制限事項:

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パラメータにINSERTUPDATEおよびDELETE文を含めるか、statement_typesパラメータを完全に省略します。特定のタイプのSQL文にポリシーを適用する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。

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_clauseMERGE文での動作は、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 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

条件付き挿入と更新: 例

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

次の文は、people_sourcepeople_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_targetpeople_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;

次の文は、people_target表の内容を表示し、ロールバックを実行します。

SELECT * FROM people_target;

PERSON_ID FIRST_NAME		LAST_NAME	     TITLE
---------- -------------------- -------------------- ----------
	 1 John 		Smith		     Mr
	 2 Alice		Jones		     Mrs.

ROLLBACK;

次の文は、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 NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

次の文は、people_target表の内容を表示し、ロールバックを実行します。

SELECT * FROM people_target;

PERSON_ID FIRST_NAME		LAST_NAME	     TITLE
---------- -------------------- -------------------- ----------
	 1 John 		Smith		     Mr
	 2 alice		jones		     Mrs
	 3 Jane 		Doe		     Miss
	 4 Dave 		Brown		     Mr

ROLLBACK;

次の文は、person_id列を使用してpeople_target表とpeople_source表の内容を比較し、people_target表で条件に従ってデータを挿入し更新します。people_source表の一致する行ごとに、people_source表の値を使用してpeople_target表の値が更新されます。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 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);

次の文は、people_target表の内容を表示し、ロールバックを実行します。

SELECT * FROM people_target;

PERSON_ID FIRST_NAME		LAST_NAME	     TITLE
---------- -------------------- -------------------- ----------
	 1 John 		Smith		     Mr
	 2 Alice		Jones		     Mrs.
	 3 Jane 		Doe		     Miss
	 4 Dave 		Brown		     Mr

ROLLBACK;

次の文は、person_id列を使用してpeople_target表とpeople_source表を比較します。person_idが一致すると、people_source表の値を使用して、people_target表で対応する行が更新されます。DELETE句は、titleが‘Mrs.’であるpeople_targetのすべての値を削除します。person_idが一致しない場合は、people_source表の行がpeople_target表に追加されます。WHERE句を指定しているので、titleが‘Mr’である値のみが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 
  DELETE where pt.title  = 'Mrs.' 
WHEN NOT matched THEN INSERT 
  (pt.person_id, pt.first_name, pt.last_name, pt.title) 
  VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title) 
  WHERE ps.title = 'Mr';

次の文は、people_target表の内容を表示し、ロールバックを実行します。

SELECT * FROM people_target;

PERSON_ID FIRST_NAME		LAST_NAME	     TITLE
---------- -------------------- -------------------- ----------
	 1 John 		Smith		     Mr
	 4 Dave 		Brown		     Mr

ROLLBACK;

Live SQL:

MERGEを使用した条件付き挿入および更新で、Oracle Live SQLの関連例を表示し、実行します。