SQL PROCEDURALE avec CURSEUR

**cobra91310** · 08/12/2008, 21h12

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 :

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

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 :

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

**SQLpro** · 08/12/2008, 22h51

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

Code :

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

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 :

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

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 :

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

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 :

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

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 :

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
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 :

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
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 +

**cobra91310** · 08/12/2008, 23h37

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

**cobra91310** · 09/12/2008, 16h31

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

SOUS FORME WHILE :

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
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 :

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
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'

SQL PROCEDURALE avec CURSEUR

Développement SQL Server

Discussions similaires

Partager

Partager