MySQL8.0でストアドプロシージャとストアドファンクションを理解する

背景

データベースの勉強で「MySQL徹底入門 第4版 MySQL 8.0対応」を読んでいるのですが，ストアドプロシージャというのが出てきて，「何それおいしいのぉ〜」状態になってしまい，本の中であまり言及されてなかったので，公式ドキュメントを見ながら自分でまとめてみました．

MySQL徹底入門 第4版 MySQL 8.0対応

まだ20%くらいしか読めてないですが，知らなかったTipsなども結構あるのでオススメの本です．

最初に以下の2記事を読んで，わかりやすかったので参考になるかもしれません．

ストアドプロシージャの基本的ななにか - Qiita

初心者からのMySQLストアドプロシージャ＆ファンクション入門 | サービス | プロエンジニア

※この記事ではMySQL8.0(Docker, tag:8.0)で実行しています

ストアドルーチン

ストアドプロシージャとストアドファンクションをまとめて，ストアドルーチンと呼びます．

じゃあストアドルーチンってなによ？って聞かれると，記述された一連の処理を行うものです．後の例をみた方がわかりやすいので，ここではのほほ〜んて感じでいいでしょう．

MySQL :: MySQL 8.0 Reference Manual :: 24.2.1 Stored Routine Syntax

ストアドプロシージャとストアドファンクションの違い

先ほど書いたとおり，ストアドプロシージャとストアドファンクションはどちらも記述された一連の処理を行うものですが，ストアドファンクションは実行結果として戻り値を返しますが，一方でストアドプロシージャは戻り値を返さないという違いがあります．

公式ではないですが，この辺りが参考になります．

ストアドプロシージャを実行してみる

MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

公式ドキュメントにサンプルSQLがあるのでこれを参考にやっていきます．今回は最新の8.0で参考にしています．英語版しかないのでGoogle翻訳して読みましたが，5.6だと日本語版があります(チョット内容違うけど)．

まず，一番上のSQL構文を見ます．が，何をやっているのか良くわからない．．．

というわけで2つ目のサンプルSQLが書かれているのを見てみます．が，そのまま実行してもデータベースとテーブルを作成していないので動きません．​

というわけでサンプルSQLとは少し違う感じになりますが動かすためにidだけのテーブルを準備します．

-- データベース作成
CREATE DATABASE sample;

-- テーブル作成
CREATE TABLE sample.mytable (
   id INTEGER PRIMARY KEY
);

-- レコードを入れる
INSERT INTO sample.mytable VALUES
   (1),
   (2),
   (3),
   (4),
   (5);

-- 確認
SELECT * FROM sample.mytable;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

準備ができたのでストアドプロシージャを作成していきたいのですが，その前に利用データベースを変更します．これをしておかないとストアドプロシージャでエラーになってしまいました．

-- 利用データベース変更
use sample

いよいよストアドプロシージャを作成します．

delimiter //

CREATE PROCEDURE sampleprocedure (IN num INT, OUT count_id INT, OUT num_increment INT)
   BEGIN
       SELECT COUNT(*) INTO count_id  FROM sample.mytable;
       SET num_increment = num + 1;
   END//

delimiter ;

delimiter // で，終端文字を変更します．mysqlでは ; が終端文字です．BEGIN~ENDで ; を使いたいのですが，delimiter // せずそのままだと，そこで区切られてしまいます．
なので，一時的に終端文字を ; から // に変更し， END// で終わるようにしています．最後に delimiter ; で終端文字を ; に戻します．

sampleprocedure はストアドプロシージャを呼び出す時の名前です．

(IN num INT, OUT count_id INT, OUT num_increment INT) のINは呼び出す時に必要なパラメータで，OUTは取得できる値になります(戻り値みたいな感じなのですがあえて戻り値とは言わないようにしています．詳細は後ほど)．

BEGIN~END では実行される処理を記述しています．
SELECT COUNT(*) INTO count_id FROM sample.mytable; ではmytableのレコード数をカウントしてcount_idとしています．
SET num_increment = num + 1; ではnumに1を足した値をnum_incrementとしてます(特にDB関係ないですが)．

ようやくストアドプロシージャを実行してみます．実行はCALLを使います

CALL sampleprocedure(10, @count_id, @num_increment);

MySQL :: MySQL 8.0 Reference Manual :: 13.2.1 CALL Statement

@count_id, @num_incrementは返ってくる値をセットするための変数です．(以下の記事が参考になるかも)

MySQL :: MySQL 8.0 Reference Manual :: 9.4 User-Defined Variables

返ってきた値を見てみます．@count_idにはmytableのレコード数の5，@num_incrementには10 + 1 = 11が入っているのでうまくいけてそうです．

SELECT @count_id, @num_increment;
+-----------+----------------+
| @count_id | @num_increment |
+-----------+----------------+
|         5 |             11 |
+-----------+----------------+​

ここで，(IN num INT, OUT count_id INT, OUT num_increment INT) のところであった疑問についてです．
「@count_id, @num_incrementって戻り値じゃん！ストアドプロシージャなのに戻り値あるじゃん！」と思った方はいないでしょうか？ぼくは思いました．
冒頭で記載した「ストアドプロシージャは戻り値を返さない」というのは，CALLでストアドプロシージャを実行した時に明示的にRETURNをしない(BEGIN~ENDにSELECT文などがあればコンソールに表示されるが，それは返り値ではない)ということのようです．
なので@count_id, @num_incrementのように，ストアドプロシージャは変数の中にパラメータをセットして返すことは実行結果として表示されないことと別物で，こちらは可能です．

理屈はわかったけど，なんか納得いかない．．．．．

ストアドプロシージャの削除

ストアドプロシージャの削除はdropで指定するだけです．

DROP PROCEDURE sampleprocedure;

ストアドファンクションを実行してみる

次にストアドファンクションをやっていきます．公式ドキュメントはストアドプロシージャと同じやつの下方にあります．

(遊び心を入れつつ)ストアドファンクションを作成します．

CREATE FUNCTION breath (breath_name VARCHAR(10), type_number VARCHAR(10), tech_name VARCHAR(10))
RETURNS VARCHAR(50) DETERMINISTIC
   RETURN CONCAT(breath_name, 'の呼吸 ' , type_number, 'の型 ', tech_name);   

今回は ; を使わないので delimiter は使用していません．

breathは呼び出すときの名前です．
breath_name VARCHAR(10), type_number VARCHAR(10), tech_name VARCHAR(10) はストアドファンクションを実行する時に必要なパラメータです．
RETURNS VARCHAR(50) で VARCHAR型の文字列を返すことを明示します．

DETERMINISTIC は記載しておかないとエラーになります．役割を調べたのですがイマイチ良くわからず，，，ストアドファンクションでは同じ引数でも違う結果を返す場合があるらしく？同じ結果を返すようにするようでした．

RETURN CONCAT(breath_name, 'の呼吸 ' , type_number, 'の型 ', tech_name); で返り値を生成します．
CONCATは文字列を連結する関数です．

MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators

ストアドファンクションの実行はSQL文中でできるので，selectで実行してみます．

SELECT breath('炎', '壱', '不知火');
+-----------------------------------+
| breath('炎', '壱', '不知火')      |
+-----------------------------------+
| 炎の呼吸 壱の型 不知火            |
+-----------------------------------+​

無事に鬼滅の映画見てないですが炎の呼吸が使えました．

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

ストアドファンクションの削除はdropで指定するだけです．

DROP FUNCTION breath;

(ストアドプロシージャとストアドファンクションってどういうときに使えばいいか，知らんけど)