MySQL Cursor

Syntax:

<i>DECLARE cursor_name cursor for select_statement;</i>

<i>Example:</i>


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;