mySQL Cursor

the MySQL cursor is used to handle a result-set inside a stored procedure.a cursor is used to iterate through a set of rows returned by a query and process each row.

you can use mysql cursors in stored procedures, stored functions and triggers.

mysql is read only, non-scrollable and asensitive.

  • read only : cursor are not updatable.
  • not scrollable: cursors can be traversed only in one direction, forward, and you can't                           skip records from fetching.
  • asensitive: once open, the cursor will not reflect changes in its source tables.in fact,                     mysql does not guarantee the cursor will be updated, so you can't rely on it.

the  declare  statement is used to define the cursor

syntax:

          declare     cursor_name        cursor  for    select_statement;

a cursor in MySQL must always be associated with a select statement.

the cursor declaration must be after any variable declaration

if you declare a cursor before variables declaration statement, mysql will generate an error.

next you open the cursor by using the  open  statement.the open statement initializes the result-set for the cursor.

syntax:

          open     cursor_name ;

the  fetch  statement to retrieve the next row pointed by the cursor and move the cursor to the next row in the result-set.

MySQL Cursor syntax:

          fetch     cursor_name     into   variables list;

you use the  close  statement to deactivate the cursor and release the memory associated with it.

syntax:

          close     cursor_name ;

note : if you use MySQL cursor, you must also declare a NOT FOUND handler to handle the situation when the cursor could not find any row.Because each time you call the FETCH statement, the cursor attempts to read the next row in the result set.When the cursor reaches the end of the result-set, it will not be able to get the data, and a condition is raised.The handler is used to handle this condition.

syntax:

          declare   continue   handler      for   not  found       set     finished=1 ;

the graphical representation how mysql cursor works:


mySQL Cursor example

   
      delimiter//
          
           create procedure  'cursor_work'  (out  param  in)
               
      begin
               
            declare  a, b, c int;
                            
            declare  cur1  cursor  for  select   col1    from   table1;
                            
            declare    continue   handler   for   not  found   set  b = 10 ;
                            
                            open  cur1
                            
                                        set  b = 0 ;
                                        
                                        set  c = 0 ;
                                        
                            while  b = 0      do
                            
                            fetch   cur1   into  a ;
                                               
                                        if b = 0            then
                                               
                                              set  c = c+a;
                                                           
                                        end  if;
                                               
                            end  while;
                                               
                            close cur1;
                                               
                                  set  param1 = c;
                                               
      end//