Creating cursor with Dynamic SQL in MySQL
Create an another Procedure and write the code of cursor in this new procedure and then call the procedure from where u want to declare a cursor...
It's not allowed a DEFINE cur CURSOR FOR prepared_statement, you must define a valid SQL statement. The good news is that you can define the cursor on a view that can be dynamically created later. For example...
DROP PROCEDURE IF EXISTS my_dynamic_proc;
DELIMITER //
CREATE PROCEDURE my_dynamic_proc(tablename varchar(64), fieldname varchar(64), country VARCHAR(64))
BEGIN
DECLARE adr_value varchar(500);
DECLARE done BOOLEAN DEFAULT FALSE;
-- Cursor definition
DECLARE cur1 CURSOR FOR SELECT address FROM tmp_view_address;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Dynamic view definition and creation
SET @v = concat('CREATE OR REPLACE VIEW tmp_view_address as SELECT `',fieldname,'` as address FROM ',tablename,' WHERE country_name = "',country,'" group by 1 order by count(1) desc');
PREPARE stm FROM @v;
EXECUTE stm;
DEALLOCATE PREPARE stm;
-- Open cursor
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO adr_value;
IF done THEN
LEAVE read_loop;
END IF;
-- Basic output result
SELECT concat("My address is ",adr_value);
-- Use every result in a dynamic update
SET @u = concat('update ',tablename,' set new_field_address = "',adr_value,'" where country_name = "',country,'" and new_field_address is null');
PREPARE stm FROM @u;
EXECUTE stm;
DEALLOCATE PREPARE stm;
END LOOP;
CLOSE cur1;
END//
DELIMITER ;