使用带有游标的准备语句

Question

我将游标声明放在准备好的语句中，然后执行它，然后返回一个错误#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

一些规则：

1. 所有声明必须在序列中的一个位置
2. 不能在游标声明中使用变量名
3. 处理程序声明必须在游标声明之后
4. 不能将局部变量名（id）用作已准备语句的绑定参数。只能使用会话变量（比如@\u id）

为了克服这些问题，您可以采用以下解决方案。

1. 使用SP的输入参数定义一个临时表。
2. 现在在同一个表上声明光标并使用它
3. 删除创建的临时表

下面的例子应该适用于您的表格。

delimiter $$

drop procedure if exists test2$$

create procedure test2( table_id varchar(25) )
begin
  set @temp_query = 'drop temporary table if exists temp_cursor_table';
  prepare pst from @temp_query;
  execute pst;
  drop prepare pst; -- or
  -- deallocate prepare pst;

  set @temp_table_query='create temporary table temp_cursor_table ';
  set @temp_table_query=concat( @temp_table_query, ' select entryId from ' );
  set @temp_table_query=concat( @temp_table_query, table_id );
  set @temp_table_query=concat( @temp_table_query, ' order by entryId' );

  prepare pst from @temp_table_query;
  execute pst;
  drop prepare pst;

  -- now write your actual cursor and update statements
  -- in a separate block
  begin
    declare done int default false;
    declare id int;
    declare id_new int;
    declare stmt1 varchar(1024);
    declare stmt2 varchar(1024);

    declare getid cursor for  
              select entryId from temp_cursor_table order by entryId;
    declare continue handler for not found set done = 1;

    set @id_new = 1; 

    open getid;
    fetch getid into id;
    repeat
      set @sqltext2 := concat( 'update ', table_id );
      set @sqltext2 := concat( @sqltext2, ' set entryId = ? where entryId = ?' );
      set @_id = id;
      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;
end;
$$

delimiter ;

现在调用具有table_id值的过程。

call test2( 'Test' );