Utilisation des curseurs et des boucles [Résolu]

[Kropernic]

Bonjour à tous,

Je ne sais pas trop comment présenté le problème de manière claire et concise donc je vais rentrer directement dans le vif du sujet ^^.

Actuellement, il existe une base de donnée nommée GIFTCARD et qui contient cette table :

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
USE [GIFTCARD]
GO
/****** Object:  Table [dbo].[ChequeAchatHistory]    Script Date: 07/01/2011 10:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChequeAchatHistory](
	[SeqNrHist] [int] IDENTITY(1,1) NOT NULL,
	[BarcodeMin] [varchar](13) COLLATE Latin1_General_CI_AS NOT NULL,
	[BarcodeMax] [varchar](13) COLLATE Latin1_General_CI_AS NOT NULL,
	[DatTim] [datetime] NOT NULL,
	[Action] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
	[Origin] [int] NULL,
	[DestType] [int] NULL,
	[Dest] [int] NULL,
	[Value] [int] NULL,
	[SaleType] [int] NULL,
	[UserType] [int] NULL
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Comme vous l'aurez probablement compris, cette table contient l'historique de toutes les transactions concernant des bons d'achat ou des giftcards. Ces bons/cartes étant souvent vendues par lot, l'auteur de la table a jugé bon (probablement à raison) d'écrire un record par lot plutôt que par bon/carte. Lors de la vente d'un lot, comme les barcodes se suivent, il écrit donc le premier (barcodemin) et le dernier (barcodemax).

Et jusqu'ici, aucun souci.

Maintenant, pour une demande particulière, j'ai besoin d'avoir un record par bon/carte.
J'ai donc créé une DB de test qui contient la table suivante :

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
USE [test]
GO
/****** Object:  Table [dbo].[tbTest]    Script Date: 07/01/2011 10:17:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbTest](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[type] [char](5) COLLATE Latin1_General_CI_AS NULL,
	[barcode] [bigint] NOT NULL,
	[date] [datetime] NOT NULL,
	[action] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[origine] [int] NULL,
	[destType] [int] NULL,
	[dest] [int] NULL,
	[valeur] [int] NULL,
	[saleType] [int] NULL,
	[userType] [int] NULL,
 CONSTRAINT [PK_tbTest] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

En gros, il s'agit de la même que précédement à ceci près que la colonne type reprend en fait les 5 premiers digits du barcode et la colonne barcode (le nom est probablement mal choisi) reprend les 7 digits restant (le checkdigit n'étant pas renseigné).


Sur base d'exemples trouvés sur divers forum et sur le site de MSDN, j'ai écrit le code suivant :

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
 
declare @strmin AS char(12), @strmax AS char(12), @date AS datetime, @action AS char(1), @origine AS int, @destinationType AS int, 
		@destination AS int, @valeur AS int, @sale AS int, @user AS int, @minimum AS bigint, @maximum AS bigint
 
DECLARE GiftCard_Cursor CURSOR FOR
SELECT barcodemin, barcodemax, dattim, action, origin, desttype, dest, value, saletype, usertype
FROM GIFTCARD.dbo.chequeachathistory
WHERE barcodemin LIKE '24501%';
OPEN GiftCard_Cursor;
FETCH NEXT FROM GiftCard_Cursor
INTO @strmin, @strmax, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user;
 
SET @minimum = cast(substring(@strmin,6,7) AS bigint);
SET @maximum = cast(substring(@strmax,6,7) AS bigint);
 
while NOT (@minimum = @maximum)
	begin
	INSERT INTO test.dbo.tbTest VALUES('24501',@minimum, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user);
 
	SET @minimum = @minimum+1;
	end
 
 
CLOSE GiftCard_Cursor;
DEALLOCATE GiftCard_Cursor;
GO

Il ne m'affiche aucune erreur et lorsque je l'exécute, j'ai bien le message m'informant que la commande a bien été exécutée mais la table tbTest reste invariablement vide.

Je dois bien avouer que c'est la première fois que j'utilise ce genre d'instruction dans sql server. Mes actions se limitent en général à des requêtes classiques.

Pourriez-vous donc m'aider à débugger ce bout de code afin que je puisse alimenter ma table tbTest ?

Voilà, j'espère avoir données toutes les informations nécessaires.

Merci d'avance,

Griftou.

[Kropernic]

Bon, ça m'apprendra à faire des trucs sans trop comprendre le pourquoi du comment...

En approfondissant, je me suis rendu compte que mon curseur ne lisait que la première ligne de la requête de sélection.

J'ai modifié comme ci-dessous et ça a l'air de fonctionné.

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
declare @strmin AS char(12), @strmax AS char(12), @date AS datetime, @action AS char(1), @origine AS int, @destinationType AS int, 
		@destination AS int, @valeur AS int, @sale AS int, @user AS int, @minimum AS bigint, @maximum AS bigint
 
DECLARE GiftCard_Cursor CURSOR FOR
SELECT barcodemin, barcodemax, dattim, action, origin, desttype, dest, value, saletype, usertype
FROM GIFTCARD.dbo.chequeachathistory
WHERE barcodemin LIKE '24501%';
OPEN GiftCard_Cursor;
FETCH NEXT FROM GiftCard_Cursor
INTO @strmin, @strmax, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user;
 
while @@FETCH_STATUS = 0
begin
 
SET @minimum = cast(substring(@strmin,6,7) AS bigint);
SET @maximum = cast(substring(@strmax,6,7) AS bigint)+1;
while NOT (@minimum = @maximum)
	begin
	INSERT INTO test.dbo.tbTest VALUES('24501',@minimum, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user);
 
	SET @minimum = @minimum+1;
	end
FETCH NEXT FROM GiftCard_Cursor
INTO @strmin, @strmax, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user;
end;
 
CLOSE GiftCard_Cursor;
DEALLOCATE GiftCard_Cursor;
GO

Je dois juste encore vérifier si les données sont cohérentes.

[aieeeuuuuu]

Bonjour


Il serait plus simple de vous passer de votre curseur et de votre boucle. Ce serait en outre plus performant.

Il vous suffit pour cela de créer (ou générer à la volée) une table de nombres couvrant l'ensemble de vos barcode, et de faire un jointure dessus

[Ptit_Dje]

Lorsque vous dites pour une demande particuliere, est ce que cette demande concerne l'historique aussi ?

Si oui, je vous suggererais d'utiliser une CTE recursive pour generer vos barcodes.

Concernant la creation d'une nouvelle table, est ce que l'impact est limite ?
Sinon une idee serait de fixer barcode min = barcode max et de reutiliser la meme table, ce qui pourrait a mon sens eviter trop de changement colateraux (apres ca depend de votre temps et de l'impact potentiel).

Suite a cela, vous pourrez supprimer vos rows ou barcode min est different de barcode max - pour autant qu'il n'y ai pas eu de lot de 1 vendu. A verifier.

[Kropernic]

Bonjour,

Déjà, merci de vous intéresser à mon problème :-)

Par contre, il faut savoir que je suis plutôt débutant. Du coup, même si je vois ce qu'est une CTE et si j'en ai déjà utilisé une fois ou deux, je ne vois pas comment l'utiliser pour le cas qui m'occupe.

Il vous suffit pour cela de créer (ou générer à la volée) une table de nombres couvrant l'ensemble de vos barcode, et de faire un jointure dessus

Comment faites-vous cela ?

Concernant la creation d'une nouvelle table, est ce que l'impact est limite ?
Sinon une idee serait de fixer barcode min = barcode max et de reutiliser la meme table, ce qui pourrait a mon sens eviter trop de changement colateraux (apres ca depend de votre temps et de l'impact potentiel).

Je ne comprends pas vraiment la question :-/



Au passage, le code que j'avais posté ne portait que sur un type de bons/cartes (ceux dont le barcode commence par 24501) et me servait de test. Avec ce code, pas de souci. Par contre après, j'ai donc retiré cette restriction. Tout avait l'air de bien fonctionner jusqu'à un moment où le script continuait de tourner mais plus aucun nouveau messages "1 row(s) affected" n'apparaissait. Est-ce un bug dans mon script ou bien l'onglet message de management studio à une limite de ligne qu'il ne peut dépasser ?

[aieeeuuuuu]

non, je pense surtout que vous entrez dans une boucle infinie car vous ne reinitialisez pas @minimum et @maximum entre deux produits.
Tel que vous le faites, il vous faudrait donc deux boucles :

Celle que vous aviez dans votre premier post, englobée dans une autre boucle qui fait le fetch et l'initialisation de @minimum et @maximum pour chaque chequeAchat
Pour être plus clair, une boucle pour chaque ligne de la table source, dans laquelle vous avez une boucle pour chaque ligne insérée dans la table cible.

pour la génération de la table des nombres, je reviens vers vous avec un exemple que je vous laisserai adapter...

[Kropernic]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
while @@FETCH_STATUS = 0
begin
 
SET @minimum = cast(substring(@strmin,6,7) AS bigint);
SET @maximum = cast(substring(@strmax,6,7) AS bigint)+1;
while NOT (@minimum = @maximum)
	begin
	INSERT INTO test.dbo.tbTest VALUES('24501',@minimum, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user);
 
	SET @minimum = @minimum+1;
	end
FETCH NEXT FROM GiftCard_Cursor
INTO @strmin, @strmax, @date, @action, @origine, @destinationtype, @destination, @valeur, @sale, @user;
end;

Il y a quand même bien 2 boucles avec un set de minimum et maximum après chaque fetch non ?

[aieeeuuuuu]

En effet, je n'avais pas vu la deuxième boucle.
L'erreur vient peut etre de vos données :
un barcodemin > barcodemax par exemple...

voici l'exemple dont je vous parlais pour la génération des nombres.

Avec ceci, vous pouvez remplacer tout votre code par une seule requete

Code SQL :

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
 
CREATE TABLE Source (
	barcodemin VARCHAR(13),
	barcodemax VARCHAR(13),
	value INT
)
 
CREATE TABLE Cible (
	barcode BIGINT,
	value INT
)
 
--insertion du jeu de donnees
INSERT INTO Source (barcodemin, barcodemax, value)
VALUES
	('1123456783456', '1123456783464', 1),
	('1123456783465', '1123456783474', 2),
	('1123456783478', '1123456783492', 3)
 
 
--et pour peupler la table cible :
;WITH N(x, maxi) AS ( --Table des code barres 
	SELECT MIN(CAST(barcodemin AS BIGINT)), MAX(barcodemax) --bornes inferieures et supérieure des codes barre
	FROM Source
	UNION ALL
	SELECT x + 1, maxi --code barre suivant...
	FROM N
	WHERE x < maxi --... jusqu'au code barre maxi
)
INSERT INTO Cible(barcode, value)
SELECT 
	x, --code barre generé par la CTE recursive
	value -- colonne de la table source
FROM N
INNER JOIN Source S
	ON N.x BETWEEN S.barcodemin AND S.barcodemax --le code barre generé est entre le code barre min et le code barre max
OPTION (MAXRECURSION 0) --pas de limite de recursion

[aieeeuuuuu]

Code SQL :

1
2
 
declare @strmin AS char(12), @strmax AS char(12),

vos codes barre ne font-ils pas 13 caractères ???

dans votre table source, c'est du varchar(13)

du coup, vous tronquez le dernier chiffre, et vos calculs sont faux.

[Kropernic]

A première vue, je crois pas que votre code fonctionnerait dans mon cas (mais il y a probablement moyen de l'adapter sans trop d'effort).

Je pense cela car vous prenez min et max de barcodemin et barcodemax mais, comme je l'ai mentionné auparavant (sans trop insisté, je vous l'accorde), il y a différent type de bons et ces types sont différencié par les 5 premiers digits du barcode.

Par exemple, il existe les types suivant :
- 24501
- 24502
- 24503

Ce sont là les 5 premiers digits des barcodes de ces bons/cartes. Les digits suivant étant l'équivalent d'un numéro de série.

Il est donc plus que certain que le range entre 245010000001 et 245010000002 ne sera pas entièrement rempli.

Maintenant, j'imagine qu'il doit être possible d'adapter le code que vous avez fourni pour tenir compte de cela.

EDIT : Ou alors je n'ai pas compris ce que fait exactement votre procédure

Concernant votre remarque :

vos codes barre ne font-ils pas 13 caractères ???

dans votre table source, c'est du varchar(13)

du coup, vous tronquez le dernier chiffre, et vos calculs sont faux.

L'auteur de la table source a prévu 13 places mais le checkdigit n'étant jamais repris, il n'y en a que 12 d'utilisées. J'ai trouvé logique alors de déclarer @strmin en char(12).

Deplus, même si le checkdigit était repris, celui-ci ne sert qu'à vérifier l'intégrité du barcode et ne devrait de toute façon pas être pris en compte dans les calculs.

Bien à vous,

[aieeeuuuuu]

en effet, la CTE est générique.
Rien ne vous empêche de restreindre la plage aux seuls barcodes nécessaire, en ajoutant par exemple votre filtre sur le préfixe des code barres dans l'ancrage de la CTE

Cependant, à part dégrader les perfs pour rien, les code barre générés en trop n'auront pas d'impact sur le résultat, puisqu'il qu'ils passeront à la trappe par la jointure interne...

pour les codes sur 12 ou 13 chiffres, un petit jeu de données rendrait cela plus concret et on pourrait faire quelques tests. je pense que l'erreur vient des données en elles-memes.

[Kropernic]

Voici en pièce jointe un fichier contenant quelques records de la table qui nous occupe.

Entre temps, je vais tenter d'adapter votre code à mes besoins afin de tester cela.

EDIT :

J'ai donc fait ceci :

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
WITH N(x, maxi) AS ( --Table des code barres 
	SELECT MIN(CAST(barcodemin AS BIGINT)), MAX(cast(barcodemax AS bigint)) --bornes inferieures et supérieure des codes barre
	FROM giftcard.dbo.chequeachathistory
	WHERE substring(barcodemin,1,5) IN ('21056','21106','21256')
	UNION ALL
	SELECT x + 1, maxi --code barre suivant...
	FROM N
	WHERE x < maxi --... jusqu'au code barre maxi
)
INSERT INTO test.dbo.tbtest(type, barcode, date, action, origine, desttype, dest, valeur, saletype, usertype)
SELECT 
	N.x, --code barre generé par la CTE recursive
	substring(s.barcodemin,1,5),
	s.dattim,
	s.action,
	s.origin,
	s.desttype,
	s.dest,
	s.value, -- colonne de la table source
	s.saletype,
	s.usertype
FROM N
INNER JOIN giftcard.dbo.chequeachathistory S
	ON N.x BETWEEN S.barcodemin AND S.barcodemax --le code barre generé est entre le code barre min et le code barre max
OPTION (MAXRECURSION 0) --pas de limite de recursion

Et j'obtiens l'erreur suivante :

Arithmetic overflow error converting expression to data type varchar.

qui pointe sur la ligne avec WITH dans la déclaration de la CTE. Je suppose que cela vient du cast mais l'erreur dit qu'il essaie de convertir en varchar. Comme la seule conversion est en bigint, je suis un peu perdu ^^.

[Ptit_Dje]

Citation:
Concernant la creation d'une nouvelle table, est ce que l'impact est limite ?
Sinon une idee serait de fixer barcode min = barcode max et de reutiliser la meme table, ce qui pourrait a mon sens eviter trop de changement colateraux (apres ca depend de votre temps et de l'impact potentiel).

Je ne comprends pas vraiment la question :-/

L'idee est que pour un changement de granularite (on veut 1 record par barcode au lieu d'un record par lot), vous recreez une nouvelle table, avec une nouvelle structure.
Cela peut avoir des impacts sur diverses applications, autant une application live, qu'un ETL, ou qu'une application de reporting.

Avec la structure actuelle de votre table, il est pourtant possible d'obtenir ce nouveau niveau de granularite, au cout d'un varchar(13) duplique. En effet, si barcodemin est egal a barcodemax, cela cree un lot de 1 objet.

[Kropernic]

Il n'a jamais été question de toucher à la structure de la table actuelle. La table créer n'est destinée qu'à un usage personnel et ponctuel afin de facilité l'écriture de certaines requêtes car je ne suis pas encore assez familier avec le language sql .

[Ptit_Dje]

Autant pour moi.

Votre erreur provient d'un mauvais alignement entre vos colonnes dans votre insert et celles de votre select.

[Kropernic]

Bonjour à tous !

J'ai trouvé ce qui provoquait la boucle infinie de ma procédure avec curseur (car c'était bel et bien une boucle infinie).

Pour 2 type de barcode, j'avais des records ou barcodemin était plus grand que barcodemax. Du coup, boucle infinie sur le while car le condition de sortie n'était jamais atteinte.

Voilà donc un problème résolu

Merci encore à tous !