Comment réutiliser une réquête dans une procédure ?

Version imprimable

30/05/2011, 09h56
tcr54

Comment réutiliser une réquête dans une procédure ?

Bonjour,

Dans un procédure, j'ai recours à un SELECT par deux fois, une fois dans un CURSOR et une autre pour un UPDATE. Ce SELECT est strictement identique dans les 2 cas.

Où écrire cette requête afin de pouvoir l’appeler n fois dans une même procédure, est-t-il possible de la mettre dans une variable ?

Merci

Cdlt
30/05/2011, 10h24
orafrance

Tu peux peut-être faire un SELECT FOR UPDATE pour pouvoir mettre à jour les lignes sélectionnées. Mais attention, ça lock la table alors faut pas qu'entre le SELECT et l'UPDATE il se passe trop de temps.
30/05/2011, 10h28
xdescamp

Tu peux peut-être également stocker le résultat de ton SELECT dans un tableau avec un BULK COLLECT, puis réutiliser ce tableau pour faire l'UPDATE avec un FORALL par exemple.
30/05/2011, 12h03
mnitu

Citation:

Envoyé par tcr54

Bonjour,

Dans un procédure, j'ai recours à un SELECT par deux fois, une fois dans un CURSOR et une autre pour un UPDATE. Ce SELECT est strictement identique dans les 2 cas.

Où écrire cette requête afin de pouvoir l’appeler n fois dans une même procédure, est-t-il possible de la mettre dans une variable ?

Merci

Cdlt

Un petit exemple pour qu'on y voie plus clair de quoi vous parlez ?

Citation:

Envoyé par mnitu

Un petit exemple pour qu'on y voie plus clair de quoi vous parlez ?

Voir ci-dessous (je débute en SQl et Pl/SQL...)

C'est la même requête utilisée pour le CURSOR et l'UPDATE : je n'utilise pas l'UPDATE dans la boucle car cela ferait n UPDATE au lieu d'un seul et j'ai cru comprendre qu'il valait mieux, voilà pourquoi j'aimerais pourvoir définir la requête 1 seule fois et la réutiliser ailleurs dans le script. j'utilise la boucle uniquement la boucle pour compter les occurrences à modifier et générer un mail d'info.

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
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
create or replace procedure CORRECTION_DOSS is
 
nb number;
i  number;
message varchar2(32767);
err_num varchar2(20);
err_msg varchar2(200); 
 
 
-- Sélection 
CURSOR dossier_a_maj is 
       select t.hos from table1 t, table2 t2
       where t.hoscod = t2.hoscod and t2.codtype = 'XX'
       and t2.anuuti = '0' and t2.inddfa = 'N'
       and t.tde = 'SS'
       and (t.pec = 'DM' OR t.pec = 'DP') 
       and t.psp ='49'
       and (t.EDIPEC ='1' OR t.EDIPEC is null)        
begin
 
nb :=0;
message :='';
titreCourriel :=  '<b>Traitement automatique du '||sysdate||'</b><br>';
 
       -- MAJ des dossiers sélectionnés  
 
       for dossier in dossier_a_maj
         loop
          dbms_output.put_line (dossier.hoscodidehos);
          message := message || '<br>' || dossier.hoscodidehos || '<br>' ;          
          nb := nb+1; 
         end loop;
 
         if (nb > 0) then
           dbms_output.put_line (nb); 
           update table1 a set a.pec = 'AC', a.edipec = Null
                  where a.hos in (
                                          select t.hos from table1 t, table2 t2
                                          where t.hoscod = t2.hoscod and t2.codtype = 'XX'
                                          and t2.anuuti = '0' and t2.inddfa = 'N'
                                          and t.tde = 'SS'
                                          and (t.pec = 'DM' OR t.pec = 'DP') 
                                          and t.psp ='49'
                                          and (t.EDIPEC ='1' OR t.EDIPEC is null)
                                          );
           --Validation de la transaction
           --COMMIT;
         message := message || '<br>Nombre de dossiers corrig&eacute;s : ' || to_char(nb);
         else
           --dbms_output.put_line ('Aucun dossier à corriger !');
           message := 'Aucun dossier &agrave; corriger !';              
         end if;
 
         -- Envoi mail
         envoiemail('dummy@nowhere.fr', 'Test', titreCourriel || message, 'html', 'test');
 
        EXCEPTION
        WHEN no_data_found THEN
          dbms_output.put_line ('Aucun dossier à corriger !');
        WHEN OTHERS THEN
              err_num := SQLCODE;
              err_msg := SUBSTR(SQLERRM, 1, 200);
              dbms_output.put_line('Erreur ! ' || err_num || ' : ' || err_msg );
             -- Annulation de la transaction
             --ROLLBACK;
             i := 1/0;
 
end CORRECTION_DOSS_;

30/05/2011, 15h21
xdescamp

Citation:

Envoyé par orafrance

Tu peux peut-être faire un SELECT FOR UPDATE pour pouvoir mettre à jour les lignes sélectionnées.

Citation:

Envoyé par xdescamp

Tu peux peut-être également stocker le résultat de ton SELECT dans un tableau avec un BULK COLLECT, puis réutiliser ce tableau pour faire l'UPDATE avec un FORALL par exemple.

Généralement pour factoriser une requête utilisée à différents endroits ou dans différentes procédures, on utilise les vues.

Dans ton cas le curseur est inutile, supprime le, puis execute l'update directement et utilise RETURNING BULK COLLECT INTO pour récupérer les info nécessaire à la rédaction du mail.

Au cas où, est ce que l'update peut être modifié comme suit pour éviter le double scan de table1 ? :
Code:

1 2 3 4 5 6 7 8 9 10 11 UPDATE table1 a SET a.pec = 'AC', a.edipec = NULL WHERE t.tde = 'SS' AND t.pec in ('DM', 'DP') AND t.psp ='49' AND (t.EDIPEC ='1' OR t.EDIPEC IS NULL) and exists ( SELECT 1 FROM table2 t2 WHERE t2.hoscod = a.hoscod AND t2.codtype = 'XX' AND t2.anuuti = '0' AND t2.inddfa = 'N')

C’est une procédure simple mais elle pose plusieurs problèmes.
D’abord votre algo est :

Traitement d’un curseur en boucle pour constituer une chaîne des caractères contenant la liste des dossiers mises à jour. Incrémentation d’une variable contient le nombre des dossiers.
Mise à jour via une requête unique si la variable conteur est supérieur à la valeur 1.
Gestion des exceptions pour traiter l’exception NO_DATA_FOUND. De plus vous utilisez 1/0 pour provoquer une exception dans la partie OTHERS.

Les problèmes :

Rien dans votre code ne lèvera l’exception NO_DATA_FOUND
Sauf conditions particulière (base de données mono-utilisateur) rien ne garanti que la liste de dossiers modifiés corresponde vraiment à la liste des enregistrements modifiés.
La variable nb est inutile, utilisez SQL%RowCount à la place de votre variable conteur nb
L’instruction 1/0 est inutile, utilisez Raise à la place de 1/0

Si les nombres de dossiers à modifiés est raisonnable la meilleure solution est de procéder comme dans l’exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Declare Type id_nt Is Table Of hr.employees.employee_id%Type; l_ids id_nt; Begin Update hr.employees e Set first_name = InitCap(first_name) Where department_id = 100 Returning e.employee_id Bulk Collect Into l_ids; -- For i in 1..l_ids.count Loop dbms_output.put_line(l_ids(i)); End Loop; End;
et bien sûr virer toute la gestion des exceptions qui est fausse et inutile.

31/05/2011, 09h43
tcr54

@mnitu : merci pour l'analyse de mon code et l'exemple.

Et également mes remerciements pour toutes les autres réponses.

Je vais regarder tout cela...

Cdlt.