Apparently the choice returns NULL

Question

I study stored procedures, cursors in mysql, and I stumble upon it:

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 ;

My question is: why does SELECT link_rewrite always return NULL (there are 9,000 rows in the prod table). SELECT link_rewrite FROM prod returns many lines (9000 lines).

Answer 1

You cannot use the same name for several different things. In particular, give the variable a different name than the column of your choice. For example, if you rename the variable v_link_rewrite, then it will probably work:

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 ;

Answer 2

If you just want to select the top 10 lines, do the following:

select link_rewrite from prod limit 10

It is much faster and you do not need to go with the cursor.