Appel de procédure stockée dans un select

**sabdoul** · 24/03/2015, 16h41

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**Pomalaix** · 24/03/2015, 16h49

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

**sabdoul** · 24/03/2015, 17h00

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

**skuatamad** · 24/03/2015, 17h44

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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>

**Sunchaser** · 24/03/2015, 21h22

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")

**sabdoul** · 28/03/2015, 17h21

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.

**sabdoul** · 30/03/2015, 14h10

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**sabdoul** · 31/03/2015, 17h30

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

**skuatamad** · 31/03/2015, 17h59

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.

**sabdoul** · 02/04/2015, 11h03

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

**mnitu** · 02/04/2015, 11h49

Envoyé par sabdoul

Je fais pas de conversion implicite

Mais si

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Appel de procédure stockée dans un select

PL/SQL Oracle

Discussions similaires

Partager

Partager