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

[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

[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.

[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.

[mnitu]

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 ?

[tcr54]

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_;

[xdescamp]

Envoyé par orafrance

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

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.

[skuatamad]

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')

[mnitu]

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

1. 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.
2. Mise à jour via une requête unique si la variable conteur est supérieur à la valeur 1.
3. 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 :

1. Rien dans votre code ne lèvera l’exception NO_DATA_FOUND
2. 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.
3. La variable nb est inutile, utilisez SQL%RowCount à la place de votre variable conteur nb
4. 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.

[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.