声明游标时使用变量

Question

delimiter ;;

drop procedure if exists reset_id;;

create procedure reset_id(table_id VARCHAR(25))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE id_new INT;
    DECLARE getid CURSOR FOR SELECT entryId FROM table_id ORDER BY entryId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @id_new = 1; 

    OPEN getid;

    FETCH getid into id;
    REPEAT
        UPDATE table_id SET entryId = @id_new WHERE entryId = id;
        SET @id_new = @id_new + 1;
        FETCH getid into id;
    UNTIL done END REPEAT;
    CLOSE getid;
END
;;

CALL reset_id('Test');

修改过程后，仍然返回错误#1324-未定义的游标：getid。我如何解决这个问题？

delimiter ;;

drop procedure if exists test2;;

create procedure test2(table_id VARCHAR(25))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT;
    DECLARE id_new INT;
    DECLARE stmt1 VARCHAR(1024);
    DECLARE stmt2 VARCHAR(1024);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @sqltext1 := CONCAT('DECLARE getid CURSOR FOR SELECT entryId FROM ',table_id,' ORDER BY entryId');
    PREPARE stmt1 FROM @sqltext1;
    EXECUTE stmt1;

    SET @id_new = 1; 

    OPEN getid;

    FETCH getid into id;
    REPEAT
        SET @sqltext2 := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');
        PREPARE stmt2 FROM @sqltext2;
        EXECUTE stmt2 USING @new_id, id;
        SET @id_new = @id_new + 1;
        FETCH getid into id;
    UNTIL done END REPEAT;
    CLOSE getid;
END
;;

CALL test2('Test');

Answer 1

必须在SQL文本中指定表名；它不能是变量。

为了完成您正在尝试的操作，您将需要动态创建一个包含要执行的SQL文本的字符串。

从任意字符串准备语句：

SET @sqltext := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');
PREPARE stmt FROM @sqltext;

EXECUTE stmt USING @new_id, @id;

完成语句后,在循环之后,最佳实践是释放语句,如下所示：

DEALLOCATE PREPARE stmt;

注：

表名不是变量的限制实际上适用于SQL语句中的所有标识符，包括表名、视图名、列名、函数名等。这些都必须是SQL文本中的文字，就像保留的关键字一样。