Example of Create Cursor in MySQL
A cursor is used to fetch row-by-row result sets from SQL for process the SQL data one by one. It is kind of loop on SQL statements.
Syntax:
-- Used for Declare the cursor
DECLARE cursor_name CURSOR FOR SELECT_statement;
-- Open cursor for use
OPEN cursor_name;
-- Fetch data one by one
FETCH cursor_name INTO variables list;
-- Close the cursor
CLOSE cursor_name;
Syntax for NOT FOUND Handler
-- Handle when no data found in cursor to close it.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Example of using Cursor in MySQL
--- Create procedure for update using cursor
DELIMITER //
CREATE PROCEDURE P_cursortest()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a CHAR(25);
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT address,id FROM test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO a, b;
IF done THEN
LEAVE read_loop;
END IF;
IF a = '' THEN
update test set address = '30 Huston' where id = b;
END IF;
END LOOP;
CLOSE cur1;
END;
//
DELIMITER
-- Sample Data having empty address column
mysql> select * from test;
+------+---------+------+---------------------+
| id | address | name | update_date |
+------+---------+------+---------------------+
| 3 | | RAM | 0000-00-00 00:00:00 |
| 3 | | SHAM | 0000-00-00 00:00:00 |
+------+---------+------+---------------------+
2 rows in set (0.00 sec)
mysql> call p_cursortest;
Query OK, 0 rows affected (0.02 sec)
-- Updated the address column
mysql> select * from test;
+------+-----------+------+---------------------+
| id | address | name | update_date |
+------+-----------+------+---------------------+
| 3 | 30 Huston | RAM | 0000-00-00 00:00:00 |
| 3 | 30 Huston | SHAM | 0000-00-00 00:00:00 |
+------+-----------+------+---------------------+
2 rows in set (0.00 sec)