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

如果 GetQuestions 是一个返回refcursor的函数,它似乎是SQL Server版本中的函数,您可以执行以下操作:

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

或者,如果在PL/SQL块中需要它,那么可以在游标中使用相同的select。

您还可以使用函数生成 dbms_output

编辑

嗯,不确定能不能 cast()

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;

sqldeveloper会自动捕获运行存储过程的输出。直接从我们的过程编辑器运行存储过程,您可以在我的文章中看到这个行为的详细信息

SQL Developer Tip: Viewing REFCURSOR Output

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

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

--GET\u EMPS()将是您的sp\u GetQuestions('OMG Ponies')调用。PRINT命令发送通过存储过程运行的“query”的输出,如下所示:

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

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