優柔不断なプログラマの日記

データベーステーブルへデータを登録する際、同じキーだったら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文から除去するため、文字列組み立てする感じになり、とても冗長に・・・カラムの多いテーブルだと長くなりそう。

でも、まあ、このレイヤなら冗長でもいいか。