SQL PROCEDURALE avec CURSEUR

Version imprimable

Bonsoir,

Voila j'aurais voulu afficher uniquement le CA mini et maxi d'un représentant par ville et donc au résultat avoir une ligne par VILLE uniquement regroupant le CA mini et maxi de celui-ci.

Sachant que j'ai 5 villes différentes au total et que chaque ville peut avoir plusieurs représentant comme la requête ci-dessous vous le montre :
Code:

1 2 3 4 SELECT R.NREP,VILLE, sum(montant) FROM BUREAU B, COMMANDE CMD, REPRESENTANT R WHERE B.NBUR = R.NBUR and R.NREP = CMD.NREP GROUP BY VILLE, R.NREP
NREP VILLE
106 NEW YORK 32958.00
109 NEW YORK 33130.00
101 CHICAGO 3978.00
103 CHICAGO 37795.00
104 CHICAGO 67500.00
105 ATLANTA 35582.00
102 LOS ANGELES 29276.00
108 LOS ANGELES 51533.00
107 DENVER 3082.00

En ce moment je bloque sur ce code :
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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 DECLARE @CA_mini dec(9,2) DECLARE @CA_maxi dec(9,2) DECLARE @VILLE varchar(25) DECLARE @montant dec(9,2) DECLARE @VILLE2 varchar(25) SET @CA_mini = 50000 SET @CA_maxi = 0 DECLARE exo2 CURSOR FOR SELECT VILLE FROM BUREAU B OPEN exo2 FETCH FROM exo2 INTO @VILLE2 BEGIN WHILE @@FETCH_STATUS = 0 BEGIN BEGIN DECLARE exo CURSOR FOR SELECT VILLE, sum(montant) FROM BUREAU B, COMMANDE CMD, REPRESENTANT R WHERE B.NBUR = R.NBUR and R.NREP = CMD.NREP and ville = @ville2 GROUP BY VILLE, R.NREP OPEN exo FETCH FROM exo INTO @VILLE, @montant BEGIN WHILE @@FETCH_STATUS = 0 BEGIN BEGIN IF @MONTANT > @CA_maxi SET @CA_maxi = @MONTANT IF @MONTANT < @CA_mini SET @CA_mini = @MONTANT END FETCH FROM exo INTO @VILLE, @montant END END CLOSE exo DEALLOCATE exo END PRINT 'La ville de ' + @ville2 + ' qui a un CA mini de '+convert(char(9),@CA_mini)+'et un CA maxi de '+convert(char(9),@CA_maxi) FETCH FROM exo2 INTO @VILLE2 END END CLOSE exo2 DEALLOCATE exo2
Celui-ci fait bien une partie de ceux que je veux mais le problème qui me coince est la réinitialisation de CA_mini et CA_maxi chaque fois que la ville change.

Pouvez-vous me proposer une aide pour me débloquer s'il vous plaît et si possible l'explication qui va avec toujours dans le cadre de ma formation et savoir ce que je fais.

Merci a vous

Pourquoi voulez-vous faire un curseur ? C'est totalement contre performant alors qu'en une seule requête c'est facile et performant !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE T_CAV
(NREP    INT,
 VILLE   VARCHAR(16),
 CA      DECIMAL(16,2))
 
INSERT INTO T_CAV VALUES (106, 'NEW YORK', 32958.00)
INSERT INTO T_CAV VALUES (109, 'NEW YORK', 33130.00)
INSERT INTO T_CAV VALUES (101, 'CHICAGO', 3978.00)
INSERT INTO T_CAV VALUES (103, 'CHICAGO', 37795.00)
INSERT INTO T_CAV VALUES (104, 'CHICAGO', 67500.00)
INSERT INTO T_CAV VALUES (105, 'ATLANTA', 35582.00)
INSERT INTO T_CAV VALUES (102, 'LOS ANGELES', 29276.00)
INSERT INTO T_CAV VALUES (108, 'LOS ANGELES', 51533.00)
INSERT INTO T_CAV VALUES (107, 'DENVER', 3082.00)

la requête :

Code:

1
2
3
4
5
6
SELECT VILLE, MAX(CA) AS CA_MAX, 
       (SELECT MIN(CA)
        FROM   T_CAV T2
        WHERE  T1.VILLE = T2.VILLE) AS CA_MIN 
FROM   T_CAV T1
GROUP  BY VILLE

Le résultat :

Code:

1
2
3
4
5
6
7
VILLE            CA_MAX             CA_MIN             
---------------- ------------------ ------------------ 
ATLANTA          35582.00           35582.00
CHICAGO          67500.00           3978.00
DENVER           3082.00            3082.00
LOS ANGELES      51533.00           29276.00
NEW YORK         33130.00           32958.00

Bien entendu si votre table n'existe pas et qu'elle est elle-même une requête, rien de plus simple...
Soit vous en faites une vue :

Code:

1
2
3
4
5
6
7
8
9
CREATE VIEW  T_CAV
AS
SELECT R.NREP, VILLE, sum(montant) AS CA
FROM   BUREAU B
       INNER JOIN REPRESENTANT R
             ON B.NBUR = R.NBUR
       INNER JOIN COMMANDE CMD
             ON R.NREP = CMD.NREP
GROUP BY VILLE, R.NREP

et la requête que je vous ais donné marche...

Soit vous l'incorporez en tant que table dérivée sous la forme d'une sous requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT VILLE, MAX(CA) AS CA_MAX, 
       (SELECT MIN(CA)
        FROM   (SELECT R.NREP, VILLE, sum(montant) AS CA
                FROM   BUREAU B
                       INNER JOIN REPRESENTANT R
                             ON B.NBUR = R.NBUR
                       INNER JOIN COMMANDE CMD
                             ON R.NREP = CMD.NREP
                GROUP BY VILLE, R.NREP) T2
        WHERE  T1.VILLE = T2.VILLE) AS CA_MIN 
FROM   (SELECT R.NREP, VILLE, sum(montant) AS CA
        FROM   BUREAU B
               INNER JOIN REPRESENTANT R
                     ON B.NBUR = R.NBUR
               INNER JOIN COMMANDE CMD
                     ON R.NREP = CMD.NREP
        GROUP BY VILLE, R.NREP) T1
GROUP  BY VILLE

Soit vous êtes sous 2005 ou 2008 et vous pouvez utiliser une CTE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH T_CAV
AS
(SELECT R.NREP, VILLE, sum(montant) AS CA
 FROM   BUREAU B
        INNER JOIN REPRESENTANT R
              ON B.NBUR = R.NBUR
        INNER JOIN COMMANDE CMD
              ON R.NREP = CMD.NREP
 GROUP BY VILLE, R.NREP
)
SELECT VILLE, MAX(CA) AS CA_MAX, 
       (SELECT MIN(CA)
        FROM   T_CAV T2
        WHERE  T1.VILLE = T2.VILLE) AS CA_MIN 
FROM   T_CAV T1
GROUP  BY VILLE

De toute façon aucune de ces requête ne sera plus ou moins rapide que l'autre. mais ce qui est certain c'est que ce sera beaucoup plus rapide qu'un curseur !

Bref, commencez par apprendre la SQL ! Mon site, comme mes bouquins sont là pour vous aider !

A +

08/12/2008, 23h37
cobra91310

Désoler j'aurais du le mentionner mais c'est justement dans le cadre d'un exercice tout comme la dernière fois ou la en l' occurence on doit obligatoirement utiliser des curseurs :s

Concernant votre livre, je l'ai parcouru mais je garde les exercices au chaud pour compléter justement ma formation actuel qui visiblement semble remplie d' ineptie ....

Mais malheureusement je dois suivre les consignes données par le Prof ;)

Bon j'ai réussi au final ça donne ça avec quelque notion de son cours supplémentaires...

SOUS FORME WHILE :

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
37
38
39
40
41
42
DECLARE @CA_mini dec(9,2)
DECLARE @CA_maxi dec(9,2)
DECLARE @VILLE varchar(25)
DECLARE @MONTANT dec(9,2)
DECLARE @CR_VILLE varchar(25)
 
DECLARE exo CURSOR FOR
SELECT VILLE, sum(montant)   
FROM BUREAU B, COMMANDE CMD, REPRESENTANT R
WHERE B.NBUR = R.NBUR and R.NREP = CMD.NREP
GROUP BY VILLE, R.NREP
 
OPEN exo
 
FETCH FROM exo INTO @VILLE, @montant
 
IF @@FETCH_STATUS = 0
BEGIN
    WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @CR_VILLE = @VILLE
	SET @CA_maxi = @MONTANT
	SET @CA_mini = @MONTANT
 
		WHILE @@FETCH_STATUS = 0 and @CR_VILLE = @VILLE         
			BEGIN
			IF @MONTANT > @CA_maxi
				begin
				SET @CA_maxi = @MONTANT
				end
			IF @MONTANT < @CA_mini
				begin
				SET @CA_mini = @MONTANT
				end
 
			FETCH FROM exo INTO @VILLE, @MONTANT 
			END			
	PRINT 'La ville de ' + @CR_VILLE + ' a un CA_mini de '+convert(char(9),@CA_mini)+' et un CA_maxi de '+convert(char(9),@CA_maxi)
	END
END
CLOSE exo
DEALLOCATE exo

SOUS FORME ETIQUETTE :

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
37
38
39
40
41
42
43
44
45
46
47
DECLARE @CA_mini dec(9,2)
DECLARE @CA_maxi dec(9,2)
DECLARE @VILLE char(16)
DECLARE @montant dec(9,2)
DECLARE @CR_VILLE varchar(25)
 
PRINT 'BUREAU          CA MINI      CA MAXI'
Print ''
DECLARE exo CURSOR FOR
SELECT VILLE, sum(montant)   
FROM BUREAU B, COMMANDE CMD, REPRESENTANT R
WHERE B.NBUR = R.NBUR and R.NREP = CMD.NREP
GROUP BY VILLE, R.NREP
-- DEBUT
open exo
 
FETCH FROM exo INTO @VILLE, @montant
 
IF @@FETCH_STATUS = 0
BEGIN
-- DEBUT VILLE
DEBUT_CA_MAXI:
	SET @CR_VILLE = @VILLE
	SET @CA_maxi = @MONTANT
	IF @montant > @CA_maxi
		begin
		SET @CA_maxi = @MONTANT
		end
-- DEBUT REPRESENTANT
DEBUT_CA_MINI:
	SET @CA_mini = @MONTANT
	IF @MONTANT < @CA_mini
		begin
		SET @CA_mini = @MONTANT
		end
-- FIN VILLE
FETCH FROM exo INTO @VILLE, @montant
IF @VILLE = @CR_VILLE and @@FETCH_STATUS = 0 GOTO DEBUT_CA_MINI
-- FIN REPRESENTANT 
PRINT @CR_VILLE+''+convert(char(12),@CA_maxi)+' '+convert(char(12),@CA_mini)
IF @@FETCH_STATUS = 0 GOTO DEBUT_CA_MAXI
-- FIN REPRESENTANTS
CLOSE exo
DEALLOCATE exo
END
else 
print 'Anomalie : Aucune ligne dans la requete'