主夫ときどきプログラマ

データベース、Webエンジニアリング、コミュニティ、etc

はじめてのMysqlストアドプロシージャ・ストアドファンクション

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

ストアドプロシージャとストアドファンクションの違いは以下のようになっています。

ストアドプロシージャ
  • 戻り値がない
  • 実行中に使用テーブルにロックがかかる(実行が速い)
ストアドファンクション
  • 戻り値がある
  • 実行中に使用テーブルにロックがかからない(実行が遅い)

ストアドプロシージャとは?

ストアドプロシージャとは「1つの要求で複数SQLを実行出来る機能」と言えます。
そして以下のようなメリット、デメリットを持ちます。

メリット
  • 1つの要求で複数SQLを実行出来るためネットワーク負荷を軽減できる
  • 事前にサーバー側でSQL構文解析機械語変換を行うため、処理時間を短くできる
デメリット
  • データベースごとに記述する構文に規約があり互換性が低い
  • アプリケーションとのインタフェースが変更になるたびにストアドプロシージャも変更する必要があり、メンテナンス性が悪くなる

ストアドプロシージャの基本操作

ではストアドプロシージャの基本的な扱い方を見ていきましょう。

■定義
mysql> CREATE PROCEDURE procedure_name()
■実行
mysql> CALL procedure_name();
mysql> CALL other_schema.procedure_name();

異なるスキーマのストアドプロシージャも実行することができます。
その場合はスキーマ名をストアドプロシージャ名の前につけて呼び出します。

■入力引数があるストアドプロシージャの定義
mysql> DELIMITER //
mysql> CREATE PROCEDURE procedure_sqrt(IN input INT)
    -> BEGIN
    ->     SELECT SQRT(input);
    -> END;
    -> //
mysql> DELIMITER;

ここで DELIMITER と呼ばれる終端文字を変更します。
通常Mysqlでは「;」がDELIMITERとなっており、これをSQLの最後として扱い実行されます。
しかし、ストアドプロシージャを登録・使用するとき必ずしも「;」が文の最後になるとは限りません。
そこで、DELIMITERコマンドを使い終端文字を「//」に変更してストアドプロシージャの登録を行います。

■入力引数があるストアドプロシージャの実行
mysql> CALL procedure_sqrt(2);
+-----------------+
| sqrt(input)     |
+-----------------+
| 1.4142135623731 |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
■出力引数があるストアドプロシージャの定義
mysql> DELIMITER //
mysql> CREATE PROCEDURE procedure_out(OUT output INT)
    ->BEGIN
    ->    SELECT COUNT(*) FROM table INTO output;
    ->END;
    -> //
mysql> DELIMITER;

SELECT ..... INTO ....; のところで取得した値を出力引数 output に代入しています。
この時 SELECT 結果が複数行や複数カラムになると実行時エラーになるので気をつけましょう。

■出力引数があるストアドプロシージャの実行
mysql> CALL procedure_out(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

ストアドプロシージャを実行するときに変数 @a を引数に渡すことで、これに出力が確認されます。
格納された値はそのあとに SELECT することで参照することができます。

■ストアドプロシージャの確認
mysql> SHOW PROCEDURE STATUS;

登録されているストアドプロシージャを一覧表示します。

■ストアドプロシージャの内容確認
myswl> SHOW CREATE PROCEDIRE procedure_name;

ストアドプロシージャの内容を表示します。

■ストアドプロシージャの削除
mysql> DROP PROCEDURE procedure_name;

登録されているストアドプロシージャを削除します。

ストアドプロシージャの書き方

これまでストアドプロシージャの周辺操作を見てきましたが、ここからはストアドプロシージャ内で使えるいろいろな処理方法について見ていきます。

■変数宣言(ストアドプロシージャ内で有効)
#DECLARE 変数名 データ型;
DECLARE created_date DATETIME;

DECLARE文は全てBEGINの直後に書く必要があり、ルーチンの途中に書くことはできません。

■カーソル(結果セットを処理する場合に使用)
# DECLARE カーソル名 CURSOR FOR 処理する結果セットを取得するSQL文;
DECLARE curUsers CURSOR FOR SELECT id, name FROM users ORDER BY id;

SELECTの結果を1行毎に何か処理したい場合はカーソルを使用します。
Java, Ruby, PHPなど各種言語で用意されているIteratorのようなものです。
カーソルのDECLAREは通常変数のDECLAREの後に定義する必要があります。

■ハンドラ定義
DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;

ここではカーソル呼び出しの際に、データが存在しなくなったら変数doneに1をセットするというハンドラを定義しています。
これでカーソルで指定されたSELECT結果がすべて終了したかどうかを判定する。

■カーソルオープン
OPEN カーソル名;
■結果セットの一行から値を取得して変数に代入
FETCH カーソル名 INTO 変数,変数,...;
■カーソルクローズ
CLOSE カーソル名;
ストアドプロシージャ例
CREATE PROCEDURE action_count (IN _opFlag INT)
BEGIN

    /* 変数宣言 */
    DECLARE done INT;
    DECLARE _pageNo INT;
    DECLARE _userNo INT;
    DECLARE _actCnt INT;

    /* カーソル定義 */
    DECLARE cur CURSOR FOR
        SELECT `fanpage_id`, `owner_id`, COUNT(*) AS `cnt` FROM `t_action`
        WHERE `created` BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL x DAY) AND CURRENT_DATE
        GROUP BY `fanpage_id`, `owner_id` ;

    /* イベント制御規定 */
    DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;

    /* デイリーかウィークリーか判断しトランケイトを実行 */
    IF x = _opFlag THEN
        TRUNCATE TABLE `t_daily_total`;
    ELSE
        TRUNCATE TABLE `t_weekly_total`;
    END IF;

    SET done = 1;
    OPEN cur;
    WHILE done DO
        /* 結果セットの一行から値を取得して変数に割当 */
        FETCH cur INTO _pageNo, _userNo, _actCnt;
        IF x = 1 THEN
            INSERT INTO `t_daily_total` VALUES (_pageNo, _userNo, _actCnt);
        ELSE
            INSERT INTO `t_weekly_total` VALUES (_pageNo, _userNo, _actCnt);
        END IF;
    END WHILE;
    CLOSE cur;

END;

ストアドファンクショとは?

基本的にはストアドプロシージャと同じですが、いくつかの点で違いがあります。

引数にIN, ONTが不要

ストアドプロシージャの引数の先頭にはIN、OUTなどの識別子の指定があり「この値は入力値です」というのを指示する必要があります。
これはC言語で言うところの参照渡しに近いものです。
ストアドファンクションの場合はRETURNで戻り値を指定するので不要になります。

戻り値の型の指定

CREATE FUNCTIONの引数リストの後に、「RETURNS INT」というのがあり、ここで戻り値の型を指定します。

DETERMINISTIC

これを付けないで定義しようとすると、以下のようなエラーが発生する場合があります。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

入力値が同じ場合に出力値が同じ場合はDETERMINISTIC、そうでない場合はNOT DETERMINISTICを指定しましょう。
ストアドファンクション生成に対するこの規制を緩和するには、グローバルシステム変数を1に設定します。

SET GLOBAL log_bin_trust_function_creators = 1;
ストアドファンクション例
mysql> DELIMITER //
mysql> CREATE FUNCTION test_func(f INT) RETURNS INT DETERMINISTIC
   -> BEGIN
   ->     RETURN (f + 2);
   -> END;
   -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER;
mysql> SELECT test_func(1);
+--------------+
| func_test(1) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)