MySQLでストアドプロシージャ戻り値対応版であるストアドファンクションの設定と利用方法

先日MySQLのストアドプロシージャに関するメモを書いたが、今回はストアドファンクションについても色々試してみたい。ざっと調べたところ基本的には一連の処理を登録しておくと関数みたいに使える、とストアドプロシージャと同じっぽいが、ストアドファンクションは戻り値があるみたい。以下に設定を利用方法をメモ。

リファレンス

リファレンスについては以下を参照。

https://dev.mysql.com/doc/refman/8.0/ja/create-procedure.html

事前準備

以下のようなテーブル及びデータを用意した。

mysql> SHOW COLUMNS FROM `test_table`;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| score      | int          | NO   |     | NULL    |                |
| regist_ymd | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> SELECT * FROM `test_table`;
+----+-----------+-------+---------------------+
| id | name      | score | regist_ymd          |
+----+-----------+-------+---------------------+
|  1 | 鈴木      |    80 | 2024-02-15 18:13:49 |
|  2 | 佐々木    |    70 | 2024-02-15 18:20:11 |
|  3 | 山田      |    50 | 2024-02-21 12:47:20 |
+----+-----------+-------+---------------------+

設定方法

ストアドファンクションの追加

DELIMITER //
CREATE FUNCTION test_function() RETURNS DOUBLE DETERMINISTIC
BEGIN 
    DECLARE avg_score DOUBLE;
    SELECT AVG(`score`) INTO avg_score FROM `test_table`;
    RETURN avg_score;
END
//
DELIMITER ;

全score(点数)の平均値を返すという内容になる。

ストアドファンクションの確認

正常に設定されているかは以下で確認できる。

SHOW CREATE FUNCTION test_function;

ストアドファンクションの実行

mysql> SELECT test_function();
+-----------------+
| test_function() |
+-----------------+
|    66.666666666 |
+-----------------+

ストアドファンクションの削除

DROP FUNCTION test_function;