Cursors in MySQL

To work with multiple records, you can use cursors. We can handle each row at a time using cursors.

Key things to know about cursor

• MySQL cursor is read-only, non-scrollable.
• MySQL cursor is asensitive. It means that cursor works on actual table data. If any other user changes the data, those changes are reflected in the cursor as well.

Creating a new cursor in MySQL

-Declare the cursor using below syntax. The cursor declaration shoud be done after other variables are declared.

 
DECLARE mycursor CURSOR FOR SELECT_statement;

-Then open the cursor using below syntax.

 
OPEN mycursor;

-Fetch one row at a time using below syntax.

 
FETCH mycursor INTO v1, v2;

-At the end, you need to close the cursor using below syntax.

 
CLOSE mycursor;

When you try to fetch the rows from the empty cursor, you get an error. So to handle this error, we need to define NOT FOUND handler using below syntax.

 

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET completed = 1;

-- Example on the cursor

DELIMITER $$

CREATE PROCEDURE getCustdata (INOUT data varchar(1000))
BEGIN

DECLARE completed INTEGER DEFAULT 0;
DECLARE tempdata varchar(50) DEFAULT “”;

DEClARE mycursor CURSOR FOR
SELECT FirstName FROM CUSTOMER;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET completed = 1;

OPEN mycursor;

myloop: LOOP

FETCH mycursor INTO tempdata;

IF completed = 1 THEN
LEAVE myloop;
END IF;

SET data = CONCAT(tempdata,”;”,data );

END LOOP myloop;

CLOSE mycursor;

END$$

DELIMITER ;

You can test your cursor using below statements.

 

SET @custdata = “”;

CALL getCustdata(@custdata);

SELECT @custdata;