単元概要

この単元では、SELECT文の結果を1行ずつループ処理で受け取り、加工する方法を学びます。

解説

CURSORの使い方

「loop文」と同様にカーソルを利用する場合には、必ず終了条件を先に入れるようにしましょう。また、「fetch」と呼ばれる1行ずつレコードを読み出すプログラムも併せて忘れないようにしましょう。失敗するとサーバが停止するなど大きな事故に繋がります。ストアドプロシージャだけでなく、ストアドファンクションでも同様に使用できます。

delimiter //

drop procedure if exists [プロシージャ名];
create procedure [プロシージャ名](in/out [引数名1] [型],in/out [引数名2] [型])
begin
    declare [値を受ける変数名] int default [初期値]；
    declare [終了判定用変数名] int default false;
    declare [カーソル名] cursor for [SELECT文];
    declare continue handler for not found set [終了判定用変数名] = true;

    open [カーソル名];
        [繰り返しラベル名]: loop

            fetch [カーソル名] into [値を受ける変数名];

            if [終了判定用変数名] = ture then
                leave [繰り返しラベル名];
            end if;
        
        end loop [繰り返しラベル名];
    close [カーソル名];
    
end;
//

delimiter ;

＜例題＞

  • もし、プロシージャ名「pro002_cursor」がすでにある場合には、削除すること。
  • プロシージャ名「pro002_cursor」で作成すること。
  • 引数：無し
  • 処理
    1. 処変数名「rec_studnet_no」（int型）を宣言すること。
    2. 処理２：変数名「rec_studnet_kj」（varchar型,255文字）初期値：空を宣言すること。
    3. 処理３：変数名「rec_test_su」（int型）初期値：0を宣言すること。
    4. 処理４：変数名「str_text」（varchar型,8000文字）初期値：空を宣言すること。
    5. 処理５：変数名「done」（int型）を宣言し、初期値[false]とすること（終了判定用）。
    6. 処理６：カーソルとして「datCursor」を宣言し、下記のデータを取得すること。
      学生マスタ（student_mst）からデータを取得すること。
      カラム：学生NO（stundet_no）、学生名（student_kj）、テスト点数（test_su）
    7. 処理７：カーソルを開く。
    8. 処理８：
      1. 繰り返し処理：ラベル名「read_loop」
        1. 一行ずつレコードを読み出し「rec_student_no」「rec_student_kj」「rec_test_su」に代入する。
        2. 分岐処理
          • カーソルの読み出しが最後に達していたらループを抜ける
        3. 文字列結合する
          • 変数名「str_text」に、下記方法で文字列結合すること。
            • concat関数：concat([文字A],[文字B],[文字C])
              concat(str_text,rec_student_kj,’,’)
    9. 処理９：「str_text」の最終文字に「,（カンマ）」が残らないようにする。
    10. 処理１０：「str_text」を表示すること。
delimiter //

drop procedure if exists pro002_cursor;
create procedure pro002_cursor()
begin

    -- レコードから値を受け取るための変数宣言
    declare rec_student_no int default 0;
    declare rec_student_kj varchar(255) default '';
    declare rec_test_su int default 0;

    -- 表示用の変数（初期値は空白）
    declare str_text varchar(8000) default '';
   
    -- カーソルがデータセットの最後かどうかを判定するための変数宣言（done=false）
    declare done int default false;

    -- ここで取得したいデータセット用ＳＥＬＥＣＴ文を発行
    declare datCursor cursor for
        select
             sm.student_no
            ,sm.student_kj
            ,sm.test_su
        from student_mst sm;

    -- レコードの最終行に到達したかどうかを判定「done=true」に変更
    declare continue handler for not found set done = true;

    open datCursor;
        read_loop :loop

            -- 1行ずつレコードを読み出す
            fetch datCursor into rec_student_no, rec_student_kj, rec_test_su;

            -- カーソルからの読み出しが最後に達していればループを抜ける
            if done = true then
                leave read_loop;
            end if;

            set str_text = concat(str_text,rec_student_kj,',');
        
        end loop;
    close datCursor;

    set str_text = left(str_text, char_length(str_text) - 1);
    select str_text;
end;
//

delimiter ;

/* ストアドプロシージャを呼び出す */
call pro002_cursor();

＜演習＞

上記の例題のストアドプロシージャでは、引数の指定がなく、固定されたデータだけを表示します。

そこで、下記のような要件を満たす、プログラミングに変更してみましょう。

/*
テストの点数が、「intLower」以上「intUpper」以下の学生名の一覧データを取得したい
引数１：in intLower int
引数２：in intUpper int
*/
次のページへ