从 Oracle 10g 存储过程中显示结果集

Question

从 Oracle 10g 存储过程中显示结果集

oraclestored-proceduresresultsetplsqldeveloper

3

我正在使用PL/SQL Developer，编写了一个运行报表的存储过程，并需要使其输出结果集。

该存储过程接受输入参数并需输出结果集。

由于存储过程调用了多个API，并接受我传入的参数，因此无法使用视图。

我从很多搜索结果中了解到，可以使用ref_cursor实现，但我无法成功。

该存储过程的简化版本如下：

CREATE OR REPLACE PROCEDURE IFSINFO.SHORTAGE_SHEET (vSite     IN VARCHAR2,
                                                    vBuyer    IN VARCHAR2,
                                                    vSupplier IN VARCHAR2,
                                                    vCursor   OUT SYS_REFCURSOR)   
AS                                                    
BEGIN
    OPEN vCursor FOR                   
        SELECT blah blah blah blah blah blah;
END;

我尝试使用以下方法执行过程并显示结果集：

BEGIN
    vsite       := 'S03';
    vbuyer      := 'AW';
    vsupplier   := '%';    
    vcursor     refcursor;

    IFSINFO.SHORTAGE_SHEET(vsite => :vsite,
                           vbuyer => :vbuyer,
                           vsupplier => :vsupplier,
                           vcursor => :vcursor);                           
    print vcursor;                           
END;

同时还有：

variable rc refcursor; 
exec IFSINFO.SHORTAGE_SHEET('S03','AW','TQ1',:rc2); 
print rc2

但是两者都不起作用。请有人给予建议，我已经束手无策了。

谢谢。罗布

- RobLaw84

您应该加上oracle标签，这不仅适用于10g版本。 - redcayuga

4个回答

1

在PL/SQL开发者中，代码如下所示。

创建一个对象来存储结果集。

CREATE OR REPLACE TYPE ABC.TEST_TYPE
AS OBJECT
( 
  "Site"                       VARCHAR2(25),
);

创建一个类型，作为上述对象的表格。

CREATE OR REPLACE TYPE ABC.TEST_COL 
    AS TABLE OF ABC.TEST_TYPE

创建一个包来执行SQL。

CREATE OR REPLACE PACKAGE ABC.TEST_RPT AS
    FUNCTION get_report(vPart     VARCHAR2,
                        vBuyer    VARCHAR2,
                        vSupplier VARCHAR2) RETURN ABC.TEST_COL 
    PIPELINED;
END;

创建包体以执行SQL。

CREATE OR REPLACE PACKAGE BODY ABC.TEST_RPT AS
  FUNCTION get_report(vPart     VARCHAR2,
                      vBuyer    VARCHAR2,
                      vSupplier VARCHAR2) RETURN ABC.TEST_COL 
    PIPELINED IS
    CURSOR cTest(vPart     VARCHAR2,
                 vBuyer    VARCHAR2,
                 vSupplier VARCHAR2) IS

          SELECT Site
            FROM table
           WHERE Part = vPart
             AND Buyer = vBuyer
             AND Supplier = vSupplier;



  BEGIN
    FOR part_rec IN cTest(vSite, vBuyer, vSupplier) LOOP
      PIPE ROW(ABC.TEST_TYPE(part_rec.Site));

    END LOOP;
    RETURN;
    CLOSE cTest;
  END;
END;

执行代码并输出结果集

SELECT * FROM TABLE(ABC.TEST_RPT.get_report('','',''))

- RobLaw84

0

I assume you're using Sql Plus to execute this. First, define a Sql Plus refcursor.

variable vcursor refcursor

Next, execute an anonymous pl/sql block. Notice the declare keyword.


DECLARE
          vsite       := 'S03';
          vbuyer      := 'AW';
          vsupplier   := '%';
          -- do not declare this, use sql plus bind var     vcursor     refcursor;
BEGIN<br>
       IFSINFO.SHORTAGE_SHEET(vsite => vsite, -- no colon
              vbuyer =>    vbuyer,
              vsupplier => vsupplier,
              vcursor   => :vcursor); 
END;
/

Then run this sql plus command.

print vcursor

print is not pl/sql

- redcayuga

请注意，Rajesh发布的解决方案更简单。exec命令创建了一个最小的PL/SQL块，对于这个例子来说应该是足够的。 - redcayuga

我没有使用SQL Plus，我正在使用PL/SQL Developer，不幸的是在这种情况下我无法使用SQL Plus。 - RobLaw84

0

您的游标变量是rc。但是您使用了rc2。

将rc2更改为rc，它应该可以工作。

- user3177508

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rajesh Chamarthi · Accepted Answer

当你执行这个过程或在SQLPLUS中运行它时，是否出现错误？能否按原样发布你的sqlplus会话？

PRINT是一个特定于sqlplus的命令，不能在过程块内调用。如果您需要打印存储过程中refcursor的结果，则需要从中提取并以所需格式打印每条记录。

SQL> create or replace procedure test_REFCURSOR (
  2     i_number in number,
  3     o_cursor out sys_refcursor) 
  4  as
  5  begin
  6     open o_cursor for
  7        'select empno, ename from emp
  8             where rownum < ' || i_number ;
  9  end;
 10  /

Procedure created.

SQL> variable rc refcursor;
SQL> exec test_refcursor(5, :rc);

PL/SQL procedure successfully completed.

SQL> print rc;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES

您还应该更改您的过程（或）过程调用以具有不同的变量名称。通常，我使用“i_”前缀所有输入变量和“o_”前缀所有输出变量。这样，您的过程声明将如下所示：

CREATE OR REPLACE PROCEDURE IFSINFO.SHORTAGE_SHEET (i_Site     IN VARCHAR2,
                                                    i_Buyer    IN VARCHAR2,
                                                    i_Supplier IN VARCHAR2,
                                                    o_Cursor   OUT SYS_REFCURSOR) AS ....

而过程调用将是...

IFSINFO.SHORTAGE_SHEET(    i_site     => vsite,
                           i_buyer    => vbuyer,
                           i_supplier => vsupplier,
                           o_cursor   => vcursor);

对于这些变量，您不需要在开头使用“：”，因为它们不是主机环境变量（这是您使用SQLPLUS进行第二次执行时使用sqlplus变量“rc”在过程调用中的情况）。