MySQL Cursor with Examples

A cursor is an iterator. They are used inside stored procedures to iterate through the result set.

The cursors have the following characteristics

• Cursors are Read-only, cannot be used to update records inside tables in the select clause.
• We can use them only in the forward direction in the order of records returned in the result set.We cannot use them to go either in both directions i.e. backward and forward or to jump or skip a few records.
• MySQL cursors are asensitive i.e. they work with a temporary copy of the data.

Cursor Usage in MySQL

To use a cursor inside a stored procedure we do the following

• Begin the stored procedure and declare any variables inside the stored procedure.
• Create a new cursor using the syntax:

DECLARE <cursor_name> CURSOR FOR <select_statement>;

Open cursor in MySQL

Open i.e. initialize the cursor using the Open statement as:

OPEN <cursor-name>;

Fetch cursor in MySQL

Retreive records using the FETCH statement

FETCH <cursor_name> INTO <variable-names>;

After every Fetch it is a good idea to check if next row exists. To do so we do:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

This ensures that if there are no more records, the cursor will not attempt to iterate through it and not fail.

Close cursor in MySQL

Close the cursor once done

CLOSE <cursor-name>;

Example of a cursor is as follows:

A stored Procdeure with a cursor for getting all the full names of employees.

DELIMITER $$
CREATE PROCEDURE getFullName (  INOUT fullNameList varchar(4000)
)
BEGIN
  DECLARE finished INTEGER DEFAULT 0;
  DECLARE fullName varchar(100) DEFAULT "";
   #Cursor declaration
      DEClARE curName
        CURSOR FOR
             SELECT concat(first_name ,' , ' , last_name) FROM employees LIMIT 10;
               #declare NOT FOUND handler
               DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    #Open cursor
               OPEN curName;
    #fetch records
               getName: LOOP
                              FETCH curName INTO fullName;
                              IF finished = 1 THEN LEAVE getName;
                              END IF;
                              SET fullNameList = CONCAT(fullName,";",fullNameList);
               END LOOP getName;
               CLOSE curName;
END$$
DELIMITER ;

To see the output, we would do:

SET @fullNameList = "";

CALL getFullName(@fullNameList);

SELECT @fullNameList;