ストアド・プロシージャで複数のレコードを扱う

前回の「ストアド・プロシージャを使う」で基本的な使い方を学びました。今回は・複数のレコードを参照する・複数のレコードを挿入するをやってみようと思います。複数のレコードを挿入できれば一度に３００万件とかのデータも作れるので、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)

Carpe Diem

備忘録。https://github.com/jun06t

ストアド・プロシージャで複数のレコードを扱う