显式打开和关闭游标

发布于 2024-08-07 18:55:32 字数 120 浏览 11 评论 0原文

我一直在阅读数据库游标,我看到的每一个示例代码都显式地打开和关闭游标。我只使用过几次,而且从来不需要这样做。谁能告诉我为什么有必要这样做?我知道如果不关闭游标,可能会造成内存泄漏,但我从来没有打开过游标。

谢谢

I've been reading up on database cursors, and every bit of sample code I've seen explicitly opens and closes the cursor. I've only used them a few times and I've never had to do this. Can anyone tell me why it is necessary to do this? I know if you don't close a cursor you can create memory leakes but i've never had to open one.

Thanks

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

吲‖鸣 2024-08-14 18:55:32

您只需打开和关闭显式游标。 隐式游标的优点是由Oracle自动管理。隐式游标示例:

DECLARE
   l_dept dept%rowtype;
BEGIN
   -- implicit SELECT INTO
   SELECT * INTO l_dept FROM dept WHERE deptno = :deptno;
   -- implicit LOOP
   FOR cc IN (SELECT emp.* FROM emp WHERE deptno = l_dept.deptno) LOOP
      dbms_output.put_line('emp='||cc.empno);
   END LOOP;
END;
/

隐式游标在代码中更加简洁,您不必担心关闭它们。我还发现实际使用光标的代码更清晰。我很少使用显式游标,只有当它们可以在包中的许多地方重用时(那么为什么不将其放在单个过程中呢?)。

you only have to open and close explicit cursors. Implicit cursors have the advantage of being managed automatically by Oracle. Example of implicit cursors:

DECLARE
   l_dept dept%rowtype;
BEGIN
   -- implicit SELECT INTO
   SELECT * INTO l_dept FROM dept WHERE deptno = :deptno;
   -- implicit LOOP
   FOR cc IN (SELECT emp.* FROM emp WHERE deptno = l_dept.deptno) LOOP
      dbms_output.put_line('emp='||cc.empno);
   END LOOP;
END;
/

Implicit cursors are more concise in the code, you don't have to worry about closing them. I also find it clearer to have the code of the cursor where it is actually used. I seldom use explicit cursor, and only if they can be reused in many places in a package (then why not put it in a single proc?).

溺渁∝ 2024-08-14 18:55:32

如果您的游标是全局的(我猜您使用了本地游标,这不是问题,因为它们超出范围时会关闭),您必须显式关闭它们。

不关闭全局游标存在几个问题

  • 用于游标的内存未释放
  • 可以打开的游标有最大数量。最终您将无法创建另一个游标,因为您将打开最大数量的游标。

如果您问为什么我必须使用游标?
- 有时您需要循环遍历行(例如使用常规的 for 循环)。你不能在面向集合的方法中做到这一点 - 你必须使用游标。

If your cursors are global(I guess you used local cursors where this is not a problem, because they are closed when they get out of scope) you must explicitly close them.

There are several problems about not closing global cursors

  • The memory used for the cursor is not deallocated
  • There is a maximum number of cursors you can have opened. Eventually you would not be able to create another one, because you will have the maximum number of cursors opened.

If you are asking why do I have to use cursors?
- Sometimes you need to loop through the rows (like using a regular for loop). You can not do that in the set oriented approach - you have to use cursors.

趁微风不噪 2024-08-14 18:55:32

这是一个很好的做法,因为您可以轻松获取任何特定查询(ROWCOUNT、NOTFOUND 等)的 SQL 状态,无论您同时是否运行了其他查询。此外,您还可以在包中重用游标、创建 ROWTYPE 的数据类型、对它们进行循环以及各种好东西!

This is quite a good practice, as you can easily get the SQL status of any particular query (ROWCOUNT, NOTFOUND etc), whether or not you have run other queries in the meantime. Also, you can reuse your cursors within a package, create data types of their ROWTYPE, make loops over them, and all kinds of good stuff!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文