DoRuby

ストアドプロシージャを使ってみましょう＠MySQL

アピリッツ広報担当

作成: 2009.03.27 更新: 2021.03.05

この記事はアピリッツの技術ブログ「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 で指定してみました。