ストアドプロシージャとか作った事ないので、作ってみたかった。
MySQLバージョン 8.0.12-1debian9
MySQLのストアドルーチンは２種類存在する。
- ストアドプロシージャ
- ストアドファンクション
それぞれの違いについてはこの記事がわかりやすく解説してくれている。
はじめてのMysqlストアドプロシージャ・ストアドファンクション - 主夫ときどきプログラマ
ここではストアドプロシージャを試してみようと思う。
ストアドプロシージャ
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.15 CREATE PROCEDURE および CREATE FUNCTION 構文
-- 関数が定義されていたら削除
drop procedure if exists current_val;
-- クエリの終端文字を変更
delimiter $$
-- プロシージャを定義
create procedure current_val (in seq_name varchar(50), out current_val int)
select 10 into current_val$$
-- クエリの終端文字をデフォルトに戻す
delimiter ;
-- 呼び出す
call current_val("company", @current_val);
-- 結果を表示する
select @current_val;
-- 10
プロシージャの定義はcreate procedureで行う。プロシージャ名の後に引数を書いていくが、入力引数か出力引数をinまたはoutで表現する必要がある事に注意。
プロシージャの呼び出しはcallで行う。出力引数はユーザ定義変数として与えてあげれば、その変数に出力がセットされるらしい。なので、プロシージャの戻り値はselectを使って取得する。
ちなみにユーザー定義変数のスコープはセッションなので jdbc pool とかだと使い回されたコネクションで変数の参照ができてしまうのだろうか？
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.4 ユーザー定義変数
プロシージャ内から他のプロシージャを呼び出すには単純にcallすればいい。また、プロシージャが複数行から成るならbegin ~ endを使う。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.1 ストアドプログラムの定義
-- 関数が定義されていたら削除
drop procedure if exists current_val;
drop procedure if exists call_current_val;
-- クエリの終端文字を変更
delimiter $$
-- プロシージャを定義
create procedure current_val (in seq_name varchar(50), out current_val int)
select 10 into current_val$$
-- 他のプロシージャを呼び出す
create procedure call_current_val (in seq_name varchar(50), out current_val int)
begin
call current_val(seq_name, current_val);
set current_val = current_val + 1;
end$$
-- クエリの終端文字をデフォルトに戻す
delimiter ;
-- 呼び出す
call call_current_val("company", @current_val);
-- 結果を表示する
select @current_val;
-- 11
今まではデータベース内のテーブルを読み込んだり、書き込んだりしてこなかったが、当然プロシージャ内ではテーブルへの読み書きを行うことができる。例えばテーブル内のint値をインクリメントするプロシージャはこんな感じ。
-- 関数が定義されていたら削除
drop procedure if exists increment_sequence;
-- クエリの終端文字を変更
delimiter $$
-- プロシージャを定義
create procedure increment_sequence(in seq_name varchar(50))
begin
update sequence set current_val = current_val + 1 where name = seq_name;
end$$
-- クエリの終端文字をデフォルトに戻す
delimiter ;
-- 呼び出す
call increment_sequence("customer_company");
-- 結果を表示する
select * from sequence;
-- 1回め 0
-- 2回め 1
-- 3回め 2
これらの機能を使えば、MySQLで擬似的にシーケンスを表現できる。
-- 関数が定義されていたら削除
drop procedure if exists increment_sequence;
drop procedure if exists current_val;
drop procedure if exists next_val;
-- クエリの終端文字を変更
delimiter $$
-- シーケンス値を更新
create procedure increment_sequence(in seq_name varchar(50))
begin
update sequence set current_val = current_val + 1 where name = seq_name;
end$$
-- 現在のシーケンス値を取得
create procedure current_val(in seq_name varchar(50), out seq_val int)
begin
select current_val into seq_val from sequence where name = seq_name;
end$$
-- シーケンス値を更新し最新の値を取得する
create procedure next_val(in seq_name varchar(50), out seq_val int)
begin
call increment_sequence(seq_name);
call current_val(seq_name, seq_val);
end$$
-- クエリの終端文字をデフォルトに戻す
delimiter ;
-- 呼び出す
call next_val("customer_company", @seq_val);
-- 結果を表示する
select * from sequence;
select @seq_val;