从 pl/sql 异常块中“一次”关闭所有游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7998442/
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
Closing all cursors 'once' from a pl/sql exception block
提问by ziggy
Is there an easier way to close all open cursors from within a PL/SQL program (Oracle 10G).
是否有一种更简单的方法可以从 PL/SQL 程序 (Oracle 10G) 中关闭所有打开的游标。
I have a program which can generate a number of exceptions. To exit properly, i need to check whether there are any cursors open and close them. This is the kind of situation i am ending up with.
我有一个程序可以生成许多异常。要正确退出，我需要检查是否有任何游标打开和关闭它们。这就是我要结束的那种情况。
Procedure test
is
--
---
Begin
--
--
Exception
when no_data_found then
if cursorA%isopen close
if cursorB%isopen close
if cursorC%isopen close
when invalid_date then
if cursorA%isopen close
if cursorB%isopen close
if cursorC%isopen close
when invalid_user then
if cursorA%isopen close
if cursorB%isopen close
if cursorC%isopen close
when others then
if cursorA%isopen close
if cursorB%isopen close
if cursorC%isopen close
End test;
Obviously the above is not ideal especially if there are many exception clauses. Instead of havin the same checks on each exception block, is there a quicker way to close all open cursor? Note: It needs to close only the cursors opened by the currently running pl/sql program because there could be other pl/sql program that could also open cursors.
显然，以上并不理想，尤其是在有很多例外条款的情况下。没有对每个异常块进行相同的检查，有没有一种更快的方法来关闭所有打开的游标？注意：它只需要关闭当前运行的 pl/sql 程序打开的游标，因为可能有其他 pl/sql 程序也可以打开游标。
Thanks in advance
提前致谢
回答by Justin Cave
Are you sure that you need to use the explicit cursor syntax in the first place rather than using implicit cursors? If you use implicit cursors, Oracle takes care of opening and closing them automatically. You can declare the query inline or out of line as in the block below
您确定首先需要使用显式游标语法而不是使用隐式游标吗？如果您使用隐式游标，Oracle 会自动打开和关闭它们。您可以在下面的块中声明查询内联或外联
DECLARE
CURSOR cursor_a
IS SELECT *
FROM emp;
BEGIN
FOR a IN cursor_a
LOOP
<<do something>>
END LOOP;
FOR b IN (SELECT *
FROM dept)
LOOP
<<do something else>>
END LOOP;
END;
In either case, Oracle will automatically take care of closing the cursor when you exit the block.
无论哪种情况，当您退出块时，Oracle 都会自动关闭游标。
If you do need to use explicit cursors for some reason, and assuming that you need to catch multiple different exceptions because you will handle those exceptions differently, you can create a nested block that closes the cursors and just call that from each exception handler
如果出于某种原因确实需要使用显式游标，并假设您需要捕获多个不同的异常，因为您将以不同的方式处理这些异常，您可以创建一个关闭游标的嵌套块，然后从每个异常处理程序中调用它
DECLARE
CURSOR cursor_a
IS SELECT *
FROM emp;
CURSOR cursor_b
IS SELECT *
FROM dept;
PROCEDURE close_open_cursors
AS
BEGIN
IF( cursor_a%isopen )
THEN
close cursor_a;
END IF;
IF( cursor_b%isopen )
THEN
close cursor_b;
END IF;
END;
BEGIN
OPEN cursor_a;
OPEN cursor_b;
RAISE no_data_found;
EXCEPTION
WHEN no_data_found
THEN
close_open_cursors;
<<do something meaningful>>
WHEN too_many_rows
THEN
close_open_cursors;
<<do something meaningful>>
WHEN others
THEN
close_open_cursors;
raise;
END;
