この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。
こんばんわ。ご無沙汰してます。トマトです。 今回はMySQLでストアドプロシージャを使ってみました。
■ ストアドプロシージャとは？
一言で表すと複数のSQLを１つの要求だけで実行できる、
ステキな機能です。
そんなステキ機能にもメリットデメリットが有ります。
メリット
・１つの要求だけで複数のSQLを実行できるため、ネットワークに対する負荷を軽減できる
・ 前もってサーバ上で構文解析や機械語に変換するため、処理時間を軽減できる
デメリット
・ データベースごとに記述する構文に規約が有り、互換性が低い
・ アプリケーションとのインターフェースが変更になる場合、
ストアドプロシージャも変更する必要が有り、メンテナンス性が悪くなる
それでは、早速使ってみる事にしましょう。
1. ストアドプロシージャを作ってみる
とにかく作ってみる事にしましょう。今回使ってみるテーブルはこちら。
mysql> select * from member_lists;
+------+-------+-------+
| id | name | money |
+------+-------+-------+
| 1 | hoge | 1000 |
| 2 | hoge2 | 2840 |
| 3 | hoge3 | 4789 |
| 5 | hoge5 | 1960 |
| 4 | hoge4 | 6840 |
| 6 | hoge6 | 840 |
| 7 | hoge7 | 784 |
| 8 | hoge8 | 1504 |
| 9 | hoge9 | 777 |
+------+-------+-------+
まずは単純に
「全レコードを表示させその後 money が 1000 以上のレコードを表示する」
というストアドプロシージャを作ってみましょう。
単純に SQL を書くと
select * from member_lists;
select * from member_lists where money>1000; ここまでは特に問題ないですね。
では、以上の内容のストアドプロシージャを作成し呼び出してみましょう。
mysql> delimiter //
mysql> create procedure select_money01()
-> begin
-> select * from member_lists;
-> select * from member_lists where money>1000;
-> end
-> //
Query OK, 0 rows affected (0.49 sec)
mysql> delimiter ;
まずはデリミタを変更します。
変更しないとストアドプロシージャを作成中に ‘;’ でSQL 文を中断されてしまいます。
ストアドプロシージャの本体は begin から end までの間に記述をします。
中身は使い慣れているSQL文ですね。
そして無事ストアドプロシージャを作り終わったら
最後にデリミタを元に戻しておきましょう。
では、せっかく作ったのだから呼び出してみないとですよね。
mysql> call select_money01();
+------+-------+-------+
| id | name | money |
+------+-------+-------+
| 1 | hoge | 1000 |
| 2 | hoge2 | 2840 |
| 3 | hoge3 | 4789 |
| 5 | hoge5 | 1960 |
| 4 | hoge4 | 6840 |
| 6 | hoge6 | 840 |
| 7 | hoge7 | 784 |
| 8 | hoge8 | 1504 |
| 9 | hoge9 | 777 |
+------+-------+-------+
9 rows in set (0.00 sec)
+------+-------+-------+
| id | name | money |
+------+-------+-------+
| 2 | hoge2 | 2840 |
| 3 | hoge3 | 4789 |
| 5 | hoge5 | 1960 |
| 4 | hoge4 | 6840 |
| 8 | hoge8 | 1504 |
+------+-------+-------+
5 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
呼び出しをするときには call を使います。
ばっちり動作しますね！
しかし、今のままでは汎用性のないストアドプロシージャになってしまいますよね。
それでは少し手を加えてみましょう。
mysql> delimiter //
mysql> create procedure select_money02(m int)
-> begin
-> select * from member_lists;
-> select * from member_lists where money>m;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
money の値を引数で指定できるようにしてみました。
では早速実行。
mysql> call select_money02(2000);
# 全レコード表示結果は省略
+------+-------+-------+
| id | name | money |
+------+-------+-------+
| 2 | hoge2 | 2840 |
| 3 | hoge3 | 4789 |
| 4 | hoge4 | 6840 |
+------+-------+-------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
今回は 2000 で指定してみました。
