当前位置：文江博客话题详情

Oracle 中游标的生命周期

发布于 2024-09-04 14:03:13 字数 70 浏览 2 评论 0原文

如果我打开一个游标并忘记关闭它，该游标会在 Oracle 中存在多久？

是否有一些设置可以配置光标的生命周期？

原文

分享到QQ

分享到微博

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

发布评论

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

剧终人散尽 2024-09-11 14:03:13

以及 DCookie 的答案：

游标生命周期的一般模式是，

OPEN 
BIND
EXECUTE
FETCH...FETCH...FETCH
BIND
EXECUTE
FETCH...FETCH...FETCH
...
CLOSE

可以通过将新变量绑定到它并重新执行它来重新使用游标。

PL/SQL 还将维护游标缓存，在会话级别，以避免重新打开最近关闭的游标的开销。因此，您以编程方式关闭的游标可能仍处于打开状态。 Oracle 将在适当的时候在幕后关闭它们。

如果游标超出范围，可以将其关闭。也就是说，如果一个调用或过程如下所示：

DECLARE
  CURSOR c_1 IS SELECT ....;
BEGIN
  OPEN c_1;
  FETCH c_1 INTO...;
END;

那么一旦完成执行，c_1 就会超出范围（并且不能在物理上再次调用）并且可以关闭。这对于异常处理特别有用，因为有可能引发异常、跳出过程并绕过所有“CLOSE c_1;”代码。通过关闭超出范围的游标，您不必担心添加异常处理代码来执行此操作。

如果您的游标具有会话范围（例如，在 PL/SQL 包规范或主体的全局级别中定义或通过引用游标返回到客户端），则它永远不会以这种方式超出范围，因此永远不会自动关闭，直到会话断开或通过 DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES);

As well as DCookie's answer:

The general pattern of a cursor lifetime is

OPEN 
BIND
EXECUTE
FETCH...FETCH...FETCH
BIND
EXECUTE
FETCH...FETCH...FETCH
...
CLOSE

That is a cursor can be re-used by binding fresh variables to it and re-executing it.

PL/SQL will also maintain a cache of cursors, at the session level, to avoid the overhead of re-opening a cursor you have recently closed. So a cursor you have programmatically closed may still be open. Oracle will close them behind the scenes when appropriate.

If a cursor goes out of scope, it can be closed. That is if a call or procedure is like:

DECLARE
  CURSOR c_1 IS SELECT ....;
BEGIN
  OPEN c_1;
  FETCH c_1 INTO...;
END;

then once it has finished executing, c_1 goes out of scope (and cannot physically be called again) and can be closed. This is especially useful for exception handling, since it is possible for an exception to be raised, jumping out of the procedure and bypassing all your 'CLOSE c_1;' code. By closing the out-of-scope cursors, you don't have to worry about adding exception handling code to do that.

If your cursor has a session scope (eg defined in a PL/SQL package specification or global level of a body or returned to a client through a ref cursor) it never goes out of scope in this way, so would never be automatically closed until the session disconnects or through a DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.FREE_ALL_RESOURCES);

回复收藏 0 原文