前回の「
ストアド・プロシージャを使う」で基本的な使い方を学びました。今回は
・複数のレコードを参照する
・複数のレコードを挿入する
をやってみようと思います。
複数のレコードを挿入できれば一度に300万件とかのデータも作れるので、INDEXの検証とかもできるようになります。
複数のレコードを参照する
今回は
mysql> SELECT * FROM article;
+----+--------+-------+
| id | number | title |
+----+--------+-------+
| 1 | 10 | piyo |
| 2 | 3 | hoge |
| 3 | 6 | fuga |
+----+--------+-------+
3 rows in set (0.00 sec)
のデータをプロシージャで扱うことにします。
SELECT文で得られた単一行の結果を変数へ代入するには
SELECT ... INTO を使用しますが、
複数行からなる結果を変数へ代入していくには、
CURSOR を使用します。
CURSORはSELECT文の実行結果(結果セット)を仮想的な作業領域として扱います。
CURSORはこの作業領域に対してループ処理を実行し、1行ずつデータを取り出し、処理を行います。
CURSORは、DECLARE(カーソル宣言)、OPEN(カーソルを開く)、FETCH(取得)、 CLOSE(カーソルを閉じる)の4つの処理でデータを取得します。よくあるDBのアクセスにちょっと似てます。
定義
delimiter //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 1;
DECLARE a,b INT;
DECLARE c VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT id, number, title FROM article;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0;
OPEN cur1;
WHILE done DO
FETCH cur1 INTO a, b, c;
IF done THEN
INSERT INTO article (number, title) VALUES (b, c);
END IF;
END WHILE;
CLOSE cur1;
END
//
delimiter ;
実行
call curdemo();
結果
SELECT * FROM article;
+----+--------+-------+
| id | number | title |
+----+--------+-------+
| 1 | 10 | piyo |
| 2 | 3 | hoge |
| 3 | 6 | fuga |
| 4 | 10 | piyo |
| 5 | 3 | hoge |
| 6 | 6 | fuga |
+----+--------+-------+
6 rows in set (0.00 sec)
------------------解説-------------------
DECLARE done INT DEFAULT 1;
DECLARE a,b INT;
DECLARE c VARCHAR(20);
プロシージャ内で使う変数を定義しています。
DECLARE cur1 CURSOR FOR SELECT id, number, title FROM article;
CURSORを定義しています。
DECLARE [カーソル名] CURSOR FOR [SELECT文]
の順で書きます。
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0;
CURSORのループが終了した時の処理です。
SQLSTATE; 02000 はデータが見つからなくなった=CURSORが最後の行まで進んだことになるので、その時に
done = 0
と変数をセットしてwhileループが終了するようにします。
OPEN cur1;
CURSORをオープンします。
FETCH cur1 INTO a, b, c;
SELECTで得られた結果を、一行ずつ宣言した変数に入れます。
CLOSE cur1;
CURSORを閉じます。
以上です。
ソース:
複数行のデータを変数へ代入する(Cursors)
例外処理(DECLARE ... HANDLER)
mysqlのストアドプロシージャの便利さをアピールしてみる
ストアドプロシージャの花形“カーソル”を使おう (1/3)