显示来自 oracle 10g 存储过程的结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4376044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
display resultset from oracle 10g stored procedure
提问by RobLaw84
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.
我正在使用 PL/SQL Developer 并且我已经编写了一个程序来运行报告，我需要程序来输出结果集。
The procedure accepts input parameters and needs to output the resultset.
该过程接受输入参数并需要输出结果集。
I cannot use a view because the procedure calls several APIs which accept the parameters i am passing into the procedure.
我无法使用视图，因为该过程调用了多个 API，这些 API 接受我传递给该过程的参数。
I understand from alot of searching that it's possible using ref_cursor but i cannot get ti to work.
我从大量搜索中了解到可以使用 ref_cursor 但我无法让 ti 工作。
A simplified version of the procedure is:
该过程的简化版本是：
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;
And also:
并且：
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.
但两者都不起作用。请有人建议我不知所措。
Thank you Rob
谢谢罗布
采纳答案by RobLaw84
To the bottom of this in pl/sql developer, the code is as follows.
在pl/sql developer中，代码如下。
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
Create a package to excute the SQL
创建包执行SQL
CREATE OR REPLACE PACKAGE ABC.TEST_RPT AS
FUNCTION get_report(vPart VARCHAR2,
vBuyer VARCHAR2,
vSupplier VARCHAR2) RETURN ABC.TEST_COL
PIPELINED;
END;
Create the package body to excute the SQL
创建包体执行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('','',''))
回答by Rajesh Chamarthi
Is there an error that you see when you execute this procedure or run it in SQLPLUS? Can you post your sqlplus session as is?
当您执行此过程或在 SQLPLUS 中运行它时，您是否看到错误？您可以按原样发布您的 sqlplus 会话吗？
PRINT is a sqlplus specific command and it cannot be called inside the procedural block. If you need to print the results of a refcursor inside a procedure , then you'll need to fetch from it and print each record in the format that you need.
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
You should also change your procedure (or) the procedure call to have different variable names.generallt, I prefix all input variables with "i_" and all output variables with "o_". This way, your procedure declaration would look like..
您还应该更改您的过程（或）过程调用以具有不同的变量名称。generallt，我将所有输入变量与“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 ....
and the procedure call would be..
程序调用将是..
IFSINFO.SHORTAGE_SHEET( i_site => vsite,
i_buyer => vbuyer,
i_supplier => vsupplier,
o_cursor => vcursor);
You need not use the ":" in the beginning for these variables since they are not host environment variables ( this is the case for your second execution using SQLPLUS where you use the sqlplus variable "rc" inside the procedure call)
您不需要在这些变量的开头使用“：”，因为它们不是宿主环境变量（这是使用 SQLPLUS 的第二次执行的情况，其中您在过程调用中使用 sqlplus 变量“rc”）
回答by user3177508
Your ref cursor variable is rc. But then you use rc2.
您的 ref 游标变量是rc. 但是随后您使用rc2.
Change rc2to rcand it should work
更改rc2为rc它应该可以工作
回答by redcayuga
I assume you're using Sql Plus to execute this. First, define a Sql Plus refcursor.
我假设您正在使用 Sql Plus 来执行此操作。首先，定义一个 Sql Plus refcursor。
variable vcursor refcursor
variable vcursor refcursor
Next, execute an anonymous pl/sql block. Notice the declarekeyword.
接下来，执行匿名 pl/sql 块。注意declare关键字。
DECLARE
vsite := 'S03';
vbuyer := 'AW';
vsupplier := '%';
-- do not declare this, use sql plus bind var vcursor refcursor;
BEGIN
IFSINFO.SHORTAGE_SHEET(vsite => vsite, -- no colon
vbuyer => vbuyer,
vsupplier => vsupplier,
vcursor => :vcursor);
END;
/
Then run this sql plus command.
然后运行这个 sql plus 命令。
print vcursor
print vcursor
print is not pl/sql
打印不是 pl/sql
