如何使用 RefCursor 返回类型测试 Oracle 存储过程？

How to test an Oracle Stored Procedure with RefCursor return type?

提问by Ryan Fisch

I'm looking for a good explanation on how to test an Oracle stored procedure in SQL Developer or Embarcardero Rapid XE2. Thank you.

我正在寻找有关如何在 SQL Developer 或 Embarcardero Rapid XE2 中测试 Oracle 存储过程的很好的解释。谢谢你。

回答by Justin Cave

Something like

就像是

create or replace procedure my_proc( p_rc OUT SYS_REFCURSOR )
as
begin
  open p_rc
   for select 1 col1
         from dual;
end;
/

variable rc refcursor;
exec my_proc( :rc );
print rc;

will work in SQL*Plus or SQL Developer. I don't have any experience with Embarcardero Rapid XE2 so I have no idea whether it supports SQL*Plus commands like this.

将在 SQL*Plus 或 SQL Developer 中工作。我对 Embarcardero Rapid XE2 没有任何经验，所以我不知道它是否支持这样的 SQL*Plus 命令。

回答by DCookie

Something like this lets you test your procedure on almost any client:

这样的事情可以让您在几乎所有客户端上测试您的程序：

DECLARE 
  v_cur SYS_REFCURSOR;
  v_a   VARCHAR2(10);
  v_b   VARCHAR2(10);
BEGIN
  your_proc(v_cur);

  LOOP
    FETCH v_cur INTO v_a, v_b;
    EXIT WHEN v_cur%NOTFOUND;
    dbms_output.put_line(v_a || ' ' || v_b);
  END LOOP;
  CLOSE v_cur;
END;

Basically, your test harness needs to support the definition of a SYS_REFCURSORvariable and the ability to call your procedure while passing in the variable you defined, then loop through the cursor result set. PL/SQL does all that, and anonymous blocks are easy to set up and maintain, fairly adaptable, and quite readable to anyone who works with PL/SQL.

基本上，您的测试工具需要支持SYS_REFCURSOR变量的定义以及在传入您定义的变量时调用您的过程的能力，然后循环遍历游标结果集。PL/SQL 做到了这一切，匿名块易于设置和维护，适应性强，并且对于使用 PL/SQL 的任何人来说都非常可读。

Another, albeit similar way would be to build a named procedure that does the same thing, and assuming the client has a debugger (like SQL Developer, PL/SQL Developer, TOAD, etc.) you could then step through the execution.

另一种虽然类似的方法是构建一个执行相同操作的命名过程，并假设客户端有一个调试器（如 SQL Developer、PL/SQL Developer、TOAD 等），然后您可以逐步完成执行。

回答by Glenn Wark

In SQL Developer you can right-click on the package body then select RUN. The 'Run PL/SQL' window will let you edit the PL/SQL Block. Clicking OK will give you a window pane titled 'Output Variables - Log' with an output variables tab. You can select your output variables on the left and the result is shown on the right side. Very handy and fast.

在 SQL Developer 中，您可以右键单击包主体，然后选择 RUN。“运行 PL/SQL”窗口将让您编辑 PL/SQL 块。单击确定将为您提供一个标题为“输出变量 - 日志”的窗口窗格，其中包含一个输出变量选项卡。您可以在左侧选择输出变量，结果显示在右侧。非常方便快捷。

I've used Rapid with T-SQL and I think there was something similiar to this.

我已经将 Rapid 与 T-SQL 一起使用，我认为有一些与此类似的东西。

Writing your own delcare-begin-end script where you loop through the cursor, as with DCookie's example, is always a good exercise to do every now and then. It will work with anything and you will know that your code works.

编写您自己的 delcare-begin-end 脚本，在其中循环游标，就像 DCookie 的示例一样，总是一个很好的练习，不时进行。它适用于任何事情，您会知道您的代码有效。

回答by Hernaldo Gonzalez

In Toad 10.1.1.8 I use:

在 Toad 10.1.1.8 中，我使用：

variable salida refcursor
exec MY_PKG.MY_PRC(1, 2, 3, :salida)  -- 1, 2, 3 are params
print salida

Then, Execute as Script.

然后，作为脚本执行。

回答by user1118468

create or replace procedure my_proc(  v_number IN number,p_rc OUT SYS_REFCURSOR )
as
begin
open p_rc
for select 1 col1
     from dual;
 end;
 /

and then write a function lie this which calls your stored procedure

然后编写一个函数谎言 this 调用您的存储过程

 create or replace function my_proc_test(v_number IN NUMBER) RETURN sys_refcursor
 as
 p_rc sys_refcursor;
 begin
 my_proc(v_number,p_rc);
 return p_rc;
 end
 /

then you can run this SQL query in the SQLDeveloper editor.

然后您可以在 SQLDeveloper 编辑器中运行此 SQL 查询。

 SELECT my_proc_test(3) FROM DUAL;

you will see the result in the console right click on it and cilck on single record view and edit the result you can see the all the records that were returned by the ref cursor.

您将在控制台中看到结果，右键单击它并单击单个记录视图并编辑结果，您可以看到引用光标返回的所有记录。

回答by Shivam Sharma

I think this link will be enough for you. I found it when I was searching for the way to execute oracle procedures.

我认为这个链接对你来说就足够了。我是在找oracle程序执行方式的时候发现的。

The link to the page

页面链接

Short Description:

简短的介绍：

--cursor variable declaration 
variable Out_Ref_Cursor refcursor;
--execute procedure 
execute get_employees_name(IN_Variable,:Out_Ref_Cursor);
--display result referenced by ref cursor.
print Out_Ref_Cursor;