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)