I am trying to make my first cursor in MySQL and I am receiving an error. It says incorrect integer value. I was thinking this would grab the value in row one from column customer_Id, and store it into the IdValue variable. How do I code this correctly and fix this error?
CREATE PROCEDURE CursorProcedure()
DECLARE IdValue int;
DECLARE myCursor CURSOR FOR
SELECT customer_Id FROM customers;
FETCH myCursor INTO IdValue;
DROP PROCEDURE IF EXISTS CursorProcedure; DELIMITER $$ CREATE PROCEDURE CursorProcedure() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE IdValue int; DECLARE myCursor CURSOR FOR SELECT customer_Id FROM customers; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN myCursor; read_loop: LOOP FETCH myCursor INTO IdValue; IF done THEN LEAVE read_loop; END IF; -- -- YOU ARE IN YOUR READ LOOP -- DO SOMETHING WITH IT HERE -- END LOOP; CLOSE myCursor; END$$ DELIMITER ;
But in the "do something with it here", don't do a SELECT on it because it will generate multiple result sets. Do something meaningful.
Manual page on Cursors.
As an aside, cursors are rarely your friends. They are extremely slow. Use them in dire emergencies only.