如何从返回引用游标的 Oracle 过程中获得格式良好的结果？

Question

在 MS SQL Server 中，如果我想检查存储过程的结果，我可以在 Management Studio 中执行以下命令。

--SQL SERVER WAY
exec sp_GetQuestions('OMG Ponies')

结果窗格中的输出可能如下所示。

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 语句。

要在 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;

其输出类似于

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 行”，并且输出很丑陋。如果有很多列和/或数据是数字，情况会更加严重。

对我来说奇怪的是，如果我在 SQL Developer 或 Management studio 中编写此语句...

SELECT 
ID, 
Title, 
ViewCount, 
Votes
FROM votes where user = 'OMG Ponies'  

结果非常相似。这让我觉得我要么错过了一项技术，要么使用了错误的工具。

Answer 1

如果 GetQuestions 是一个返回引用游标的函数，这似乎是 SQL Server 版本中的函数，那么您可能可以执行如下操作：

select * from table(MyPackage.GetQuestions('OMG Ponies'));

或者如果您在 PL/ SQL 块然后您可以在游标中使用相同的选择。

您还可以让该函数生成 dbms_output 语句，以便它们始终可用于调试，尽管这会增加一点开销。

编辑

嗯，不确定是否可以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;

并且您可以在另一个过程或函数中使用调用的结果；只是在 PL/SQL 之外实现它似乎有点棘手。

编辑添加：通过这种方法，如果它是一个过程，您基本上可以做同样的事情：

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;

Answer 2

SQL Developer 自动捕获运行存储过程的输出。直接从我们的过程编辑器运行存储过程，您可以在我的文章中详细了解此行为

SQL 开发人员提示：查看 REFCURSOR 输出

现在，如果您想将 refcursor 作为 SQL 工作表中 anon 块的一部分运行，您可以执行与此类似的操作

var rc refcursor
exec :rc := GET_EMPS(30)
print rc

--其中 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            

现在，您说的是 10g。如果您使用的是 12c，我们已增强了 PL/SQL 引擎以支持隐式游标结果。因此，这变得更容易一些，不再需要设置光标，您只需调用即可获取数据，如此处记录的：
http://docs.oracle.com/database/121/DRDAA/migr_tools_feat .htm#DRDAA230

Answer 3

/*
    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;  
/************************************************************************/