在存储过程的循环中使用Cursor [英] Using Cursor in a Loop of a stored procedure

问题描述

为了使用MySQL动态使用游标，是否可以在存储过程的循环中声明游标？
我试过并得到一个错误：

So as to use cursors dynamically using MySQL is it possible to declare a cursor in a loop of a stored procedure? I've tried and got an error:

increment: LOOP
DECLARE cur1 CURSOR FOR SELECT person_id, publication_id  FROM p_publication WHERE person_id = new_count;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
    FETCH cur1 INTO pub_id, per_id;
IF NOT done THEN
          INSERT INTO test.t2 VALUES (pub_id, per_id);
    END IF;
SET new_count = new_count + 1;
  UNTIL done END REPEAT;

     CLOSE cur1;
IF !(new_count < old_count ) THEN LEAVE increment;
END IF;
END LOOP increment;

您的SQL语法有错误;请检查与您的MySQL
服务器版本对应的手册，以获取正确的语法在第12行使用DECLARE cur1 CURSOR FOR SELECT person_id，
publication_id FROM person_has_public。

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 'DECLARE cur1 CURSOR FOR SELECT person_id, publication_id FROM person_has_public' at line 12

感谢提前

推荐答案

光标位于 BEGIN ... END 块中。请参阅 Roland Bouman的嵌套MySQL游标循环文章更多细节。可能值得注意的是，他对这种技术的评论通常是不必要的，因为通常可以重写查询，而不必执行嵌套游标。

You can do what you want by nesting the cursor in a BEGIN ... END block. See Roland Bouman's "Nesting MySQL Cursor Loops" article for more details. It may be worth noting his comments about this technique often being unnecessary, as it is often possible to rewrite the query instead of having to perform a nested cursor.

需要将光标嵌套在一个循环中，代码应该看起来像这样：

Should you still need to nest your cursor in a loop, your code should look something like this:

increment: LOOP
    block_cursor: BEGIN
        DECLARE cur1 CURSOR FOR SELECT person_id, publication_id  FROM p_publication WHERE person_id = new_count;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN cur1;
        REPEAT
            FETCH cur1 INTO pub_id, per_id;
            IF NOT done THEN
                INSERT INTO test.t2 VALUES (pub_id, per_id);
            END IF;
            SET new_count = new_count + 1;
        UNTIL done END REPEAT;
        CLOSE cur1;
        IF !(new_count < old_count ) THEN
            LEAVE increment;
        END IF;
    END block_cursor;
END LOOP increment;

这篇关于在存储过程的循环中使用Cursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！