为什么mysql不允许在游标中读取用户自定义变量？

Question

我有这样的代码，mysql发布了一个错误：

这一行：FETCH cur_recycle_batch INTO @id, @created_by, @created_date;发布了错误。

有人知道为什么mysql不允许获取用户定义的变量吗？

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

有一个解决办法：DECLARE局部变量，而不是依赖于用户变量。

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

考虑到这种变通方法，实现FETCH to user变量肯定不是一个优先事项。

对于您的示例，还有一个更简单的解决方法:根本不使用游标，只需使用INSERT...SELECT插入一组行。

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

我不知道你是否简化了你的例子。您可能需要使用游标对值进行其他逐行操作，而不是简单地插入。