oracle oracle什么时候不需要关闭游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28330884/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
When you do not need to close a cursor in oracle?
提问by Dzyann
I am working in Oracle 10.2 with TOAD.
我正在使用 TOAD 在 Oracle 10.2 中工作。
I have a script similar to this:
我有一个类似的脚本：
DECLARE
my_query VARCHAR2(500) := 'select C1, C2, C3 FROM MYSCHEMA.MYTABLE';
TYPE my_record_type IS RECORD
(
C1 MYTABLE.C1%TYPE,
C2 MYTABLE.C2%TYPE,
C3 MYTABLE.C3%TYPE
);
TYPE my_table_type IS TABLE OF my_record_type;
my_records my_table_type;
my_cursor SYS_REFCURSOR;
BEGIN
OPEN my_cursor FOR my_records;
FETCH my_cursor
BULK COLLECT INTO my_records
LIMIT 1000;
FOR indx IN 1 .. my_records.COUNT
LOOP
/* SOME CODE HERE*/
IF /* CONDITION */ THEN
EXIT;
END IF;
END LOOP;
CLOSE my_cursor;
END;
I have read that if you CLOSEa cursor that is already closed, oracle will raise the INVALID_CURSOR exception.
我读过如果你关闭一个已经关闭的游标，oracle 将引发 INVALID_CURSOR 异常。
I have also read that when you use a CURSOR in a FOR LOOP oracle closes the cursor explicitly when the LOOP ends. So in theory I don't need to CLOSE the cursor as I am, and I should get an exception, which I am not.
我还读到，当您在 FOR LOOP 中使用 CURSOR 时，oracle 会在 LOOP 结束时显式关闭游标。所以理论上我不需要像我一样关闭光标，我应该得到一个例外，我不是。
Now I need to add a condition inside the LOOP that will cause it to EXIT the LOOP. I read that oracle implicitly closes CURSORS when you use EXITinside a FOR LOOP or when an EXCEPTION is thrown.
现在我需要在 LOOP 中添加一个条件，使其退出 LOOP。我读到，当您在 FOR 循环中使用EXIT或抛出异常时，oracle 会隐式关闭 CURSORS 。
So should I close the CURSOR explicitly? If I do so, and the CURSOR gets closed because I called EXIT, should it throw an Exception?
那么我应该明确关闭 CURSOR 吗？如果我这样做，并且 CURSOR 因为我调用 EXIT 而关闭，它是否应该抛出异常？
If I remove the explicit call that closes the cursor, will I get memory leaks?
如果删除关闭游标的显式调用，是否会出现内存泄漏？
What is the correct way to use the CURSOR in this case?
在这种情况下使用 CURSOR 的正确方法是什么？
回答by Allan
It seems that you're confusing cursors in for loopswith cursor for loops. The former is what you illustrated. The latter looks like this:
似乎您将for 循环中的游标与游标 for 循环混淆了。前者是你所说明的。后者看起来像这样：
DECLARE
cursor my_cursor is select C1, C2, C3 FROM MYSCHEMA.MYTABLE;
BEGIN
FOR my_record in my_cursor LOOP
/* SOME CODE HERE*/
IF /* CONDITION */ THEN
EXIT;
END IF;
END LOOP;
CLOSE my_cursor;
END;
In this case, the closestatement will throw an error because the cursor was implicitly opened by the forstatement and implicitly closed when the loop concluded.
在这种情况下，该close语句将引发错误，因为游标被for语句隐式打开并在循环结束时隐式关闭。
To summarize, if you have an openstatement, you should also have a closestatement. If you implicitly open a cursor, you can trust that it will be implicitly closed.
总而言之，如果你有一个open陈述，你也应该有一个close陈述。如果您隐式打开一个游标，您可以相信它会被隐式关闭。
回答by Bob Jarvis - Reinstate Monica
There's a simple rule - if you have to code an OPENstatement to open the cursor, you must code a matching CLOSEstatement to close the cursor.
有一个简单的规则——如果你必须编写一个OPEN语句来打开游标，你必须编写一个匹配的CLOSE语句来关闭游标。
Thus if you have
因此，如果你有
DECLARE
CURSOR some_cursor IS SELECT * FROM SOME_TABLE;
some_table_row SOME_TABLE%ROWTYPE;
BEGIN
OPEN some_cursor;
LOOP
FETCH some_cursor INTO some_table_row;
EXIT WHEN some_cursor%NOTFOUND; -- this exit **WILL NOT** close some_cursor
END LOOP;
then at some point later in your code you must have
然后在您的代码稍后的某个时刻，您必须拥有
CLOSE some_cursor;
END;
If you use a cursor FOR-loop to open the cursor, e.g.
如果您使用游标 FOR 循环打开游标，例如
BEGIN
FOR aRow IN some_cursor LOOP
-- whatever
IF aRow.SOME_THING = 'xyz' THEN
EXIT; -- this EXIT **will** close the cursor
END IF;
END LOOP;
or in the case where you don't even have an explicit cursor declaration:
或者在您甚至没有显式游标声明的情况下：
FOR aRow IN (SELECT * FROM SOME_TABLE) LOOP
-- blah, blah
IF aRow.YADA_YADA = 'BADA BOOM!' THEN
EXIT; -- this EXIT **will** close the cursor
END IF;
END LOOP;
then you don'tneed or want to write a CLOSEstatement.
那么你并不需要或想要写一个CLOSE说法。
Share and enjoy.
分享和享受。
回答by mmmmmpie
From the documentation:
从文档：
When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is opened, it will stay open until you explicitly close it or your session is terminated.
... And if it is local, then including a CLOSE statement will also show other >developers and your manager that you are paying attention.
当您在包中声明一个游标（即不在包的子程序中）并且游标被打开时，它将保持打开状态，直到您明确关闭它或您的会话终止。
...如果它是本地的，那么包含 CLOSE 语句也会向其他开发人员和您的经理表明您正在关注。
So I think you close it because you're told to and its a good idea if you intend on migrating your code to a package.
所以我认为你关闭它是因为你被告知，如果你打算将代码迁移到包，这是一个好主意。
