単元概要

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

解説

CURSORの使い方

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

delimiter //

drop procedure if exists [プロシージャ名];
create procedure [プロシージャ名](in/out [引数名1] [型],in/out [引数名2] [型])
begin
    declare [値を受ける変数名] int;
    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. 処理2:変数名「rec_studnet_kj」(varchar型,255文字)を宣言すること。
    3. 処理3:変数名「rec_test_su」(int型)を宣言すること。
    4. 処理4:変数名「str_text」(varchar型,8000文字)を宣言し、初期値[”(空)]とすること。
      New!defaultは、初期値を設定します。
    5. 処理5:変数名「done」(int型)を宣言し、初期値[false]すること(終了判定用)。
    6. 処理6:カーソルとして「datCursor」を宣言し、下記のデータを取得すること。
      学生マスタ(student_mst)からデータを取得すること。

      カラム:学生NO(stundet_no)、学生名(student_kj)、テスト点数(test_su)
    7. 処理7:カーソルを開く。
    8. 処理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. 処理9:「str_text」の最終文字に「,(カンマ)」が残らないようにする。
    10. 処理10:「str_text」を表示すること。
delimiter //

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

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

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

    -- ここで取得したいデータセット用SELECT文を発行
    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」以下の学生名の一覧データを取得したい
引数1:in intLower int
引数2:in intUpper int
*/