MySQL Cursors exist and are set to traverse in datasets, returned by query, and process the data accordingly. However CURSOR is a read only and data cannot be updated. Rows can be traversed in a single direction only. Servers can make a temporary copy of data called insensitive cursor or cursor can point to the actual data itself called insensitive cursor.

NOTE: Cursor is one of the components of stored procedures, stored functions and triggers.

Cursors have 4 commands:

  • DECLARE syntax,
  • OPEN syntax,
  • FETCH syntax,
  • CLOSE syntax.

DECLARE Cursor

Initially, a cursor has to be declared first. A cursor declaration has to be done after variable declaration, else MYSQL throws an error. Cursor has to be associated with SELECT statements. Syntax for declaring cursor is shown below:

DECLARE cursorName CURSOR FOR statements;

OPEN cursor

OPEN statement is used to open cursor and initialize the result set for the cursor. OPEN statements are called before fetching results from result sets. Syntax for OPEN cursor is:

OPEN cursorName;

FETCH cursor

This statement fetches the next row in a result set and moves the cursor to next row of result set. The fetched rows are stored in named variables. Hence, the number of rows fetched must be equal to the number of output variables in FETCH statements.

FETCH cursorName INTO outputVariables;

CLOSE cursor

This statement deactivates the cursor and releases the memory associated with it. Syntax for CLOSE cursor is:

CLOSE cursorName;

Flow of MySQL CURSOR declaration and usage is:

  1. Firts it's declared.
  2. Cursor is initialized using OPEN syntax.
  3. Result set is fetched using FETCH sytnax.
  4. If there is no result set, it is closed using CLOSE syntax.

When cursor reaches the end of result set, a declaration for NOT FOUND is executed. The example for NOT FOUND is:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET resultCount = 0;

The variable resultCount is made 0 when there are no results to return.

The example of a full MySQL cursor is given below. This example reads all the names from tblPatients database and gives the list as space separated values.

Example

The example of a full MySQL cursor:

DELIMITER //

CREATE PROCEDURE curdemo(INOUT userNames VARCHAR(1000))

BEGIN

   DECLARE done INT DEFAULT 0;

   DECLARE temp VARCHAR(100)DEFAULT "";

   DECLARE curName CURSOR FOR

      SELECT NameOfPatient FROM tblPatients;

   DECLARE CONTINUE HANDLER FOR

      NOT FOUND SET done = 1;

   OPEN curName;

   read_loop: LOOP;

   FETCH curName INTO temp;

   IF done

      THEN LEAVE read_loop;

   END IF;

   SET userNames = CONCAT(temp," ",userNames);

   END LOOP read_loop;

   CLOSE curName;

END //

DELIMITER ;

 

SET @names="";

CALL curdemo(@names);

SELECT @names;

This example outputs the NameOfPatient column of the tblpatients table separated by ' '.