使用带游标的准备好的语句 [英] Using prepared statements with cursor

问题描述

我将游标声明放在准备好的语句中，然后执行它，然后返回一个错误 #1324 - Undefined CURSOR: getid.

我该如何解决这个问题?

<前>分隔符;;如果存在 test2，则删除程序；；创建过程 test2(table_id VARCHAR(25))开始DECLARE done INT DEFAULT FALSE；声明 id INT;声明 id_new INT;声明 stmt1 VARCHAR(1024);声明 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');从@sqltext1 准备 stmt1;执行 stmt1;设置@id_new = 1;打开 getid;将 getid 获取到 id 中；重复SET @sqltext2 := CONCAT('UPDATE ',table_id,' SET entryId = ? WHERE entryId = ?');从@sqltext2 准备 stmt2;执行 stmt2 使用 @new_id, id;SET @id_new = @id_new + 1;将 getid 获取到 id 中；直到完成结束重复；关闭 getid;结尾;;调用 test2('测试');

解决方案

一些规则:

1. 所有声明都必须在一个序列中的一个地方.
2. 不能在游标声明中使用变量名.
3. 处理程序声明必须在游标声明之后.
4. 不能使用局部变量名 (id) 作为绑定参数准备好的陈述.您只能使用 session 变量(比如 @_id).

为了解决此类问题，您可以采用以下解决方案.

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

以下示例应该适用于您的桌子.

分隔符 $$删除过程如果存在 test2$$创建过程 test2( table_id varchar(25) )开始set @temp_query = '如果存在临时表，则删除临时表 temp_cursor_table';从@temp_query 准备 pst；执行pst；下降准备 pst;-  或者-- 解除分配准备 pst；set @temp_table_query='创建临时表 temp_cursor_table ';set @temp_table_query=concat( @temp_table_query, ' select entryId from ' );设置@temp_table_query=concat(@temp_table_query, table_id);set @temp_table_query=concat( @temp_table_query, ' order by entryId' );从@temp_table_query 准备 pst；执行pst；下降准备 pst;-- 现在编写您的实际游标和更新语句-- 在一个单独的块中开始声明完成 int 默认为假；声明 id 整数；声明 id_new int;声明 stmt1 varchar(1024);声明 stmt2 varchar(1024);声明 getid 游标按 entryId 从 temp_cursor_table order 中选择 entryId；为未找到的集合声明继续处理程序 done = 1;设置@id_new = 1;打开 getid；将 getid 获取到 id 中；重复set @sqltext2 := concat('update', table_id);set @sqltext2 := concat( @sqltext2, ' set entryId = ? where entryId = ?' );设置@_id = id;从@sqltext2 准备 stmt2；使用@new_id、@_id 执行stmt2；设置@id_new = @id_new + 1;将 getid 获取到 id 中；直到完成结束重复；关闭 getid；结尾;结尾;$$分隔符；

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

调用 test2( 'Test' );

I put the cursor declaration in the prepared statement and then executed it, then returns an error #1324 - Undefined CURSOR: getid.

How do I solve this problem?

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');

解决方案

Some rules:

1. All declarations must be at one place in a sequence.
2. You can't use variable names in cursor declarations.
3. Handler declarations must be after cursor declarations.
4. You can't use local variable names (id) as bound parameters for prepared statements. You can only use session variables (say @_id).

To overcome such problems, you can adopt following solution.

1. Define a temporary table using the input parameter to the SP.
2. Now declare the cursor on the same table and use it.
3. Drop the temporary table created.

Following example should work on your tables.

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 ;

Now call the procedure with table_id value.

call test2( 'Test' );

这篇关于使用带游标的准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！