Appel de procédure stockée dans un select

Version imprimable

Bonsoir, j'ai crée une procédure stockée qui me permet de récupérer les différentes dates maximum pour chaque mois compris dans une période donnée.
la procédure fonctionne très bien et j'arrive à voir le résultat sous sql développer avec print . Cependant j'arrive pas à l’utiliser dans un select l'objectif étant cela
voici le code pl sql:

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
27
28
29
30
31
CREATE OR REPLACE PROCEDURE dateMaxMult(nomEtabl IN VARCHAR2,date1 IN date,date2 IN date, dateMax OUT varchar2) IS 
	Nomb Number;
	Annee Number;
	Mois Number;
	dateM date;
Begin
	Nomb := Months_Between(Date2, Date1);
	Annee := Trunc( Nomb/12);
	Mois := Trunc(Nomb-Annee*12);
		IF (Mois>0) THEN
			FOR compteur IN  0..Mois 
			LOOP
				IF (compteur=0) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND LAST_DAY(date1);	
					dateMax:=''''||dateM||'''';
				ELSIF(compteur=Mois) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN '01'||'/'||to_char(date2,'MM/YYYY') AND date2;
					dateMax:=dateMax||','''||dateM||'''';
				ELSE
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN ADD_MONTHS( '01'||'/'||to_char(date1,'MM/YYYY'), compteur ) AND ADD_MONTHS( LAST_DAY(date1), compteur );
					dateMax:=dateMax||','''||dateM||'''';
				END IF;	
			END LOOP;
		ELSE
			SELECT MAX(date_generation) INTO dateMax FROM ventes
			WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND date2;
		END IF;
End;

et voici le code pl sql permettant de l'appeler dans un select

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
periode VARCHAR2(100);
code VARCHAR2(10) :='132';
date1 date :='01/08/2014';
date2 date :='31/12/2014';
begin
select CODE_PRODUIT "CODE PRODUIT",DESIGNATION,to_char(round(sum(QTE_VENDUE)),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
		 to_char(round(sum(VALEUR_VENTE)),'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
		 to_char(round(sum(VALEUR)),'999G999G999G999',  'nls_numeric_characters='', ''') "VALEUR REV" ,
		 to_char(round(sum(MARGE_HT)),'999G999G999G999',  'nls_numeric_characters='', ''') as MARGE,
		 to_char(round((sum(VALEUR_VENTE)-sum(VALEUR))*100/nullif(sum(VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX",
		 to_char(round(sum(QTE_STOCK_FINAL)) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock Final" 
	    from ventes where CODE_ETABL =(select CODE_ETABL from ETABLISSEMENT where DESCRIPTION='VGE') and DATE_GENERATION in (select * from dateMaxMult(code,date1,date2,periode)) group by CODE_PRODUIT,DESIGNATION order by CODE_PRODUIT asc;
end;

mais j'ai l'erreur:
Rapport d'erreur :
ORA-06550: Ligne 13, colonne 162 :
PL/SQL: ORA-00907: parenthèse de droite absente
ORA-06550: Ligne 7, colonne 1 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

j'ai alors essayé d'appeler la procedure dans un simple select:

Code:

1
2
3
4
5
6
7
select CODE_PRODUIT "CODE PRODUIT",DESIGNATION,to_char(round(sum(QTE_VENDUE)),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
		 to_char(round(sum(VALEUR_VENTE)),'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
		 to_char(round(sum(VALEUR)),'999G999G999G999',  'nls_numeric_characters='', ''') "VALEUR REV" ,
		 to_char(round(sum(MARGE_HT)),'999G999G999G999',  'nls_numeric_characters='', ''') as MARGE,
		 to_char(round((sum(VALEUR_VENTE)-sum(VALEUR))*100/nullif(sum(VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX",
		 to_char(round(sum(QTE_STOCK_FINAL)) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock Final" 
	    from ventes where CODE_ETABL =(select CODE_ETABL from ETABLISSEMENT where DESCRIPTION='VGE') and DATE_GENERATION in (select * from dateMaxMult('132','01/08/2014','31/12/2014',periode)) group by CODE_PRODUIT,DESIGNATION order by CODE_PRODUIT asc;

mais j'ai toujours la même erreur
j'ai aussi essayé d'enlever les cotes autour des dates avec des retour à la ligne comme dans un select dans le code pls\sql mais c'est toujours le même résultat:

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
27
28
29
30
31
CREATE OR REPLACE PROCEDURE dateMaxMult(nomEtabl IN VARCHAR2,date1 IN date,date2 IN date, dateMax OUT varchar2) IS 
	Nomb Number;
	Annee Number;
	Mois Number;
	dateM date;
Begin
	Nomb := Months_Between(Date2, Date1);
	Annee := Trunc( Nomb/12);
	Mois := Trunc(Nomb-Annee*12);
		IF (Mois>0) THEN
			FOR compteur IN  0..Mois 
			LOOP
				IF (compteur=0) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND LAST_DAY(date1);	
					dateMax:=dateM;
				ELSIF(compteur=Mois) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN '01'||'/'||to_char(date2,'MM/YYYY') AND date2;
					dateMadx:=dateMax|| chr(10) ||dateM;
				ELSE
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN ADD_MONTHS( '01'||'/'||to_char(date1,'MM/YYYY'), compteur ) AND ADD_MONTHS( LAST_DAY(date1), compteur );
					dadateMax:=dateMax|| chr(10) ||dateM;
				END IF;	
			END LOOP;
		ELSE
			SELECT MAX(date_generation) INTO dateMax FROM ventes
			WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND date2;
		END IF;
End;

J'ai besoin de votre aide pour pourvoir appeler cette procédure dans mon select

24/03/2015, 16h49
Pomalaix

Une procédure ne peut pas être appelée dans un SELECT, c'est une fonction qu'il faut créer à la place.
24/03/2015, 17h00
sabdoul

Merci j'avais pensé à cette solution cependant les fonctions ne retournant qu'une seule valeur comment s'y prendre alors avec les fonctions si on veut retourner plusieurs lignes

dateMax OUT varchar2 ne renvoie qu'une valeur également.

Vous devriez regarder du côté des collections ce qui vous permettra de renvoyer une collection de date au lieu d'une chaîne de caractère, avec les conversions implicites que ça entraine impliquant un code qui ne fonctionne que dans vos conditions de paramétrage de la variable NLS_DATE_FORMAT...

Exemple :
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 27 28 29 30 31 SQL> create type t_date as table of date 2 / Type created. SQL> create or replace function f_list_date return t_date as 2 l_date t_date; 3 begin 4 l_date := t_date(trunc(sysdate)); 5 -- 6 l_date.extend; 7 l_date(2) := trunc(sysdate)+1; 8 -- 9 l_date.extend; 10 l_date(3) := trunc(sysdate)+2; 11 -- 12 return l_date; 13 end; 14 / Function created. SQL> select * from table(f_list_date); COLUMN_VAL ---------- 24/03/2015 25/03/2015 26/03/2015 SQL>

24/03/2015, 21h22
Sunchaser

Citation:

Envoyé par sabdoul

Merci j'avais pensé à cette solution cependant les fonctions ne retournant qu'une seule valeur comment s'y prendre alors avec les fonctions si on veut retourner plusieurs lignes

Bonsoir,
Une fonction table ... ?
(Regarder du côté "Pipelined Table Functions")
28/03/2015, 17h21
sabdoul

Salut merci et excusez moi du retard vos différentes réponses la solution avec les fonctions fonctionne effectivement mais le résultat étant une chaîne de caractère je suis obligé de le retraiter. Donc je vais regarder du côté des collections ou table préconisé. Merci j'essaie et je vous tient au courant.

Citation:

Envoyé par skuatamad

dateMax OUT varchar2 ne renvoie qu'une valeur également.

Vous devriez regarder du côté des collections ce qui vous permettra de renvoyer une collection de date au lieu d'une chaîne de caractère, avec les conversions implicites que ça entraine impliquant un code qui ne fonctionne que dans vos conditions de paramétrage de la variable NLS_DATE_FORMAT...

Exemple :

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
27
28
29
30
31
SQL> create type t_date as table of date
  2  /
 
Type created.
 
SQL> create or replace function f_list_date return t_date as
  2    l_date t_date;
  3  begin
  4    l_date := t_date(trunc(sysdate));
  5    --
  6    l_date.extend;
  7    l_date(2) := trunc(sysdate)+1;
  8    --
  9    l_date.extend;
 10    l_date(3) := trunc(sysdate)+2;
 11    --
 12    return l_date;
 13  end;
 14  /
 
Function created.
 
SQL> select * from table(f_list_date);
 
COLUMN_VAL
----------
24/03/2015
25/03/2015
26/03/2015
 
SQL>

Salut j'essaie votre méthode avec les nested table mais sans succès j'ai cette erreur:Warning: exécution terminée avec avertissement
function f_list_date(nomEtabl Compilé. alors qu'il ne m'affiche rien dans le dictionnaire de données avec show errors ou SELECT line, position, text "texte erreur"
FROM USER_ERRORS
WHERE name = 'f_list_date';
Voici le code de ma fonction:

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
27
28
29
30
31
32
create or replace function f_list_date(nomEtabl IN VARCHAR2,date1 IN date,date2 IN date) return t_date as
	dateMax t_date;
   	Nomb Number;
	Annee Number;
	Mois Number;
	dateM date;
 begin
 	Nomb := Months_Between(Date2, Date1);
	Annee := Trunc( Nomb/12);
	Mois := Trunc(Nomb-Annee*12);
		IF (Mois>0) THEN
			FOR compteur IN  0..Mois 
			LOOP
				IF (compteur=0) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND LAST_DAY(date1);	
				ELSIF(compteur=Mois) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN '01'||'/'||to_char(date2,'MM/YYYY') AND date2;
				ELSE
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN ADD_MONTHS( '01'||'/'||to_char(date1,'MM/YYYY'), compteur ) AND ADD_MONTHS( LAST_DAY(date1), compteur );
				END IF;	
				dateMax:=dateM;
			END LOOP;
		ELSE
			SELECT MAX(date_generation) INTO dateM FROM ventes
			WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND date2;
			dateMax:=dateM;
		END IF;
	RETURN dateMax;
 end;

Donc quand je fais appel à la fonction çà marche pas j'ai des messages d'erreurs:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Erreur commençant à la ligne 1 de la commande :
select * from table(f_list_date('432','01/08/2014','31/12/2014'))
Erreur à la ligne de commande : 1, colonne : 20
Rapport d'erreur :
Erreur SQL : ORA-06575: Fonction F_LIST_DATE est dans un état non valide
06575. 00000 -  "Package or function %s is in an invalid state"
*Cause:    A SQL statement references a PL/SQL function that is in an
           invalid state. Oracle attempted to compile the function, but
           detected errors.
*Action:   Check the SQL statement and the PL/SQL function for syntax
           errors or incorrectly assigned, or missing, privileges for a
           referenced object.

Salut j'ai trouvé la solution à mon problème même si l'exécution de la requête Select dure plus avec cette fonction PL/SQL.
Le problème était au niveau de l'initialisation du table dateMax et de ses indices.

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
27
28
29
30
31
32
33
34
35
36
create or replace
function f_list_date(nomEtabl IN VARCHAR2,date1 IN date,date2 IN date) return t_date is
	dateMax t_date;
   	Nomb Number;
	Annee Number;
	Mois Number;
	dateM date;
 begin
 	Nomb := Months_Between(date2, date1);
	Annee := Trunc( Nomb/12);
  Mois := Trunc(Nomb-Annee*12);
	dateMax:= t_date(trunc(sysdate));
		IF (Mois>0) THEN
			FOR i IN 0..Mois
			LOOP
       dateMax.EXTEND ;
				IF (i=0) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND LAST_DAY(date1);	
				ELSIF(i=Mois) THEN
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN '01'||'/'||to_char(date2,'MM/YYYY') AND date2;
					
				ELSE
					SELECT MAX(date_generation) INTO dateM FROM ventes
					WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN ADD_MONTHS( '01'||'/'||to_char(date1,'MM/YYYY'), i ) AND ADD_MONTHS( LAST_DAY(date1), i );
				END IF;	
				dateMax(i+1):=dateM;
			END LOOP;
		ELSE
			SELECT MAX(date_generation) INTO dateM FROM ventes
			WHERE CODE_ETABL=nomEtabl AND date_generation BETWEEN date1 AND date2;
			dateMax(1):=dateM;
		END IF;
	RETURN dateMax;
 end;

Cependant l'appel à la fonction dans le select dure plus que lorsque j'appelais la fonction datemult au debut de mon poste qui me retournais une chaine de caractère que plaçais dans le in du select. Voici la requete select :

Code:

1
2
3
4
5
6
7
8
 
select CODE_PRODUIT "CODE PRODUIT",DESIGNATION,to_char(round(sum(QTE_VENDUE)),'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Vendue",
to_char(round(sum(VALEUR_VENTE)),'999G999G999G999',  'nls_numeric_characters='', ''') "C.A.H.T",
to_char(round(sum(VALEUR)),'999G999G999G999',  'nls_numeric_characters='', ''') "VALEUR REV" ,
to_char(round(sum(MARGE_HT)),'999G999G999G999',  'nls_numeric_characters='', ''') as MARGE,
to_char(round((sum(VALEUR_VENTE)-sum(VALEUR))*100/nullif(sum(VALEUR_VENTE),0),2),'999G999G999G999D99',  'nls_numeric_characters='', ''') "TAUX",
to_char(round(sum(QTE_STOCK_FINAL)) ,'999G999G999G999',  'nls_numeric_characters='', ''') "Qte Stock Final" 
from ventes where CODE_ETABL =(select CODE_ETABL from ETABLISSEMENT where ETABLISSEMENT.DESCRIPTION='VGE') and DATE_GENERATION in (select * from table(f_list_date(CODE_ETABL,'01/08/2014','31/12/2014'))) group by CODE_PRODUIT,DESIGNATION order by CODE_PRODUIT asc;

31/03/2015, 17h59
skuatamad

Pour les problèmes de perfs il faut regarder le plan, le problème classique avec les tables de fonction est la cardinalité estimée.
Le hint cardinality peut être intéressant si le nombre de ligne renvoyé par la fonction est à peu près stable :
collections cardinality

Par contre ne faites pas de conversion implicite (dans la fonction et dans l'appel de la fonction), il faut toujours utiliser to_date pour convertir la chaîne de caractères en date afin de préciser le format.
02/04/2015, 11h03
sabdoul

Citation:

Par contre ne faites pas de conversion implicite (dans la fonction et dans l'appel de la fonction), il faut toujours utiliser to_date pour convertir la chaîne de caractères en date afin de préciser le format.

Je fais pas de conversion implicite
02/04/2015, 11h49
mnitu
Citation:

Envoyé par sabdoul

Je fais pas de conversion implicite

Mais si
Code:

1 2 ...select * from table(f_list_date(CODE_ETABL,'01/08/2014','31/12/2014'))) ...