在游标循环中捕获创建的 ID

发布于 2024-10-07 05:34:43 字数 426 浏览 4 评论 0原文

我有以下光标（无论如何都是类似的东西），我用它来复制属于多对多关系的表的条目。我需要在 INSERT INTO B 语句期间生成的新 ID 来更新连接表。

DECLARE

BEGIN
   FOR rec IN (SELECT id
                 FROM A
                WHERE group_id = 7)
   LOOP
      INSERT INTO B (b_id, thing2, ...stuff...);

      INSERT INTO C (rec.id, /* new ID generated by previous insert statement */
      COMMIT;
   END LOOP;
END;
/

如何捕获插入后的 b_id 值以在第二个 INSERT 语句中使用？

原文

I have the following cursor (something like it anyways) that I am using to copy entries for a table that is part of a many to many relationship. I need the new IDs that are generated during the INSERT INTO B statement to update a junction table with.

DECLARE

BEGIN
   FOR rec IN (SELECT id
                 FROM A
                WHERE group_id = 7)
   LOOP
      INSERT INTO B (b_id, thing2, ...stuff...);

      INSERT INTO C (rec.id, /* new ID generated by previous insert statement */
      COMMIT;
   END LOOP;
END;
/

How do I capture the b_id value after the insert to use in the second INSERT statement?

分享到QQ

分享到微博

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

发布评论

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

战皆罪 2024-10-14 05:34:43

使用 RETURNING 子句：

DECLARE

  v_bid B.b_id%TYPE;

BEGIN
   FOR rec IN (SELECT id
                 FROM A
                WHERE group_id = 7)
   LOOP
     INSERT INTO B (b_id, thing2, ...stuff...) RETURNING b_id INTO v_bid;

     INSERT INTO C (rec.id, /* new ID generated by previous insert statement */
     COMMIT;
   END LOOP;
END;

也就是说，它是可能可以在不使用游标的情况下执行此操作。

Use the RETURNING clause:

DECLARE

  v_bid B.b_id%TYPE;

BEGIN
   FOR rec IN (SELECT id
                 FROM A
                WHERE group_id = 7)
   LOOP
     INSERT INTO B (b_id, thing2, ...stuff...) RETURNING b_id INTO v_bid;

     INSERT INTO C (rec.id, /* new ID generated by previous insert statement */
     COMMIT;
   END LOOP;
END;

That said, it's possible this operation could be performed without the use of a cursor.

回复收藏 0 原文

~没有更多了~