显示来自Oracle 10g存储过程的结果集 [英] display resultset from oracle 10g stored procedure

问题描述

我正在使用PL/SQL Developer，并且已经编写了运行报告的过程，并且需要执行过程以输出结果集.

I am using PL/SQL Developer and i have written a procedure to run a report and i need to procedure to output the resultset.

该过程接受输入参数，需要输出结果集.

The procedure accepts input parameters and needs to output the resultset.

我无法使用视图，因为该过程调用了几个API，这些API接受我要传递给该过程的参数.

I cannot use a view because the procedure calls several APIs which accept the parameters i am passing into the procedure.

我从大量搜索中了解到，可以使用ref_cursor，但是我无法使ti正常工作.

I understand from alot of searching that it's possible using ref_cursor but i cannot get ti to work.

该过程的简化版本是:

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;

我尝试使用以下方法来体现该过程并显示结果集:

I have tried to execture the procedure and display the resultset using:

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

但是都行不通.请有人告诉我我处于机智.

But neither work. please can someone advise i am at my wits end.

谢谢 罗布

推荐答案

在pl/sql developer中，其代码如下.

To the bottom of this in pl/sql developer, the code is as follows.

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

Create a object to store the resultset

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

将类型创建为对象上方的表

Create a type as a table of he above object

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

创建一个执行SQL的程序包

Create a package to excute the 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 the package body to excute the 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;

执行和输出结果集的代码

The code to excute and output the resultset

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

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

这篇关于显示来自Oracle 10g存储过程的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！