Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Cursor

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 240
    Comment on it

    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//
    
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: