Procédures stockées et tables temporaires

**zekabyle** · 03/06/2009, 17h21

je cherche à faire une procédure stockée mais

Voilà les tables temporaires:

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
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
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
 
CREATE GLOBAL TEMPORARY TABLE lstIdDestinataire
(
tie_id integer
) ON COMMIT PRESERVE ROWS
 
CREATE GLOBAL TEMPORARY TABLE lstTauStockAgence
(
tau_id integer,
stock int
) ON COMMIT PRESERVE ROWS
/  
CREATE GLOBAL TEMPORARY TABLE lstTauStockUrceo
(
tau_id integer,
stock int
) ON COMMIT PRESERVE ROWS
/  
 
CREATE OR REPLACE
PROCEDURE PS_GET_COMMANDE_DETAILS
  (
    CMD_ID        IN integer DEFAULT null,
    CMD_NUMERO    IN integer DEFAULT null,
    cmdNumeroSage IN integer DEFAULT null,
    COL_ID        IN integer DEFAULT null,
    SCO_LIBELLE   IN VARCHAR2 DEFAULT null,
    --statut en cours--F07_RG1
    SCO_CODE             IN CHAR DEFAULT null,
    TAU_NOM_COURT        IN VARCHAR2 DEFAULT null,
    TAU_NU_TRAVAIL       IN VARCHAR2 DEFAULT null,
    ART_DESIGNATION      IN VARCHAR2 DEFAULT null,
    ART_CODE             IN VARCHAR2 DEFAULT null,
    TIE_NOM_EMETTEUR     IN VARCHAR2 DEFAULT null,
    TIE_NOM_DESTINATAIRE IN VARCHAR2 DEFAULT null,
    TIE_ID_EMETTEUR      IN integer DEFAULT null,
    TIE_ID_DESTINATAIRE  IN integer DEFAULT null,
    ShowEmptyCommandLine IN number DEFAULT 1,
    --Si renseigné, on retourne les commandes dont l'utilisateur connecté
    --est soit l'emetteur soit le destinataire
    cmdTiersEmetDestId IN OUT integer,
    --by defaut with filter with the primary key
    sortedDictionary IN NVARCHAR2 DEFAULT '',
    --paging params
    startRowIndex IN int DEFAULT null,
    maxRows       IN int DEFAULT null,
    p_recordset OUT SYS_REFCURSOR )
  --paging params
AS
/*tbllstIdDestinataire TBL_LSTIDDESTINATAIRE;
tbllstTauStockAgence TBL_LSTTAUSTOCKAGENCE;
tbllstTauStockUrceo TBL_LSTTAUSTOCKURCEO;*/
  --récupérer la liste des tiers asssocié à l'utilisateur connecté
  --récupérer la liste des tiers asssocié à l'utilisateur connecté
BEGIN
  IF( cmdTiersEmetDestId is null and TIE_ID_DESTINATAIRE is not null) THEN
    BEGIN
      /*récupérer la liste des tiers associé au destinataire*/
      /*récupérer la liste des tiers associé au destinataire*/
      INSERT
      --INTO tbllstIdDestinataire
      INTO lstIdDestinataire
      SELECT DISTINCT TIEID
      FROM TABLE(fn_get_third_belonging(ps_get_commande_details.TIE_ID_DESTINATAIRE)) t;
    END;
  ELSE
    IF( cmdTiersEmetDestId is not null) THEN
      BEGIN
        /*on ne recupère que les cmds dont l'utilisateur connecté*/
        /*est l'unique destinataire*/
        /*on ne recupère que les cmds dont l'utilisateur connecté*/
        /*est l'unique destinataire*/
        INSERT
        --INTO tbllstIdDestinataire
        INTO lstIdDestinataire
        SELECT ps_get_commande_details.cmdTiersEmetDestId FROM SYS.DUAL;
      END;
    END IF;
  END IF;
  INSERT INTO lstTauStockAgence
  --INSERT INTO tbllstTauStockAgence
  SELECT
    /*recupere stock du lot qui appartiennent au tiers emetteur*/
    /*recupere stock du lot qui appartiennent au tiers emetteur*/
    (NVL(lot.tau_id, art.tau_id)) as taureau_id,
    (SELECT SUM(STO_QUANTITE)
    FROM STOCK_STO adv_alias_1
    WHERE (NVL(lot.tau_id, art.tau_id)) = (NVL(lot.tau_id, art.tau_id))
    ) as stock_cumule
    /*, count(*) as nbre_ligne --select count(*)*/
  FROM STOCK_STO sto,
    LOT_LOT lot,
    ref_article_art art,
    RANGEMENT_EMPLACEMENT_EMP emp,
    v_rangement_available ran,
    TABLE(fn_get_third_belonging(ps_get_commande_details.TIE_ID_EMETTEUR)) tie_belonging
  WHERE sto.lot_id = lot.lot_id
  AND (art.art_id  = lot.art_id
    /*--article pour récupérer le taureau*/
    )
  AND (emp.emp_id = sto.emp_id
    /*--RANGEMENT_RAN storage system are not for display*/
    )
  AND (ran.ran_id                               = emp.ran_id)
  AND (tie_belonging.TIEID                     = ran.tie_id_rattachement)
  AND (ps_get_commande_details.TIE_ID_EMETTEUR is not null);
  /*recupere stock total urceo du lot*/
  /*recupere stock total urceo du lot*/
  INSERT
  INTO tbllstTauStockUrceo
  SELECT NVL(lot.tau_id, art.tau_id) as taureau_urceo_id,
    SUM(sto.STO_QUANTITE)            as stock_urceo_cumule
  FROM STOCK_STO sto,
    RANGEMENT_EMPLACEMENT_EMP emp,
    v_rangement_available ran,
    LOT_LOT lot,
    ref_article_art art
  WHERE emp.emp_id = sto.emp_id
    /*----RANGEMENT_RAN storage system are not for display*/
  AND (ran.ran_id = emp.ran_id)
  AND (sto.lot_id = lot.lot_id)
  AND (art.art_id = lot.art_id
    /*--article pour récupérer le taureau  */
    )
  AND (NVL(lot.tau_id, NVL(art.tau_id, '')) <> '')
  GROUP BY NVL(lot.tau_id, art.tau_id);
 
  OPEN p_recordset FOR 
 
  SELECT CMD_ID,
        bop.CMD_NUMERO,
        bop.CMD_NUMERO_SAGE,
        bop.CMD_STATUT_DATE,
        bop.SCO_CODE,
        bop.SCO_LIBELLE,
        bop.COL_ID,
        bop.COL_QUANTITE,
        bop.COL_NU_LIGNE,
        bop.BLG_QUANTITE,
        bop.TAU_ID,
        bop.TAU_NOM_COURT,
        bop.TAU_NU_TRAVAIL,
        bop.ART_ID,
        bop.ART_DESIGNATION,
        bop.ART_CODE,
        bop.TIE_ID_EMETTEUR,
        bop.TIE_NOM_EMETTEUR,
        bop.TIE_ID_DESTINATAIRE,
        bop.TIE_NOM_DESTINATAIRE,
        bop.TIE_ID_DESTINATAIRE_FINAL,
        bop.TIE_NOM_DESTINATAIRE_FINAL,
        stock_cumule,
        stock_urceo_cumule,
        qot_quantite_emetteur,
        qot_quantite_initial_emetteur,
        qot_quantite_destinataire,
        qot_quantite_initial_dest,
        bop.rank,
        full_label 
  FROM
    (SELECT cmd.CMD_ID,
                cmd.CMD_NUMERO,
                cmd.CMD_NUMERO_SAGE,
                cmd.CMD_STATUT_DATE,
                cmd.SCO_CODE,
                cmd.SCO_LIBELLE,
                cmd.COL_ID,
                cmd.COL_QUANTITE,
                cmd.COL_NU_LIGNE,
                cmd.BLG_QUANTITE,
                cmd.TAU_ID,
                cmd.TAU_NOM_COURT,
                cmd.TAU_NU_TRAVAIL,
                cmd.ART_ID,
                cmd.ART_DESIGNATION,
                cmd.ART_CODE,
                cmd.TIE_ID_EMETTEUR,
                cmd.TIE_NOM_EMETTEUR,
                cmd.TIE_ID_DESTINATAIRE,
                cmd.TIE_NOM_DESTINATAIRE,
                cmd.TIE_ID_DESTINATAIRE_FINAL,
                cmd.TIE_NOM_DESTINATAIRE_FINAL,
                /*, cmd.[rank_defaut]*/
                taureau_stock_tiers_details.stock     as stock_cumule,
                taureau_stock_urceo_details.stock     as stock_urceo_cumule,
                qot_emetteur.qot_quantite             as qot_quantite_emetteur,
                qot_emetteur.qot_quantite_initial     as qot_quantite_initial_emetteur,
                qot_destinataire.qot_quantite         as qot_quantite_destinataire,
                qot_destinataire.qot_quantite_initial as qot_quantite_initial_dest,
                TO_CHAR(CMD_NUMERO) + NVL('('
                || TO_CHAR(CMD_NUMERO_SAGE)
                || ')', '')
                || ' - '
                ||
                case
                  when cmd.tau_id is not null
                  then cmd.TAU_NOM_COURT
                  else ART_CODE
                end
                || ' - '
                || TO_CHAR(NVL(COL_QUANTITE, - 1)) as full_label,
                row_number() over(PARTITION BY 1 ORDER BY
                case
                  when nvl(sortedDictionary, '') = ''
                  then rank_defaut
                end,
                case
                  when sortedDictionary = 'CMD_NUMERO'
                  then CMD_NUMERO
                end,
                case
                  when sortedDictionary = 'CMD_NUMERO DESC'
                  then CMD_NUMERO
                end DESC,
                case
                  when sortedDictionary = 'CMD_NUMERO_SAGE'
                  then CMD_NUMERO
                end,
                case
                  when sortedDictionary = 'CMD_NUMERO_SAGE DESC'
                  then CMD_NUMERO
                end DESC,
                case
                  when sortedDictionary = 'TAU_NOM_COURT'
                  then TAU_NOM_COURT
                end,
                case
                  when sortedDictionary = 'TAU_NOM_COURT DESC'
                  then cmd.TAU_NOM_COURT
                end DESC,
                case
                  when sortedDictionary = 'TAU_NU_TRAVAIL'
                  then TAU_NU_TRAVAIL
                end,
                case
                  when sortedDictionary = 'TAU_NU_TRAVAIL DESC'
                  then cmd.TAU_NU_TRAVAIL
                end DESC,
                case
                  when sortedDictionary = 'COL_QUANTITE'
                  then COL_QUANTITE
                end,
                case
                  when sortedDictionary = 'COL_QUANTITE DESC'
                  then COL_QUANTITE
                end DESC,
                case
                  when sortedDictionary = 'COL_NU_LIGNE'
                  then COL_NU_LIGNE
                end,
                case
                  when sortedDictionary = 'COL_NU_LIGNE DESC'
                  then COL_NU_LIGNE
                end DESC,
                case
                  when sortedDictionary = 'TIE_NOM_EMETTEUR'
                  then TIE_NOM_EMETTEUR
                end,
                case
                  when sortedDictionary = 'TIE_NOM_EMETTEUR DESC'
                  then TIE_NOM_EMETTEUR
                end DESC,
                case
                  when sortedDictionary = 'TIE_NOM_DESTINATAIRE'
                  then TIE_NOM_DESTINATAIRE
                end,
                case
                  when sortedDictionary = 'TIE_NOM_DESTINATAIRE DESC'
                  then TIE_NOM_DESTINATAIRE
                end DESC) as rank
        FROM v_commandes_details cmd,
          (SELECT qot_emetteur.*
                 FROM v_commandes_details cmd,
                      table(fn_get_quota_agence(ps_get_commande_details.TIE_ID_EMETTEUR, null)) qot_emetteur,
                      table(fn_get_quota_agence(ps_get_commande_details.TIE_ID_destinataire, null)) qot_destinataire,
                      lstIdDestinataire t,
                      lstTauStockAgence taureau_stock_tiers_details,
                      lstTauStockUrceo taureau_stock_urceo_details
                      /*tbllstIdDestinataire t,
                      tbllstTauStockAgence taureau_stock_tiers_details,
                      tbllstTauStockUrceo taureau_stock_urceo_details*/
                WHERE qot_emetteur.tau_id                    = cmd.tau_id
                AND ps_get_commande_details.TIE_ID_EMETTEUR is not null
          ) qot_emetteur,
          (SELECT qot_destinataire.*
                 FROM v_commandes_details cmd,
                      table(fn_get_quota_agence(ps_get_commande_details.TIE_ID_EMETTEUR, null)) qot_emetteur,
                      table(fn_get_quota_agence(ps_get_commande_details.TIE_ID_destinataire, null)) qot_destinataire,
                      lstIdDestinataire t,
                      lstTauStockAgence taureau_stock_tiers_details,
                      lstTauStockUrceo taureau_stock_urceo_details
                     /* tbllstIdDestinataire t,
                      tbllstTauStockAgence taureau_stock_tiers_details,
                      tbllstTauStockUrceo taureau_stock_urceo_details*/
                WHERE qot_emetteur.tau_id                        = cmd.tau_id
                AND ps_get_commande_details.TIE_ID_destinataire is not null
          /*) qot_destinataire, tbllstIdDestinataire t, tbllstTauStockAgence taureau_stock_tiers_details, 
            tbllstTauStockUrceo taureau_stock_urceo_details*/
          ) qot_destinataire, lstIdDestinataire t, lstTauStockAgence taureau_stock_tiers_details, 
            lstTauStockUrceo taureau_stock_urceo_details
        WHERE qot_emetteur.tau_id                         = cmd.tau_id
        AND (qot_emetteur.tau_id                          = cmd.tau_id)
        AND (t.tie_id (+)                                 = cmd.TIE_ID_DESTINATAIRE)
        AND (taureau_stock_tiers_details.tau_id (+)       = cmd.tau_id)
        AND (taureau_stock_urceo_details.tau_id (+)       = cmd.tau_id)
        AND (ps_get_commande_details.cmdTiersEmetDestId is null  OR (ps_get_commande_details.cmdTiersEmetDestId   is not null
                AND (cmd.TIE_ID_DESTINATAIRE                      = ps_get_commande_details.cmdTiersEmetDestId
                              OR cmd.TIE_ID_Emetteur                            = ps_get_commande_details.cmdTiersEmetDestId)))
        AND (ps_get_commande_details.CMD_ID              is null
                              OR (ps_get_commande_details.CMD_ID               is not null
                              AND CMD_ID                                        = ps_get_commande_details.CMD_ID))
        AND (ps_get_commande_details.CMD_NUMERO          is null
                              OR (ps_get_commande_details.CMD_NUMERO           is not null
                              AND CMD_NUMERO                                    = ps_get_commande_details.CMD_NUMERO))
        AND (ps_get_commande_details.cmdNumeroSage       is null
                              OR (ps_get_commande_details.cmdNumeroSage        is not null
                              AND CMD_NUMERO_SAGE                               = ps_get_commande_details.cmdNumeroSage))
        AND (ps_get_commande_details.COL_ID              is null
                              OR (ps_get_commande_details.COL_ID               is not null
                              AND COL_ID                                        = COL_ID))
        AND (ps_get_commande_details.SCO_LIBELLE         is null
                     OR ps_get_commande_details.SCO_LIBELLE           is not null
                    AND SCO_LIBELLE like '%'
                      || LTRIM(RTRIM(ps_get_commande_details.SCO_LIBELLE))
                      || '%')
        AND (ps_get_commande_details.SCO_CODE is null
                OR ps_get_commande_details.SCO_CODE   is not null
                AND SCO_CODE like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.SCO_CODE))
                  || '%')
        AND (ps_get_commande_details.TAU_NOM_COURT is null
                OR ps_get_commande_details.TAU_NOM_COURT   is not null
                AND cmd.TAU_NOM_COURT like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.TAU_NOM_COURT))
                  || '%')
        AND (ps_get_commande_details.TAU_NU_TRAVAIL is null
                OR ps_get_commande_details.TAU_NU_TRAVAIL   is not null
                AND cmd.TAU_NU_TRAVAIL like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.TAU_NU_TRAVAIL))
                  || '%')
        AND (ps_get_commande_details.ART_DESIGNATION is null
                OR ps_get_commande_details.ART_DESIGNATION   is not null
                AND ART_DESIGNATION like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.ART_DESIGNATION))
                  || '%')
        AND (ps_get_commande_details.ART_CODE is null
                OR ps_get_commande_details.ART_CODE   is not null
                AND ART_CODE like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.ART_CODE))
                  || '%')
        AND (ps_get_commande_details.TIE_NOM_DESTINATAIRE is null
                OR (ps_get_commande_details.TIE_NOM_DESTINATAIRE  is not null
                AND TIE_NOM_DESTINATAIRE like '%'
                  || LTRIM(RTRIM(ps_get_commande_details.TIE_NOM_DESTINATAIRE))
                  || '%'))
        AND (ps_get_commande_details.TIE_NOM_EMETTEUR is null
                    OR (ps_get_commande_details.TIE_NOM_EMETTEUR  is not null
                    AND TIE_NOM_EMETTEUR like '%'
                      || LTRIM(RTRIM(ps_get_commande_details.TIE_NOM_EMETTEUR))
                      || '%'))
        AND (ps_get_commande_details.TIE_ID_DESTINATAIRE is null
                    OR (ps_get_commande_details.TIE_ID_DESTINATAIRE  is not null
                    AND t.tie_id                                     is not null))
        AND (ps_get_commande_details.TIE_ID_EMETTEUR     is null
                    OR (ps_get_commande_details.TIE_ID_EMETTEUR      is not null
                    AND TIE_ID_EMETTEUR                               = ps_get_commande_details.TIE_ID_EMETTEUR))
        AND (ps_get_commande_details.ShowEmptyCommandLine = 1
                  /*--commandes sans lignes de cmdes*/
                    OR (ps_get_commande_details.ShowEmptyCommandLine = 0
                  /*--commamndes uniquement avec lignes de cmdes*/
                  AND col_id                                   is not null)) 
        ) bop
  WHERE (ps_get_commande_details.startRowIndex is null
  AND ps_get_commande_details.maxRows          is null
  OR (bop.rank between (ps_get_commande_details.startRowIndex + 1) and ((ps_get_commande_details.startRowIndex + ps_get_commande_details.maxRows))));
END;

j'obtiens l'erreur suivante n'arrivant pas à insérer la procédure stockée:
Erreur: ORA-00922: option erronée ou absente

j'ai essayé d'utiliser les types en faisant des tables de types (ce que j'ai mis entre commentaire) mais pour les insert, ça ne marche pas et ça me met table ou vue inexistente.

Je sollicite de l'aide.

Merci d'avance pour votre compréhension

**GoLDoZ** · 03/06/2009, 18h05

Essayez en remplaçant AS par IS.

**zekabyle** · 03/06/2009, 18h18

finalement, une table temporaire ne sera pas une bonne idée car s'il faut la créer avant de créer la procédure stockée, à la nouvelle session où la procédure sera appelée la table temporaire ne sera plus là, comment faire pour déclarer une table dans la procédure stockée pour l'utiliser pour faire l'insert comme dans le premier message?

un

Code :

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

declare lstIdDestinataire table(tie_id bigint)

ne marche pas alors je n'ai plus beaucoup de solution car ça ne marche pas chez moi sur oracle 10g

**NGasparotto** · 03/06/2009, 20h34

Envoyé par zekabyle

finalement, une table temporaire ne sera pas une bonne idée car s'il faut la créer avant de créer la procédure stockée, à la nouvelle session où la procédure sera appelée la table temporaire ne sera plus là, comment faire pour déclarer une table dans la procédure stockée pour l'utiliser pour faire l'insert comme dans le premier message?

un

Code :

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

declare lstIdDestinataire table(tie_id bigint)

ne marche pas alors je n'ai plus beaucoup de solution car ça ne marche pas chez moi sur oracle 10g

En fait, il y a un probleme de conception. Une table temporaire n'est rien d'autre qu'une table reelle, sans avoir besoin de la creer et de la supprimer a chaque fois - seule les donnees sont temporaires ! -. Les tables temporaires doivent donc etre creer un fois pour toute et c'est tout.

Nicolas.

**zekabyle** · 03/06/2009, 22h48

donc le temporaire d'une table temporaire ne concerne que les lignes de la table temporaire, je comprends mieux maintenant, Merci

quand la procédure stockée est exécutée alors le contenu d'une table temporaire est supprimé après la fin de l'exécution de la procédure automatiquement ou bien il faut mettre un drop rows on commit à la place du preserve rows?

**NGasparotto** · 03/06/2009, 22h54

donc le temporaire d'une table temporaire ne concerne que les lignes de la table temporaire?

Exactement. Je dois bien dire qu'elles portent bien mal leur nom. Mais il s'agit bien de table reelle a donnees volatiles (ou temporaires). Les donnees sont preservees jusqu'au commit/rollback suivant ou bien le temps de la transaction et ne sont visible que par la session les ayant inserees !

quand la procédure stockée est exécutée alors le contenu d'une table temporaire est supprimé après la fin de l'exécution de la procédure automatiquement?

Tout a fait, le contenu seulement est supprime, pas la table elle-meme.

Nicolas.

Procédures stockées et tables temporaires

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager