MariaDB(MySQL) でストアドファンクションでローカル変数を使う

環境

• Ubuntu 20.04
• MariaDB Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

背景

会員のアクセス記録から、最後のアクセスが今年中だったかどうかを知りたい。

アクセスした一覧を得る目的であれば、普通のSELECT文でもできる。
しかしながらこの条件だけではなく、複数の複雑な条件を AND / OR で論理演算した結果を個人ステータスに表示したい。

なので条件ごとに、True/False で値を返すストアドファンクションを作っておき、それを組み合わせることで実現したい。

特定の日付にアクセスしているかどうかについては、今年中にアクセスしていれば 1 , アクセスしていないと 0 を返すものを作ろうとした。

 SELECT アクセス年 FROM アクセス記録 WHERE 会員番号=_会員番号 ORDER BY アクセス年 DESC LIMIT 1;

とし、得られたアクセス年をローカル変数 _アクセス年 に入れ、

 IF _アクセス年 = 2021 THEN RETURN 1;
   ELSE    RETURN 0;
 END IF;

とするにはどうしたらいいかな？

SELECT ... INTO 構文

MariaDB/MySQL の INTO 構文は 3 つの用法がある。
https://qiita.com/drafts/601832b2da00b451d55d

• SELECT ... INTO var_list
• SELECT ... INTO OUTFILE
• SELECT ... INTO DUMPFILE

この中の、

• SELECT ... INTO var_list

で、SELECT 結果を変数に入れられるそうだ。

ストアドファンクションを作る

DELIMITER //
 CREATE FUNCTION IS今年のアクセス(_会員番号 TEXT) RETURNS INT NOT DETERMINISTIC
 BEGIN
 DECLARE _アクセス年 INT ;
 SELECT アクセス年 INTO _アクセス年 FROM アクセス記録 WHERE 会員番号=_会員番号;
 IF _アクセス年 = 2021 THEN    RETURN 1;
   ELSE    RETURN 0;
 END IF;
 END//
DELIMITER ;

実行

SELECT IS今年のアクセス("123-4567");
+---------------------------------+
| IS今年のアクセス("123-4567")    |
+---------------------------------+
|                              1 |
+---------------------------------+
1 row in set (0.009 sec)

注意

• ユーザ定義変数は @ をつけるが、ストアドファンクションで DECLARE で宣言するのはローカル変数なので @ をつけなくてもいいようだ。

• ユーザ変数を使った場合は、セッションを意識しないといけないのでユーザ定義変数にしないほうが良さそう。

cf., 「MySQLの SELECT … INTO 構文で結果を変数に書き込むときの注意 | while(isプログラマ)」
https://am-yu.net/2018/09/06/mysql_select_into_storedprocedure/

• ローカル変数はテーブルカラム名と同じだといけない。当然かな。
  cf.,https://dev.mysql.com/doc/refman/5.6/ja/local-variable-scope.html

• クエリーは、単一行を返すようにしないといけない。
  cf.,https://dev.mysql.com/doc/refman/5.6/ja/select-into.html