Oracle-如何在存储过程中使用out ref游标参数? [英] Oracle - How to have an out ref cursor parameter in a stored procedure?

问题描述

我们的应用程序将信息从oracle存储过程传递到oracle .net提供程序的标准方式是通过out ref游标参数.

The standard way that our applications pass information from oracle stored procedures to the oracle .net provider is via an out ref cursor parameter.

过去，我们所有的存储过程以前都在软件包中，并且具有以下内容:

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;
/

现在，我想将get_info过程移出程序包，并移入常规过程，但不知道如何获取refcur类型.如何在包范围之外创建它?

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?

创建或替换类型为"ref CURSOR"；

CREATE OR REPLACE TYPE refcur IS REF CURSOR;

不起作用.

推荐答案

我在这里无法测试(没有Oracle)，但是您可以这样做:

I can't test it here (no Oracle) but you can do:

create or replace procedure get_info(p_cursor out sys_refcursor)
is
begin
  open p_cursor for
    select *
    from   v$database; 
end;
/

在Oracle 9及更高版本中，不再需要声明 TYPE result_crsr IS REF CURSOR

In Oracle 9 and higher it is no longer needed to declare TYPE result_crsr IS REF CURSOR

改为使用 sys_refcursor .

这篇关于Oracle-如何在存储过程中使用out ref游标参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！