oracle 如何关闭 PL/SQL 中的返回游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6128101/
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
How to close a returning cursor in PL/SQL?
提问by Y_Y
I am new to PL/SQL and I just got to cursors in my learning process. I've been seeing stored procedure parameters with type OUT SYS_REFCURSORwhich, as far as I understand, its purpose is to 'return data' just like a pointer in C language. I been wondering who is going to close such SYS_REFCURSORparameter if the procedure has to open it and can't close it? (If the procedure closes the out OUT SYS_REFCURSORthen there will not be any data to return).
我是 PL/SQL 的新手，在我的学习过程中刚接触到游标。我一直在看到带有类型的存储过程参数OUT SYS_REFCURSOR，据我所知，它的目的是像 C 语言中的指针一样“返回数据”。我想知道SYS_REFCURSOR如果程序必须打开它而不能关闭它，谁会关闭这样的参数？（如果程序关闭了，OUT SYS_REFCURSOR那么将没有任何数据要返回）。
Also, I think, it is bad design to rely on external functions other than the stored procedure that contains OUT SYS_REFCURSORparameter to close the cursor. Is there a way I can return a table from a stored procedure without using cursors?
另外，我认为，依赖包含OUT SYS_REFCURSOR参数的存储过程以外的外部函数来关闭游标是一种糟糕的设计。有没有一种方法可以在不使用游标的情况下从存储过程返回表？
回答by DCookie
All you should need to do is issue a CLOSE on the cursor when you're done with it, regardless of where it was actually opened:
您需要做的就是在完成光标后在光标上发出 CLOSE ，而不管它实际打开的位置：
-- A function to return a SYS_REFCURSOR
CREATE OR REPLACE FUNCTION f_c RETURN SYS_REFCURSOR IS
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR SELECT LEVEL FROM dual CONNECT BY LEVEL < 10;
RETURN cur;
END;
Here's a sample run:
这是一个示例运行：
DECLARE
cc SYS_REFCURSOR;
r VARCHAR2(10);
BEGIN
cc := f_c; -- Get the cursor from the function
LOOP
FETCH cc INTO r;
EXIT WHEN cc%NOTFOUND;
dbms_output.put_line('Output is: '||r);
END LOOP;
CLOSE cc; -- Close the SYS_REFCURSOR returned from the function
END;
/
Output is: 1
Output is: 2
Output is: 3
Output is: 4
Output is: 5
Output is: 6
Output is: 7
Output is: 8
Output is: 9
As for returning a set of values from a function or procedure, here's another SO questionon the topic.
至于从函数或过程返回一组值，这是关于该主题的另一个SO 问题。
