08-02.データー操作語（DML）【MERGE文】

■MERGE文
　-MERGE文は条件により、データーの追加、更新、削除を一気にできる。
　-該当行が存在する場合、UPDATE(DELETEを含められる)を実施し、
　　新しい行の場合INSERTを実施する。
　-対象テーブルに対するUPDATE/INSERT条件はON～により決定される。
　-MERGE文でCLOB使用時、アップデートする内容が2000bytesが超えると
　　ORA-00600エラーが発生し、patch set 11.2.0.2バージョンで解決できる。


■MERGE Syntax

MERGE [hint]
    INTO [schema.]{table | view} [t_alias]
    USING { [schema.]{table|view}
           | subquery
          } [t_alias]
    ON (condition)
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT }]...
[DELETE where_clause]
WHEN NOT MATCHED THEN
INSERT [(column [, column]...)]
VALUES ({ expr [, expr]... | DEFAULT})

-INTO:DATAをUPDATE、INSERTされるテーブルまたはビューを指定。
-USING:比較するSOURCEテーブルまたはビュー、サブクエリを指定。
-ON:UPDATE、INSERTをする条件で、該当条件を果たすDATAがあればWHEN MATCED句を実施、
    なければWHEN NOT MATCHED以下を実施する。
-WHEN MATCHED:ON条件句がTRUEであるROWに実施する内容(UPDATE,DELETEも可能)
-WHEN NOT MATCHED:ON条件句に合うROWがない場合実施する内容（INSERT）

■MERGE例１

--部署番号20,30の社員が存在すれば給与を10%アップする。
　存在しなければ給与が1000より多い社員情報を登録する。（INSERT, UPDATE）

--部署番号10,20の社員情報を持つテーブルを作成
SQL> CREATE TABLE emp_merge_test
  2  AS SELECT empno, deptno, sal FROM emp WHERE deptno IN (10, 20);

表が作成されました。


--データーの確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        800
      7566         20       2975
      7782         10       2450
      7788         20       3000
      7839         10       5000
      7876         20       1100
      7902         20       3000
      7934         10       1300

8行が選択されました。

--社員が存在すれば給与10%UP、なければINSERT
SQL> MERGE INTO emp_merge_test m
  2  USING ( SELECT empno, deptno, sal
  3          FROM emp
  4          WHERE deptno IN (20,30)) e
  5  ON (m.empno = e.empno)
  6  WHEN MATCHED THEN
  7    UPDATE SET m.sal = ROUND(m.sal*1.1)
  8  WHEN NOT MATCHED THEN
  9  INSERT (m.empno, m.deptno, m.sal)
 10  VALUES (e.empno, e.deptno, e.sal)
 11  WHERE e.sal > 1000
 12  ;

10行がマージされました。


--部署番号20の社員達の給与が20％UPされて、部署番号30が登録されているか確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        880
      7566         20       3273
      7782         10       2450
      7788         20       3300
      7839         10       5000
      7876         20       1210
      7902         20       3300
      7934         10       1300
      7844         30       1500
      7698         30       2850
      7499         30       1600

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7521         30       1250
      7654         30       1250

13行が選択されました。


--次の例のため30部署のデーターを削除
SQL> DELETE FROM emp_merge_test WHERE deptno = 30;

5行が削除されました。

SQL> COMMIT;

コミットが完了しました。

■MERGE例２

--10部署の給与を10%UPし20部署の社員情報は削除、30部署の給与を30%UPする(INSERT,UPDATE,DELETE)

--データーの確認
SQL> SELECT * FROM emp_merge_test m;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7369         20        880
      7566         20       3273
      7782         10       2450
      7788         20       3300
      7839         10       5000
      7876         20       1210
      7902         20       3300
      7934         10       1300

8行が選択されました。


--MERGE文作成
SQL> MERGE INTO emp_merge_test m
  2  USING emp e
  3  ON (m.empno = e.empno)
  4  WHEN MATCHED THEN
  5     UPDATE SET m.sal = ROUND(m.sal*1.1)
  6     DELETE WHERE (m.deptno  = 20)
  7  WHEN NOT MATCHED THEN
  8  INSERT (m.empno, m.deptno, m.sal)
  9  VALUES (e.empno, e.deptno, ROUND(e.sal*1.2));

14行がマージされました。


--変更後の確認
SQL> SELECT * FROM emp_merge_test;

     EMPNO     DEPTNO        SAL
---------- ---------- ----------
      7782         10       2695
      7839         10       5500
      7934         10       1430
      7844         30       1800
      7521         30       1500
      7654         30       1500
      7698         30       3420
      7499         30       1920
      7900         30       1140

9行が選択されました。


--テストテーブルの削除
SQL> DROP TABLE emp_merge_test;

表が削除されました。

SQL>