在存储过程的循环中使用游标

发布于 2024-09-09 04:16:06 字数 752 浏览 6 评论 0原文

为了使用 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;

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

提前致谢

原文

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;

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

Thanks In advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

赠佳期 2024-09-16 04:16:06

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

如果您仍然需要将光标嵌套在循环中，您的代码应如下所示：

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;

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;

回复收藏 0 原文

~没有更多了~