Comment obtenir des résultats bien formatés à partir d'une procédure Oracle qui renvoie un curseur de référence?

dans MS SQL Server si je veux vérifier les résultats D'une procédure stockée, je peux exécuter ce qui suit dans Management Studio.

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

la sortie dans le volet des résultats pourrait ressembler à ceci.

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)

pas besoin d'écrire des boucles ou D'imprimer des déclarations.

pour faire la même chose dans Oracle je pourrais exécuter le bloc suivant anonyme dans le développeur SQL

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

cette sortie comme

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

donc la version SQL a 1 ligne et l'oracle a 18 et la sortie est laide. Son exacerbé s'il ya beaucoup de colonnes et/ou les données sont numériques.

ce qui est étrange pour moi, c'est que si j'écris cette déclaration dans SQL Developer ou Management studio...

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

les résultats sont assez similaires. J'ai l'impression de manquer une technique ou d'utiliser le mauvais outil.

17
demandé sur Conrad Frix 2010-08-20 02:28:50
la source

3 ответов

si GetQuestions est une fonction retournant un refcursor, ce qui semble être ce que vous avez dans la version SQL Server, alors plutôt vous pouvez être en mesure de faire quelque chose comme ceci:

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

ou si vous en avez besoin dans un bloc PL/SQL, vous pouvez utiliser le même select dans un curseur.

vous pouvez également avoir la fonction produire les instructions dbms_output à la place de sorte qu'ils sont toujours disponibles pour le débogage, bien que cela ajoute un peu de surchauffe.

Modifier

Hmmm, pas sûr qu'il soit possible de cast() le refcursor retourné à un type utilisable, sauf si vous êtes prêt à déclarer votre propre type (et une table de ce type) en dehors du paquet. Vous pouvez le faire cependant, juste pour Dumper les résultats:

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;

et vous pouvez utiliser le résultat de l'appel dans une autre procédure ou fonction; il est juste d'y arriver en dehors de PL / SQL qui semble être un peu délicat.

modifié pour ajouter: avec cette approche, si c'est une procédure, vous pouvez faire essentiellement la même chose:

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;
16
répondu Alex Poole 2010-08-23 01:04:54
la source

SQL Developer saisit automatiquement la sortie de l'exécution de vos procédures stockées. En exécutant la procédure stockée directement à partir de notre éditeur de procédure, vous pouvez voir ce comportement détaillé dans mon post ici ""

Conseil du développeur SQL: affichage de la sortie REFCURSOR

maintenant, si vous voulez exécuter le refcursor dans le cadre d'un bloc anon dans notre feuille de travail SQL, vous pouvez faire quelque chose de similaire à ce

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

-- où GET_EMPS () serait votre appel sp_GetQuestions ('OMG Ponies'). La commande PRINT envoie la sortie de la 'requête' qui est exécutée via la procédure stockée, et ressemble à ceci:

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            

Maintenant, vous avez dit 10g. Si vous êtes en 12c, nous avons amélioré le moteur PL/SQL pour supporter les résultats implicites du curseur. Donc cela devient un peu plus facile, plus de réglage du curseur, vous faites juste un appel pour obtenir les données, comme documenté ici: http://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA230

1
répondu thatjeffsmith 2014-07-23 23:39:04
la source
/*
    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;  
/************************************************************************/  
0
répondu user3260206 2014-02-01 14:53:43
la source

Autres questions sur