MySQL のカーソル (CURSOR)

ここでは MySQL のカーソル (CURSOR) の使い方についてご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちら のスクリプトを実行して school_db データベースを作成しておいてください。

MySQL のカーソル (CURSOR) の使い方

MySQL でカーソル (CURSOR) を使うと、SELECT 文の結果セットを一行ずつループして何か処理を行うことができます。

便利ですが、カーソルを使わずにクエリーで同等のことができる場合は、カーソルを使わないほうがパフォーマンスが良いことが多いです。


MySQL のカーソルの基本的な使い方のステップは次の通りです。

  1. 終了判定用の変数を FALSE で定義する。
  2. カーソルを定義する。
  3. CONTINUE HANDLER FOR NOT FOUND をカーソルが結果セットの終わりに達した時に、終了判定用の変数に TRUE をセットするように定義する。
  4. カーソルを OPEN する。
  5. ループで、カーソルからデータを FETCH、終了判定、処理を繰り返す。
  6. カーソルを CLOSE する。

カーソルは、変数の定義の後、ハンドラーの定義の直前の位置に定義します。

MySQL のカーソルは読み取り専用で、決まった方向に一行ずつしか動けません。


それでは、実際にシンプルな MySQL のカーソル (CURSOR) を使ったストアドプロシージャを作成してみましょう。

次のような students テーブルがあります。

MySQL のカーソル (CURSOR) 1


この first_name と last_name をカンマ区切りでつなげた文字列をカーソルを使って生成するストアドプロシージャは次の通りです。。

DROP PROCEDURE IF EXISTS get_student_names;
DELIMITER //
CREATE PROCEDURE get_student_names (OUT student_names VARCHAR(4000))
BEGIN
	
  DECLARE student_name VARCHAR(100) DEFAULT "";
  DECLARE done INT DEFAULT FALSE;
    
  DECLARE cur_student CURSOR FOR
    SELECT  CONCAT(first_name, ' ', last_name)
    FROM    students
    ORDER BY first_name,
            　last_name;
                
  DECLARE CONTINUE HANDLER 
  FOR NOT FOUND SET done = TRUE;

  SET student_names = '';

  OPEN cur_student;

  loop1: LOOP    
    FETCH cur_student INTO student_name;
    IF done THEN
      LEAVE loop1;
    END IF;
    SET student_names = CONCAT(student_names, ", ", student_name);	
  END LOOP loop1;
    
  CLOSE cur_student;

  SET student_names = SUBSTRING(student_names, 3);
    
END//
DELIMITER ;

まず、7 行目で終了判定用の変数 done を FALSE で定義しています。

9 ~ 13 行目で、 DECLARE カーソル名 CURSOR FOR SELECT 文で cur_student というカーソルを定義しています。

SELECT 文 は students テーブルの first_name と last_name をつなげたものを first_name、last_name 順に取得するものです。


15 ~ 16 行目では、DECLARE CONTINUE HANDLER FOR NOT FOUND で、カーソルが結果セットの終わりに達した時に done に TRUE がセットされるように定義しています。

20 行目でカーソルを OPEN しています。


22 ~ 28 行目で、ループしています。

23 行目の FETCH カーソル名 INTO 変数名 で、cur_student カーソルから student_name に値を代入しています。

24 行目の IF 文で done が TRUE かチェックし、TRUE の時は 25 行目の LEAVE 文でループを抜けています。

27 行目で student_names 変数に student_name の値をカンマ区切りで追加していっています。


30 行目でカーソルを CLOSE しています。

32 行目は student_names から、先頭の ', ' を削除しているだけです。


このストアドプロシージャを作成して実行すると次のようになります。

CALL get_student_names (@student_names);
SELECT @student_names;

MySQL のカーソル (CURSOR) 2

first_name と last_name のカンマ区切りの文字列が取得できていますね。


以上、MySQL のカーソル (CURSOR) の使い方をご説明しました。

© 2024 MySQL 入門