如何从返回引用游标的 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.

编辑

嗯，不确定是否可以将返回的 refcursor 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屋！