Oracle - 如何在存储过程中拥有输出引用游标参数？

发布于 2024-07-19 06:15:33 字数 629 浏览 13 评论 0原文

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

过去，我们所有的存储过程都位于包中，并且具有类似以下内容：

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 类型。如何在包范围之外创建它？

创建或替换类型 refcur 是引用游标；

不起作用。

原文

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

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?

CREATE OR REPLACE TYPE refcur IS REF CURSOR;

doesn't work.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谜泪 2024-07-26 06:15:33

我无法在这里测试它（没有 Oracle），但您可以这样做：

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

使用 sys_refcursor 代替。

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

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

Use sys_refcursor instead.

回复收藏 0 原文

裂开嘴轻声笑有多痛 2024-07-26 06:15:33

TYPE result_crsr IS 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;

TYPE result_crsr IS REF CURSOR;

An example of anonymous block of SQL using a ref cursor:

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;

回复收藏 0 原文

蓝海似她心 2024-07-26 06:15:33

试试这个：

CREATE OR REPLACE PROCEDURE get_info(o_cursor OUT sys_refcursor) IS
BEGIN
  OPEN o_cursor FOR SELECT * FROM dual;
END;
/

你的问题提出了两个重要的问题：

1）你说它“不起作用”。我认为这意味着当您执行该语句时 Oracle 将返回异常。 Oracle 返回的错误消息是什么？它应该以 ORA-nnnnn 开头，后面跟着一些文本。

2) 将程序从包中移出有何目的？包中还有几行代码，包规范和包主体中都重复了过程签名，但是在包中包含过程提供了几个重要的好处。

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.

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.

回复收藏 0 原文

~没有更多了~