MySQL Cursor

Here we are going to describe about the MySQL cursor and how to declare, open, fetch and close it.

MySQL Cursor

MySQL Cursor

     

Here we are going to describe about the MySQL cursor and how to declare, open, fetch and close it. Cursor can be created inside the stored procedures, functions and triggers. Cursors are used for rows iteration returned by a query on a row-by-row basis. It is different from typical SQL commands that operate on all the rows in the set returned by a query at one time.

 

 

 

 

 

There are following types of properties of MySQL cursor:

  1. Asensitive : Server may or may not make a copy of result table.
  2. Read only:  Can not be updated.
  3. Non-scrollable: Can traverse only in one direction and can not skip rows.

Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.

 To create a cursor you must be familiar with the following statements:

  1. Cursor declare
  2. Cursor open statement
  3. Cursor fetch statement
  4. Cursor close statement  

1. Cursor declaration: To declare a cursor you must use the DECLARE statement.

Syntax:

DECLARE cursor_name cursor for select_statement;   

If you want to use the multiple cursor then the cursor name must have an unique name and each cursor have specified block. 

2. Cursor open statement: To open the cursor you must use the OPEN statement. If you want to fetch rows from it then you must open the cursor. 

Syntax:

OPEN cursor_name;  

3. Cursor fetch statement: If you want to retrieve next row from the cursor and move the cursor to next row then you need to fetch the cursor.  

Syntax:

FETCH cursor_name INTO var_name;

If a row exists, then the above statement fetches the next row and cursor pointer moves ahead to the next row. If no more data left "no data condition" with SQLSTATE value 02000 occurs. 

4. Cursor close statement: This statement is used to close the opened cursor.

Syntax:

CLOSE cursor_name;  

 

Cursor Example:  

student table:

Declare, open, fetch and close the Cursor:

DELIMITER //  
CREATE FUNCTION student_list() RETURNS VARCHAR(255) 
  
BEGIN    
  
DECLARE record_not_found INTEGER DEFAULT 0; 
  
DECLARE student_name VARCHAR(50) DEFAULT ""; 
  
DECLARE stu_list VARCHAR(255) DEFAULT ""; 
   
DECLARE my_cursor CURSOR FOR SELECT studentName FROM student;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;     
  
OPEN my_cursor;    
  
allStudents: LOOP 
  
FETCH my_cursor INTO student_name; 
  
IF record_not_found THEN 
  
LEAVE allStudents; 
  
END IF; 
  
SET stu_list = CONCAT(stu_list,", ",student_name); 
  
END LOOP allStudents;    
   
CLOSE my_cursor;    
  
RETURN SUBSTR(stu_list,3); 
  
END 
  
//    
DELIMITER ;  
SELECT student_list() AS Cities;
DROP FUNCTION student_list;

 Output:

  0