用途
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
オブジェクト権限、およびソース表に対するREAD
またはSELECT
オブジェクト権限が必要です。merge_update_clause
のDELETE
句を指定するには、対象となる表に対するDELETE
オブジェクト権限も必要です。
構文
merge::=
注意: 少なくともmerge_update_clause またはmerge_insert_clause 句のどちらかを指定する必要があります。 |
(merge_update_clause ::=、merge_insert_clause ::=、error_logging_clause ::=を参照)
where_clause::=
セマンティクス
INTO句
INTO
句を使用すると、更新または挿入の対象となる表またはビューを指定できます。データをビューにマージする場合、そのビューは更新可能であることが必要です。詳細は、「更新可能なビューの注意事項:」を参照してください。
ターゲット・ビューの制限事項: INSTEAD
OF
トリガーが定義されたターゲット・ビューを指定することはできません。
USING句
USING
句を使用すると、更新または挿入の対象となるデータのソースを指定できます。ソースには、表、ビューまたは副問合せの結果を指定できます。
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
パラメータにINSERT
、UPDATE
およびDELETE
文を含めるか、statement_types
パラメータを完全に省略します。特定のタイプのSQL文にポリシーを適用する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。
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
を指定すると、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の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