In MySQL, to handle a result set inside a procedure we use cursors. Cursors are:

• Read only
• Non-scrollable
• Asensitive

Cursors can be used in stored procedure, stored functions and triggers.

Syntax:

Declare cursor:

Declare cursor_name cursor for select_statements;

Open the Cursor:

Open cursor_name;

Fetch Cursor:

Fetch cursor_name into variables list;

Close Cursor:

Close cursor_name;

For Example:

create procedure name_list(inout list varchar(4000))
    -> begin
    -> declare finish integer default 0;
    -> declare sname varchar(100) default "";
    -> declare name_cursor cursor for
    -> select name from student;
    -> declare continue handler for not found set finish=1;
    -> open name_cursor;
    -> get_name:loop
    -> fetch name_cursor into sname;
    -> if(finish=1) then
    -> leave get_name;
    -> end if;
    -> set list=concat(sname," ",list);
    -> end loop get_name;
    -> close name_cursor;
    -> end//