読者です 読者をやめる 読者になる 読者になる

Carpe Diem

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

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

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