如何从返回引用游标的 Oracle 过程中获得格式良好的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3526798/
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
How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
提问by Conrad Frix
In MS SQL Server if I want to check the results from a Stored procedure I might execute the following in Management Studio.
在 MS SQL Server 中，如果我想检查存储过程的结果，我可能会在 Management Studio 中执行以下操作。
--SQL SERVER WAY
exec sp_GetQuestions('OMG Ponies')
The output in the results pane might look like this.
结果窗格中的输出可能如下所示。
ID Title ViewCount Votes
----- ------------------------------------------------- ---------- --------
2165 Indexed View vs Indexes on Table 491 2
5068 SQL Server equivalent to Oracle's NULLS FIRST 524 3
1261 Benefits Of Using SQL Ordinal Position Notation? 377 2
(3 row(s) affected)
No need to write loops or PRINT statements.
无需编写循环或 PRINT 语句。
To do the same thing in Oracle I might execute the following anonymous block in SQL Developer
要在 Oracle 中做同样的事情，我可能会在 SQL Developer 中执行以下匿名块
--ORACLE WAY
DECLARE
OUTPUT MYPACKAGE.refcur_question;
R_OUTPUT MYPACKAGE.r_question;
USER VARCHAR2(20);
BEGIN
dbms_output.enable(10000000);
USER:= 'OMG Ponies';
recordCount := 0;
MYPACKAGE.GETQUESTIONS(p_OUTPUT => OUTPUT,
p_USER=> USER,
) ;
DBMS_OUTPUT.PUT_LINE('ID | Title | ViewCount | Votes' );
LOOP
FETCH OUTPUT
INTO R_OUTPUT;
DBMS_OUTPUT.PUT_LINE(R_OUTPUT.QUESTIONID || '|' || R_OUTPUT.TITLE
'|' || R_OUTPUT.VIEWCOUNT '|' || R_OUTPUT.VOTES);
recordCount := recordCount+1;
EXIT WHEN OUTPUT % NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Record Count:'||recordCount);
CLOSE OUTPUT;
END;
This outputs like
这输出像
ID|Title|ViewCount|Votes
2165|Indexed View vs Indexes on Table|491|2
5068|SQL Server equivalent to Oracle's NULLS FIRST|524|3
1261|Benefits Of Using SQL Ordinal Position Notation?|377|2
Record Count: 3
So the SQL version has 1 line and the oracle has 18 and the output is ugly. Its exacerbated if there are a lot of columns and/or the data is numeric.
所以SQL版本有1行，oracle有18行，输出很难看。如果有很多列和/或数据是数字，它会加剧。
What's odd to me about this is that if I write this statement in either SQL Developer or Management studio...
对此我感到奇怪的是，如果我在 SQL Developer 或 Management Studio 中编写此语句...
SELECT
ID,
Title,
ViewCount,
Votes
FROM votes where user = 'OMG Ponies'
The results are fairly similar. This makes me feel like I'm either missing a technique or using the wrong tool.
结果非常相似。这让我觉得我要么错过了一项技术，要么使用了错误的工具。
采纳答案by Alex Poole
If GetQuestionsis a function returning a refcursor, which seems to be what you have in the SQL Server version, then rather you may be able to do something like this:
如果GetQuestions是一个返回 refcursor 的函数，这似乎是您在 SQL Server 版本中所拥有的，那么您可以执行以下操作：
select * from table(MyPackage.GetQuestions('OMG Ponies'));
Or if you need it in a PL/SQL block then you can use the same select in a cursor.
或者，如果您在 PL/SQL 块中需要它，那么您可以在游标中使用相同的选择。
You can also have the function produce the dbms_outputstatements instead so they're always available for debugging, although that adds a little overhead.
您也可以让函数生成dbms_output语句，以便它们始终可用于调试，尽管这会增加一些开销。
Edit
编辑
Hmmm, not sure it's possible to cast()the returned refcursor to a usable type, unless you're willing to declare your own type (and a table of that type) outside the package. You can do this though, just to dump the results:
嗯，不确定是否可以cast()将返回的 refcursor 返回为可用类型，除非您愿意在包外声明自己的类型（以及该类型的表）。你可以这样做，只是为了转储结果：
create package mypackage as
function getquestions(user in varchar2) return sys_refcursor;
end mypackage;
/
create package body mypackage as
function getquestions(user in varchar2) return sys_refcursor as
r sys_refcursor;
begin
open r for
/* Whatever your real query is */
select 'Row 1' col1, 'Value 1' col2 from dual
union
select 'Row 2', 'Value 2' from dual
union
select 'Row 3', 'Value 3' from dual;
return r;
end;
end mypackage;
/
var r refcursor;
exec :r := mypackage.getquestions('OMG Ponies');
print r;
And you can use the result of the call in another procedure or function; it's just getting to it outside PL/SQL that seems to be a little tricky.
并且您可以在另一个过程或函数中使用调用的结果；它只是在 PL/SQL 之外进行，这似乎有点棘手。
Edited to add:With this approach, if it's a procedure you can do essentially the same thing:
编辑添加：使用这种方法，如果它是一个程序，您可以做基本相同的事情：
var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;
回答by thatjeffsmith
SQL Developer automatically catches the output from running your stored procedures. Running the stored procedure directly from our procedure editor, you can see this behavior detailed in my post here
SQL Developer 自动捕获运行存储过程的输出。直接从我们的过程编辑器运行存储过程，您可以在我的帖子中详细了解此行为here
SQL Developer Tip: Viewing REFCURSOR Output
Now, if you want to run the refcursor as part of an anon block in our SQL Worksheet, you could do something similar to this
现在，如果您想在我们的 SQL 工作表中将 refcursor 作为 anon 块的一部分运行，您可以执行类似的操作
var rc refcursor
exec :rc := GET_EMPS(30)
print rc
--where GET_EMPS() would be your sp_GetQuestions('OMG Ponies') call. The PRINT command sends the output from the 'query' which is ran via the stored procedure, and looks like this:
-- 其中 GET_EMPS() 将是您的 sp_GetQuestions('OMG Ponies') 调用。PRINT 命令发送通过存储过程运行的“查询”的输出，如下所示：
anonymous block completed
RC
-----------------------------------------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------------- ---------- ---------- -------------- ---------- -------------
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-94 12.00.00 PU_MAN 11000 100 30
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-95 12.00.00 PU_CLERK 3100 114 30
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-97 12.00.00 PU_CLERK 2900 114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-97 12.00.00 PU_CLERK 2800 114 30
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-98 12.00.00 PU_CLERK 2600 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-99 12.00.00 PU_CLERK 2500 114 30
Now, you said 10g. If you're in 12c, we have enhanced the PL/SQL engine to support implicit cursor results. So this gets a bit easier, no more setting up the cursor, you just make a call to get the data, as documented here: http://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA230
现在，你说的是 10g。如果您使用的是 12c，我们已经增强了 PL/SQL 引擎以支持隐式游标结果。所以这变得更容易了，不再设置游标，您只需拨打电话即可获取数据，如此处所述：http: //docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA230
回答by user3260206
/*
Create Sample Package in HR Schema
*/
CREATE OR REPLACE PACKAGE PRINT_REF_CURSOR
AS
PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
p_DEPARTMENT_ID IN INTEGER,
Out_Cur OUT SYS_REFCURSOR);
END PRINT_REF_CURSOR;
CREATE OR REPLACE PACKAGE BODY PRINT_REF_CURSOR
AS
PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
p_DEPARTMENT_ID IN INTEGER,
Out_Cur OUT SYS_REFCURSOR)
AS
BEGIN
OPEN Out_Cur FOR
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = p_DEPARTMENT_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.Put_Line('SP_S_EMPLOYEES_BY_DEPT' || ',' || '-20000' || ',' );
WHEN OTHERS
THEN
DBMS_OUTPUT.Put_Line('SP_S_EMPLOYEES_BY_DEPT' || ',' || '-20001' || ',' );
END SP_S_EMPLOYEES_BY_DEPT;
END PRINT_REF_CURSOR;
/*
Fetch values using Ref Cursor and display it in grid.
*/
var RC refcursor;
DECLARE
p_DEPARTMENT_ID NUMBER;
OUT_CUR SYS_REFCURSOR;
BEGIN
p_DEPARTMENT_ID := 90;
OUT_CUR := NULL;
PRINT_REF_CURSOR.SP_S_EMPLOYEES_BY_DEPT ( p_DEPARTMENT_ID, OUT_CUR);
:RC := OUT_CUR;
END;
/
PRINT RC;
/************************************************************************/
