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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
/* On supprimer le lien avec le serveur LullyResultat */
EXEC sp_dropserver 'LullyResultat', 'droplogins'
GO
/* On creer un lien avec la base de donnée ACCESS */
EXEC sp_addlinkedserver
@server = N'LullyResultat',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'D:\SQL-Micrologic\4F\resultatlully.mdb';
GO
Print 'Debut de déclaration des variables'
DECLARE @Adatemarq as datetime
DECLARE @Anumserie as int
DECLARE @Anumuv as int
DECLARE @Adateres as char(25)
DECLARE @Aresultat as int
DECLARE @Atestdef as int
DECLARE @Aindexres as int
DECLARE @Tindexres as int
DECLARE @Tnumtest as int
DECLARE @Tresultat as int
DECLARE @Tval1 as float
DECLARE @Tval2 as float
DECLARE @Tval3 as float
DECLARE @Tval4 as float
DECLARE @Tval5 as float
DECLARE @Tval6 as float
DECLARE @Tval7 as float
DECLARE @Tval8 as float
DECLARE @Tval9 as float
DECLARE @Tval10 as float
DECLARE @Tval11 as float
DECLARE @Tval12 as float
DECLARE @Tval13 as float
DECLARE @OldNumSerie as int
-- Delclaration du curseur, avec ça requéte
DECLARE MyCursor CURSOR FOR
SELECT Appareil.datemarq, Appareil.numserie, Appareil.numuv, Appareil.dateres, Appareil.resultat, Appareil.testdef,
Appareil.indexres, TEST.indexres, TEST.numtest, TEST.resultat, TEST.val1, TEST.val2, TEST.val3,
TEST.val4, TEST.val5, TEST.val6, TEST.val7, TEST.val8, TEST.val9, TEST.val10, TEST.val11, TEST.val12, TEST.val13
FROM LullyResultat...Appareil LEFT JOIN LullyResultat...TEST ON Appareil.indexres = TEST.indexres
WHERE Appareil.numserie>0 AND Appareil.numuv>0 AND Appareil.datemarq>='20/05/2011' ORDER BY Appareil.numserie, Appareil.datemarq
-- ouverture du curseur
OPEN MyCursor
-- Initialisation du numero de serie
Set @OldNumSerie=0
-- On remplie avec les premiéres données reçus
FETCH MyCursor INTO @Adatemarq, @Anumserie, @Anumuv, @Adateres, @Aresultat, @Atestdef, @Aindexres, @Tindexres, @Tnumtest,
@Tresultat, @Tval1, @Tval2, @Tval3, @Tval4, @Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13
Print 'Début de la boucle'
WHILE @@fetch_Status = 0
BEGIN
-- si le ces un nouveau produit
IF @OldNumSerie != @Anumserie
BEGIN
-- Memorise le numero de serie
Set @OldNumSerie = @Anumserie
-- Insertion du produit dans la table declencheurs
INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].Declencheurs
([Date], [N_Serie], [N_UV], [res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20], [pris_en_compte])
VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Aresultat, @Atestdef, 0, @Anumuv,
0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1)
END
-- Insertion des controles liés au produit dans la table TESTS
IF @Aresultat=2 AND @Atestdef = @Tnumtest
-- Test Defectueux
INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].TESTS
([Date], [N_Serie], [N_UV], [N_TEST], [N_Testeur], [pris_en_compte], [Perturbant],
[res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20])
VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Tnumtest, 1, 0, 0, 2, @Tval1, @Tval2, @Tval3, @Tval4,
@Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13, 0, 0, 0, 0, @TResultat, 0)
Else
-- Test bon
INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].TESTS
([Date], [N_Serie], [N_UV], [N_TEST], [N_Testeur], [pris_en_compte], [Perturbant],
[res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20])
VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Tnumtest, 1, 0, 0, 1, @Tval1, @Tval2, @Tval3, @Tval4,
@Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13, 0, 0, 0, 0, @TResultat, 0)
-- Données suivantes
FETCH MyCursor INTO @Adatemarq, @Anumserie, @Anumuv, @Adateres, @Aresultat, @Atestdef, @Aindexres, @Tindexres,
@Tnumtest, @Tresultat, @Tval1, @Tval2, @Tval3, @Tval4, @Tval5, @Tval6, @Tval7,
@Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13
END
-- Fermeture du curseur, libération de la memoire
CLOSE myCursor
DEALLOCATE myCursor
Print '----------------- Terminé ------------------------' |
Optimisation Procédure Stockée
Répondre avec citation
Partager