初のMySQLストアド

  • このエントリーをはてなブックマークに追加

MySQLロゴ

先日、とある案件で、MySQLのストアドを初めて開発しました。

今までOracleのものは幾度も開発したことがあったものの、
初めてのMySQLはなかなか難敵でした。
※SQLServerはそれはそれで独特だった記憶があるけど

ごりごりプログラミングしなくなって何年経つねん、という身ではありつつ、
もしまた開発する日がきた場合にと、備忘として行き詰った点を含めて残そうと思います。

特定のことしかやってないけど、それでも諸々気になったこと。
概要は以下のとおり。
①配列は使えない
②カーソルのネストが苦手
③日本語(全角文字)を扱う変数にはそれ用の宣言が必要
④結局なにかわからないDELIMITER
⑤WHERE句を動的に生成させれない?
⑥プロシージャとファンクションで色々違いがある

<①配列は使えない>

ストアドといえば、カーソルや配列、を使いますよね。
でも配列は使えないんですって・・・。(なんと!)

<②カーソルのネストが苦手>

そしてカーソルループをネストさせる場合、一筋縄ではいきません。
なぜならHANDLERが1つしかないから・・・。(なんと!)
だから一工夫して成立させないといけません。

また、例外処理についてネット上でよく見かけたのは以下の記述なのですが、何故かうまくいかず。
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
結局、思ったような動きが得られたのはこっちでした。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = TRUE;

<③日本語(全角文字)を扱う変数にはそれ用の宣言が必要>

変数に全角文字を代入すると、文字化けします。
例えば、以下のように明示的に「CHARSET utf8」をつけなくてはいけないようです。
(×)DECLARE v_userName VARCHAR(100);
(○)DECLARE v_userName VARCHAR(100) CHARSET utf8;
※最初はDB自体の文字コード設定が原因かと思ったのですが、ちゃんとUTF-8なのに化ける。。

ちなみにFUNCTIONの場合は、もちろん戻り値にも同様の宣言が必要です。

<④結局何かわからないDELIMITER>

DELIMITERについてはよくある”おまじない”として記述しましたが、
これが一体どういう使い道があるのかはわからず終いでした。
※DELIMITERを替えるというのは理解できるが、だからどうした状態

<⑤WHERE句を動的に生成させられない?>

最後に、今回はとくに必要となりませんでしたが、どうやらSQL文そのものを
動的に生成することはMySQLでは出来ないようですね。
テーブル名とか、WHEREの対象カラム名などを、PREPARE構文で置換する程度は出来るようだけど、
それも実装してみてないので不明。

<⑥プロシージャとファンクションで色々違いがある>

先の開発ではそんなにシビアな処理ではなかったので問題にせず、素直にFUNCTIONにしたけど、
戻り値の有無以外にも色々あるらしい。
・処理速度はPROCEDUREの方が早いけど、使用テーブルにロックが掛かるらしい。
・またFUNCTIONだとトランザクション開始ができないらしい。
・その他も色々あるらしい。

うーん、制約が多いぞMySQL。

今回はMySQLでしたがそれに限らず、とっかかりで躓くことって意外と多いように思います。
今回もただSELECTした結果を返す、という至極単純なものだったのに、
これがなかなかトータルしてわかる ”ひとつのサイト” っていうのが見つからず、
あっちでチョイと摘み、こっちでチョイと摘みと、なかなか苦労しました。

今後も何か製造させられる機会があれば記事にしたいと思います。

※※※参考までに開発したストアドは以下みたいな感じ※※※

 
DROP FUNCTION IF EXISTS func_sample;
DELIMITER //
CREATE FUNCTION func_sample()
RETURNS LONGTEXT CHARSET utf8 NOT DETERMINISTIC
BEGIN

    -- 宣言は、変数、カーソル、ハンドラの順に

    -- =================================================================
    -- 変数宣言
    -- =================================================================
    DECLARE なんか色々な宣言を・・・・・・・・・・
    DECLARE v_chapterId     INT;                                        -- 章ID
    DECLARE done            INT DEFAULT 0;

    -- =================================================================
    -- カーソル定義
    -- =================================================================

    -- 顧客を取得
    DECLARE cur_t_user CURSOR FOR 
        SELECT XXX, XXX FROM ZZZZZ
    ;

    -- 章を取得
    DECLARE cur_m_chapter CURSOR FOR 
        SELECT XXX FROM ZZZZZ
    ;

    -- 節を取得(章ID指定あり)
    DECLARE cur_m_clause CURSOR FOR 
        SELECT XXX, XXX, XXX FROM ZZZZZ
        WHERE XXX = v_chapterID                                         -- 変数名を直接記述
    ;

    -- =================================================================
    -- ハンドラ定義
    -- =================================================================
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;         -- CURSORのループが終了した時にdone変数に1をセットする(ループ終了判定用)

    -- =================================================================
    -- 処理本体
    -- =================================================================

    -- -----------------------------------------------------------------
    -- ■会員ループ
    OPEN cur_t_user;
    lblUser: LOOP

        FETCH cur_t_user INTO ○○○, ○○○;
        IF done THEN
            LEAVE lblUser;
        END IF;

        -- -------------------------------------------------------------
        -- ■章ループ
        OPEN cur_m_chapter;
        lblChapter: LOOP

            FETCH cur_m_chapter INTO v_chapterId;
            IF done THEN
                LEAVE lblChapter;
            END IF;

            -- ---------------------------------------------------------
            -- ■節ループ
            OPEN cur_m_clause;
            lblClause: LOOP

                FETCH cur_m_clause INTO □□□, □□□, □□□;
                IF done THEN
                    LEAVE lblClause;
                END IF;


                なんか色々な処理を・・・・・・・・・・


            END LOOP lblClause;                                         -- 節ループ
            CLOSE cur_m_clause;
            SET done = 0;                                               -- 親ループのために初期化

        END LOOP lblChapter;                                            -- 章ループ
        CLOSE cur_m_chapter;
        SET done = 0;                                                   -- 親ループのために初期化

    END LOOP lblUser;                                                   -- 会員ループ
    CLOSE cur_t_user;

    RETURN (・・・・・・・・・・);

END;
//
  • このエントリーをはてなブックマークに追加

SNSでもご購読できます。

コメントを残す

*