UPSERTストアドサンプル

環境

• DBMS = SQL Server 2005ぐらい

UPSERTとは

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

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

Tweet