今日はストアドプロシージャの話でも(*・ω・)ノ

■ストアドプロシージャとは?
一連のSQL文や処理を名前をつけてサーバ側に保存し、関数呼び出しのように使用できるもの。

■ストアドプロシージャを使うメリットは?
・アプリ側の言語を選ばない。(MySQLで実装されていて、呼ぶだけなので)
・アプリサーバの負荷が減る。(複雑な処理はMySQL側で実行されているため)

・・といったところでしょうか。

少し前に書いたトリガーもそうですが、便利!・・ですけどメンテナンスがしにくいとか良くないところもあると思うので、メリット、デメリットを考えて、上手に使っていきたいですね。

あ、よく似たのに、ストアドファンクションってのもあります。
どっちも大体同じ。返り値の有無と、呼び出し方の違いくらいです。

で、ストアドプロシージャですよ。具体的なデータを作って試していきます。まずはentryテーブルと、commentテーブルを作ります。(entry has many commentsの関係)

CREATE TABLE entry (
    id int auto_increment primary key,
    title varchar(255),
    body text,
    delete_flag tinyint default 0
);

CREATE TABLE comment (
    id int auto_increment primary key,
    name varchar(255),
    content varchar(255),
    entry_id int not null
);

で、適当なデータを入れます。この時点で、テーブルの中身はこんな感じになっています。

select * from entry;
-------------------------------------
| id | title  | body  | delete_flag |
-------------------------------------
   1 | title1 | body1 |           0 |
   2 | title2 | body2 |           0 |
   3 | title3 | body3 |           0 |
   3 | title4 | body4 |           0 |
-------------------------------------

select * from comment;
------------------------------------
| id | name  | comment  | entry_id |
------------------------------------
|  1 | name1 | content1 |        1 |
|  2 | name2 | content2 |        2 |
|  3 | name3 | content3 |        4 |
|  4 | name4 | content4 |        1 |
|  5 | name5 | content5 |        2 |
|  6 | name6 | content6 |        3 |
|  7 | name7 | content7 |        1 |
------------------------------------

では、準備も出来たので、早速やってみますよ。(・∀・)

例1) ストアドプロシージャに引数を渡す場合。

commentテーブルからentry_idを指定してtitleを取得してみます。まずはストアドプロシージャの定義です。

delimiter //
CREATE PROCEDURE get_comment_content(IN id INT)
BEGIN
    select content from content where entry_id = id;
END
//

ストアドプロシージャの呼び出しは、callを使います。

call get_comment_content(3);
------------
| content  |
------------
| content6 |
------------

call get_comment_content(1);
------------
| content  |
------------
| content1 |
| content4 |
| content7 |
------------

ストアドプロシージャでは、引数と出力の変数をはっきりと教えてあげる必要があります。ので、必ずIN or OUT を明記する必要があります!

CREATE PROCEDURE hoge(IN name INT);
CREATE PROCEDURE fuga(OUT name2 VARCHAR(255));

例2) ストアドプロシージャから出力がある場合。

entryテーブルのcountを取得してみます。

delimiter //
CREATE PROCEDURE count_entry(OUT cnt INT)
BEGIN
    select count(*) from entry int cnt;
END
//

call count_entry(@data);
select @data;
---------
| @data |
---------
|     4 |
---------

今回の例では、callしてストアドプロシージャを呼び出すときに、@dataというものを渡しています。実は、ストアドプロシージャでは、出力用の変数も渡してあげないといけません。この変数に値が入るのです。ちょっとわかりにくい(^^;

でも、この2つの例だとイマイチ使い道がわかりません。。ゴ━━━(#゚Д゚)=○)`Д)、;'.・━━━ルァ!!
そう思った方もいるかも。そんな方には次の例を見ていただきたい!

例3) ストアドプロシージャ実用例。

entry_id毎にcommentの数をカウントし、それをcountテーブルに保存してみます。2件以上コメントされていたら、many_flagを1にする、という仕様で考えます。

countテーブルはこんな感じ。

CREATE TABLE count (
    id int auto_increment primary key,
    entry_id int not null,
    count int not null,
    many_flag tinyint
);

では、このストアドプロシージャをご覧ください!「カーソル」というものを利用してみました。

delimiter //
CREATE PROCEDURE summary()
BEGIN
    DECLARE done int;
    DECLARE _entry_id int;
    DECLARE _count int;
    DECLARE cur CURSOR FOR
        select entry_id, count(entry_id) as count from comment group by entry_id;
    DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;

    SET done = 1;
    OPEN cur;
    WHILE done DO
        FETCH cur INTO _entry_id, _count;
        IF _count > 1 THEN
            insert into count values(null, _entry_id, _count, 1);
        ELSE
            insert into count values(null, _entry_id, _count, 0);
        END IF;
    END WHILE;
    CLOSE cur;
END
//

call summary;
select * from count;
-------------------------------------
| id | entry_id | count | many_flag |
-------------------------------------
|  1 |        1 |     3 |         1 |
|  2 |        2 |     2 |         1 |
|  3 |        3 |     1 |         0 |
|  4 |        4 |     1 |         0 |
-------------------------------------

ちょっとだけ解説すると

DECLARE cur CURSOR FOR
    select entry_id, count(entry_id) as count from comment group by entry_id;

まず、curというカーソルと呼ばれるものを定義します。カーソルには、SQLを指定します。

FETCH cur INTO _entry_id, _count;

で、この部分でそのカーソルの結果から、1行ずつ _entry_id と _count という変数に値を入れていきます。どうですか?便利そうじゃないですか?

もっと詳しく知りたい方はこの辺がわかり易いと思います。もう眠いので、、すいません(;´Д`)

[参考]
MySQL ストアドプロシージャ
MySQL5開拓団 - ストアドルーチン&トリガでスリムに自動化

まぁとにかく、ストアドプロシージャ便利!!要は色んなことをMySQL側でごにょごにょと出来るわけですよ。mysqlには他にも色々な機能があるので、どんどん試していきますよ〜

現場で使える MySQL (DB Magazine SELECTION)現場で使える MySQL (DB Magazine SELECTION)
著者:松信 嘉範
販売元:翔泳社
発売日:2006-03-17
おすすめ度:5.0
クチコミを見る
このエントリーをはてなブックマークに追加