ストアドプロシージャ、ストアドファンクションについて

背景

実業務でRailsからDBを叩くときに簡単な判定をストアドファンクションでまとめようという話がでた。
簡単に要点をまとめておきます。

ストアドプロシージャ、ストアドファンクション

1つの要求で複数のSQLを実行出来る機能です。

• ストアドプロシージャ
  • 戻り値がない

• ストアドファンクション
  • 戻り値がある

/sample.sql

DELIMITER //
DROP FUNCTION IF EXISTS is_sample;
CREATE FUNCTION is_sample(val INT) RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE T TINYINT;
  DECLARE F TINYINT;
  SET @T = 1;
  SET @F = 0;

  IF val IN(1, 2, 3) THEN
    RETURN @F;
  ELSEIF val BETWEEN 10 AND 20 THEN
    RETURN @T;
  ELSE
    RETURN @F;
  END IF;
END;
//
DELIMITER ;

DELIMITER //

• クエリ内で「;」を使用するためデリミタを最初に変更してあげる。

DROP FUNCTION IF EXISTS is_sample;

• もし同じ名前でDB内に登録されていたらドロップして再登録

CREATE FUNCTION is_sample(val INT) RETURNS TINYINT DETERMINISTIC

• ファンクション名と引数、引数の型、戻り値の方を定義する

　レプリケーションを行っていてかつ、ファンクション内で動的に処理を行う場合は
　NOT DETERMINISTIC
　レプリケーションを行っていてかつ、同じ引数を与えると毎回同じ値が返ってくるような処理の場合は
　DETERMINISTIC　（今回は数値の判定のみなのでこちら）

DELIMITER ;

• デリミタをもとに戻す

判定について

普通にif,else ifが使用できます。条件のあとに処理を書きたい場合はTHENを記述します。

bool型について

数値の値に応じてTRUE,FALSEを返すストアドを作りたかったんですが、どうやらbool型を返すことができないようなので
TINYINT 型を使用しています。
まぁ実際プログラムでもTRUE,FALSEは1,0で表現されているので問題なさげです。