MySQL ストアドプロシージャ

Posted by on 2009年12月2日 Leave a comment (0) Go to comments

mysql に保存されているデータベースに対して一気に特定の処理をする必要に迫られ、表記について学ぶ。
参考ページはhttp://dev.mysql.com/doc/refman/5.1/ja/stored-procedures.html。
要はMySQLデータベースに保存可能な手続き、或いはプログラムということ。

もちろん、Perlなどから同じ処理は出来るが、頻繁に行うことでも無いし、何をしたのか良く分からなくなるプログラムが放置されるのも後で整理する時に困るので今回はこれを試してみた。

覚えておくと良い事項は、

  • デリミタを変更するコマンド DELIMITER を用いてプログラム内の1命令の終わりを示すセミコロンをMySQLコマンドの終わりとして解釈されないようにする。
  • プログラムの定義は “CREATE PROCEDURE プログラム() BEGIN” で開始、”END” で終了。
  • 変数宣言は “DECLARE”。
  • ある一定の条件を満たす行に対して、その内容を変更したい時、ストアドプロシージャではカーソル (CURSOR) という概念が使われる。
  • 条件を満たす行が複数あり、各行に同じ処理を施すならば、繰り返しを使うが、その終了条件は “SQLSTATE ‘0200’” 。
  • プログラムの保存先は、mysql.proc テーブル。

例えば、userinfo テーブルの RATE 列が 0 であるとき、それを 1 にしたい場合は以下のようなプログラムで実現可能。

DELIMITER $$
CREATE PROCEDURE myProc()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE r INT;
  DECLARE cur CURSOR FOR SELECT id FROM userinfo WHERE RATE=0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

  OPEN cur;
  REPEAT FETCH cur INTO r;
    UPDATE userinfo SET RATE=1 WHERE id=r;
  UNTIL done END REPEAT;
  CLOSE cur;
END$$

“SELECT * FROM mysql.proc\G” というコマンドを発行すると実際に宣言したプログラムが保存されていることを確認出来る。
また、実行する場合は、CALL myProc;というコマンドを発行する。

記録