強力な SQL Upsert（アップサート)
ご存知のとおり、ビジネス・コンピューティングで一般的な作業として、存在するエンティティー (顧客、ベンダー、発注書明細行) を更新し、存在しない場合は追加する作業があります。RPG では、条件ステートメントで update と write という 2 つの命令が必要です。SQL では 1 つのステートメントでシバン全体を処理します。では、それがどのように動作するのか見てみましょう。
こうした状況を図示した RPG III コードを下に示します。
FCUSTF UF E K DISK
. . . コード省略
C CUSKEY KLIST
C KFLD COMP
C KFLD ACCT
C*
. . . さらにコード省略
C* HILOEQ
C CUSKEY CHAINCUSTREC 99
C*
. . . フィールドに読み込むコード省略
C*
C *IN99 IFEQ *OFF
C UPDATCUSTREC
C ELSE
C WRITECUSTREC
C ENDIF
. . . 他
プログラムはカスタマー・ファイルにランダム読み取り (CHAIN) を行います。読み取りが成功すると (顧客がデータベースにいるなど)、プログラムは標識 99 をオフにします。読み取りが失敗すると、標識 99 がオンになります。
いったんフィールドが変更されたら、データをデータベースに格納します。プログラムは標識 99 を使用して、データの更新または追加を制御します。
SQL の世界では、こうしたタイプの出力命令は非公式に "update" と "insert" を組み合わせて "upsert" と呼ばれています。upsert を処理する SQL ステートメントは MERGE です。
RPG III の例で行ったようなことを行う SQL のプログラム片を以下に示します。
D aCompany s 3p 0
D aAccount s 5p 0
D aName s 20a
D aCity s 15a
D aState s 2a
D aZip s 10a
exec sql
merge into custf as tgt
using (values(:aCompany, :aAccount, :aName,
:aCity, :aState, :aZip))
as src (Company, Account, Name, City, State, Zip)
on (tgt.Comp, tgt.Acct) = (src.Company, src.Account)
when matched then（マッチしたら次に）
update set tgt.Comp = src.Company,
tgt.Acct = src.Account,
tgt.Name = src.Name,
tgt.City = src.City,
tgt.State = src.State,
tgt.Zip = src.Zip
when not matched then（マッチしなかったら次に）
insert values(src.Company, src.Account, src.Name,
src.City, src.State, src.Zip);
ホスト変数 aCompany、aAccount、aName、aCity、aState、aZip に適切な値が読み込まれたら、データをデータベースに格納します。MERGE を一つずつ分解すると以下のようになります。
merge into custf as tgt
更新するデータベース表は CUSTF で、ここでは相関名 tgt (target) が指定されています。
using (values(:aCompany, :aAccount, :aName,
:aCity, :aState, :aZip))
as src (Company, Account, Name, City, State, Zip)
データベースにマージするデータは、前述の 6 つのホスト変数です。values 関数は相関名 src (source) として変数を 1 行の派生表にグループ分けしています。この派生表には Company、Account、Name、City、State、Zip という 6 つの列があります。
on (tgt.Comp, tgt.Acct) = (src.Company, src.Account)
CUSTF および値により作成された派生表は、会社およびアカウント番号と照合されます。
when matched then（マッチしたら次に）
update set tgt.Comp = src.Company,
tgt.Acct = src.Account,
tgt.Name = src.Name,
tgt.City = src.City,
tgt.State = src.State,
tgt.Zip = src.Zip
会社およびアカウントの行 (レコード) がすでに CUSTF にある場合、ホスト変数から作成された派生表のデータで行を更新します。
when not matched then（マッチしなかったら次に）
insert values(src.Company, src.Account, src.Name,
src.City, src.State, src.Zip);
指定された会社およびアカウントの行が存在しない場合は、新しい行を CUSTF 表に追加します。
少し練習すれば、誰にも負けないぐらい upsert できることでしょう。
