最初に
Nendを担当させて頂いてるtosh01です。
最初に質問なのですが、MySQLにおいて、あなたはストアドプロシージャをどのようなケースで利用されたことがありますでしょうか?
自身の経験が業務系にわりと寄っており、DBへのクエリはすべてストアドプロシージャに持たせてしまうことも多く、バッチや計算処理を要求される際によく使用していたせいか、使いたい気持ちがあります。
とはいうものの、それらは他のDB製品での話であり、MySQLに関しては過去にそれほど使う機会もなく現在お勉強中、もちろんMySQLでストアドプロシージャなど使ったことがない為、質問させていただいてます。
MySQL 5.0から実装されているらしいストアドプロシージャですが、やはりアプリ側のプログラムからの問合せだけの場合より、MySQLにおいてもDB操作の幅が広がりそうで、MySQLでも状況によっては役に立つ場面がありそう、というか個人的に好きな為、ブログという名目で時間を取ることが出来るこの機会に確認してみました。
必要性
そもそもストアドプロシージャのメリットってなんでしょうか?
MySQLの場合は、DBサーバへの負荷が大きくなるだとか、パフォーマンスもよくないという意見もあり、メリットよりもデメリットの方が多く見られたりもするのですが、DBの種類にこだわらなければ下記のようなものが見られました。
メリット ・セキュリティ性 ・複数のSQLや複雑な処理を扱いやすい ・処理時間の短縮(サーバーへの負荷軽減?) ・メンテナンス性がよい(場合によりけり?) ・いつでも処理を呼び出せる
デメリット ・製品ごとに構文の規約が異なる ・アプリ側のロジックと別れているため見通しが悪い ・デバッグしづらい?
もちろんこれらはDBの種類によって違うのでしょうが、実際にMySQLでも使える以上、下記に使用例を挙げてみたいと思います。(以下 MySQL 5.5.8にて検証。)
Stored ProcedureとCursor
色々調べてみるとMySQLのストアドプロシージャの定義構文自体、他のDB製品とSQLの規約こそ違うものの、書き方はあまり違うものではないことがわかりましたので下記に記述します。(※OracleのPL/SQL等との違いは参照をご参考下さい。)
[sql]
-- 区切り文字変換(';'によるSQLの中断を防ぐため)
DELIMITER $$
-- プロシージャ削除
DROP PROCEDURE IF EXISTS db_name
.procedure_name
$$
-- プロシージャ作成
CREATE PROCEDURE db_name
.procedure_name
()
BEGIN
-- 行いたい処理をここに記述
END $$
-- 区切り文字変換
DELIMITER ;
[/sql]
上記がMySQLでの定義構文になります。
また、SQLにおけるカーソル(Cursor)なのですが、Wikipediaには
データベースにおけるカーソル(Cursor)は、一連のデータに順にアクセスする際の検索条件および「現在位置」を保持するデータ要素である。
と若干難解な解説がありますが、複数レコードを1行ずつ指定した条件をもとに更新していく必要がある場合に便利だと思いますので以下に構文を載せてみます。
[sql] -- カーソル宣言(select文により指定) DECLARE cursor_name CURSOR FOR SELECT id,name FROM test_table WHERE id > 10; -- カーソルオープン OPEN cursor_name; -- カーソル取得結果の一行から値を取得して変数に代入(ここでの場合idとname) FETCH cursor_name INTO variable1,variable2,...; -- ここにvariable1,variable2,...を用い例えばUPDATE文などのSQLを記述 -- カーソルクローズ CLOSE cursor_name; [/sql]
[sql] mysql> CREATE TABLE test_table ( -> id int auto_increment primary key, -> name varchar(255) NOT NULL collate utf8_bin DEFAULT '', -> point int not null default 0, -> change_flg int not null default 0, -> result int not null default 0 -> );
mysql> SELECT * FROM test_table; +----+--------+--------+--------------+--------+ | id | name | point | change_flg | result | +----+--------+--------+--------------+--------+ | 1 | Tim | 5 | 0 | 0 | | 2 | Ron | 8 | 0 | 0 | | 3 | John | 13 | 1 | 0 | | 4 | Tom | 10 | 1 | 0 | | 5 | Kurt | 3 | 1 | 0 | +----+--------+--------+--------------+--------+ 5 rows fetched in 0.0023s (0.0004s) [/sql]
を操作する、実際のストアドプロシージャを作ります。
ここでは上記テーブルにおいて、resultカラムを更新する簡単なupdate処理を書いてみたいと思います。
[sql] DELIMITER $$
DROP PROCEDURE IF EXISTS test
.test_procedure
$$
CREATE DEFINER=root
@localhost
PROCEDURE test
.test_procedure
()
BEGIN
-- DECLAREで宣言
DECLARE done int;
DECLARE output int;
DECLARE id int;
DECLARE name_len int;
DECLARE point int;
DECLARE change_flg int;
DECLARE cur CURSOR FOR
select id, length(name) as name_len, point, change_flg from test_table WHERE id < 10;
-- 取得レコードが見つからなかった場合変数doneに0をセット(HANDLERの使い方は参照にて)
DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;
SET done = 1;
OPEN cur;
WHILE done DO
FETCH cur INTO _id, _name_len, _point, _change_flg;
-- _change_flgによってresultカラムへ出力する計算を変化。
IF _change_flg = 1 THEN
SET output = _point * _name_len;
UPDATE test_table SET result = output WHERE id = _id;
ELSE
SET output = _point;
UPDATE test_table SET result = output WHERE id = _id;
END IF;
END WHILE;
CLOSE cur;
END $$
DELIMITER ; [/sql]
として、上記プロシージャを保存します。(※作成にはMySQL Query Browserを使用。)
そして作ったプロシージャはCALLで呼び出すことができます。
[sql] mysql> CALL test_procedure(); mysql> SELECT * FROM test_table; +----+--------+--------+--------------+--------+ | id | name | point | change_flg | result | +----+--------+--------+--------------+--------+ | 1 | Tim | 5 | 0 | 5 | | 2 | Ron | 8 | 0 | 8 | | 3 | John | 13 | 1 | 52 | | 4 | Tom | 10 | 1 | 30 | | 5 | Kurt | 3 | 1 | 12 | +----+--------+--------+--------------+--------+ 5 rows fetched in 0.0015s (0.0002s) [/sql]
最後に
今回は調査という名目でシンプルな使用例を挙げさせていただくだけに留まってしまいましたが、CURSORにしても使い方次第で大きくパフォーマンス等変わってくると思いますのでMySQLにおけるストアドプロシージャの利点も検証しつつ、データ処理の仕様を考えていけたらと思います。
引数を使ったもの等、より詳しい用法やストアドファンクションに関しては参照を参考にして下さい。
参照
19.2. Using Stored Routines (Procedures and Functions) - MySQL 5.5 Reference Manual MySQL ストアドプロシージャ 例外処理(DECLARE ... HANDLER) - bnote 3 トリガーおよびストアド・プロシージャ - Oracle® SQL Developer MySQLからの移行のための追加情報