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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
| -- ====================================================================== --
-- Author: ROMBEAU Jonathan --
-- Create date: 05 SEPTEMBER 2007 --
-- Description: Suppression des records pour une année concernant un fond --
-- ====================================================================== --
USE DEV_COMMISION -- On choisi la base de données à utiliser
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SUPPRESS_YEAR_OF_FUND
-- Add the parameters for the stored procedure here
@NUM_FOND INT = 0, -- CONTIENT L'ID DU FOND
@NUM_ANNEE INT = 0 -- CONTIENT L'ANNEE QUE L'ON VEUT SUPPRIMER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @NUM_ANNEE > 0 AND @NUM_FOND > 0 -- ON VERIFIE QUE L'ANNEE ET L'ID DU FOND SONT DONNES
BEGIN
-- DECLARATION DES VARIABLES
DECLARE @IDFOND AS BIGINT
DECLARE @IDYEAR AS BIGINT
DECLARE @IDINVEST AS BIGINT
DECLARE @IDLIST AS BIGINT
DECLARE @IDEXPO AS BIGINT
DECLARE @IDEXPOTYPE AS BIGINT
DECLARE @IDDERIV AS BIGINT
DECLARE @IDDERIVTYPE AS BIGINT
DECLARE @IDUNDER AS BIGINT
DECLARE @IDUNDERTYPE AS BIGINT
DECLARE @IDSHORT AS BIGINT
DECLARE @IDSHORTTYPE AS BIGINT
DECLARE @IDPUT AS BIGINT
DECLARE @IDPUTTYPE AS BIGINT
DECLARE @ERREURS INT -- VARIABLE POUR LES ERREURS
SET @ERREURS = 0 -- INTIALISATION A ZERO
-- OUVERTURE D'UN CURSEUR
DECLARE REC1 CURSOR FOR
SELECT FOND.ID_FUNDS as 'FOND',
ANNEE_FOND.ID_YEAR AS 'ANNEE',
INVEST.ID_INVESTMENT AS 'INVEST',
LISTING.ID_LISTING AS 'LISTING',
EXPOSURE.ID_EXPOSURE AS 'EXPOSURE',
EXPOTYPE.ID_EXPOSURE_TYPE AS 'EXPO TYPE',
DERIV.ID_DERIVATIVES AS 'DERIV',
DERIVTYPE.ID_DERIVATIVES_TYPE AS 'DERIV TYPE',
UNDER.ID_UNDERLYING AS 'UNDER',
UNDERTYPE.ID_UNDERLYING_TYPE AS 'UNDER TYPE',
SHORT.ID_SHORT_LONG AS 'SHORT',
SHORTTYPE.ID_SHORT_LONG_TYPE AS 'SHORT TYPE',
PUT.ID_PUT_CALL AS 'PUT CALL',
PUTTYPE.ID_PUT_CALL_TYPE AS 'PUT CALL TYPE'
FROM
(((((((((((((COM_USER.FUNDS AS FOND LEFT JOIN COM_USER.FUNDS_YEAR AS ANNEE_FOND ON FOND.ID_FUNDS = ANNEE_FOND.ID_FUNDS)
LEFT JOIN COM_USER.INVESTMENT AS INVEST ON ANNEE_FOND.ID_YEAR = INVEST.ID_YEAR)
LEFT JOIN COM_USER.LISTING_STATUS AS LISTING ON INVEST.ID_INVESTMENT = LISTING.ID_INVESTMENT)
LEFT JOIN COM_USER.EXPOSURE AS EXPOSURE ON LISTING.ID_LISTING = EXPOSURE.ID_LISTING)
LEFT JOIN COM_USER.EXPOSURE_TYPE AS EXPOTYPE ON EXPOSURE.ID_EXPOSURE_TYPE = EXPOTYPE.ID_EXPOSURE_TYPE)
LEFT JOIN COM_USER.DERIVATIVES AS DERIV ON EXPOSURE.ID_EXPOSURE = DERIV.ID_EXPOSURE)
LEFT JOIN COM_USER.DERIVATIVES_TYPE AS DERIVTYPE
ON (EXPOTYPE.ID_EXPOSURE_TYPE = DERIVTYPE.ID_EXPOSURE_TYPE
AND DERIV.ID_DERIVATIVES_TYPE = DERIVTYPE.ID_DERIVATIVES_TYPE))
LEFT JOIN COM_USER.UNDERLYING AS UNDER ON DERIV.ID_DERIVATIVES = UNDER.ID_DERIVATIVES)
LEFT JOIN COM_USER.UNDERLYING_TYPE AS UNDERTYPE ON UNDERTYPE.ID_UNDERLYING_TYPE = UNDER.ID_UNDERLYING_TYPE)
LEFT JOIN COM_USER.SHORT_LONG AS SHORT ON UNDER.ID_UNDERLYING = SHORT.ID_UNDERLYING)
LEFT JOIN COM_USER.SHORT_LONG_TYPE AS SHORTTYPE
ON (SHORT.ID_SHORT_LONG_TYPE = SHORTTYPE.ID_SHORT_LONG_TYPE
AND SHORTTYPE.ID_UNDERLYING_TYPE = UNDERTYPE.ID_UNDERLYING_TYPE))
LEFT JOIN COM_USER.PUT_CALL_TYPE AS PUTTYPE
ON SHORTTYPE.ID_SHORT_LONG_TYPE = PUTTYPE.ID_SHORT_LONG_TYPE))
LEFT JOIN COM_USER.PUT_CALL AS PUT
ON (SHORT.ID_SHORT_LONG = PUT.ID_SHORT_LONG
AND PUTTYPE.ID_PUT_CALL_TYPE = PUT.ID_PUT_CALL_TYPE)
WHERE FOND.ID_FUNDS = @NUM_FOND
AND FOND.ID_FUNDS = ANNEE_FOND.ID_FUNDS
AND ANNEE_FOND.ID_YEAR = (SELECT ID_YEAR
FROM COM_USER.FUNDS_YEAR
WHERE ID_FUNDS = @NUM_FOND
AND ANNEE = @NUM_ANNEE)
-- OUVERTURE DU 1ER RECORD
OPEN REC1
-- ON RECUPERE LES INFORMATIONS DU RECORD
FETCH REC1 INTO @IDFOND, @IDYEAR, @IDINVEST, @IDLIST, @IDEXPO, @IDEXPOTYPE,
@IDDERIV, @IDDERIVTYPE, @IDUNDER, @IDUNDERTYPE, @IDSHORT, @IDSHORTTYPE,
@IDPUT, @IDPUTTYPE
-- TANT QU'IL EXISTE DES ENREGISTREMENTS
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------------
-- DEBUT DE LA TRANSACTION --
-----------------------------
BEGIN TRANSACTION EFFACEMENT_DES_DONNEES
-- ON AFFICHE LES DONNEES QUI VONT ETRE SUPPRIMEES
SELECT @IDFOND AS 'FOND', @IDYEAR AS 'ANNEE', @IDINVEST AS 'INVEST',
@IDLIST AS 'LIST', @IDEXPO AS 'EXPO', @IDEXPOTYPE AS 'EXPO TYPE',
@IDDERIV AS 'DERIV', @IDDERIVTYPE AS 'DERIV TYPE',
@IDUNDER AS 'UNDER', @IDUNDERTYPE AS 'UNDER TYPE',
@IDSHORT AS 'SHORT', @IDSHORTTYPE AS 'SHORT TYPE',
@IDPUT AS 'PUT CALL', @IDPUTTYPE AS 'PUT CALL TYPE'
-- SUPPRESSION DANS LA TABLE PUT_CALL (2)
IF @IDPUT <> NULL
BEGIN
DELETE FROM COM_USER.PUT_CALL
WHERE ID_SHORT_LONG = @IDPUT
AND ID_PUT_CALL_TYPE = @IDPUTTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE PUT_CALL_TYPE
IF @IDPUTTYPE <> NULL
BEGIN
DELETE FROM COM_USER.PUT_CALL_TYPE
WHERE ID_PUT_CALL_TYPE = @IDPUTTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE SHORT_LONG_TYPE (2)
IF @IDSHORTTYPE <> NULL
BEGIN
DELETE FROM COM_USER.SHORT_LONG_TYPE
WHERE ID_SHORT_LONG_TYPE = @IDSHORTTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE SHORT_LONG
IF @IDSHORT <> NULL
BEGIN
DELETE FROM COM_USER.SHORT_LONG
WHERE ID_SHORT_LONG = @IDSHORT
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE UNDERLYING_TYPE
IF @IDUNDERTYPE <> NULL
BEGIN
DELETE FROM COM_USER.UNDERLYING_TYPE
WHERE ID_UNDERLYING_TYPE = @IDUNDERTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE UNDERLYING
IF @IDUNDER <> NULL
BEGIN
DELETE FROM COM_USER.UNDERLYING
WHERE ID_UNDERLYING = @IDUNDER
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE DERIVATIVES_TYPE (2)
IF @IDDERIVTYPE <> NULL
BEGIN
DELETE FROM COM_USER.DERIVATIVES_TYPE
WHERE ID_EXPOSURE_TYPE = @IDEXPOTYPE
AND ID_DERIVATIVES_TYPE = @IDDERIVTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE DERIVATIVES
IF @IDDERIV <> NULL
BEGIN
DELETE FROM COM_USER.DERIVATIVES
WHERE ID_DERIVATIVES = @IDDERIV
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE EXPOSURE_TYPE
IF @IDEXPOTYPE <> NULL
BEGIN
DELETE FROM COM_USER.EXPOSURE_TYPE
WHERE ID_EXPOSURE_TYPE = @IDEXPOTYPE
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE EXPOSURE
IF @IDLIST <> NULL
BEGIN
DELETE FROM COM_USER.EXPOSURE
WHERE ID_LISTING = @IDLIST
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE LISTING_STATUS
IF @IDLIST <> NULL
BEGIN
DELETE FROM COM_USER.LISTING_STATUS
WHERE ID_INVESTMENT = @IDINVEST
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE INVESTMENT (2)
IF @IDINVEST <> NULL
BEGIN
DELETE FROM COM_USER.INVESTMENT
WHERE ID_YEAR = @IDYEAR
AND ID_INVESTMENT = @IDINVEST
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- SUPPRESSION DANS LA TABLE FUNDS_YEAR (2)
IF @IDYEAR <> NULL
BEGIN
DELETE FROM COM_USER.FUNDS_YEAR
WHERE ID_FUNDS = @IDFOND
AND ID_YEAR = @IDYEAR
SET @ERREURS = @ERREURS + @@ERROR -- ON RECUPERE L'ERREUR EVENTUELLE
END
-- ON PASSE AU RECORD SUIVANT
FETCH REC1 INTO @IDFOND, @IDYEAR, @IDINVEST, @IDLIST, @IDEXPO, @IDEXPOTYPE,
@IDDERIV, @IDDERIVTYPE, @IDUNDER, @IDUNDERTYPE, @IDSHORT, @IDSHORTTYPE,
@IDPUT, @IDPUTTYPE
----------------------------------
-- VALIDATION DE LA TRANSACTION --
----------------------------------
PRINT 'Statut de l''erreur : ' + CAST(@ERREURS AS VARCHAR(10)) --On affiche le statut de l'erreur
IF @ERREURS = 0 -- SI ERREUR EST EGAL A ZERO IL N'Y A PAS EU D'ERRREURS
COMMIT TRANSACTION EFFACEMENT_DES_DONNEES -- ON VALIDE LA TRANSACTION
ELSE -- S'IL Y A EU DES ERREURS
ROLLBACK TRANSACTION EFFACEMENT_DES_DONNEES -- ON ANNULE TOUS LES CHANGEMENTS
END -- FIN DE LA BOUCLE WHILE
CLOSE REC1 -- FERMETURE DU RECORD
DEALLOCATE REC1 -- DESTRUCTION DE L'OBJET
END -- FIN DE LA CONDITION
ELSE -- SI ON L'UN DES DEUX OU LES DEUX VARIABLES NE SONT PAS MENTIONNEES
RETURN -1 -- ON RETOURNE UNE VALEUR FICTIVE
END |
[SQL] DELETE avec jointures
Répondre avec citation
Envoyé par DroP
N'hésitez pas à consulter 
N'oubliez pas le bouton 
et pensez aux balises [code]
Partager