我正在学习存储过程,mysql中的游标,我偶然发现它：
delimiter //
CREATE PROCEDURE some_func()
BEGIN
DECLARE link_rewrite VARCHAR(255);
DECLARE link_rewrite_cursor CURSOR FOR SELECT link_rewrite FROM prod;
OPEN link_rewrite_cursor;
SET @count = 0;
WHILE @count < 10 DO
FETCH link_rewrite_cursor INTO link_rewrite;
SELECT link_rewrite;
set @count = @count + 1;
END WHILE;
CLOSE link_rewrite_cursor;
END//
delimiter ;
我的问题是：为什么SELECT link_rewrite总是返回NULL(在prod表中有9000行). SELECT link_rewrite FROM prod返回很多行(9000行).
最佳答案
您应避免对多个不同的事物使用相同的名称.具体来说,为变量指定与您选择的列不同的名称.例如,如果重命名变量v_link_rewrite,那么它可能会起作用：
delimiter //
DROP PROCEDURE IF EXISTS some_func //
CREATE PROCEDURE some_func()
BEGIN
DECLARE v_link_rewrite VARCHAR(255);
DECLARE link_rewrite_cursor CURSOR FOR SELECT link_rewrite FROM prod;
OPEN link_rewrite_cursor;
SET @count = 0;
WHILE @count < 10 DO
FETCH link_rewrite_cursor INTO v_link_rewrite;
SELECT v_link_rewrite;
set @count = @count + 1;
END WHILE;
CLOSE link_rewrite_cursor;
END//
delimiter ;