Aller au contenu

Code source wiki de Requête SQL dynamique

Modifié par Julien EYMERY le 2015/08/07 15:13
Afficher les derniers auteurs
1 (((
2 = Introduction =
3 )))
4
5 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)L’utilisation d’une requête SQL dynamique plutôt que des instructions SQL uniquement Adélia s’avère utile, voire nécessaire, dans 2 cas :(%%)
6 (%%)
7 1. Eviter la création de plusieurs curseurs ou chargements Adélia afin de sélectionner des enregistrements base de données conformément à plusieurs critères de sélection aléatoires,
8 1. Permettre d’utiliser des paramètres SQL n’ayant pas d’équivalence dans les instructions SQL Adélia afin de simplifier les traitements (GROUP BY, IN, NOT IN, etc.)
9
10
11 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Dans le cadre d’une requête SQL dynamique, (%%)(% style="color: rgb(255,102,0);" %)**la requête SQL est codée directement en langage SQL dans une variable de travail alphanumérique**(%%)(% style="color: rgb(0,0,0);" %). La requête est construite dynamiquement en fonction des critères de sélection aléatoires renseignés dans le programme et/ou conformément aux paramètres SQL souhaités (IN, NOT IN, etc.).(%%)
12 (%%)
13 (((
14 == Instructions concernées et syntaxe ==
15 )))
16
17 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)CURSEUR(%%)** NomCurseur :VarRequeteSQL.(%%)
18 (% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)CHARGEMENT(%%)**(% style="color: rgb(0,0,0);" %)NomChargement NomListeGraph:liste *SQL_D VarRequeteSQL SuiteVariablesHotes Pas(%%).(%%)
19 (% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)EXEC_SQL(%%)** (:VarRequeteSQL).(%%)(%%)
20 (((
21 == (% style="color: rgb(0,0,0);" %)Construction de requête : description et exemples(%%) ==
22 )))
23 (((
24 === (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Construction d'une requête(%%)(%%) ===
25 )))
26
27 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Exemple avec critère de sélection aléatoire.(%%)(%%)
28
29 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Calcul de l’ancienneté moyenne de tous les employés, ou seulement de celle des hommes ou des femmes.
30 L’entité PERSONNEL est à l’origine de la table PERSONP.(%%)(%%)(%%)
31
32
33 {{code title="Sans requête, il faut définir 2 curseurs" language="none"}}
34 [Déclaration]
35 * Curseur sans sélection pour traiter tous les employés :
36 CURSEUR CURS_ANCIEN_TOUS PERSONNEL -
37 *COL(PE_DAT_ENTREE)
38
39 * Curseur avec sélection sur le code sexe :
40 CURSEUR CURS_ANCIEN_F_H PERSONNEL -
41 *COL(PE_DAT_ENTREE) -
42 *COND(PE_COD_SEXE_PER = :ZPE_CODE_SEXE)
43 {{/code}}
44
45
46
47
48 {{code title="Avec requête dynamique" language="none"}}
49 [Déclaration]
50 * Variable contenant la requête SQL
51 ALPHA(300) W_REQUETE_SQL
52
53 * Curseur en référence à la variable requête (préfixée par « : »)
54 CURSEUR CURS_ANCIEN :W_REQUETE_SQL
55
56 [(Traitement)]
57 * Construction de la requête SQL dans la variable W_REQUETE_SQL
58 W_REQUETE_SQL = *BLANK
59 W_REQUETE_SQL = 'SELECT PEDATPENT FROM schema.PERSONP’
60 SI ZPE_CODE_SEXE = 'F';’H’
61 W_REQUETE_SQL = W_REQUETE_SQL /// -
62 ' WHERE PEPSEX = ''' /// ZPE_CODE_SEXE /// ''''
63 FIN 
64 {{/code}}
65
66
67 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)La variable requête **(% style="color: rgb(255,102,0);" %)W_REQUETE_SQL(%%)** est une variable alpha de longueur libre, suffisante, bien sûr, pour contenir toute l’instruction SQL. La requête SQL qu’elle contient est donc une chaîne de caractères alphanumérique qui doit être délimitée par des cotes.(%%)
68 (% style="color: rgb(0,0,0);" %)Soit dans notre exemple :(%%)(%%)(%%)(%%)
69
70
71 {{code language="none"}}
72 * Sélection de tous les employés
73 W_REQUETE_SQL = 'SELECT PEDATPENT FROM schema.PERSONP’
74 {{/code}}
75
76
77 {{hardis-info title="Attention" type="note" icon="true"}}
78 (% style="color: rgb(0,0,0);" %)Dans la requête en langage SQL, il faut indiquer le nom réel des colonnes et des tables au lieu des noms Adélia (sauf pour les variables hôtes).(%%)
79 (% style="color: rgb(0,0,0);" %)En outre, la syntaxe des requêtes doit respecter la syntaxe attendue par le SGBD cible. Ainsi, pour une base de données DB2 400 (native ou SQL) le schéma n’est pas spécifié. Le fichier ou la table étant recherché dans la liste de bibliothèques ou collections en ligne(%%).
80 {{/hardis-info}}
81
82 (((
83 === Utilisation de variables programme ===
84 )))
85
86 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Par concaténation, il est possible d’inclure dans la requête des valeurs provenant de variables hôtes (noms Adélia de variables programme), numériques et alphanumériques, afin de bâtir dynamiquement la requête.(%%)(%%)
87 (((
88 === Variable hôte alphanumérique ===
89 )))
90
91 Reprenons l’exemple précédent avec ajout de la sélection sur le code sexe :
92
93
94 {{code language="none"}}
95 SI ZPE_CODE_SEXE = 'F';’H’
96 W_REQUETE_SQL = W_REQUETE_SQL /// -
97 ' WHERE PEPSEX = ''' /// ZPE_CODE_SEXE /// ''''
98 FIN
99 {{/code}}
100
101
102 Dans ce cas, la variable requête (% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)W_REQUETE_SQL(%%)**(%%) est complétée, par concaténation, avec sélection sur le code sexe.
103 (% style="color: rgb(0,0,0);" %)La première partie de ce complément d’instruction SQL est une chaîne de caractères. (%%)(% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Celle-ci doit donc être délimitée par deux cotes. Soit : (%%)(%%)
104
105
106 {{code language="none"}}
107 W_REQUETE_SQL = W_REQUETE_SQL /// ' WHERE PEPSEX = '
108 {{/code}}
109
110
111 Mais la variable code sexe (% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)ZPE_CODE_SEXE(%%)**(%%) est elle aussi une variable alphanumérique dont la valeur, (% style="color: rgb(0,0,0);" %)**(% style="color: rgb(255,102,0);" %)F(%%)**(%%) ou (% style="color: rgb(255,102,0);" %)**H**(%%), doit donc être délimitée dans la requête par des cotes.
112
113 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Pour inclure une cote dans une chaîne de caractères alphanumériques il faut utiliser deux côtes : «''».(%%)
114 (% style="color: rgb(0,0,0);" %)D’où les trois cotes terminant la première chaîne de caractères du complément d’instruction de la requête. Deux pour créer la première cote délimitant la valeur alphanumérique du code sexe, et une pour délimiter la fin de la chaîne de caractères du complément d’instruction : (%%)(%%)
115
116
117 {{code language="none"}}
118 W_REQUETE_SQL = W_REQUETE_SQL /// ' WHERE PEPSEX = '''
119 {{/code}}
120
121
122 (% style="color: rgb(0,0,0);" %)Le contenu de « W_REQUETE_SQL » est donc maintenant :(%%)
123
124 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)« SELECT (%%)**(% style="color: rgb(255,102,0);" %)PEDATPENT(%%)**(% style="color: rgb(0,0,0);" %) FROM schema.(%%)(% style="color: rgb(255,102,0);" %)**PERSONP**(%%)(% style="color: rgb(0,0,0);" %) WHERE PEPSEX ~= ' »(%%)(%%)
125
126 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %) (%%)(% style="color: rgb(0,0,0);" %)Ensuite, la chaîne de caractères est concaténée avec la valeur du code sexe contenue dans la variable hôte (% style="color: rgb(255,102,0);" %)**ZPE_CODE_SEXE**(%%), soit **(% style="color: rgb(255,102,0);" %)F(%%)** ou **(% style="color: rgb(255,102,0);" %)H(%%)** : (%%)(%%)
127
128
129 {{code language="none"}}
130 W_REQUETE_SQL = W_REQUETE_SQL /// ' WHERE PEPSEX = ''' /// ZPE_CODE_SEXE
131 {{/code}}
132
133
134 Le contenu de « W_REQUETE_SQL » est donc maintenant :
135
136 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)« SELECT (%%)**PEDATPENT**(% style="color: rgb(0,0,0);" %) FROM schema.(%%)(% style="color: rgb(255,102,0);" %)**PERSONP**(%%)(% style="color: rgb(0,0,0);" %) WHERE PEPSEX ~= '(%%)(% style="color: rgb(255,102,0);" %)**F**(%%)(% style="color: rgb(0,0,0);" %) »(%%)
137
138 (% style="color: rgb(0,0,0);" %)Il faut maintenant coder dans la requête la cote fermant la valeur de la variable hôte (% style="color: rgb(255,102,0);" %)**‘F’**(%%). Pour cela, comme nous l’avons vu, il faut utiliser deux côtes : «**(% style="color: rgb(255,102,0);" %)''(%%)**».(%%)
139 (% style="color: rgb(0,0,0);" %)Et pour que cette cote puisse être concaténée avec le contenu de la variable requête il faut que celle-ci soit elle-même délimitée par deux cotes. D’où les quatre cotes de fin : (%%)(%%)
140
141
142 {{code language="none"}}
143 ' WHERE PEPSEX = ''' /// ZPE_CODE_SEXE /// ''''
144 {{/code}}
145
146
147 Le contenu de « W_REQUETE_SQL » est donc maintenant :
148
149 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)« SELECT (%%)(% style="color: rgb(255,102,0);" %)**PEDATPENT**(%%)(% style="color: rgb(0,0,0);" %) FROM schema.(%%)(% style="color: rgb(255,102,0);" %)**PERSONP**(%%)(% style="color: rgb(0,0,0);" %) WHERE PEPSEX ~= '(%%)(% style="color: rgb(255,102,0);" %)**F**(%%)(% style="color: rgb(0,0,0);" %)' »(%%)(%%)
150 (((
151 === Variable hôte alphanumérique ===
152 )))
153
154 (% style="color: rgb(0,0,0);" %)Dans le cas d’une valeur numérique, il n’est pas nécessaire de la délimiter par des cotes. La variable peut donc être directement concaténée à la variable requête.(%%)
155 (% style="color: rgb(0,0,0);" %)Exemple :(%%)
156
157
158 {{code language="none"}}
159 * Soit la sélection de la date d’entrée de chaque employé ayant un code * société égal à celui saisi par l’utilisateur.
160 W_REQUETE_SQL = 'SELECT PEDATPENT FROM PERSONP WHERE PECODSTE = ' /// ZPE_COD_SOCIETE
161 {{/code}}
162
163 (((
164 == Quelques exemples ==
165 )))
166 (((
167 === (% style="color: rgb(0,0,0);" %)(%%)Exemple de curseur ===
168 )))
169
170 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Critère de sélection aléatoire et utilisation de paramètres SQL non disponibles sur l’instruction de déclaration de curseur Adélia : fonction « COUNT » et paramètre « GROUP BY ».(%%)
171 (% style="color: rgb(0,0,0);" %)Dans une procédure, alimentation d’une liste mémoire avec le résultat de la recherche du nombre d'employés par société. L’analyse doit prendre en compte soit tous les employés, soit faire une sélection sur le code sexe : (%%)(%%)
172
173
174 {{code title="Procédure NB_EMPLOYE_SOCIETE" language="none"}}
175 [DECLARATION]
176 * Variable requête
177 ALPHA(300) W_REQUETE_SQL
178
179 * Colonnes de la liste mémoire contenant le résultat de la requête
180 ALPHA(30) W_NOM_SOCIETE
181 NUM_BIN_4 W_NB_EMPLOYE
182
183 * Paramètres de la procédure
184 ALPHA(1) WP_CODE_SEXE
185 LISTE LSM_NB_EMP_SOC W_NOM_SOCIETE W_NB_EMPLOYE
186
187 * Curseur dynamique
188 CURSEUR CUR_NB_EMP_SOC :W_REQUETE
189
190 * Déclaration des paramètres gérés en entrée/sortie par la procédure
191 PARAM WP_CODE_SEXE LSM_NB_EMP_SOC
192
193 [(Traitement)]
194 * Construction de la requête SQL dans la variable W_REQUETE
195 W_REQUETE_SQL = *BLANK
196 W_REQUETE_SQL = 'SELECT SONOMSOC, COUNT(PECODMAT) FROM PERSONP, SOCIETE -
197 WHERE PECODSTE=SOCODSTE'
198 *
199 SI WP_CODE_SEXE = 'F';’H’
200 W_REQUETE_SQL = W_REQUETE_SQL /// ' AND' /// -
201 ' PECODSEX = ''' /// WP_CODE_SEXE /// ''''
202 FIN
203 *
204 W_REQUETE_SQL = W_REQUETE_SQL /// ' GROUP BY SONOMSOC'
205 *
206 OUVRIR_SQL_C CUR_NB_EMP_SOC
207 * Alimentation des colonnes de la liste par SONOMSOC et COUNT(PECODMAT)
208 LIRE_AV_SQL_C CUR_NB_EMP_SOC :W_NOM_SOCIETE, :W_NB_EMPLOYE
209 TANT_QUE *SQLCODE = *NORMAL
210 INSERER_ELT LSM_NB_EMP_SOC
211 LIRE_AV_SQL_C CUR_NB_EMP_SOC :W_NOM_SOCIETE, :W_NB_EMPLOYE
212 REFAIRE
213 FERMER_SQL_C CUR_NB_EMP_SOC
214 {{/code}}
215
216 (((
217 === (% style="color: rgb(0,0,0);" %) (%%)Exemple de chargement ===
218 )))
219
220 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)(Voir la syntaxe de l’instruction « CHARGEMENT » en début de document). (%%)(%%)
221
222
223 {{code title="Procédure NB_EMPLOYE_SOCIETE" language="none"}}
224 [Déclaration]
225 * Variable requête
226 ALPHA(1000) W_REQUETE_SQL
227 ALPHA(7) W1_COND
228 *
229 CHARGEMENT CHRG_ENT FEN_LISTE.LST_DE_ENT:LISTE *SQL_D W_REQUETE_SQL -
230 ZZ_NUM_PROMESSE ZZ_NUM_DER_ENT ZZ_NUM_DER_ORI ZZ_COD_TYP_DER –
231 ZZ_COD_FOU_APP ZZ_NOM_FOU_APP _VALEUR_PAS
232
233 [Traitement]
234 * Constitution de la requête
235 * Dans un premier temps la requête est renseignée avec les éléments
236 * invariants de la sélection.
237 W_REQUETE = *BLANK
238 W_REQUETE_SQL = 'SELECT DENOPROM, DENODERENT, DENODERORI, DECDTPDER, -
239 FOCDFOAPP, FONMFOAPP FROM ENTDERP, NATUREP, FOUAPP –
240 WHERE DECDNTPRO=NACDNTPRO AND FONOFOUAPP = DENOFOUAPP'
241 *
242 W1_COND = ' AND'
243 *
244 * La requête est ensuite complétée en fonction de la saisie des champs
245 * constituant le filtre de sélection des enregistrements en base de données.
246 *
247 * N° promesse. Variable hôte numérique. Il suffit donc de la concaténer à la requête
248 SI CHS_NUM_PROMESSE <> 0
249 W_REQUETE = W_REQUETE /// W1_COND /// ' DENOPROM = ' // CHS_NUM_PROMESSE
250 FIN
251 * N° dérogation. Variable hôte alphanumérique et utilisation de la fonction SQL « LIKE ‘xxx%’». Permet de sélectionner les enregistrements commençant par la chaîne de caractères « CHS_NUM_DER_ENT ». Il faut reprendre ici le même principe vu précédemment pour coder une cote dans la variable requête. Il faut pour cela utiliser deux côtes : «''»
252 SI CHS_NUM_DER_ENT <> *BLANK
253 W_REQUETE = W_REQUETE /// W1_COND /// ' DENODERENT LIKE ''' // CHS_NUM_DER_ENT /// '% '''
254 FIN
255 * Sélection du n° de DA.
256 SI P_NUM_DA <> 0
257 * Sélection pour la DA. Variable hôte numérique et utilisation de la
258 * fonction SQL « IN » qui permet de sélectionner les enregistrements ayant
259 * une valeur égale à celles extraite dans la seconde sélection.
260 * A noter l’utilisation d’une fonction prédéfinie directement dans la
261 * requête : &NUM_ALPHA
262 W_REQUETE = W_REQUETE /// W1_COND /// ' DENOPROM IN –
263 (SELECT LVNOPROM FROM LIGVTEDP WHERE LVNODAV = ' // -
264 &NUM_ALPHA(CHO_NUM_DA) /// ')'
265 FIN
266 * Date commercialisation. Variable hôte de type date. Utilisation de la
267 * fonction SQL « DATE » et de la fonction prédéfinie Adélia &DATE_ALPHA afin
268 * de gérer le format et les séparateurs de date.
269 SI ZW_DAT_COMM = _DAT_COMM_ACTU
270 W_REQUETE = W_REQUETE /// W1_COND // ' PRDTCOMPRD <= DATE( ''' //&DATE_ALPHA ('*ISO';W_DATE)// ''' )'
271 FIN
272 W_REQUETE = W_REQUETE /// ' ORDER BY DENODERENT'
273 ...
274 {{/code}}
275
276
277 (% style="color: rgb(0,0,0);" %)(% style="color: rgb(0,0,0);" %)Le chargement de la liste s’effectue ensuite par une boucle de chargement CHARGT_LST/FIN_ CHARGT_LST.(%%)(%%)
278 (((
279 == Adélia iSeries : Gestion de sous-fichier ==
280 )))
281 (((
282 === (% style="color: rgb(0,0,0);" %)Instructions concernées et syntaxe(%%) ===
283 )))
284
285 **(% style="color: rgb(255,102,0);" %)SQL_SFL_D(%%)**(% style="color: rgb(0,0,0);" %) VarRequeteSQL(%%)
286
287
288 {{code language="none"}}
289 * Remplissage SFL par requête Dynamique SQL_SFL_D
290 W_REQUETE = ‘SELECT PPMAT, PPNPE FROM PERSONP’
291 SQL_SFL_D W_LIB_REQ 1 ZPCOD_MATRICULE ZPNOM_PERSONNE
292
293 ** La vue *1 est alors indiquée en remplissage par requête SQL et sans fichier guide
294 EFFACER 1
295 GESTION_SFL 1
296 MAJ_SFL
297 FIN_GESTION_SFL
298 {{/code}}
299
300
301
302