Oracle の MERGE 構文に関する備忘録 - バインド変数の扱い方

DB アプリを書いていると、どうしても使いたくなるのが UPSERT 機能。
実際には UPSERT という構文はありませんが、意味としてはレコードがあれば UPDATE で更新し、レコードがなければ INSERT で追加をするってことです。
従って、普通に書くなら、SELECT → UPDATE/INSERT と場合分けというロジックでプログラムを書くわけですが、トランザクションで考えるとコレが意外とやっかいなときがあります。そのような場合は SELECT FOR UPDATE とか使って行ロックをかけたりする訳なのですが、これまた予期せぬロック待ちの元になったりする場合があり得るので、あまり人に書かせたくない。

Oracle には UPSERT に相当する構文として MERGE 構文があるので、結構重宝します。ただし Oracle9 以降で実装された機能なのですが、今時 8i 以前を使ってる環境も少ないと思うので問題ないでしょう。

MERGE 構文は通常次のように説明されていると思います。Oracle の SQL マニュアルも以下のようになっています。

MERGE INTO 表名1
  USING 表名2 | 副問い合わせ
  ON ( 結合条件 )
WHEN MATCHED THEN
  UPDATE SET 列名1 = 値1, 列名2 = 値2, ・・・
WHEN NOT MATCHED THEN
  INSERT [( 列名1, 列名2, ・・・ )] VALUES ( 値1, 値2, ・・・ )

で、多くの例題においては、表1と表2を2つの表を元にゴニョゴニョって処理がかかれていて、実際よく使う UPSERT 処理とは別用途の例題なんですよね。
ってわけで、今から登録するデータが既に表1にあれば UPDATE 処理で無ければ INSERT 処理って言う実際の UPSERT 用途に則した例題を共有しておこうと思いましてこの記事を書いています。
もっとも、 MERGE 構文で UPSERT 処理を書こうと思ったら、アレ?どうするんだっけ?と小一時間ほど悩んだのでこうしてメモに残しているわけですけど。(^^ゞ

- スポンサーリンク -

さて、実際の UPSERT 用途に対応させるためには DUAL 表を活用する手があります。実際に例を見て貰うのが早いでしょう。
まずはテスト用の表とデータを作成する。

CREATE TABLE TEST01 (
  ID             INTEGER PRIMARY KEY,
  TEXT           VARCHAR(30),
  ON_CREATED     DATE DEFAULT SYSDATE
);

INSERT INTO TEST01 (ID, TEXT) VALUES( 1, 'AAAA' );
INSERT INTO TEST01 (ID, TEXT) VALUES( 2, 'BBBB' );
INSERT INTO TEST01 (ID, TEXT) VALUES( 3, 'CCCC' );
commit;
SQL> SELECT * FROM TEST01;

        ID TEXT                           ON_CREAT
---------- ------------------------------ --------
         1 AAAA                           08-07-03
         2 BBBB                           08-07-03
         3 CCCC                           08-07-03

間違いを先に示しても紛らわしいので、まずは正解から。

ID=4, TEXT='DDDD' を INSERT 処理したい時はどうするか?

MERGE INTO TEST01
USING DUAL
ON (ID=4)
WHEN MATCHED THEN
    UPDATE SET TEXT='DDDD'
WHEN NOT MATCHED THEN
    INSERT ( ID, TEXT ) VALUES ( 4,'DDDD' )
;

実行結果はこうなります。正常に ID=4 の列が INSERT されていることがわかります。

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

SQL> SELECT * FROM TEST01;

        ID TEXT                           ON_CREAT
---------- ------------------------------ --------
         1 AAAA                           08-07-03
         2 BBBB                           08-07-03
         3 CCCC                           08-07-03
         4 DDDD                           08-07-03

では次に、ID=3, TEXT='DDDD' と UPDATE 処理したい時はどうするか?

MERGE INTO TEST01
USING DUAL
ON (ID=3)
WHEN MATCHED THEN
    UPDATE SET TEXT='DDDD'
WHEN NOT MATCHED THEN
    INSERT ( ID, TEXT ) VALUES ( 3,'DDDD' )
;

実行結果はこうなります。正常に ID=3 の列が INSERT されていることがわかります。

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

SQL> SELECT * FROM TEST01;

        ID TEXT                           ON_CREAT
---------- ------------------------------ --------
         1 AAAA                           08-07-03
         2 BBBB                           08-07-03
         3 DDDD                           08-07-03

さて、これで DUAL 表を使うとウマイ具合に自分自身の表を UPDATE/INSERT できることがわかってきたかと思います。
基本構文はこうです。

MERGE INTO 表名1
  USING DUAL
  ON (列名1 = 値1, 列名2 = 値2, ・・・)
WHEN MATCHED THEN
  UPDATE SET 列名1 = 値1, 列名2 = 値2, ・・・
WHEN NOT MATCHED THEN
  INSERT [( 列名1, 列名2, ・・・ )] VALUES ( 値1, 値2, ・・・ )

さて、実際の業務においては当然のことのようにバインド変数を使うことになるでしょう。次はバインド変数対応にしてみたいと思います。

DECLARE
  id     NUMBER;
  text   VARCHAR(30);
  u_cur  INTEGER;
  u_sql  VARCHAR2(1000);
  u_row  INTEGER;

BEGIN
  id   := 4;
  text := 'DDDD';

  u_cur := DBMS_SQL.OPEN_CURSOR;
  u_sql := 'MERGE INTO TEST01 USING DUAL ON (ID=:b1) WHEN MATCHED THEN UPDATE SET TEXT=:b2 WHEN NOT MATCHED THEN INSERT ( ID, TEXT ) VALUES ( :b1, :b2 )';
  DBMS_SQL.PARSE(u_cur, u_sql, dbms_sql.v7);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b1', id);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b2', text);
  u_row := DBMS_SQL.EXECUTE(u_cur);
  DBMS_SQL.CLOSE_CURSOR(u_cur);

End
;
/

実行結果はこうなります。正常に ID=4 の列が INSERT されていることがわかります。

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM TEST01;

        ID TEXT                           ON_CREAT
---------- ------------------------------ --------
         1 AAAA                           08-07-03
         2 BBBB                           08-07-03
         3 CCCC                           08-07-03
         4 DDDD                           08-07-03

同じく、ID=3, TEXT='DDDD' と UPDATE 処理したい時をバインド変数でやってみます。id := 3 の部分が違うだけです。

DECLARE
  id     NUMBER;
  text   VARCHAR(30);
  u_cur  INTEGER;
  u_sql  VARCHAR2(1000);
  u_row  INTEGER;

BEGIN
  id   := 3;
  text := 'DDDD';

  u_cur := DBMS_SQL.OPEN_CURSOR;
  u_sql := 'MERGE INTO TEST01 USING DUAL ON (ID=:b1) WHEN MATCHED THEN UPDATE SET TEXT=:b2 WHEN NOT MATCHED THEN INSERT ( ID, TEXT ) VALUES ( :b1, :b2 )';
  DBMS_SQL.PARSE(u_cur, u_sql, dbms_sql.v7);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b1', id);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b2', text);
  u_row := DBMS_SQL.EXECUTE(u_cur);
  DBMS_SQL.CLOSE_CURSOR(u_cur);

End
;
/

実行結果はこうなります。正常に ID=3 の列が UPDATE されていることがわかります。

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM TEST01;

        ID TEXT                           ON_CREAT
---------- ------------------------------ --------
         1 AAAA                           08-07-03
         2 BBBB                           08-07-03
         3 DDDD                           08-07-03

つまり、バインド変数を使った MERGE 構文はこうなります。
注意点は、UPDATE 句の列名には ON 句で使用した列名は使用できないという点です。これは意味を考えればわかると思いますが、マッチしている自分自身のキーを変更できないってわけです。キー以外は更新可能ってわけです。
INSERT 句はそのような制限はありません。だってまだ表中に存在していないのですから。

MERGE INTO 表名1
  USING DUAL
  ON (列名1 = ?, 列名2 = ?, ・・・)
WHEN MATCHED THEN
  UPDATE SET 列名n1 = ?, 列名n2 = ?, ・・・
WHEN NOT MATCHED THEN
  INSERT [( 列名1, 列名2, ・・・ )] VALUES ( ?, ?, ・・・ )


さて、Perl DBI から MERGE 構文を利用するならこうなります。

my $sth = $dbh->prepare(qq{
MERGE INTO TEST01 
  USING DUAL 
  ON ( ID=? ) 
WHEN MATCHED THEN 
  UPDATE SET TEXT=?
WHEN NOT MATCHED THEN
  INSERT ( ID, TEXT ) VALUES ( ?, ? )
});

my $id   = 4;
my $text = 'DDDD';
$sth->execute( $id, $text, $id, $text );

$id   = 3;
$text = 'DDDD';
$sth->execute( $id, $text, $id, $text );

同じ引数を渡すのがなんだか・・・って感じる場合には、さらに MERGE 文をこんな感じに変更すると良いかもしれません。

my $sth = $dbh->prepare(qq{
MERGE INTO TEST01 A
  USING (SELECT ? ID, ? TEXT FROM DUAL) B 
  ON ( A.ID=B.ID ) 
WHEN MATCHED THEN 
  UPDATE SET TEXT=B.TEXT
WHEN NOT MATCHED THEN
  INSERT ( ID, TEXT ) VALUES ( B.ID, B.TEXT )
});

my $id   = 4;
my $text = 'DDDD';
$sth->execute( $id, $text );

$id   = 3;
$text = 'DDDD';
$sth->execute( $id, $text );


PL/SQL でも書いておくとこんな感じです。最終的には僕は DBI から書くことを考えて以下の形式が収まりがよく感じています。

DECLARE
  id     NUMBER;
  text   VARCHAR(30);
  u_cur  INTEGER;
  u_sql  VARCHAR2(1000);
  u_row  INTEGER;

BEGIN
  id   := 3;
  text := 'DDDD';

  u_cur := DBMS_SQL.OPEN_CURSOR;
  u_sql := 'MERGE INTO TEST01 A USING (SELECT :b1 ID, :b2 TEXT FROM DUAL) B ON ( A.ID=B.ID ) WHEN MATCHED THEN UPDATE SET TEXT=B.TEXT WHEN NOT MATCHED THEN INSERT ( ID, TEXT ) VALUES ( B.ID, B.TEXT )';
  DBMS_SQL.PARSE(u_cur, u_sql, dbms_sql.v7);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b1', id);
  DBMS_SQL.BIND_VARIABLE(u_cur, ':b2', text);
  u_row := DBMS_SQL.EXECUTE(u_cur);
  DBMS_SQL.CLOSE_CURSOR(u_cur);

End
;
/

間違いを最後に書こうかと思ったんですけど、間違いは書かなくても良いと感じてきたので、これにてお終い。

- スポンサーリンク -