How to use Cursor in MySQL

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:

ID	StudentName	address
1	Vinod	        Rohini,Delhi
2	Ravi	        Lucknow,Up
NULL	NULL	        NULL

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:

ID	StudentName	address
1	Vinod	        Rohini,Delhi