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.
3 réponses
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;
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
/*
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;
/************************************************************************/