【SQL Server】ストアドプロシージャを作成するための基礎知識

SQL Server snippet

前書き

私はSQLを書くこと自体は別に嫌いじゃないんですが、ストアドを作ったり使ったりするのは大嫌いです。理由は性にあったIDEを知らないからです。逆に言うとそれだけです。

とは言え、やむにやまれぬ事情で何らかの関数を自作しないとやってられないことが多々あります。「嫌いだから作りません」じゃ話にならないので、SQL Serverでストアドプロシージャを作ることがあるんですが、いつも通りドキュメントがうんこすぎて毎回「これどーやって書くんだっけ？」とネットの海を彷徨うことになります。

いい加減嫌になったので、よく使う基本文法的なあれとかtipsみたいなあれを個人的にメモしておきます。

CREATE文を作る

大体こんな感じのテンプレがあればOKです。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    -- やりたい処理
END

ファンクションならこんな感じ。

CREATE FUNCTION [dbo].[TEST_FUNC]
RETURNS INT -- ASの前に「RETURNS [返す型]」と書く
AS
BEGIN
    -- やりたい処理

    --返すときは普通にRETURNを書く
    RETURN 0;
END

ブロックと言う概念はあらゆるものがBEGIN-ENDで表現されます。IFでも、WHILEでも。FOR？そんなものはないよ。まるで石器時代ですね。

引数を設定する

すっごいわかりにくいけどCREATE PROCEDURE (Transact-SQL)ってところに書いてあります。

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    -- ここに引数を設定する
    @i_Hoge VARCHAR(max)
    , @i_Piyo INT
)
AS
BEGIN
    -- やりたい処理
END

引数にデフォルト値を設定したい場合はこんな感じ。

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    -- ここに引数を設定する
    @i_Hoge VARCHAR(max)
    , @i_Piyo INT = 0
)
AS
BEGIN
    -- やりたい処理
END

アウトプットパラメータも当然指定できますが、ファンクションだと無理です。（参考：OUTPUT パラメータを使用してデータを返す処理）

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    -- ここに引数を設定する
    @i_Hoge VARCHAR(max)
    , @i_Piyo INT = 0
    , @o_Fuga DATETIME OUTPUT
)
AS
BEGIN
    -- やりたい処理
END

変数

DECLAREステートメントを使って変数宣言します。（参考：Transact-SQL 変数）

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    DECLARE @foo VARCHAR(max);
END

SETステートメントで値をセットすることが出来ます。また、SELECTでもセットすることが可能です。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    DECLARE @foo VARCHAR(max);
    SET @foo = 'fooooooo';
    
    -- @fooに[FOOBAR].[BAR]の値をセットする
    -- [FOOBAR].[BAR]の値がヒットしない場合は@fooはNULLになる
    SELECT
     @foo = BAR
    FROM
     FOOBAR
    WHERE
     HOGE_PIYO = 'Fuga'
    
    -- 別にSELECTだからと言って本当にSELECT文を書く必要はない
    SELECT @foo = 'fuuuuoooo'
END

宣言した時点ではNULLなので、何らかの初期化を行わないと演算子による結合etcの結果も全部NULLになります。

引数と同じように初期値を与えられるので、忘れないようにしましょう。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    /* ちなみに
     * DECLARE @foo VARCHAR(max), @bar VARCHAR(max);
     * でも宣言できる
     */
    DECLARE @foo VARCHAR(max);
    DECLARE @bar VARCHAR(max);
    
    -- @fooがNULLなので@barにはNULLがセットされる
    SET @bar = @foo + 'BAR';
    
    DECLARE @foobar VARCHAR(max) = 'FOOBAR';
    -- @foobarは初期化されているので@barには「FOOBARBAR」と言う文字列がセットされる
    SET @bar = @foobar + 'BAR';
END

IF文

IFはあくまでステートメントなので、一行しか書けません。なので、複数の処理を行う場合はBEGIN-ENDでくくってあげる必要があります。

ELSEもまたステートメントです。大体以下のような形になることがほとんどでしょう。

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    @i_Piyo INT = 0
)
AS
BEGIN
    IF @i_Piyo = 0
    BEGIN
        -- True Part
    END
    ELSE
    BEGIN
        -- False Part
    END
END

ループ処理（WHILE）

FORとかDOとかそう言うものはないので、ループは全部WHILEでどうにかしなくてはなりません。

当然WHILEもステートメントです。BEGIN-ENDでくくります。

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    @i_Hoge VARCHAR(max)
)
AS
BEGIN
    -- 擬似FOR文
    DECLARE @i BIGINT = 0;
    WHILE @i < LEN(@i_Hoge)
    BEGIN
        -- やりたい処理
        @i = @i + 1
    END
END

BREAKやCONTINUEと言ったステートメントも用意されています。

CREATE PROCEDURE [dbo].[TEST_PROC]
(
    @i_Hoge VARCHAR(max)
    , @o_Fuga DATETIME OUTPUT
)
AS
BEGIN
    
    /* 無限ループを作ってBREAKとCONTINUEだけで制御することも出来る
     * （Do Whileの再現などに便利）
     */
    WHILE 0 = 0
    BEGIN
        -- やりたい処理
        
        IF @o_Fuga IS NOT NULL
        BEGIN
            BREAK;
        END
        ELSE
        BEGIN
            CONTINUE;
        END
    END
END

エラーハンドリング

何故かTRY-CATCHはちゃんとあります。それよりもっと実装すべき構文がある気がしてなりません。

これはステートメントではなくブロックなので、今までの制御フロー構文とは若干違います。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    BEGIN TRY
        -- UPDATEしたりとかいろいろ
    END TRY
    BEGIN CATCH
        -- ROLLBACKしたりとかいろいろ
    END CATCH
END

こんな書き方が出来るんならIFやWHILEもそうしろよと思わなくもないですね。

ちなみにBEGIN TRY〜END TRYのみで使うことは出来ません。必ずCATCHも書く必要があります。

また、TRY-CATCHでトラップできるエラーには若干の制限があります。ちょっと引用しておきます。

TRY…CATCH 構造では、次の条件はトラップされません。

  • 重大度が 10 以下の警告または情報メッセージ。
  • 重大度が 20 以上で、そのセッションの SQL Server データベース エンジン タスク処理を終了させるエラー。 重大度が 20 以上のエラーが発生し、データベース接続が切断されない場合、TRY…CATCH によってエラーが処理されます。
  • クライアントの割り込み要求や中断されたクライアント接続などのアテンション。
  • システム管理者による KILL ステートメントを使用したセッションの終了。

次の種類のエラーは、TRY…CATCH 構造と同じ実行レベルで発生した場合には、CATCH ブロックによって処理されません。

まぁ要するに実行時エラーじゃないとダメだよ、って話ですね。そりゃそうだって感じですが。

重大度に関してはデータベース エンジン エラーの重大度と言うドキュメントを読んでください。ええ、仰りたいことはわかります。「結局10以下のエラーって具体的にどんなの？」とかあると思います。ええ。ええ。そうですね。いい加減にして欲しいですね。（完）

（2014/11/05追記：このドキュメントに各エラーメッセージと重大度が記載されています。）

また、エラー時の情報はシステム関数で取得することが出来ます。こちらも引用しておきます。

CATCH ブロックのスコープ内では、次のシステム関数を使用して、CATCH ブロックが実行される原因となったエラーに関する情報を取得できます。

  • ERROR_NUMBER() は、エラーの番号を返します。
  • ERROR_SEVERITY() は、重大度を返します。
  • ERROR_STATE() は、エラー状態番号を返します。
  • ERROR_PROCEDURE() は、エラーが発生したストアド プロシージャまたはトリガーの名前を返します。
  • ERROR_LINE() は、エラーを発生させたルーチン内の行番号を返します。
  • ERROR_MESSAGE() は、エラー メッセージのテキストの全文を返します。 このテキストには、長さ、オブジェクト名、時間など、代替可能なパラメーターに対して提供された値が含まれます。

CATCH ブロックのスコープの外側から呼び出されると、これらの関数は NULL を返します。 エラー情報は、CATCH ブロックのスコープ内のどこからでも、これらの関数を使用して取得できます。

例外の伝播（THROW / RAISERROR）

なんとSQL Server 2012からはTHROWステートメントも用意されました。いやだからFORとかさ…そう言うのをさ…ね？

それまではRAISERRORと言う関数ライクなものを使ってました。THROWステートメントの方が圧倒的に使いやすいので、今更使う必要はないです。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    BEGIN TRY
        -- UPDATEしたりとかいろいろ
    END TRY
    BEGIN CATCH
        -- ROLLBACKしたりとかいろいろ
        
        -- エラーの伝播
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        -- 以下の2文は全く同じ
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        THROW;
    END CATCH
END

もっと他にも色々できますが、まぁドキュメント読んでください。

また、エラー処理全般はデータベース エンジン エラーの処理と言うドキュメントが詳しいです。(2008 R2のものなのでTHROWはないけど）

厳格なエラーハンドリングが要求される場合はすべて一読しておいたほうがいいです。特に今回説明してない@@ERRORやPRINTなんかはデバッグ時に重宝します。

カーソルのフェッチ

まずはカーソルを宣言する必要があります。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    -- カーソルで使用するSELECTを記述する
    DECLARE hogeCursor CURSOR FOR
        SELECT
            PIYO
        FROM
            FUGA;
    
    -- カーソルを開く
    OPEN hogeCursor;
    
    -- TODO:フェッチしてなんかする
    
    -- カーソルを閉じる
    CLOSE hogeCursor;
    -- メモリ解放
    DEALLOCATE hogeCursor;
END

ちゃんと後片付けまでしないといけません。石器時代って感じですね。（二回目）まぁストアドプロシージャ自体が投石器みたいなもんですからね。（暴言）

じゃあ実際にフェッチしてみましょう。@@FETCH_STATUSを使うことで全件取得できます。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    DECLARE @piyo VARCHAR(max);
    
    -- カーソルで使用するSELECTを記述する
    DECLARE hogeCursor CURSOR FOR
        SELECT
            PIYO
        FROM
            FUGA;
    
    -- カーソルを開く
    OPEN hogeCursor;
    
    -- フェッチする
    FETCH NEXT FROM hogeCursor
    INTO @piyo; -- INTOで変数にセットできる
    
    -- 何らかのデータをフェッチできたら@@FETCH_STATUSが0になる
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- フェッチした結果（@piyo）で何かする
        
        -- 次の結果をフェッチする
        FETCH NEXT FROM hogeCursor
        INTO @piyo;
    END
    
    -- カーソルを閉じる
    CLOSE hogeCursor;
    -- メモリ解放
    DEALLOCATE hogeCursor;
END

@@FETCH_STATUSの返り値はこの辺に書いてありました。まぁ、あんまりこれを使って判定することはないと思いますが。

テーブル変数

OracleでもりもりPL/SQLを書いてた人だとRECORD型のようなものが欲しくなると思うんですが、SQL Serverにはないです。

強いて言うならTABLE型がそれに近いです。擬似的なテーブルを生み出し、それに対してSELECT/DELETE/INSERT/UPDATEすることが出来ます。

と言うか、ほんとにただのテーブルのように扱うので、カーソルでフェッチしたりとかも出来ます。

CREATE PROCEDURE [dbo].[TEST_PROC]
AS
BEGIN
    -- 変数宣言
    DECLARE @t_Hoge TABLE
    (
        PIYO INT NOT NULL
        , HUGA VARCHAR(max)
    );
    
    -- INSERTすればOracleのRECORD型のように使えなくもない
    INSERT INTO @t_Hoge VALUES(0, 'hugahuga');
    
    -- 勿論SELECTしてもOK
    DECLARE @piyo INT;
       SELECT
           @piyo = PIYO
       FROM
           @t_Hoge;
     
    -- For Eachライクなことがしたければカーソルを作ればOK
    DECLARE hogeCursor CURSOR FOR
        SELECT
            PIYO
        FROM
            @t_Hoge;
    
END

ちなみにこれ、返り値としても使えます。

CREATE FUNCTION [dbo].[TEST_FUNC]
RETURNS TABLE
AS
BEGIN
    DECLARE @t_Hoge TABLE
    (
        PIYO INT NOT NULL
        , HUGA VARCHAR(max)
    );
    
    INSERT INTO @t_Hoge VALUES(0, 'hugahuga');
    INSERT INTO @t_Hoge VALUES(1, 'piyopiyo');
    
    RETURN @t_Hoge
END

実際に呼び出すときはこんな感じ。

DECLARE @t_Hoge TABLE;

SET @t_Hoge = [dbo].[TEST_FUNC]();

SELECT
    PIYO
FROM
    @t_Hoge;

ちなみにさっきの関数はこんな風にも書ける。SQLだいすきマンならともかく、ストアドのコードを読もう！ってメンタルで臨んでいると凄いびっくりするので出来ればやめてほしい。

CREATE FUNCTION [dbo].[TEST_FUNC]
RETURNS TABLE
AS
-- 要はRETURNだけ書いてる
RETURN
    SELECT 0 PIYO, 'hugahuga' HUGA
    UNION ALL
    SELECT 1 PIYO, 'piyopiyo' HUGA;

まとめ

とまぁ、つらつらと書いていきました。思った以上に長くなってしまったので文字数制限にひっかからないことを祈るのみです。