Renvoyer une table avec une proc. stock. [Résolu]

[Darcynette]

Bonjour à tous,

J'ai une questionqui certainement vous semblera à tous très facile !!

J'ai créé une procédure qui fait des INSERT et des UPDATE dans une table. Jusque là tout va bien.
Là où ça pose problème c'est que je souhaite récupérer en sortie la table en question.
Voilà ce que je voudrais faire :

Declare
-- variables et curseurs
Begin
--Insert et Update dans MA_TABLE
/*partie qui ne fonctionne pas */
select * from MA_TABLE;
end;

Je suis sous Orable 9i et pour répondre à la question qui brule vos levres : je suis obligée de passer parce biais car j'utilise derrière un outil de décisionnel qui demande que les procédures renvoient des valeurs pour fonctionner.

Merci par avance pour votre aide et n'hésitez pas à poser des questions si je n'est pas été claire !!

@+

[zefennec]

Si j'ai bien compris ce que tu veux, il te faut utiliser une fonction et renvoyer un curseur sur ta table.
1)il te faut créer un type (dans ton package, si tu en as un ou dans un nouveau) pour la référence de curseurqu’on va renvoyer
create or replace package Types AS
type curseur_type is ref cursor;
end Types;


2) ta fonction ressemblerait à :

create or replace
function maFonction()
return Types.curseur_type
is
monCurseur Types.curseur_type;
begin
open monCurseur for
select * from MA_TABLE;
return monCurseur;
end;

[Darcynette]

Merci pour l'info, je teste de ce pas !!!

Juste une précision : peut-on appeler une fonction dans une procédure ?

Pourquoi ? Parce que forcément mon outil décisionnel n'accepte que les procédures stockées et pas autres choses

@+
Darcynette

[zefennec]

Envoyé par Darcynette

Juste une précision : peut-on appeler une fonction dans une procédure ?

Tu peux.
Ou alors, tu gardes ta procédure et tu lui mets le curseur en paramètre out

procedure maProc(monCurseur out Types.curseur_type)

[Darcynette]

euh...
Je comprends pas comment utiliser la fonction dans ma procédure...
d'après les docs, il faut affecter la fonction à une variable !
Je pensais utiliser un curseur mais du coup, je ne vois pas comment afficher le résultat de ma fonction

Si quelqu'un peut encore m'aider

Merci
Darcynette

[arouche]

essaye ceci

Code :

1
2
3
4
5
6
7
 
 
procedure maProc ( C1 OUT SYS_REFCURSOR)
IS
BEGIN
open C1 FOR SELECT taFonction FROM dual;
END;

[Darcynette]

Ca marche !!!

Merci pour votre aide, j'ai une épine dans le pied en moins !!

@+
Darcynette

[jcd]

Bonjour,

Curieux de creuser également ce problème j'ai créé (comme expliqué sauf erreur de ma part...) un type, une fonction et une procédure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE PACKAGE TYPES AS
	-- Nouveau type de données de type "curseur"
	TYPE CURSEUR_TYPE IS REF CURSOR;
END TYPES;
 
 
CREATE OR REPLACE FUNCTION f_lit_table RETURN types.curseur_type IS moncurseur types.curseur_type;
BEGIN
	-- Lecture de la table dans le curseur
  OPEN moncurseur FOR
  SELECT DISTINCT first_name,
    last_name
  FROM employees
  ORDER BY last_name,
    first_name;
  RETURN moncurseur;
END;
 
 
CREATE OR REPLACE PROCEDURE sp_lit_table(parCurseur OUT sys_refcursor) AS
BEGIN
  -- Lecture du curseur
  OPEN parCurseur FOR
  SELECT f_lit_table()
  FROM dual;
END sp_lit_table;

Si je fais un

Code :

SELECT F_Liste_table() FROM DUAL;

je n'obtiens qu'une ligne d'un champ qui contient les données imbriquées ("nested")... pourquoi n'obtiendrais-je pas toutes les lignes (avec tous les champs) ?? Et comment le faire ?

ensuite, si je fais un

Code :

CALL SP_lit_table();

j'obtiens une erreur "ORA06-553 : numéro ou type d'arguments erronés dans appel sp_lit_table()...

Et, dernière chose pour ma gouverne personnelle, dans une procédure stockée MySQL (je ne recherche pas la polémique dans cette salle Oracle ), en plus des paramètres IN et OUT je peux faire des

Code :

SELECT x, y, z FROM toto;

pour retourner tout simplement un résultat. Quelle justification pouvez vous me donner pour Oracle de ne pas le permettre (aussi simplement) ?

D'avance, merci

[jcd]

Voici ma réponse à ma question :

Tout d'abord, en préambule, je crée un type personnalisé : un curseur

Code :

1
2
3
4
CREATE OR REPLACE PACKAGE TYPES AS
	-- Nouveau type de données de type "curseur"
	TYPE CURSEUR_TYPE IS REF CURSOR;
END TYPES;

Ensuite différents cas de figure :

• 1ère possibilité : créer une fonction qui retourne le résultset
• 2ème possibilité : créer une procédure qui retourne le resultset
• 3ème possibilité : créer en plus de la 1ère fonction qui retourne le resultset une procédure qui englobe cette fonction

1ère possibilité (fct simple) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
-- -----------------------------------------------------------------------
-- Fonction qui retourne un curseur
-- -----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION F_LIT_TABLE RETURN TYPES.CURSEUR_TYPE IS MONCURSEUR TYPES.CURSEUR_TYPE;
BEGIN
	-- Lecture de la table dans le curseur
	OPEN MONCURSEUR FOR
	  SELECT DISTINCT FIRST_NAME, LAST_NAME
		FROM EMPLOYEES
			ORDER BY LAST_NAME, FIRST_NAME;
	RETURN MONCURSEUR;
END;

Son utilisation dans SQL+ (ou autre) :

Code :

1
2
3
4
-- -----------------------------------------------------------------------
-- Utilisation dans SQL+ de la fonction
-- -----------------------------------------------------------------------
SELECT f_lit_table FROM dual;

2ème possibilité (procédure simple) :

Code :

1
2
3
4
5
6
7
8
9
10
11
-- -----------------------------------------------------------------------
-- Procédure "autonome", sans utilisation de la fonction
-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_lit_table(parCurseur OUT types.curseur_type) AS
BEGIN
	-- Lecture du curseur
	OPEN PARCURSEUR FOR 
		SELECT DISTINCT FIRST_NAME, LAST_NAME 
		FROM EMPLOYEES 
		ORDER BY LAST_NAME, FIRST_NAME;
END sp_lit_table;

Son utilisation dans SQL+ (ou autre) :

Code :

1
2
3
4
5
6
-- -----------------------------------------------------------------------
-- Utilisation dans SQL+ de la procédure
-- -----------------------------------------------------------------------
variable c refcursor
	exec sp_lit_table (:c)
	print c

3ème possibilité (procédure qui englobe la 1ère fonction) :

Code :

1
2
3
4
5
6
7
8
9
10
-- -----------------------------------------------------------------------
-- Ajout d'une procédure pour englober la fonction
-- -----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_LIT_TABLE_2 (PARCURSEUR OUT SYS_REFCURSOR) AS
BEGIN
  -- Lecture du curseur
  OPEN PARCURSEUR FOR
  SELECT F_LIT_TABLE()
  FROM DUAL;
END SP_LIT_TABLE_2;

Son utilisation dans SQL+ (ou autre) :

Code :

1
2
3
4
5
6
-- -----------------------------------------------------------------------
-- Utilisation dans SQL+ de la procédure
-- -----------------------------------------------------------------------
variable c refcursor
	exec sp_lit_table_2 (:c)
	print c

Voili, voilou...

Bon c'est peut être trivial pour les habitués, mais pour les gens qui viennent d'autres bases de données ("MySQL" ou "SQL Server"/"Transact SQL" par exemple pour ne pas les nommer) le problème n'est pas des plus simples à résoudre (vu que dans les autres SGBD cela se fait de manière immédiate).

J'aurais donc bien aimé trouver directement un exemple simple de ce type dans la FAQ...

Si quelqu'un à un peu de temps dispo, libre à lui d'intégrer mon message dans la FAQ pour d'autres personnes....

[ilalaina]

Bonjour.
Je relance la discussion parce que j'ai essaiéce que JCD a conseillé mais ça n'a pas marché.
J'ai utilisé la première solution :

Code :

1
2
3
4
CREATE OR REPLACE PACKAGE TYPES AS
	-- Nouveau type de données de type "curseur"
	TYPE CURSEUR_TYPE IS REF CURSOR;
END TYPES;

Le package est bien créé.
Ensuite :

Code :

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE FUNCTION F_LIT_TABLE RETURN TYPES.CURSEUR_TYPE IS MONCURSEUR TYPES.CURSEUR_TYPE;
BEGIN
	-- Lecture de la table dans le curseur
	OPEN MONCURSEUR FOR
	  SELECT DISTINCT FIRST_NAME, LAST_NAME
		FROM EMPLOYEES
			ORDER BY LAST_NAME, FIRST_NAME;
	RETURN MONCURSEUR;
END;

La fonction est bien créée.
Et puis quand je fais :

Code :

SELECT f_lit_table FROM dual;

Ca me retourne l'erreur :

ORA-00902: Type de données non valide

En fait mon but c'est d'utiliser le résultat d'un curseur comme une table, c'est à dire pouvoir faire du genre

Code :

SELECT * FROM mon_curseur

.

J'utilise Oracle 8 pour information.

Merci d'avance.

[jcd]

@ilalaina : Mes tests étaient effectués avec Oracle 10 XE, je pense que ton problème est lié à cette différence de version.
Il me semble vaguement me souvenir avoir vu quelque chose sur la disponibilité de retour d'un ensemble d'enregistrements à partir de Oracle "N", mais je ne souviens plus ni où, ni quand, et je ne suis plus très sur de ce détail... ;-(

Je te conseille de faire des recherches dans ce sens.
Bon courage.

[ilalaina]

J'ai recherché tout l'après midi, mais je n'ai pas trouvé un moyen d'effectuer un retour de collection ou d'enregistrement avec Oracle 8.
J'ai alors deux solutions :

• Créer des tables temporaires.
• Faire des Select imbriqués.

Côté performance je crois que les Select imbriqués sont plus lourds pour la base, alors que si j'utilise des tables temporaires j'aurai du mal à declarer des curseurs appelant ces tables.

Un conseil les experts?

[jcd]

J'ai effectué sur google une recherche rapide avec les termes : "pl/sql" recordset "oracle 8"

A priori Oracle8 le permet, google m'a notamment retourné cette adresse :
http://vbcity.com/page.asp?p=chapter...oks-wrox-vbora

A creuser...