在存储过程的循环中使用Cursor

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:

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

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;

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

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

Thanks In advance

提前致谢

1 个解决方案

#1

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.

您可以通过将光标嵌套在BEGIN ... END块中来执行您想要的操作。有关更多详细信息，请参阅Roland Bouman的“嵌套MySQL游标循环”一文。值得注意的是，他对这种技术的评论通常是不必要的，因为通常可以重写查询而不必执行嵌套游标。

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;

#1

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

1 个解决方案

#1

#1

相关文章