データベーステーブルへデータを登録する際、同じキーだったらUPDATE、同じキーが無ければINSERTして欲しい場面って、たまにありますよね。
UPDATE+INSERTで、俗に"UPSERT"とか言うようです。
ORACLEではMERGE INTOって構文があるのでそれでOKなんですが、今いじっているMS SQL Server 2005では…どうやらなさそう。（ひょっとしたらある？）
まあ、触り初めだし、練習がてらストアドでも作ってみる。
●サンプルテーブル準備
CREATE TABLE shouhin
( shouhin_code int
, shouhin_name nvarchar(100)
, shouhin_bunrui_code int
,CONSTRAINT "PK_shouhin" PRIMARY KEY CLUSTERED
(shouhin_code ASC)
);
●ストアド作成
CREATE PROCEDURE upsert_shouhin
@shouhin_code int = NULL
,@shouhin_name nvarchar(100) = NULL
,@shouhin_bunrui_code int = NULL
AS
DECLARE
@sqlstr NVARCHAR(max)
, @setstr NVARCHAR(max)
, @paramstr NVARCHAR(max)
;
-- ■■パラメータのチェック
IF @shouhin_code IS NULL
RETURN -1;
-- ■■UPDATEしてみる
-- ベースとなるUPDATE SQL
SET @sqlstr = '
UPDATE shouhin
{setstring}
WHERE shouhin_code = @shouhin_code
';
-- ■UPDATE文文字列を組み立てる（指定されたパラメータだけ更新する）
SET @setstr = '';
IF @shouhin_name IS NOT NULL
SET @setstr = @setstr + 'shouhin_name = @shouhin_name,';
IF @shouhin_bunrui_code IS NOT NULL
SET @setstr = @setstr + 'shouhin_bunrui_code = @shouhin_bunrui_code,';
-- ■"SET A=1,B=2"の文字列に整形し、ベースSQLにはめ込み
IF RIGHT(@setstr, 1) = ','
SET @setstr = LEFT(@setstr, LEN(@setstr) - 1);
SET @setstr = 'SET ' + RTRIM(@setstr);
SET @sqlstr = REPLACE(@sqlstr, '{setstring}', @setstr);
-- ■完成したUPDATE SQLテキストを実行する
SET @paramstr = N'
@shouhin_code int
, @shouhin_name nvarchar(100)
, @shouhin_bunrui_code int
';
EXECUTE sp_executesql @sqlstr
, @paramstr
, @shouhin_code = @shouhin_code
, @shouhin_name = @shouhin_name
, @shouhin_bunrui_code = @shouhin_bunrui_code
;
-- ■■UPDATE対象が無かった場合はINSERTする
IF @@ROWCOUNT < 1
INSERT INTO shouhin
( shouhin_code, shouhin_name, shouhin_bunrui_code)
VALUES ( @shouhin_code, @shouhin_name, @shouhin_bunrui_code);
-- ROWCOUNTでも返しとく
RETURN @@ROWCOUNT;
●実行してみる
exec upsert_shouhin 101,'鉛筆HB',1;
exec upsert_shouhin 101,'鉛筆HB １ダース',99;
exec upsert_shouhin 102,'鉛筆2B １ダース',99;
動作は一応予定通り。いきなりUPDATEしちゃうのは乱暴かなあ。でも、SELECTで確認するとSELECT+UPDATA/INSERTで常に2回SQL発行しちゃうんですよね。いきなりUPDATEだと、UPDATEにヒットしたら1回で終わります。まあ、この辺はそれぞれでしょうか。
それより問題なのは、冗長すぎるソースと、カラムの値をNULLに更新できないことですね。
汎用的にUPDATEする場面を考えたとき、必ずしも全部のカラム値を更新するとは限らないですよね。
なので、更新したくないカラムパラメータを省略可能にしたかったのですが、省略値＝NULLにしたため、呼出元がNULLで渡したのか、省略したのか判断不能になってしまいました。でも、パラメータを省略するにはデフォルト値を指定しろって書いてあるしなあ。この辺りは追々調査必要ですね。（←大体プロジェクトが終わるころに判明して、修正できずそのままになってしまうパターン）
また、省略したカラムをUPDATE SQL文から除去するため、文字列組み立てする感じになり、とても冗長に・・・カラムの多いテーブルだと長くなりそう。
でも、まあ、このレイヤなら冗長でもいいか。
