Oracle の MERGE 構文に関する備忘録 - バインド変数の扱い方
DB アプリを書いていると、どうしても使いたくなるのが UPSERT 機能。
実際には UPSERT という構文はありませんが、意味としてはレコードがあれば UPDATE で更新し、レコードがなければ INSERT で追加をするってことです。
従って、普通に書くなら、SELECT → UPDATE/INSERT と場合分けというロジックでプログラムを書くわけですが、トランザクションで考えるとコレが意外とやっかいなときがあります。そのような場合は SELECT FOR UPDATE とか使って行ロックをかけたりする訳なのですが、これまた予期せぬロック待ちの元になったりする場合があり得るので、あまり人に書かせたくない。
Oracle には UPSERT に相当する構文として MERGE 構文があるので、結構重宝します。ただし Oracle9 以降で実装された機能なのですが、今時 8i 以前を使ってる環境も少ないと思うので問題ないでしょう。
MERGE 構文は通常次のように説明されていると思います。Oracle の SQL マニュアルも以下のようになっています。
USING 表名2 ｜ 副問い合わせ
ON ( 結合条件 )
WHEN MATCHED THEN
UPDATE SET 列名1 = 値1, 列名2 = 値2, ･･･
WHEN NOT MATCHED THEN
INSERT [( 列名1, 列名2, ･･･ )] VALUES ( 値1, 値2, ･･･ )
で、多くの例題においては、表１と表２を２つの表を元にゴニョゴニョって処理がかかれていて、実際よく使う UPSERT 処理とは別用途の例題なんですよね。
ってわけで、今から登録するデータが既に表１にあれば 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 できることがわかってきたかと思います。
基本構文はこうです。
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 句はそのような制限はありません。だってまだ表中に存在していないのですから。
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 ; /
間違いを最後に書こうかと思ったんですけど、間違いは書かなくても良いと感じてきたので、これにてお終い。
コメントやシェアをお願いします！
ht
基本構文は
merge into TBL_NAME using TBL_NAME|SUBQUERY on ( CONDITION )
[ when matched then MERGE_UPDATE_CLAUSE ]
[ when not matched then MERGE_INERT_CLAUSE ]
[ ERROR_LOGGING_CLAUSE] です。
オラクルのドキュメントを参照するのがよいかと思います。
今回の場合、比較対象のテーブルが未定義のためダミーテーブル(dual)を使用している点を付け加えると誤解がないかと思います。
なので using dual on (ID=4) 以外に
using ( select 4 ID from dual) DUMMY on (DUMMY.ID=TEST01.ID) といった書き方もあります。mergeしていることを想像しやすく応用できる利点があります。