Oracle - 如何在存储过程中有一个 out ref 游标参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/869055/
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
Oracle - How to have an out ref cursor parameter in a stored procedure?
提问by George Mauer
The standard way that our applications pass information from oracle stored procedures to the oracle .net provider is via an out ref cursor parameter.
我们的应用程序将信息从 oracle 存储过程传递到 oracle .net 提供程序的标准方式是通过 out ref 游标参数。
In the past all of our stored procedures used to be in packages and had something like this:
过去，我们所有的存储过程都在包中，并且具有以下内容：
CREATE OR REPLACE PACKAGE test_package IS
TYPE refcur IS REF CURSOR;
PROCEDURE get_info ( o_cursor OUT refcur );
END test_package;
/
CREATE OR REPLACE PACKAGE BODY test_package IS
PROCEDURE get_info ( o_cursor OUT refcur ) AS
BEGIN
OPEN o_cursor FOR
SELECT * FROM v$database;
END get_info;
END test_package;
/
Now I would like to move that get_info procedure out of the package and into a regular procedure but don't know what to do to get the refcur type. How do I create it outside the package scope?
现在我想将 get_info 过程移出包并进入常规过程，但不知道如何获取 refcur 类型。如何在包范围之外创建它？
CREATE OR REPLACE TYPE refcur IS REF CURSOR;
创建或替换类型 refcur IS REF CURSOR;
doesn't work.
不起作用。
回答by tuinstoel
I can't test it here (no Oracle) but you can do:
我不能在这里测试它（没有 Oracle）但你可以这样做：
create or replace procedure get_info(p_cursor out sys_refcursor)
is
begin
open p_cursor for
select *
from v$database;
end;
/
In Oracle 9 and higher it is no longer needed to declare TYPE result_crsr IS REF CURSOR
在 Oracle 9 及更高版本中，不再需要声明TYPE result_crsr IS REF CURSOR
Use sys_refcursorinstead.
请改用sys_refcursor。
回答by Brian
TYPE result_crsr IS REF CURSOR;
An example of anonymous block of SQL using a ref cursor:
使用引用游标的匿名 SQL 块示例：
DECLARE
TYPE result_crsr IS REF CURSOR;
crsr_test_result result_crsr;
BEGIN
OPEN crsr_test_result FOR
SELECT * from user_objects;
? := crsr_test_result;
END;
回答by spencer7593
Try this:
尝试这个：
CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT sys_refcursor) IS
BEGIN
OPEN o_cursor FOR SELECT * FROM dual;
END;
/
Your question has raises two important questions:
你的问题提出了两个重要问题：
1) You say it "doesn't work". I take that to mean that Oracle is returning an exception when you execute the statement. What is the error message Oracle is returning? It should start with ORA-nnnnn and be followed by some text.
1）你说它“不起作用”。我认为这意味着当您执行该语句时，Oracle 正在返回一个异常。Oracle 返回的错误消息是什么？它应该以 ORA-nnnnn 开头，然后是一些文本。
2) What purpose would be served by moving a PROCEDURE out of a PACKAGE? There are a few more lines of code with the package, the procedure signature repeated in both the package spec and the package body, but having the procedure within a package provides several important benefits.
2) 将 PROCEDURE 从包中移出有什么目的？包还有几行代码，包规范和包主体中都重复了过程签名，但是将过程放在包中提供了几个重要的好处。
