Discussion :

[djilos]

bonjour,

j'aimerais créer une Fonction qui me permettrait de calculer les chiffre d'affaire par client en utilisant la notion de Curseur et on ayant comme structure des tables :


client (id_client,nom,prenom)
facture (id_facture,id_client,montant)

voici la fonction que j'ai créer mais qui génere des erreurs lors de l'éxécution :

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
create function CA_Client (@num_client int) return money 
as
begin
declare @ca money ;
declare @id_client int;
declare my_cursor cursor for
select CLI_ID from dbo.T_CLIENT
open my_cursor fetch next from mu_cursor into @id_client
while @@fetch_status =0
  begin
   select @ca =@ca + montant from dbo.T_FACTURE where (dbo.T_CLIENT.CLI_ID = dbo.T_FACTURE.CLI_ID) and (dbo.T_FACTURE.CLI_ID=@id_client)
	fetch next from my_cursor into @id_client
	end 
	Close my_cursor
	deallocate my_cursor
	RETURN @ca
 
end

les erreurs générés sont les suivante :

Msg*156, Niveau*15, État*1, Procédure*CA_Client, Ligne*1
Syntaxe incorrecte vers le mot clé 'return'.
Msg*178, Niveau*15, État*1, Procédure*CA_Client, Ligne*16
Une instruction RETURN avec une valeur de retour ne peut être utilisée dans ce contexte.

j'ai compris qui y avait une erreur de syntaxe déja que j'ai pas réussi a trouvé et aussi la question que je me pose et : est ce que une fonction dans ce cas la est nécéssaire ?


merci de votre aide ,

[elsuket]

Bonjour,

Retenez que ce qu'il y a de pire en terme de performances pour une implémentation en SQL, c'est d'utiliser des curseurs.

Le langage SQL est par nature ensembliste. Il faut vous efforcer de voir les données que vous traitez non plus comme une entité mais comme un ensemble de données.

Votre requête se résume à cela :

Code :

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

1
2
3
4
SELECT C.id_client, SUM(F.montant) AS CA
FROM dbo.client AS C
INNER JOIN dbo.facture AS F ON C.id_client = F.id_client
GROUP BY C.id_client

Et que la clé étrangère id_client de la table facture soit indexée, ou pas, vous obtiendrez de bien meilleures performances (encore meilleure si elle est indexée )

Si vous voulez rechercher le CA par client :

Code :

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

1
2
3
4
5
SELECT C.id_client, SUM(F.montant) AS CA
FROM dbo.client AS C
INNER JOIN dbo.facture AS F ON C.id_client = F.id_client
WHERE C.num_client = @num_client
GROUP BY C.id_client

@++

[djilos]

c'est noté merci ..

ceci etant j'aimerais bien savoir comment ecrire cela en utilisant le curseur , pour savoir ou je me suis trompé . merci merci

[elsuket]

Il y a plusieurs erreurs dans votre fonction :

- sur la 1e ligne, le mot-clé est RETURNS (et non pas RETURN )

- vous n'avez pas initialisé @ca, donc @ca est à NULL, et NULL + quelque chose retourne NULL (NULL n'est pas une valeur, c'est une propriété signifiant l'absence de valeur)

- vous avez spécifié votre jointure dans la clause WHERE. Dans le cas de votre requête ça n'a peut-être pas d'impact, mais l'optimiseur s'attend à trouver les jointures derrière l'opérateur JOIN, et cela peut être contre-performant dans certains cas

@++

[djilos]

MERCI POUR REPONSES ,

est ce que ce bout de code qui est tiré de la fonction qui est posté plus haut est correct en initalisant @ca a zero

Code :

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

set @ca=0

?? merci

Code :

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

 SELECT @ca =@ca + montant FROM dbo.T_FACTURE WHERE (dbo.T_CLIENT.CLI_ID = dbo.T_FACTURE.CLI_ID) AND (dbo.T_FACTURE.CLI_ID=@id_client)

[elsuket]

Pour écrire plus proprement votre fonction, vous pouviez par exemple faire:

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 FUNCTION CA_Client (@num_client INT)
	RETURNS MONEY 
AS
BEGIN
	DECLARE @ca MONEY; SET @ca = 0;
	DECLARE @id_client INT;
 
	DECLARE my_cursor CURSOR FOR
		SELECT CLI_ID
		FROM dbo.T_CLIENT
	FOR READ ONLY;
 
	OPEN my_cursor;
	FETCH NEXT FROM mu_cursor INTO @id_client;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @ca = @ca + F.montant 
		FROM dbo.T_FACTURE AS F
		INNER JOIN  dbo.T_CLIENT AS C ON C.CLI_ID = F.CLI_ID
		WHERE F.CLI_ID = @id_client
 
		FETCH NEXT FROM my_cursor INTO @id_client
	END
	CLOSE my_cursor
	DEALLOCATE my_cursor
 
	RETURN @ca
END

ou encore :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION CA_Client (@num_client INT)
	RETURNS MONEY 
AS
BEGIN
	SELECT @ca = ISNULL(@ca, 0) + F.montant 
	FROM dbo.T_FACTURE AS F
	INNER JOIN  dbo.T_CLIENT AS C ON C.CLI_ID = F.CLI_ID
 
	RETURN @ca
END

Mais finalement votre fonction devrait être :

Code :

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

1
2
3
4
5
6
7
CREATE FUNCTION CA_Client (@num_client INT)
	RETURNS MONEY 
AS
BEGIN
	SELECT CAST(SUM(montant) AS MONEY) AS CA
	FROM dbo.facture
END

Car vous aurez compris que le traitement ensembliste que vous propose CTEMan est clairement plus rapide que le traitement itératif par curseur.

Vous voyez donc que le paramètre @num_client n'est plus utile (comme dans le code de la fonction que vous avez donné dans votre premier post ).
Dès lors votre fonction n'accepte plus de paramètres, et vous devriez donc en faire une procédure stockée.

Voici deux exemples de procédure et un cas d'utilisation pour chacune d'entre-elles :

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
CREATE PROCEDURE ps_CA_Client
	@ca MONEY = 0 OUTPUT
AS
BEGIN
	SELECT @ca = ISNULL(CAST(SUM(montant) AS MONEY), 0)
	FROM dbo.facture
END
GO
 
DECLARE @chiffre_affaire MONEY
EXEC dbo.ps_CA_Client @chiffre_affaire OUTPUT
SELECT @chiffre_affaire
GO
----------------------------------------------------------------
CREATE PROCEDURE ps_CA_Client_2
AS
BEGIN
	RETURN 
	(
		SELECT ISNULL(CAST(SUM(montant) AS MONEY), 0)
		FROM dbo.facture
	)
END
GO
 
DECLARE @chiffre_affaire MONEY
EXEC @chiffre_affaire = dbo.ps_CA_Client_2
SELECT @chiffre_affaire
GO

A vous de choisir celui qui vous plaît

@++