Why does mysql not allow to fetch into user-defined variable in cursor?

Question

I have code like this and mysql post an error out :

The line : FETCH cur_recycle_batch INTO @id, @created_by, @created_date; post the error.

Does anyone know why mysql does not allow to fetch user-defined variable?

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_row`()
BEGIN

    DECLARE done TINYINT DEFAULT FALSE;
    DECLARE cur_recycle_batch CURSOR FOR SELECT 
                                                id, created_by, created_date
                                            FROM
                                                outsourcing_recycle_batch
                                            order by created_date;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START transaction;

        OPEN cur_recycle_batch;

            insert_loop : loop

                FETCH cur_recycle_batch INTO @id, @created_by, @created_date;

                IF done THEN

                    LEAVE insert_loop;
                END IF;

                INSERT INTO `test_db`.`outsourcing_recycle_batch_history_list`
                (`outsourcing_recycle_batch_pk`,
                `outsourcing_history_pk`,
                `created_by`,
                `created_by_full_name`,
                `created_date`)
                VALUES
                (@id,
                1,
                @created_by,
                'AAAAAAAA',
                @created_date);


            END LOOP;

        CLOSE cur_recycle_batch;

    commit;

END

Answer 1

There's a workaround: DECLARE the local variables instead of relying on user variables.

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_row`()
BEGIN
    DECLARE local_id INT;
    DECLARE local_created_by VARCHAR(20);
    DECLARE local_created_date DATE;
    DECLARE done TINYINT DEFAULT FALSE;
    DECLARE cur_recycle_batch CURSOR FOR SELECT id, created_by, created_date
                                            FROM outsourcing_recycle_batch
                                            ORDER BY created_date;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    START transaction;
        OPEN cur_recycle_batch;
            insert_loop : loop
                FETCH cur_recycle_batch INTO local_id, local_created_by, local_created_date;
                IF done THEN
                    LEAVE insert_loop;
                END IF;
                INSERT INTO `test_db`.`outsourcing_recycle_batch_history_list`
                (`outsourcing_recycle_batch_pk`,
                `outsourcing_history_pk`,
                `created_by`,
                `created_by_full_name`,
                `created_date`)
                VALUES
                (local_id,
                1,
                local_created_by,
                'AAAAAAAA',
                local_created_date);
            END LOOP;
        CLOSE cur_recycle_batch;
    commit;
END

Given this workaround, it must have never been a priority to implement FETCH INTO user variables.

There's an even easier workaround, given your example: Don't use a cursor at all, just use INSERT...SELECT to insert a set of rows.

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_row`()
BEGIN
    INSERT INTO `test_db`.`outsourcing_recycle_batch_history_list`
        (`outsourcing_recycle_batch_pk`,
        `outsourcing_history_pk`,
        `created_by`,
        `created_by_full_name`,
        `created_date`)
    SELECT id, 1, created_by, 'AAAAAAAA', created_date
    FROM outsourcing_recycle_batch;
END

I don't know if you have simplified your example. You might need to use a cursor to do other row-by-row manipulation of the values, instead of simply inserting.