当前的 MYSQL 游标

Question

*DELIMITER //
create procedure test(OUT l_out INT)
begin
DECLARE done INT DEFAULT FALSE;
declare l_sno INT default 0;
declare a INT default 0;
declare b INT default 0;
declare cur_1 cursor for select sno,interest from temp t  where x_coord between 55 and 60 for update of interest;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur_1; 
read_loop: LOOP
fetch cur_1 into a,b;
if done then
    Leave read_loop;
end if;
set l_sno=l_sno+1;
update temp set interest =1 where CURRENT OF cur_1;
END LOOP;
close cur_1;
set l_out=l_sno;
end //
;*

错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of interest; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open cur_1;' at line 7

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OF cur_1; END LOOP; close cur_1; set l_out=l_sno; end' at line 16

Answer 1

在我看来，您好像来自不同类型的 SQL，例如 DB2 —— 那些有用的东西。

引自 http://dev.mysql.com/doc/refman/5.0/en/cursor-restrictions.html

Cursors are read only; you cannot use a cursor to update rows.

UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF are not implemented, because updatable cursors are not supported.

所以我猜你不能那样做，你必须指定一个类似于 mysql 的 where 子句。