【MySQL】テーブルの動的データを活用するストアド関数の作成方法

MySQLストアド関数とテーブル参照の基本ルール

MySQLのストアド関数はテーブルを参照することができますが、結果セットを返すステートメントを使用することはできません。つまり、通常のSELECTクエリのように結果一覧を出力する処理は関数内に記述できないという制限があります。

この制限を回避するために活用できるのが「SELECT INTO」構文です。SELECT INTOを使えば、テーブルから取得した値を変数へ格納でき、その後関数内で自由に計算や加工を行うことができます。

サンプルデータの準備

ここでは、学生ごとの各教科の点数を管理する「Student_marks」テーブルを使用します。以下のようなレコードが格納されているものとします。

mysql> Select * from Student_marks;
+-------+------+---------+---------+---------+
| Name  | Math | English | Science | History |
+-------+------+---------+---------+---------+
| Raman |   95 |      89 |      85 |      81 |
| Rahul |   90 |      87 |      86 |      81 |
+-------+------+---------+---------+---------+
2 rows in set (0.00 sec)

Avg_marks関数の作成手順

続いて、生徒名を引数として受け取り、4教科（数学・英語・理科・歴史）の平均点を計算して返す「Avg_marks」というストアド関数を作成します。関数本体ではSELECT INTOにより各教科の点数を変数に取り込み、その合計を4で割って平均値を求めています。

mysql> DELIMITER //
mysql> Create Function Avg_marks(S_name Varchar(50))
    -> RETURNS INT
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE M1,M2,M3,M4,avg INT;
    -> SELECT Math,English,Science,History INTO M1,M2,M3,M4 FROM Student_marks WHERE Name = S_name;
    -> SET avg = (M1+M2+M3+M4)/4;
    -> RETURN avg;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

コーディング時のポイントは以下の通りです。

• RETURNS INT： 関数が整数型の値を返すことを宣言しています。
• DETERMINISTIC： 同じ入力に対して常に同じ結果を返すことを示すキーワードで、バイナリログ環境などでは指定が推奨されます。
• DELIMITER //： 複数行にわたるSQLを登録する際、区切り文字を一時的に変更してエラーを防ぎます。定義完了後に元の区切り文字へ戻すのを忘れないようにしましょう。

作成した関数の実行例

それでは、作成した関数を呼び出して、各生徒の平均点を計算してみます。

mysql> Select Avg_marks('Raman') AS 'Raman_Marks';
+-------------+
| Raman_Marks |
+-------------+
|          88 |
+-------------+
1 row in set (0.07 sec)

mysql> Select Avg_marks('Rahul') AS 'Rahul_Marks';
+-------------+
| Rahul_Marks |
+-------------+
|          86 |
+-------------+
1 row in set (0.00 sec)

Ramanの場合は(95+89+85+81)÷4=87.5がINT型として88に丸められ、Rahulの場合は(90+87+86+81)÷4=86が正しく返されています。

まとめ

MySQLのストアド関数内では、結果セットを返すSELECT文を直接使用できませんが、SELECT INTO構文を組み合わせれば、テーブルの動的データを読み込んで柔軟な計算処理を実現できます。集計処理などを頻繁に行う場合、こうしたストアド関数を活用することでSQLの再利用性と可読性を高めることができます。