oracle merge 9 iから10 gまでの強化

MERGE文は、UPDATE文とINSERT文を統合するOracle 9 iの新しい構文です.接続条件マッチング上のUPDATEを行い、マッチングできない実行INSERT.この構文は1回のフルテーブルスキャンですべての作業を完了し,実行効率はINSERT+UPDATEより高い.
具体例を見てみましょう.

hr@ORCL> select * from p;

        ID NAME
---------- ----------
         1 d
         3 e
         8 f

hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

hr@ORCL> merge into t using p
  2         on (t.id=p.id)
  3       when matched then
  4     update set
  5       t.name=p.name
  6       when not matched then
  7     insert values
  8       (p.id,p.name)
  9  ;

3 rows merged.

hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 d
         2 b
         3 e
         8 f

hr@ORCL> commit;

Commit complete.

例を少し修正します.

hr@ORCL> insert into p values(1,'h');

1 row created.

hr@ORCL> commit;

Commit complete.

hr@ORCL> merge into t using p
  2         on (t.id=p.id)
  3       when matched then
  4     update set
  5       t.name=p.name
  6       when not matched then
  7     insert values
  8       (p.id,p.name)
  9  ;
 merge into t using p
                    *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

このエラーはMERGEを使用する最も一般的なエラーです.このエラーの原因は、接続条件によって得られるソーステーブルのレコードが一意ではないためです.merge intoではkey値を設定する必要があり、このkey値に基づいてmerge intoの操作(updateかinsert intoか)が決定されるので、mergeではこのkey値が一意であることが要求される.したがって、ソーステーブルからすべてのデータを選択し、key値が一意である必要があります.
もう1つのエラーもよくあります.

hr@ORCL> create table mm (id number,name varchar2(10));

Table created.

hr@ORCL> create table mn (id number,name varchar2(10));

Table created.

hr@ORCL> insert into mm values(1,'A');

1 row created.

hr@ORCL> insert into mn values(1,'B');

1 row created.

hr@ORCL> merge into mn using mm
  2         on (mn.id=mm.id)
  3       when matched then
  4     update set mn.id=mm.id
  5       when not matched then
  6     insert values
  7      (mm.id,mm.name);
       on (mn.id=mm.id)
           *
ERROR at line 2:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "MN"."ID"

10 g拡張1:where句
次の例を示します.

hr@ORCL> select * from p;

        ID NAME
---------- ----------
         1 d
         2 f
         7 g

hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

hr@ORCL> merge into t using p
  2         on (p.id=t.id)
  3      when matched then
  4        update set
  5          t.name=p.name
  6        where p.id=2;

1 row merged.

hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 f
         3 c

10 g強化二:delete句

hr@ORCL> select * from p;

        ID NAME
---------- ----------
         1 d
         2 f
         7 g


hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 f
         3 c

hr@ORCL> merge into t using p
  2         on (p.id=t.id)
  3       when matched then
  4     update set     
  5         t.name=p.name
  6     delete where
  7        (t.id=2);

2 rows merged.

hr@ORCL> select * from t;

        ID NAME
---------- ----------
         1 d
         3 c