调用在直接 PL/SQL 中返回引用游标的 Oracle 包过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3972338/
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
Calling Oracle package procedures which return ref cursors in straight PL/SQL
提问by Cade Roux
I've got an Oracle 10g database which is accessed from an ASP.NET application. Although I've used SQL Server heavily in many different aspects and Oracle for querying and reporting, this is my first time using Oracle as the OLTP database for an application.
我有一个从 ASP.NET 应用程序访问的 Oracle 10g 数据库。尽管我在许多不同方面大量使用 SQL Server 并使用 Oracle 进行查询和报告，但这是我第一次使用 Oracle 作为应用程序的 OLTP 数据库。
The database-level procedures in the packages are typically of the form:
包中的数据库级过程通常采用以下形式：
-- TYPE refcur IS REF CURSOR;
PROCEDURE get_some_stuff(o_cursor OUT refcur, p_param1 IN INTEGER, p_param2 IN INTEGER) IS
BEGIN
OPEN o_cursor FOR
SELECT whatever
FROM whatever
END
I assume these are done this way for the benefit of the ADO.NET layer able to use the cursor from the output param and it is my understanding that this is the acceptable best practice for calling Oracle procs from .NET.
我假设这些是为了 ADO.NET 层能够使用输出参数中的游标而这样做的，据我所知，这是从 .NET 调用 Oracle procs 的可接受的最佳实践。
In SQL Server, for example, we don't have explicit ref cursors, if a proc returns a result set (or several result sets), that's accessible as an output result set in both ADO.NET and SSMS, and you can simply test the SPs by doing EXEC spname param1, param2.
例如，在 SQL Server 中，我们没有显式引用游标，如果 proc 返回一个结果集（或多个结果集），则在 ADO.NET 和 SSMS 中都可以作为输出结果集访问，您可以简单地测试SP通过做EXEC spname param1, param2。
The problem I'm having is that I don't know how to call these directly in SQL in Toad, for example, to be able to test changes at the PL/SQL level first before going to the app. I'm very used to being able to exercise and even re-mix stored procs and functions in SQL Server to be able to refactor the database interface layer without affecting the external interface to application-level code.
我遇到的问题是我不知道如何在 Toad 中直接在 SQL 中调用这些，例如，在转到应用程序之前能够首先测试 PL/SQL 级别的更改。我非常习惯于能够在 SQL Server 中练习甚至重新混合存储的过程和函数，以便能够在不影响应用程序级代码的外部接口的情况下重构数据库接口层。
采纳答案by Cade Roux
Best solution was found in the link which OMG Ponies provided:
在 OMG Ponies 提供的链接中找到了最佳解决方案：
Easiest method to test an Oracle Stored Procedure
and here:
和这里：
回答by Harrison
look at the link that OMG Ponies posted, but what you can do is
看看 OMG Ponies 发布的链接，但你能做的是
var x refcursor;
declare
PROCEDURE GET_SOME_STUFF(O_CURSOR OUT SYS_REFCURSOR, P_PARAM1 IN NUMBER, P_PARAM2 IN NUMBER) IS
BEGIN
OPEN O_CURSOR FOR
SELECT LEVEL, p_param1 ,P_PARAM2 FROM DUAL CONNECT BY LEVEL < 3;
END ;
BEGIN
GET_SOME_STUFF(:x , 5, 10);
END;
/
PRINT X;
you pretty much just wrap it in a anonymous block ad it will run. I use SQL Developer (highly recommmend, free with plenty of support) or SQL plus so I cannot help with TOAD, but I would expect it to be the same. In SQL Developer (and in SQL Navigator if memory serves correct) you can simply right click the package/method you wish and it will create the script for you.
in toad and navigator I believe you may be able to get the ref cursor in a pretty grid while in developer you get it in text.
您几乎只需将它包装在一个匿名块广告中，它就会运行。我使用 SQL Developer（强烈推荐，免费提供大量支持）或 SQL plus，所以我无法帮助使用 TOAD，但我希望它是相同的。在 SQL Developer（如果没记错的话，也可以在 SQL Navigator 中）您只需右键单击您想要的包/方法，它就会为您创建脚本。
在蟾蜍和导航器中，我相信您可以在漂亮的网格中获得参考光标，而在开发人员中，您可以在文本中获得它。
回答by Bob Jarvis - Reinstate Monica
Try this:
尝试这个：
DECLARE
aCursor SYS_REFCURSOR;
someVariable SOME_TYPE;
FUNCTION SOME_FUNC_RETURNING_A_CURSOR RETURN SYS_REFCURSOR IS
csrLocal SYS_REFCURSOR;
BEGIN
OPEN csrLocal FOR SELECT whatever FROM wherever;
RETURN csrLocal;
END SOME_FUNC_RETURNING_A_CURSOR;
BEGIN
aCursor := SOME_FUNC_RETURNING_A_CURSOR;
WHILE TRUE LOOP
FETCH aCursor INTO someVariable;
EXIT WHEN aCursor%NOTFOUND;
...do whatever with variables...
END LOOP;
COMMIT;
END;
Share and enjoy.
分享和享受。
回答by Zeus
I found an easier way to this ...try it (This will also generate script for you)
我找到了一个更简单的方法......试试看（这也会为你生成脚本）
In the Procedure Editor, load your procedure. Click on the lightning bolt to execute and you will see the Set Parameters window, which is also available via the button on the Proc Editor toolbar that has an image similar to (...) on it, next to the lightning bolt. Click on the output options button and you'll see your options. If this is a weak ref cursor then you must use the in-memory grid option. Results go to the cursor results tab at the bottom of the PE after you execute.
在程序编辑器中，加载您的程序。单击要执行的闪电，您将看到“设置参数”窗口，也可以通过 Proc 编辑器工具栏上的按钮访问该窗口，该按钮上有一个类似于 (...) 的图像，位于闪电旁边。单击输出选项按钮，您将看到您的选项。如果这是一个弱引用游标，那么您必须使用内存网格选项。执行后，结果将转到 PE 底部的光标结果选项卡。
http://toad.10940.n7.nabble.com/display-ref-cursor-in-toad-td1427.html
http://toad.10940.n7.nabble.com/display-ref-cursor-in-toad-td1427.html
