您如何从返回引用游标的Oracle过程中获得格式正确的结果? [英] How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
问题描述
在MS SQL Server中,如果要检查存储过程的结果,可以在Management Studio中执行以下操作.
In MS SQL Server if I want to check the results from a Stored procedure I might execute the following in 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)
无需编写循环或PRINT语句.
No need to write loops or PRINT statements.
要在Oracle中做同样的事情,我可能会在SQL Developer中执行以下匿名块
To do the same thing in Oracle I might execute the following anonymous block in 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;
输出类似
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
因此,SQL版本有1行,而oracle有18行,输出是丑陋的.如果有很多列和/或数据是数字的,它会加剧.
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 Developer或Management Studio中编写此语句,那么...
What's odd to me about this is that if I write this statement in either SQL Developer or 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.
推荐答案
如果GetQuestions
是返回refcursor的函数,这似乎是您在SQL Server版本中所拥有的,那么您也许可以执行某些操作像这样:
If GetQuestions
is 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:
select * from table(MyPackage.GetQuestions('OMG Ponies'));
或者,如果需要在PL/SQL块中使用它,则可以在游标中使用相同的选择.
Or if you need it in a PL/SQL block then you can use the same select in a cursor.
您还可以让函数生成dbms_output
语句,以便它们始终可用于调试,尽管这会增加一些开销.
You can also have the function produce the dbms_output
statements instead so they're always available for debugging, although that adds a little overhead.
编辑
嗯,除非您愿意在包外声明自己的类型(以及该类型的表),否则不确定是否可以将返回的前导cast()
设置为可用的类型.不过,您可以这样做,只是要转储结果:
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:
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;
您可以在另一个过程或函数中使用调用结果;只是在PL/SQL之外使用它似乎有些棘手.
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.
编辑后添加:使用这种方法,如果它是一个过程,则可以执行基本相同的操作:
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;
这篇关于您如何从返回引用游标的Oracle过程中获得格式正确的结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!