MySQL ストアドプロシージャ/ファンクションを使ってみよう！

いやー・・・本当に情報が少なくて苦労した。というか現在進行形で苦労してます。表題の通り、MySQLのストアドプロシージャ/ストアドファンクションの件なのですが、oracle触っているときも、db2触っているときも、「管理が複雑になるから」ともっともらしいことをいって避けていた道なんですけど、なんとなくわかったところで、「あ、これ結構つかえるじゃん？」と思ってきた。まあ、たしかにプロシージャ側がデータベースサーバサイドで動くわけなのでアプリケーションとの比重を考える必要があるかと思うけど、phpとでもjavaでもアプリケーション視点で考えてみれば、小難しいこと考えなくてもよくなるので、パラメータ渡したら期待した結果が戻ればそれで良いわけでSQLでの過程なんてどーでも良いわけだ、またある意味アプリケーションのプログラムと、ストアドプロシージャは分離されているので、細かい計算ロジックをストアドプロシージャ側で実装すれば、仕様変更があっても、もしかしたらストアドプロシージャだけを変更するだけですむかもしれないですしね。

冒頭で、ストアドプロシージャとストアドファンクションとあえてわけて書きましたが、自分なりの解釈では

ストアドプロシージャ・CALL で呼び出す・戻り値は、SELECT @RRRR; で取得する・上記の方法で複数の戻り値をリターンすることができる
ストアドファンクション・SELECT で呼び出す・戻り値は一つだけ・列関数(UDF）として利用することができる

こんなところですかね？たとえば、前回掲載しました、「MySQLで二点間の距離を正確に」なんてのは、ストアドファンクションで作成しております。SELECTの一部として呼び出せるのはすごく便利です。ひとつだけ例を入れておきます。前回地図データベースを作成しましたので、それをベースに「こんなストアドファンクションがあれば便利かな？」というものです。

【ストアドファンクション処理概要】

設定した緯度と経度でもっとも近いエリア情報をカンマ区切りで返す

【ストアドプロシージャ】以下のように作成しました、尚、下記の例では、コメントを入れておりますが(-- XXXX)このまま実行するとシンタックスエラーになりますのでご注意ください

DELIMITER //
CREATE FUNCTION gis_getareaInfo(
  in_lat decimal(12,9),
  in_lng decimal(12,9)
)
 RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
-- レコードの有無判定
  DECLARE done int;
-- 戻り値の定義
  DECLARE _result_area VARCHAR(500);
-- レコードの戻り値
  DECLARE _result VARCHAR(500);
-- カーソル定義
  DECLARE cur CURSOR FOR 
  select 
    concat(
      jis_prefecture_code,',',
      prefecture,',',
      city_code,',',
      city,',',
      area_code,',',
      area,',',
      lat,',',
      lng
    ) as result_area
  from 
    gis.cyome s1 
  where
   MBRContains(
    GeomFromText(
      Concat('LineString(',
        in_lng + 0.2 , ' ',
        in_lat + 0.2 , ',',
        in_lng - 0.2 , ' ',
        in_lat - 0.2 , ')'
      )
    ),
    geom
   ) and
    gis.distance_sphere(in_lat,in_lng,Y(s1.geom),X(s1.geom)) < 1000
  order by
    gis.distance_sphere(in_lat,in_lng,Y(s1.geom),X(s1.geom))
  limit 1;
-- レコードが存在しない場合、dune 変数にZEROをセットするハンドラ 
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0; 

  SET done = 1;
-- レコード有無フラグに、1をデフォルトでセットする
  OPEN cur;

- カーソルから、result_area列を読み込み、_result_areaにセットする
  FETCH cur INTO _result_area;
-- レコードが存在した場合は、結果を_resultにセットする、存在しない場合は、NOT FOUNDをセットする
  IF done = 1 THEN
    set _result = _result_area;
  ELSE
    set _result = 'NOT FOUND';
  END IF;   
-- カーソルのクローズ
  CLOSE cur;
-- resultセットのクローズ
  RETURN _result_area;
END;
//
DELIMITER ;

以下が上記、ストアドファンクションの実行結果です。

【実行結果】

mysql> select gis_getareaInfo(35.672558912630656,139.7632384300232) as AREA_CSV;
+--------------------------------------------------------------------------------------+
| AREA_CSV                                                                             |
+--------------------------------------------------------------------------------------+
| 13,東京都,13102,中央区,131020002005,銀座五丁目,35.670850000,139.764397000            |
+--------------------------------------------------------------------------------------+
1 row in set, 66 warnings (1.20 sec)

上記の例では、カンマ区切りで結果を返しましたが、用途によってはは、jsonで返しても良いかと思いますし、xml形式で返しても良いかもしれません。しかしながら文字列加工の話なのでここではあえてCSVで表現しました。

尚、カンマ区切りで返される列の内容は以下のようなフォーマットです JIS都道府県コード,当該範囲の都道府県名,JIS市区町村コード,当該範囲の市区町村名,当該範囲の大字・町丁目名,十進経緯度（少数第9位まで、半角）,十進経緯度（少数第9位まで、半角）

たとえばこれを、プログラムに書くとしても、設定した緯度と経度からもっとも近くにある、エリア情報を返すには、上記のようなＳＱＬを延々とかかなければいけません。とはいつつも、アプリケーション側で関数化すれば結果としては同じかもしれませんが、プログラムソースの可読性が悪くなるのも考え物です。適材適所で、ストアドプロシージャ、ファンクションを利用するのがベストな利用方法かと。

Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL ストアドプロシージャ/ファンクションを使ってみよう！