目次
- ストアドルーチンとは
- ストアドプロシージャ
- ストアドファンクション（ユーザ定義関数）
- HeidiSQLでの登録済のストアドルーチン確認方法
- ストアドルーチン・・ごく基本的な構文
- ストアドルーチン以外で複合ステートメントだけ使う
ストアドルーチンとは
ひと塊のSQLに名前をつけてサーバ上に格納しておいて呼び出して使います。
単体で発行するSQLでは使用できない条件分岐やループの処理、変数定義などの処理が使えるので、プログラム言語のように自由度の高い処理を記述することができます。
ストアドルーチンには、ストアドプロシージャとストアドファンクション（ユーザ定義関数）があります。
ストアドプロシージャとストアドファンクションはほぼ同じ動作をします。
異なる点は。
- プロシージャには戻り値がないが、ファンクションには戻り値がある
- プロシージャは再帰的に呼び出して使用可能だが、ファンクションはできない
などです。
ただし後者はMySQLのみの特徴で、バージョンがあがればなくなるかもしれません。
ストアドルーチンを定義しておくことで、ネットワーク負荷を軽減したり、新規にクエリを発行するよりも実行が速いというメリットもあります。
ストアドプロシージャ
戻り値はない（RETURNできない）のですが、処理結果を返すことはできます。
実際に例を作ってやってみます。
サンプルとして、年齢計算をするルーチンを作ってみます。
考え方は以下の記事を参考にして「前日に1日加算する・・法律通りのやり方」を採用しました。
まず、ソースです。
DBがUTF-8なので、ファイルもUTF-8で保存するようにします。
ストアドプロシージャのサンプルソース
DROP PROCEDURE if exists pr_calc_age;
DELIMITER //
CREATE PROCEDURE pr_calc_age(
IN date_birth date,
IN date_base date,
OUT out_result int(11))
BEGIN
DECLARE _date_calc date;
SET _date_calc = DATE_ADD(date_base, INTERVAL 1 day);
SET out_result = timestampdiff(YEAR,date_birth,_date_calc);
END
//
DELIMITER ;
簡単に捕捉します。
DELIMITER //
これは、記述中に「;」を使うので、通常のSQLのデリミタをいったん「//」に変えてバッティングしないようにしています。
なので、処理が終わったら「DELIMITER ;」でデリミタを元に戻します。
プロシージャ名（pr_calc_age）を定義して、引数を定義します。
そこで「IN」となっているのは「入力値」、OUTとなっているのは「出力値」です。
ほかに「INOUT」として入力値を上書きする引数を定義することもできます。
BEGINーENDで囲んだ部分が処理本体で、この中で「誕生日（date_birth）」と「年齢計算基準日（date_base）を受け取り、基準日に1日加算した結果用の変数（_date_calc date）を使って、時点の年単位の差分を求めて、出力値（out_result）にセットします。
ストアドプロシージャをHeidiSQLでストアする
これを書いて、ファイル名をつけて保存して、ストア（登録）します。
HeidiSQLを使って「SQLファイルを実行」で保存したファイルを指定すればいいです。
実行時に「UTF-8」を指定するのを忘れずに。
ストアできたら使ってみます。
ストアドプロシージャのサンプルを実行する
HeidiSQLのクエリウインドウで、以下を実行します。
CALL pr_calc_age('1958-01-18','2022-01-17',@ret);
SELECT @ret;
1958年1月18日生まれの人が、2022年1月17日に何歳になるかが「@ret」に格納されるので、それをSELECTで表示してます。
実行結果は「64」・・あってますね。
ストアドファンクション（ユーザ定義関数）
同じ年齢計算を、ストアドファンクションでやってみます。
ほぼほぼストアドプロシージャと同じ感じでいけます。
違いは。
- PROCEDUREを「FUNCTION」にする
- 引数の「IN」「OUT」の指定はしない。
- 戻り値（RETURNS）の型の定義が追加になる
- 処理中で「return」することができる
です。
ストアドファンクションのサンプル
まずはソースです。
DROP FUNCTION if exists pr_calc_age;
DELIMITER //
CREATE FUNCTION fc_calc_age(
date_birth date,
date_base date) RETURNS int(11)
BEGIN
DECLARE _date_calc date;
SET _date_calc = DATE_ADD(date_base, INTERVAL 1 day);
return timestampdiff(YEAR,date_birth,_date_calc);
END
//
DELIMITER ;
ストアドファンクションのストアと実行
ファイル名をつけて保存し、ストア（登録）する方法は同じです。
使い方は、timestampdiffなどのSQL内で使える関数と同じです。
例えば、こんな感じにHeidiSQLのクエリに入力して実行すると。
select fc_calc_age('1958-01-18','2022-01-17') AS age;
結果は。
みたいになります。
HeidiSQLでの登録済のストアドルーチン確認方法
登録済のストアドプロシージャを確認するには
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
これを実行すると
みたいに出力されます。
同じように。
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';
とすると、ストアドファンクションがリストされます。
定義を確認したいときは、HeidiSQLのクエリウインドウで以下を実行します。
プロシージャ名（pr_calc_age）は適宜変更してください。
SHOW CREATE PROCEDURE pr_calc_age;
表示される結果の「Create Procedure」カラムで定義を確認できます。
「...」の部分をクリックすると、エディタで表示できます。
これもファンクションの場合はこうなります。
SHOW CREATE FUNCTION fc_calc_age;
ファンクション名（fc_calc_age）の部分は適宜変更して使ってください。
ストアドルーチン・・ごく基本的な構文
ストアドルーチンは「BEGIN ー END」の間に処理を記述します。
そこでとりあえず使ってみるために必要な主要構文を整理します。
ローカル変数
ローカル変数は「DECLARE」で定義します。
例えば。
DECLARE name INT DEFAULT 0;
DECLARE name VARCHAR(256) ;'
DECLARE name FLOAT(3,2);;
みたいな感じで、DECLAREの後ろに変数名・型（MariaDBの型）と必要なら、DEFAULTでデフォルト値を設定します。
ローカル変数のスコープは、宣言されている BEGIN ... END ブロックの中だけです。
変数に値を代入するには「SET」を使います。
SET name = value;
条件分岐
IF文とCASE文は、なじみがあるので形だけ書いておきます。
IF文。
IF ・・・ THEN ・・・
ELSEIF ・・・ THEN ・・・.
ELSE ・・・
END IF
CASE文。
CASE [変数]
WHEN [値] THEN ・・・
WHEN [値] THEN ・・・
ELSE ・・・
END CASE
ループ（繰り返し）
LOOPとREPEATとWHILEがあります。
繰り返しの内部で「ループを継続する＝ITERATE」と「ループを抜ける＝LEAVE」が使えます。
LOOPとREPEATとWHILEにはラベルを付けることができるので、ループが複数あっても「ITERATE ラベル」みたいにラベルで指定することができます。
LOOPは終了条件がないので、ITERATEとLEAVEで継続するか抜けるかを制御します。
例：
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
REPEATは終了条件まで繰り返します。
後ろで条件判断します。
例：
SET @x = 0;
REPEAT
SET @x = @x + 1;
UNTIL @x > p1 END REPEAT;
WHILEも終了条件まで繰り返します。
前で条件判断します。
例：
WHILE v1 > 0 DO
SET v1 = v1 - 1;
END WHILE;
カーソル
カーソルは、データの「検索条件」と「現在位置」を保持して、複数の検索結果を1件ずつ処理する仕組みです。
SELECTして取得した一塊の結果をカーソルにぶちこんで、あとで1件ずつ取り出して処理する・・みたいに言い換えたほうがわかりやすいかもしれないですけど。
簡単な例です。
DECLARE done tinyint(1) DEFAULT FALSE;
DECLARE a char(16);
DECLARE b int(11);
DECLARE cur1 CURSOR FOR select keyid,data from test_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a, b;
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cur1;
最初にselect文の結果を格納するカーソルを定義してます。
test_tableテーブルから「keyid」と「data」というカラムをSELECTしてます。
DECLARE cur1 CURSOR FOR select keyid,data from test_table;
カーソルは使う前にOPENします。
OPEN cur1;
カーソルから1レコードずつデータをとりだして、a,bという変数に格納します。
カーソルには複数件のデータが含まれているので、当然、ループで回して処理します。
FETCH cur1 INTO a, b;
データが最後まできたことを認識して処理を終わらせるためのハンドラです。
カーソルが最後まできて取り出せるデータがなくなると、doneがTRUEになります。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
そして、終わったあとはカーソルをクロースしておく。
CLOSE cur1;
とまあ、こんな感じです。
1件だけ取得して変数にセット
ほしいデータは１件だけだから、カーソルを使うまでもない・・。
そんなときには「SELECT INTO」を使います。
DECLARE a char(16);
DECLARE b int(11);
SELECT id, data INTO a,b from test_table limit 1;
test_tableからidとdataカラムを1行だけとりだして、a,bにセットしてます。
などなど。
とりあえず試す程度の情報です。
もっと詳しくがっつりやる場合は、こちらの本家の情報とかを見るべきです。
ストアドルーチン以外で複合ステートメントだけ使う
ストアドプロシージャやストアドファンクションの「BEGIN・・END」ブロックで使える複合ステートメントをストアドルーチン以外で使うこともできます。
使うときの注意点はひとつだけ。
BEGINの代わりに「BEGIN NOT ATOMIC」を使うことです。
そうしないと、トランザクションの「BEGIN」と混同してしまうので。
BEGIN NOT ATOMICのサンプル
サンプルです。
DELIMITER |
BEGIN NOT ATOMIC
DECLARE x INT DEFAULT 0;
WHILE x <= 10 DO
SET x = x + 1;
SELECT x;
END WHILE;
END|
DELIMITER ;
以上、ざっと整理してみました。
今回はこんなところで。
ではでは。
