Discussion :

[erigoal]

Bonjour voila j'ai une table BILAN qui contient différente matiere avec des eleves j'aimerai faire un pivot pour avoir un resultat qui ressemble à çà :

Code :

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

1
2
3
NOM  MATIERE1  MATIERE2  ETC..
ELEVE1   12            13
ELEVE2   14            11

Voici ma procédure stockée pour le moment :

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
 
CREATE OR REPLACE PROCEDURE pivotBilan AS
v_matiere VARCHAR2(25);
v_req_deb VARCHAR2(50);
v_req_fin VARCHAR(50);
v_req VARCHAR2(300);
v_req_final VARCHAR(500);
v_long NUMBER;
CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere
BEGIN
	OPEN monCurseur;
	v_long:=0;
	v_req_deb := 'SELECT nom,';
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= CONCAT(v_req, "max(decode(matiere,'"v_matiere"',moyenne)),");
		FETCH monCurseur INTO v_matiere;
	END LOOP;
	CLOSE monCurseur;
	v_long:=LENGTH(v_req);
	v_req:=SUBSTR(v_req,v_long-1,0);
	v_req_final := v_req_deb || v_req || v_req_fin;
	EXECUTE IMMEDIATE v_req_final;
END;
/

çà me retourne l'erreur suivante :

Code :

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

1
2
3
4
5
6
 
ERREUR à la ligne 13 : PLS-00103: Symbole "=" rencontré à la place d'un des symboles suivants :
 
   constant exception 
    table LONG_ double ref char
   time timestamp interval date binary national character nchar

la ligne 13 est :

Code :

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

v_req_fin := 'FROM bilan GROUP BY nom';

Je vous remercie par avance pour votre aide !

[xdescamp]

Bonjour,
Il y a plusieurs problèmes dans ton code :
- il manque le ";" à la fin de la déclaration de "monCurseur",
- à la ligne 17, il faut utiliser des apostrophes et non des doubles quotes ; personnellement je préfère utiliser le double pipe "||" plutôt que la fonction CONCAT :

Code :

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

v_req:= v_req || 'max(decode(matiere,''' || v_matiere || ''',moyenne)),';

Xavier

[fatsora]

Pourquoi faire un PLSQL ici ???


Il y a une reponse en SQL deja ...

Quand tu peux le faire en SQL reste en SQL !!!

[erigoal]

@fatsora : Avec le SQL cela m'oblige à connaitre les matières à l'avance il me semble, je ne connais pas à l'avance les matieres présentent dans ma table BILAN.

@xdescamp : Merci beaucoup, ma procédure se crée bien grâce à tes corrections.

Par contre lorsque je la test :

Code :

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

1
2
3
4
5
 
BEGIN
pivotBilan();
END;
/

j'obtiens l'erreur suivante :

Code :

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

ORA-00936: expression absente

Sais-tu d'ou celà peut venir ?
Voici ma procédure mise à jour :

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
CREATE OR REPLACE PROCEDURE pivotBilan AS
v_matiere VARCHAR2(25);
v_req_deb VARCHAR2(50);
v_req_fin VARCHAR(50);
v_req VARCHAR2(300);
v_req_final VARCHAR(500);
v_long NUMBER;
CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere;
BEGIN
	OPEN monCurseur;
	v_long:=0;
	v_req_deb := 'SELECT nom,';
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= v_req || 'max(decode(matiere,''' || v_matiere || ''',moyenne)),';
		FETCH monCurseur INTO v_matiere;
	END LOOP;
	CLOSE monCurseur;
	v_long:=LENGTH(v_req);
	v_req:=SUBSTR(v_req,v_long-1,0);
	v_req_final := v_req_deb || v_req || v_req_fin;
	EXECUTE IMMEDIATE v_req_final;
END;
/

Merci beaucoup !

[NGasparotto]

Ce que tu essaies de faire est complique, est-ce vraiment utile ?
Tu as un nombre de colonne dynamique, ce qui n'est franchement pas commode pour recuperer/manipuler les donnees. Qu'en fais-tu ?
Le plus simple serait d'eviter PL/SQL, mais d'avoir un petit SQL qui creer un autre script avec toutes les colonnes comme il faut, juste pour un rapport.

Quant a ton erreur "ORA-00936: expression absente" elle provient de l'"execute immediate", un cauchemar a debugger, et en plus sans clause INTO. Et la on rejoint ce que je disais auparavant, comment va tu faire pour savoir quoi metter dans la clause INTO ? Puisque par definition, tu ne connais pas les colonnes que tu vas recuperer.

Nicolas.

[erigoal]

Enfaite, j'ai une table BILAN vide à la base avec comme champs

NOM, MATIERE, MOYENNE

Celle-ci je la garni avec une procédure

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
37
38
39
40
41
42
 
CREATE OR REPLACE PROCEDURE maProcedure(numeroClasse in NUMBER) AS
v_nomEleve VARCHAR2(25);
v_prenomEleve VARCHAR2(25);
v_libelleMatiere VARCHAR2(25);
v_MatCourante VARCHAR2(25);
v_NomCourant VARCHAR2(25);
v_total NUMBER;
v_moyenne NUMBER(4,2);
v_nbr NUMBER;
v_note NUMBER;
CURSOR monCurseur 
IS SELECT e.nomEleve, n.note, m.libelleMatiere
FROM eleves e, notes n, matieres m, evaluations ev, classes c
WHERE e.numeleve=ev.numeleve
AND n.numnote=ev.numnote
AND m.nummatiere=ev.nummatiere
AND c.numClasse=ev.numClasse
AND c.numClasse=numeroClasse
ORDER BY e.nomEleve, m.libelleMatiere ASC;
BEGIN
	OPEN monCurseur;
	v_nbr:=0;
	v_total:=0;
	v_note:=0;
	FETCH monCurseur INTO v_nomEleve, v_note, v_libelleMatiere;
	WHILE (monCurseur%FOUND)
	LOOP
			v_MatCourante:=v_libelleMatiere;
			v_NomCourant:=v_nomEleve;
		WHILE((monCurseur%FOUND)AND(v_MatCourante=v_libelleMatiere)AND(v_NomCourant=v_nomEleve))
		LOOP
			v_nbr:=v_nbr+1;
			v_total:=v_total+v_note;
			fetch monCurseur INTO v_nomEleve, v_note, v_libelleMatiere;
		END LOOP;
		v_moyenne:=v_total/v_nbr;
		INSERT INTO BILAN (nom,matiere,moyenne) VALUES (v_NomCourant,v_MatCourante,v_moyenne);
	END LOOP;
	CLOSE monCurseur;
END;
/

Donc après cette procédure, ma table bilan contient les moyennes de tous les élèves d'UNE classe passé en paramètre pour chaque matières enseignés à cette classe. (Toutes les classes non pas les mêmes matières enseignés)

Et ce que j'aimerai faire, c'est d'afficher cette table BILAN en pivot, j'ai donc pensé le faire en PL/SQL mais si il y à une autre je suis à l'écoute.

Je n'ai pas bien compris ta proposition "petit SQL qui creer un autre script avec toutes les colonnes" pourrais-tu m'en dire plus ?


Merci à vous !

[GoLDoZ]

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code :

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

SELECT nom, FROM bilan GROUP BY nom

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code :

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

1
2
3
4
5
6
7
8
        v_req_deb := 'SELECT nom '; -- pas de virgule ici
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin
		FETCH monCurseur INTO v_matiere;
	END LOOP;

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code :

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

1
2
v_long:=LENGTH(v_req);
v_req:=SUBSTR(v_req,v_long-1,0);

[NGasparotto]

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

C'est vrai, mais dans le cas precis, ca ne solutionne pas le fait que l'on ne connais pas le nombre de colonnes a l'avance pour pouvoir les recuperer.

Nicolas.

[GoLDoZ]

Bien entendu. Je me contentais de corriger son erreur.
S'il tient à le faire en PL/SQL, il peut récupérer le nom des colonnes et les moyennes (éventuellement NULL) dans un tableau, et traiter après.

[erigoal]

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code :

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

SELECT nom, FROM bilan GROUP BY nom

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code :

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

1
2
3
4
5
6
7
8
        v_req_deb := 'SELECT nom '; -- pas de virgule ici
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin
		FETCH monCurseur INTO v_matiere;
	END LOOP;

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code :

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

1
2
v_long:=LENGTH(v_req);
v_req:=SUBSTR(v_req,v_long-1,0);

Merci de ton aide et de tes conseils, je peux pas testé çà maintenant je le fait en rentrant chez moi !

C'est vrai, mais dans le cas precis, ca ne solutionne pas le fait que l'on ne connais pas le nombre de colonnes a l'avance pour pouvoir les recuperer.
Nicolas.

Je comprends pas bien pourquoi tu dis çà, je m'explique,
ma table BILAN (NOM, MATIERE, MOYENNE) par exemple est garni après ma première procédure comme ceci :

Code :

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

1
2
3
4
5
6
7
 
NOM      MATIERE    MOYENNE
 
ELEVE1   MATHS       12
ELEVE2   INFO          11
ELEVE1   INFO          16  
ELEVE1   FRANCAIS    12

Dans ma procedure bilanPivot() j'ai un curseur

Code :

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

1
2
 
CURSOR monCurseur IS SELECT DISTINCT matiere FROM BILAN ORDER BY matiere;

Donc je connais le nombre de matières différente et donc de futur colonne non ?

Ensuite pour chaque matière présente dans mon curseur je l'ajoute à ma requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
OPEN monCurseur;
 
v_req_deb := 'SELECT nom '; -- pas de virgule ici
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin
		FETCH monCurseur INTO v_matiere;
	END LOOP;
CLOSE monCurseur;
v_req_final := v_req_deb || v_req || v_req_fin;

Dans la théorie je me retrouverais donc avec une requête final ressemblant à:

Code :

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

1
2
3
4
5
6
 
SELECT nom ,max(decode(matiere,'MATHS',moyenne))
,max(decode(matiere,'INFO',moyenne))
,max(decode(matiere,'FRANCAIS',moyenne))
FROM bilan
GROUP BY nom

Ma logique n'est pas bonne ?

Merci pour votre aide !

[fatsora]

Bonjour,

tu peux essayer un truc comme ca

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
CREATE OR REPLACE package pivot_note
as
   type rc is ref cursor;
   procedure data ( p_cursor in out rc );
end;
/ 
 
 
create or replace package body pivot_note as
    procedure data(p_cursor in out rc )
    is
        l_stmt long;
    begin
 
        l_stmt := 'select  nom';
        for x in ( select distinct MATIERE  from BILANCLASSE order by 1 )
        loop
        ---   l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ||'"'|| x.MATIERE||'"';
                    l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ;
       end loop;
       l_stmt := l_stmt || ' from BILANCLASSE group by NOM ';
           dbms_output.put_line( l_stmt );
 
       open p_cursor for l_stmt;
   end;
 
   end;
   /

et en sqlplus

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
 
> set serverout on lines 200
 
> variable x refcursor
 
> exec pivot_note.data(:x);
select  nom, max(decode(MATIERE,'FRANCAIS', MOYENNE )) , max(decode(MATIERE,'MATHS', MOYENNE ))  from BILANCLASSE group by NOM
 
PL/SQL procedure successfully completed.
 
> print x;
 
NOM                  MAX(DECODE(MATIERE,'FRANCAIS',MOYENNE)) MAX(DECODE(MATIERE,'MATHS',MOYENNE))
-------------------- --------------------------------------- ------------------------------------
ELEVE2                                                    14                                    8
ELEVE1                                                    11                                   12
 
>

[erigoal]

Bonjour,

tu peux essayer un truc comme ca

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
CREATE OR REPLACE package pivot_note
as
   type rc is ref cursor;
   procedure data ( p_cursor in out rc );
end;
/ 
 
 
create or replace package body pivot_note as
    procedure data(p_cursor in out rc )
    is
        l_stmt long;
    begin
 
        l_stmt := 'select  nom';
        for x in ( select distinct MATIERE  from BILANCLASSE order by 1 )
        loop
        ---   l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ||'"'|| x.MATIERE||'"';
                    l_stmt := l_stmt || ', max(decode(MATIERE,' || ''''|| x.MATIERE ||''''|| ', MOYENNE )) ' ;
       end loop;
       l_stmt := l_stmt || ' from BILANCLASSE group by NOM ';
           dbms_output.put_line( l_stmt );
 
       open p_cursor for l_stmt;
   end;
 
   end;
   /

et en sqlplus

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
 
> set serverout on lines 200
 
> variable x refcursor
 
> exec pivot_note.data(:x);
select  nom, max(decode(MATIERE,'FRANCAIS', MOYENNE )) , max(decode(MATIERE,'MATHS', MOYENNE ))  from BILANCLASSE group by NOM
 
PL/SQL procedure successfully completed.
 
> print x;
 
NOM                  MAX(DECODE(MATIERE,'FRANCAIS',MOYENNE)) MAX(DECODE(MATIERE,'MATHS',MOYENNE))
-------------------- --------------------------------------- ------------------------------------
ELEVE2                                                    14                                    8
ELEVE1                                                    11                                   12
 
>

Bonsoir, j'ai essayé d'insérer le bout de code que tu m'as donné, mais je ne sais pas l'appeler...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
 
BEGIN
pivot_note();
END;
/
 
Erreur : ORA-06550: Ligne 2, colonne 1 :
PLS-00221: 'PIVOT_NOTE' n'est pas une procédure ou est indéfini
ORA-06550: Ligne 2, colonne 1 :
PL/SQL: Statement ignored

Si ta table est vide, la requête de ton EXECUTE IMMEDIATE vaut:

Code :

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

SELECT nom, FROM bilan GROUP BY nom

Ca plante à cause de la virgule.

Quand on crée une requête dynamiquement, il vaut mieux ajouter les virgules au début de la chaîne ajoutée.

Code :

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

1
2
3
4
5
6
7
8
        v_req_deb := 'SELECT nom '; -- pas de virgule ici
	v_req_fin := 'FROM bilan GROUP BY nom';
	FETCH monCurseur INTO v_matiere;
	WHILE(monCurseur%FOUND)
	LOOP
		v_req:= v_req || ' ,max(decode(matiere,''' || v_matiere || ''',moyenne)) '; -- une virgule au début, pas à la fin
		FETCH monCurseur INTO v_matiere;
	END LOOP;

De plus, ça t'évite de bidouiller la requête avec ça, qui devient inutile:

Code :

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

1
2
v_long:=LENGTH(v_req);
v_req:=SUBSTR(v_req,v_long-1,0);

Merci, j'ai ajouté ton code ma procédure se crée, lorsque je l'appel la procedure j'ai "Instruction traitée." mais aucun affichage...


Merci beaucoup pour votre aide à tous !

[NGasparotto]

Bon, il y a eu une discussion comme celle-la aujourd'hui sur OTN - nombre de colonnes inconnu-, tu peux aller voir la et t'inspirer de l'une des reponses donnees, mais encore une fois, tu n'as pas repondu ce que tu aller faire de la sortie.
Je suis fondamentalement contre ce genre de pratique (nombre de colonnes dynamiques), et je ne comprend pas vraiment pourquoi on en a besoin. En tout cas, Oracle avec SQL et PL/SQL n'est pas designe pour cela.

Nicolas.

[erigoal]

Bon, il y a eu une discussion comme celle-la aujourd'hui sur OTN - nombre de colonnes inconnu-, tu peux aller voir la et t'inspirer de l'une des reponses donnees, mais encore une fois, tu n'as pas repondu ce que tu aller faire de la sortie.
Je suis fondamentalement contre ce genre de pratique (nombre de colonnes dynamiques), et je ne comprend pas vraiment pourquoi on en a besoin. En tout cas, Oracle avec SQL et PL/SQL n'est pas designe pour cela.

Nicolas.

Merci, je vais lire çà !
Je veux juste afficher ma table BILAN en pivot, sachant que je ne connais pas à l'avance le nombre de matières présentent dans ma colonne MATIERE.

[fatsora]

Bonsoir, j'ai essayé d'insérer le bout de code que tu m'as donné, mais je ne sais pas l'appeler...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
 
BEGIN
pivot_note();
END;
/
 
Erreur : ORA-06550: Ligne 2, colonne 1 :
PLS-00221: 'PIVOT_NOTE' n'est pas une procédure ou est indéfini
ORA-06550: Ligne 2, colonne 1 :
PL/SQL: Statement ignored

Merci, j'ai ajouté ton code ma procédure se crée, lorsque je l'appel la procedure j'ai "Instruction traitée." mais aucun affichage...


Merci beaucoup pour votre aide à tous !

Mais la c'est du copier coller !!

pour executer

Code :

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

1
2
3
4
 
set serverout on 
 
exec pivot_note.DATA(:x);

Mais oui , mais il faut lire aussi .....